Phases of Database Growth and Cost

https://www.crunchydata.com/blog/phases-of-database-growth-and-cost

71 26 4 years ago

azurezyq2• 4 years ago

Databases are "heavy", something once chosen, seldom changes. Many companies just stick to whatever DB stack they chose very early into the development.

Many tech blogs say you can use NoSQL to solve A, relax your consistency requirements to solve B and use offline pipelines to solve C, but these solutions only alleviate the problem, not solve the fundamental problem, the DB.

Fast-growing companies really need to plan this from day one. It's already 2022 and we have scalable distributed RDBMS like Spanner / TiDB / Cockroach, and some of them can do a bit of analytical workload as well. Plan ahead.

drewcoo• 4 years ago

But if we use ORMs, they're "services" we can swap, right? Sort of?

If we design architectures that hide db access (should it exist) behind a service, better yet. A FOO data service for FOO data and callers don't care how it's stored . . . then it's not heavy, is it? Not that the caller cares about.

You're talking actual implementation details before we talk about partitioning responsibility.

Hermitian909• 4 years ago

But if we use ORMs, they're "services" we can swap, right? Sort of?

That's the dream, but often the answer is "no, not even sort of".

Plenty of companies with super smart and experienced engineers laying the DB foundations for a company early in its life have failed at this goal. A few problems come into effect, two immediately spring to mind:

1. Eventually some engineer comes along with a reasonably compelling business case to either pass by the abstraction layer, or extend it so that it's no longer general (e.g. now it only works for Postgres).

2. As the company scales Hyrum's law comes into effect and a thousand pieces of code come to have subtle dependencies on implementation details of the DB.

(2) is particularly hard to avoid because DBs are often at least the nominal source of performance bottlenecks and engineers will do almost anything to make performance acceptable no matter how horribly it wrecks abstraction layers.

azurezyq2• 4 years ago

If DB works beautifully, it should not be a problem. But in reality it doesn't. Also DB doesn't live in vacuum, so you are facing these challenges:

* ORM bypass, for better performance. * Sharding and application level handling of sharding (making DB not transparent) * Replication (and obviously, delays)

And there are always tons of application level code just written because DB just cannot do it efficiently.

bcrosby95• 4 years ago

Actually abstracting your database would look like taking the lowest common denominator and assuming your database can only support that.

Unless you're building a piece of software where database agnosticism is actually a required feature, then I generally find it to be a very poor tradeoff. Databases are incredibly powerful, you should be using them to their fullest extent rather than purposefully handicapping yourself.

ransom1538• 4 years ago

"Spanner / TiDB / Cockroach"

Eh. Use old boring dbs: mysql, etc. Systems with decades of fixes, stackoverflow posts, work arounds, migration tools, etc. Otherwise - you will learn Spanner doesn't support deletes of 100k rows in 2022 and it is discontinued in 2023 (lol made up prediction). New db tech is pretty terrible (excluding vitess db - which is now industry standard). I wouldn't treat it like a JS framework.

yehosef• 4 years ago

you should check out singlestore.

KronisLV• 4 years ago

TiDB also claims MySQL compatibility at the protocol level, but the amount of unsupported features is slightly demotivating: https://docs.pingcap.com/tidb/stable/mysql-compatibility#uns...

Here's some of what's not supported:

  Stored procedures and functions
  Triggers
  Events
  User-defined functions
  FOREIGN KEY constraints #18209
  FULLTEXT syntax and indexes #1793
  SPATIAL (also known as GIS/GEOMETRY) functions, data types and indexes #6347
  Character sets other than ascii, latin1, binary, utf8, utf8mb4, and gbk.
  SYS schema
  Optimizer trace
  ...

Not being able to use FOREIGN KEY constraints makes me distrust the viability of something like that, because in my experience DB schemas and how they're utilized without foreign key end up being dumpsterfires.

Then again, at a certain scale you kind of have to look into alternative ways of how to look at working with your data, so there's that.

That said, the idea of giving people something that's compatible (for the most part) with the drivers and syntax that they already utilize is an excellent one!

billythemaniam• 4 years ago

Spanner can delete >100k rows if partitioned DML is used. TiDB is already what Vitess is trying to be. I think you would be surprised the number of companies who touted Vitess publicly that either went to TiDB or back to vanilla MySQL.

tmountain2• 4 years ago

Case in point, Drizzle. It had backing from Google, RedHat, Sun, HP, etc. The fork had over 10k commits, and it was supposed to unseat MySQL as the SaaS cloud DB. Then, the project was just canceled because, “nobody had time to work on it”.

Picking boring tech for crucial areas of a business is almost always the best plan.

https://en.m.wikipedia.org/wiki/Drizzle_(database_server)

mathgladiator• 4 years ago

