[luhn]

> By Jan 2024, our largest table had roughly 100 million rows.

I did a double take at this. At the onset of the article, the fact they're using a distributed database and the mention of a "mid 6 figure" DB bill made me assume they have some obscenely large database that's far beyond what a single node could do. They don't detail the Postgres setup that replaced it, so I assume it's a pretty standard single primary and a 100 million row table is well within the abilities of that—I have a 150 million row table happily plugging along on a 2vCPU+16GB instance. Apples and oranges, perhaps, but people shouldn't underestimate what a single modern server can do.

[hliyan]

Call me old fashioned, but when records start reaching the 100 million range, it's usually an indication that either your dataset is too wide (consider sharding) or too deep (consider time based archival) to fit into a monolithic schema. For context, I've dealt with multiple systems that generate this volume of data between 2003 - 2013 (mostly capital markets, but also some govt/compliance work) with databases and hardware from that era, and we rarely had an issue that could not be solved by either query optimization, caching, sharding or archival, usually in that order.

Secondly, we did most of these things using SQL, Bash scripts, cron jobs and some I/O logic built directly into the application code. They were robust enough to handle some extremely mission critical systems (a failure could bring down a US primary market and if it's bad enough, you hear it on the news).

[hylaride]

It obviously depends on how you use your data, but it really is surprising how far one can go with large tables when you implement sharding, caching, and read replicas.

For tables with a lot of updates, Postgres used to fall over with data fragmentation, but that's mostly been moot since SSDs became standard.

It's also easier than ever to stream data to separate "big data" DBs for those separate use cases.

[hliyan]

Thanks, I knew I forgot something: read replicas

[electroly]

From the point of view of an SQL engine in 2025, 100 million rows is a tiny table. You can add a surprising number of zeroes onto that figure and a single modest SQL node will handle it with no special effort. 100 billion, with a B, is not difficult on a single beefy node today. I think your points all still stand but consider refreshing the actual numbers. I personally start getting "the itch" around 10 billion (or if it looks like it's going to become 10 billion) these days. If a table gets there, I better have a plan to do something else.

I had a 200 billion row table that was operationally still manageable but, IMO, I had allowed to grow out of control. The enterprise storage costs a fortune. Should have nipped that in the bud by 20 billion at the latest.

[paulddraper]

Depends on the read/write workload and row size, but yeah after 100-200m rows PostgreSQL vacuums can take a while. And index rebuilding (which you have to do on an active table) too.

It all depends though, sometimes 1b is passe.

But 100m is a good point to consider what comes next.

[thehappyfellow]

It’s incredible how much Postgres can handle.

At $WORK, we write ~100M rows per day and keep years of history, all in a single database. Sure, the box is big, but I have beautiful transactional workloads and no distributed systems to worry about!

[rastignack]

At $WORK, we are within the range of 2 billion rows per day on one of our apps. We do have beefy hardware and ultra fast SSD storage though.

[maz1b]

A single PG database on one server? What are the specs?

[geoka9]

Those rows are never pruned and rarely read?

[wvh]

Two days ago, I'd have said the same. Yesterday, big box went down, and because it was so stable, it was a joint less oiled and the spare chickened out at the wrong time and apparently even managed to mess up the database timeline. Today was the post-mortem, and it was rough.

I'm just saying, simple is nice and fast when it works, until it doesn't. I'm not saying to make everything complex, just to remember life is a survivor's game.

[thehappyfellow]

You’re right, there are downsides like turbine you mention! We mitigate it by running a hot backup we can switch to in seconds and a box in which we test restoring backups every 24h, that’s necessary! But it requires 3x the number of big expensive boxes.

I still think it’s the right tradeoff for us, operating a distributed system is also very expensive in terms of dev and ops time, costs are more unpredictable etc.

It’s all tradeoffs, isn’t it?

[icedchai]

You don't even need to be that "modern." Back in 2010 I was working on a MySQL 5.x system with about 300 million rows on a dual Xeon box with 16 gigs RAM and a few hundred gigs of RAID 10. This was before SSDs were common.

The largest table was over 100 million rows. Some migrations were painful, however. At that time, some of them would lock the whole table and we'd need to run them overnight. Fortunately, this was for an internal app so we could do that.

[luhn]

The improvements to migrations have been the biggest boon for running even modestly-sized Postgres DBs. It wasn't that long ago that you couldn't add a column with a default value without rewriting the whole table, or adding NOT NULL without an exclusive lock while the whole table was scanned. That becomes unfeasible pretty quickly.

[nasretdinov]

Does adding a default value into a column finally work without locking up an entire table now at least?

[williamdclt]

Yes, it has for a few years :) There's almost always non-locking ways to migrate things with Postgres now

[luhn]

Like most ALTER TABLE subcommands, you need an exclusive lock on the table while the catalog is updated. But no table scan or rewrite is required, so that lock is sub-second and can usually be done without disrupting a live application.

[throwaway7783]

Yeah, we have 300m+ rows in a table as well. It's partitioned by time and chugs along with no issues. Granted It's a 30 vcpu, 100gb ram machine, but it hosts billions of rows in aggregate

[SchemaLoad]

Last app I worked on had a few tables in the billions of rows. Seemed to work fine as we were only really accessing it by unique keys which seems to remain fast no matter how large the table is.

[lelanthran]

> we were only really accessing it by unique keys which seems to remain fast no matter how large the table is.

Even a naive B-tree index has a logarithmic curve, which means that the time to find a record asymptotically flattens out as the number of records increases.

[thomasfromcdnjs]

Does mid six figure mean ~$500k?

That sounds insane for a crud app with one million users.

What am I missing?

[gbear605]

I’ve seen startups with a thousand active users paying $50k/month (though that’s overall costs, not just db). It’s really easy to waste a lot of money doing nothing.

[bigfatkitten]

It’s especially easy to waste money on databases.

People just throw more compute power (ie money) at performance problems, rather than fixing their queries or making better use of indices.

[mplanchard]

You can see this in the article here, where they are just using whatever garbage queries Prisma spits out.

I’ve contended for a long time that ORMs and their ilk automatically building queries is an antipattern for anything but small data scale. At any reasonable size of db, you’re going to need to know sql well enough to write optimized queries anyway. There’s essential complexity in the DB queries, which ORMs can only hide for so long.

[cies]

And do not consider owning the hardware. See what hardware you can buy for that money.

[aitchnyu]

How many servers are needed to bounce back from a server failure in a few minutes? Should we consider 3 VMs instead of 1 physical?

[sgarland]

Two. Run one in warm/hot standby, optionally with synchronous commits if you want (nearly) guaranteed zero data loss, and can tolerate the increased write latency.

Technically you’ll need a third server to perform the failover, but it doesn’t need to be nearly as big, as it’s just watching heartbeats and issuing commands.

[graemep]

Server failures are rare. Its still usually going to be cheaper to have physical servers even with spare capacity for failures.

[cies]

I think these days it's more: were do we find the grey old unix guy who confidently will host your db for you on bare metal.

[TheNewsIsHere]

As a small business owner — I recently spent an hour canceling things that just added up over time and that I don’t now need. It’s just so easy to waste money, period.

Directly to your point though, I once encountered a salesperson who was running an entire sandbox environment of a very large platform to the tune of about $25k/mo. It sat idle for almost half a year before someone came knocking. The cloud team did an audit and they were a little spicy about it, understandably.

[ies7]

$500k for only 100 millions rows db also sounds crazy

[wbercx]

The largest table was 100 million rows. They could have had hundreds more tables.

[reinhardt]

Also curious why every comment mentions just the number of rows as the only factor that matters. A 100M rows table of 3 integer columns is quite different from 50+ columns, 5 of which are text up to a few MB long.

[imhoguy]

I bet it is cost of query processing (CPU) and traffic (network throughput) plus ofc provider markup.

[sgarland]

Agreed. Devs usually do a double take when I tell them that their table with 100K rows is not in fact big, or even medium. Everyone’s experiences are different, of course, but to me, big is somewhere in the high hundreds of millions range. After a billion it doesn’t really matter; the difference between 5 billion and 1 billion isn’t important, because it’s exceedingly unlikely that a. Your working set is that large b. That your server could possibly cope with all of it at once. I hope you have partitions.

[williamdclt]

Yeah, 100mil is really not that much. I worked on a 10B rows table on an rds r6g.4xl, and Postgres handled it fine, even with 20+ indexes. Really not ideal and I'd rather have fewer indexes and sharding the table, but postgres dealt with it.

[ringeryless]

OTOH they are admittedly using an ORM (Prisma, known for its weight)

[monero-xmr]

It is truly amazing how mature developers always wind up at the same result - old tech that has stood the test of time. Betting the company on alpha solutions of dubious quality keeps the devs employed at least.

[cies]

> It is truly amazing how mature developers ...

...use ORMs!

They are always bad, but especially bad when the code base and/or query complexity grows.

https://dev.to/cies/the-case-against-orms-5bh4

[KronisLV]

> They make the simple queries slightly simpler, but they do not help you for the harder queries. — me

I dont think it’s possible to say they’re always bad when offering an example of when they make things better. In my eyes, it’s pretty simple: use the ORM for the simple CRUD and simple data querying, because you’ll have to do a lot of it in most codebases.

For everything else, there is in-database processing or dropping down to raw SQL. Even when using an ORM, nobody is preventing you from making an entity mapping against a DB view or calling stored procedures so you can keep the complex querying logic in pure SQL but get an object that’s easy to work with out of it. Plus, you know, any ORM worth their salt will let you point it at a live DB that you’ve run your (hopefully versioned, for example, with dbmate) migrations against and will let you output the backend code for the entity mapping for everything in a single command.

It’s kind of unfortunate that a lot of ORMs do kinda suck and have jagged edges, as well that most attempts at dynamic SQL also remain pretty niche, like: https://mybatis.org/mybatis-3/dynamic-sql.html

[const_cast]

> In my eyes, it’s pretty simple: use the ORM for the simple CRUD and simple data querying, because you’ll have to do a lot of it in most codebases.

The "danger" here is the movement towards an object-oriented design instead of a SQL-first approach to querying the database.

In my experience, I have seen ORMs used for these simple CRUD cases and what tends to happen is you have objects with dozens of members when you only need one or two. And then down the line you also start doing JOINs in memory - you just don't recognize it like that.

You see, you get Object X using CRUD and then object Y using CRUD and then you need a way to say which relates to what and... oops now we've done joins in memory. And sometimes, maybe even often times, we're doing all of that for a single field we need in this one place.

[KronisLV]

> The "danger" here is the movement towards an object-oriented design instead of a SQL-first approach to querying the database.

That might just be an inherent problem with object oriented languages, you probably have some mapping that you need to do even with raw SQL and before long you'll have the same use cases emerge.

Whether that means any of the following is true:

  OOP is bad for interacting with SQL
  SQL is bad for being used by OOP (or rather, relational databases, maybe not object stores)
  other approaches might help make it less bad, but ORMs don't help in common usage
  maybe we just don't have the right pattern yet, perhaps the "object relational impedance mismatch" folks have a point

I can't tell, not even sure what a proper solution would be.

I've seen what happens when people try to put all of the business logic inside of the DB and it's absolute trash (from the perspective of developer experience, versioning, debugging etc.; the performance was actually stellar) to work with.

[h1fra]

Also screamed in my head, I have way more rows than that in a Postgres right now and paying less than $500!

[javier2]

We have a couple of tables with about a billion rows now on single nodes in mysql. 256GB RAM and a number of 2TB nvme drives. It works completely fine, but you can forget about timely restore if something goes completely fucked. And we cant do any operation that isnt directly using the index or the whole performance suffers immediately. Which means we basically have to use those tables like they are a distributed database, but at least we have transactionality.

[sethammons]

I missed that number, but caught they migrated all data in 15 minutes and I blinked: "wait, how little data are we talking about for how much money!?"

[TheNewsIsHere]

When I was running tech for a (tiny) nonprofit we self-hosted a geographic database because it was cheaper and easier.

There was something like 120 million rows in the database. It ran on a single VM. It really needed the indexes, but once those were built it just sang.

This was easily 10+ years ago.

[casper14]

Nice! What optimizations have you put in llace yo support 150 mil? Just some indexing or other fancy stuff?

[luhn]

You don't need to optimize anything beyond appropriate indices, Postgres can handle tables of that size out of the box without breaking a sweat.

[gopalv]

> Postgres can handle tables of that size out of the box

This is definitely true, but I've seen migrations from other systems struggle to scale on Postgres because of decisions which worked better in a scale-out system, which doesn't do so well in PG.

A number of well meaning indexes, a very wide row to avoid joins and a large number of state update queries on a single column can murder postgres performance (update set last_visited_time= sort of madness - mutable/immutable column family classifications etc.)

There were scenarios where I'd have liked something like zHeap or Citus, to be part of the default system.

If something was originally conceived in postgres and the usage pattern matches how it does its internal IO, everything you said is absolutely true.

But a migration could hit snags in the system, which is what this post celebrates.

The "order by" query is a good example, where a bunch of other systems do a shared boundary variable from the TopK to the scanner to skip rows faster. Snowflake had a recent paper describing how they do input pruning mid-query off a TopK.

[sgarland]

That’s not the fault of the DB, though, that’s bad schema design. Avoiding JOINs is rarely the correct approach.

[williamdclt]

You really don't need anything special. 150M is just not that much, postgres has no problem with that.

Obv it depends on your query patterns

[perrygeo]

Mid 6 figure DB bill, let's estimate $500k. Divided into 100 million rows (ignore the rest, because db provisioning is typically dominated by the needs of a few core tables). They get 200 rows per dollar.

Your table on a small VPS (which I concur is totally reasonable, am running something similar myself): Let's say your VPS costs $40/mo x 12 = $480/yr. Divide into 150 million. You get 312,500 rows per dollar.

I'd wager you server was faster under normal load too. But is it webscale? /s

There's waste, then there's "3 orders of magnitude" waste. The pain is self-inflicted. Unless you have actual requirements that warrant a complex distributed database, you should "just use postgres".

And just to calibrate everyone's expectations, I've seen a standard prod setup using open source postgres on AWS EC2s (1 primary, 2 replicas, 1 haproxy+pgbouncer box to load balance queries) that cost ~ $700k annually. This system was capable of handling 1.2 million rows inserted per second, while simultaneously serving thousands of read queries/s from hundreds of internal apps across the enterprise. The cost effectiveness in their case came out to ~ 20k rows per dollar, lower than your VPS since the replicas and connection pooling eat into the budget. But still: 2 orders of magnitude more cost effective than the hosted distributed hotness.