I'm believe there has to be a better way where planning can be more freeform. I've seen both sides of NoSQL versus a real DB, and I'm using a real DB for my current product which is where I package a small DB inside a Document within a NoSQL design and I have a real DB to index the documents.

An interesting aspect is that the Database is a large part of the data pipe, but it often is insufficient for the product. The key issue being torn writes between the database and a service like twilio or elastic-search. A good way to broker this is via a queue, or to use a tailer via kafka for your data pipeline.

My gut is that so many things are just held together by bail wire, and the complexity is depressing to me. So, here I am, inventing a new thing.

I've invented a language called Adama ( https://www.adama-platform.com/ ) which allow a document to have a schema. Accidentally, I added transformation logic to the schema and then hooked it up to a socket to discover that many things were possible. I had intentions of using this thing to help get my state under control with node.js for a complex board game, but the language took over such that the entire game was eventually built within the language. Kind of neat.

My thesis is that the NoSQL pattern is fantastic for building products quickly if you don't mind the mess that schemaless evolves into, and a key weakness of what I have is that the document is limited in size in memory (which is then made tighter due to the reactive elements). However, I can leverage a logger much like a NoSQL solution to produce many of a stream of changes which I can shred into various solutions. For instance, I could put data changes directly into snowflake to provide massive scale queries.

There are some neat possibilities with this approach, but limits as well. However, I'm not sure if planning ahead is the most effective thing for a startup. I believe a new discipline over the coming decades will emerge regarding how to pick the appropriate level atomic unit of data, and then the skills to break down that unit will be more common place.

dominotw• 4 years ago

some of them can do a bit of analytical workload as well.

Analytical dbs seem to adding transactional workload support to them. Snowflake announced something to that effect this week.

https://siliconangle.com/2022/06/14/snowflake-bids-transacti...

eatonphil• 4 years ago

I was hoping this was going to talk about the phases of database growth/cost as in the development of PostgreSQL but rather it's about the phases as a user.

Still a good article where the first phase lines up with my experience exactly but I kind of lost the point of the second phase. To me phase 2 is 1) indexes, 2) partioning/sharding (and maybe introducing a Postgres addon or switching Postgres vendors to support this), and 3) rethinking what gets stored in Postgres in the first place or 4) setting up processes to migrate old data out of Postgres into s3 or something.

collaborative• 4 years ago

90% of my traffic is fraud/scams/pyramid schemes.

I don't make money from them, but I tolerate them because they really help me scale right. Countless data and other optimizations have come from monitoring their usage

stevenjgarner3• 4 years ago

Having helped "people run databases for their businesses" since the 1970's (yes I am a dinosaur), my approach has simplified dramatically into approaching all database requirements as a collection of just 7 EAV (entity-attribute-value) tables [1] - one 3-field table for each field type: date, decimal, integer, text, time, varchar and then one for id/tenant. I then optimize the hardware for a few (very) long narrow tables, and shard as required. The beauty of this is that the database structure is all in data (where it should be) and I never have to alter the tables. As hardware has evolved, this approach serves my clients better, plus the turnaround time for changes is closer to the client instant gratification requirement.

[1] https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80...

magicalhippo2• 4 years ago

Do you machine-generate views and stored procs for reading/writing data?

How do you handle referential data? Like, order lines should have a foreign key linking them to the relevant order "head". Self-referential foreign key on the id table?

Also, how's performance on a single node with a wide-ish "table"? Like, say I need to insert 1k rows x 30 columns, a fairly common, light load for us.

edit: oh yeah, and what about indexing? We have a lot of indexes that touches 3-5 columns, and some even more, and almost all of those mix data types. For example we might have an (varchar, timestamp, varchar) index. Some of these are unique indexes, for consistency. These are crucial for normal operation.

stevenjgarner3• 4 years ago

I appreciate your interest. On about my 2nd iteration of things I relied on MySQL Views for queries, but I felt that kind of defeated the purpose (in being truly data independent), so I wrote my own database and eav crud library (initially in PHP now refactored in production MVC in PHP 7.4 and a development version under way in node js). The library was pretty ugly at first for the SELECT but elegant and fast for INSERT, UPDATE and DELETE (as you're basically just doing up to 7 queries that are insert into / update / delete from table where parent_id=xyz). The ugliness with the SELECT initially was that I could not see past using giant IFNULL(MAX(CASE ... statements, which are not great on performance.

As I refactored the library over the years, I realized more and more that increasing the scope and encompassing an entire content management system actually made things easier (still retaining the ability to INCLUDE any custom php file), as I could do the entirety of every web page in a single MySQL connection (open the database connection as the start of the page, doing the myriad queries the page required, then close the connection at the end of the page). In the general case, MySQL typically spends a lot more time making connections that it does executing queries.

And then I realized the bulk of my SELECT statements were used to create grid tables from large data sets. Standard data grid table with CRUD + search + sort capabilities on every column and every row.

I took a look at this from first principles and realized if I split the queries into two (one for the rows and another for the columns), I could eliminate the ugly IFNULL(MAX(CASE ... statements. In other words, you create one loop just pulling out the id for each row and inside that loop call another loop for all the grid columns based on that id needed to display that row. Assuming a grid of 30 rows with 20 columns say, that is 21 queries (but all over a single database connection). Of course you manage all the grid tables with pagination, so you might have 10,000 grid pages displaying 300,000 records 30 at a time on each page. If you present those grid tables in a usable manner (sorted by records most frequently accessed/updated, most recently accessed/updated etc), then statistically most of your users stay on the first grid page. And you manage the LIMIT statement to only ever get the 30 "virtual" records on the current page, whether in PHP or in JSON. Pretty fast!

As far as indexing is concerned, well each of the 7 real MySQL tables was indexed, so yeah EVERY field is indexed whether you want it or not. Creating combined indexes between tables has not been attempted.

So I got that approach performing nicely, then took it to another level putting the database schema itself inside the database. I could now build a "virtual" table from combining as many of the 7 fields as required for any given table definition. And just like a real MySQL table, you could join multiple "virtual" tables. After all an EAV database is about as normalized as you are ever going to get (one field per table, plus 2 indexes). Of course this is all done in data, so imagine a standard web table grid being used to Create/Retrieve/Update/Delete all the "virtual" table field definitions, labels, css values, etc..

This approach satisfied two of my business requirements - one need was to have a single framework to support a small number of clients with large datasets (e.g. millions of records, hundreds of tables). For them, I now had a tool where you do not need to be a programmer to create a simple web table grid of data. And where they needed hand-holding on more complex requirements, solutions could be deployed in a fraction of the time it would have taken me using traditional methods.

The other need was to support a large number of clients (thousands) with very simple database requirements (e.g. for the business customers of an ISP). Now I had one database framework to do both.

Doesn't satisfy every need, but it does one heck of a lot and takes care of all the low-hanging fruit. Like most developers who have been working for decades, it may have taken me that long to arrive at, but recreating it from scratch could be done very quickly.

remram• 4 years ago

You have basically recreated your own datastore on top of an existing DBMS, on top of which you have to create your own database. You will have to translate queries and reimplement many features like schema/data migrations, constraints, ... to get to a similar product with worse performance but (hopefully) better suited for your deployment strategy.

Do what works for you I guess.

stevenjgarner3• 4 years ago

For sure. Performance has not been anywhere near as big a challenge as my own productivity. This EAV approach has enabled me to do at least more than an order of magnitude more solutions for customers in the same time.

I basically manage 2 EAV databases - a production one with the 7 tables (discussed) and a development one with the same 7 tables, where I will do imports, migrations, and such. One thing that is truly chaos is when you have hundreds of "virtual" EAV tables with 10's of millions of records (or more) in just 7 tables .... and the house of cards comes down for whatever reason (master-master replication failure, clustering failure etc). Gives you a whole new appreciation of the beauty of a simple flat file database.

davidthewatson• 4 years ago

I would agree generally with this approach having done a lot of experimental work around y2K with schemaless databases that predates the use of similar approaches in cloud. If you read the Wikipedia page, it refers to the use of json in this approach with postgresql but doesn’t mention the more modern bjson as EAV approach which addresses some concerns arising from the eav design.

pphysch• 4 years ago

This article/marketing material elides the fact that the king of Phase 1 is developer productivity, and skipping the ORM to write raw SQL is simply horrible for general productivity, even if it will certainly boost your query performance. It's a quantity/quality trade-off that this piece doesn't make explicit, because it's trying to sell a narrow set of services.

winslett• 4 years ago

Hey, I'm the author this blog. It's intention is to connect technology decisions to financial decisions. Appreciate the feedback.

I totally agree with you on developer productivity. Said another way, to connect technology & finance: early on, when developer cost is much more significant to the company than infrastructure costs, emphasizing developers building the right product is more important than optimizing the infrastructure costs.

Secondly, I am a fan of ORMs. I exclusively use ORMs on CRUD-based actions. The typical first-fix for ORMs I see is to move dashboards, reports, and aggregations to native SQL. Just doing that get you a lot of performance improvements. Recently, for a friend, not associated with Crunchy & not using Crunchy, I helped them lower their cost of database operations from $2500 / month to $500 / month. All I did was find the slowest queries, determine the ORM was running a slow query, and doing an N+1 query , then point them in a direction to fix it. Because their cost of operations were looking at increasing so quickly, they were looking at migrating to a different database, NoSQL, or something else.

Again, appreciate the feedback. Cheers.

Phases of Database Growth and Cost

Related Stories