> 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.
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).
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.
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.
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.
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!
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
I am in a startup that's using Prisma and it we really wish we had not:
- The query objects can become hard to read with anything more or less complex.
- If you need an unsupported Postgres extension you are out of luck.
- One large file in a schema, impossible to shard.
- We have many apps in a monorepo and they cannot have separate prisma connections cause the schema gets baked into "@prisma/client"
Basically the only thing useful about it are the TS types which is something SQL-builder libraries solve better. Long story short, use Kysely, Prisma provides no value that I see.
"Instead, it sends individual queries and joins the data on the application level. However, this doesn't mean that Prisma's relational queries are per se slower"
Wow, what the fuck.
"Also, this chapter about Query Performance Optimization from the High Performance MySQL book has some great insights. One of the techniques it mentions is JOIN decomposition:
Many high-performance web sites use join decomposition. You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application."
This belief that they can write JavaScript that outperforms decades of bare-metal executed optimisations in mainstream database engines is just astonishing.
> This belief that they can write JavaScript that outperforms decades of bare-metal executed optimisations in mainstream database engines is just astonishing.
In my first job fresh out of uni, I worked with a "senior" backend developer who believed this. He advocated for crusty, bug-ridden ORMs like Sequelize and Prisma (still very early stage back then, so lots more issues than now though I'd still steer well clear of it). Claiming they did "query optimizations". I knew it made zero sense, but also that I wasn't going to be able to convince him.
The application joins are (soon to be were) done in Rust service that's side loaded with the node server.
Also, this is an old quote. Databases didn't all support things like JSON at the time, so joins and subqueries presented an N+1 problem and could balloon data fetch requirements fairly easily. Being a GraphQL-focused ORM originally too, this made some sense.
The default is now being changed and correlated subqueries, JOINs, & JSON aggregation will replace the old approach unless explicitly toggled.
Every ORM is bad. Especially the "any DB" ORMs. Because they trick you into thinking about your data patterns in terms of writing application code, instead of writing code for the database. And most of the time their features and APIs are abstracted in a way that basically means you can only use the least-common-denominator of all the database backends that they can support.
I've sworn off ORMs entirely. My application is a Postgres application first and foremost. I use PG-specific features extensively. Why would I sacrifice all the power that Postgres offers me just for some conveniences in Python, or Ruby, or whatever?
I don’t understand the hate, the only truly limiting factor for Prisma right now is its poor support for polymorphism, apart from that it has quite good support for complicated index setups, and if you need anything more performant, just drop to typed raw sql queries, it also supports views (materialized or otherwise) out of the box.
I recently wanted to check it out and wrote a small app that had good use of pgvector for embeddings, custom queries with ctes for a few complex edge cases, and it was all quite smooth.
Now it might not be at the level of active record, ecto or sqlalchemy but it was quite decent.
If you know your sql at any point it gave me options to drop down a level of abstraction, but still keep the types so as not to break the abstraction too much for the rest of the code.
> It's true that Prisma currently doesn't do JOINs for relational queries. Instead, it sends individual queries and joins the data on the application level.
..........I'm sorry, what? That seems........absurd.
edit: Might as well throw in: I can't stand ORMs, I don't get why people use it, please just write the SQL.
I believe it’s either released now or at least a feature flag (maybe only some systems). It’s absolutely absurd it took so long. I can’t believe it wasn’t the initial implementation.
Funny relevant story: we got an OOM from a query that we used Prisma for. I looked into it - it’s was a simple select distinct. Turns out (I believe it was changed like a year ago, but I’m not positive), event distincts were done in memory! I can’t fathom the decision making there…
But I've found with that you can get better performance in _few_ situations with application level joins than SQL joins when the SQL join is causing a table lock and therefore rather than slower parallel application joins you have sequential MySQL joins. (The lock also prevents other parallel DB queries which is generally the bigger deal than if this endpoint is faster or not).
Although I do reach for the SQL join first but if something is slow then metrics and optimization is necessary.
It is. But wait... it doesn't join the data on the application level of your application. You have to deploy their proxy service which joins the data on the application level.
I didn't mind prisma for managing the schema etc but also seen your linked github issue. I found other people recommend combining Prisma with Kysley. I have only used this in toy projects so take this with a grain of salt.
I'm not the most experienced in huge DBs and can't write anything off, but I've never seen a horizontally sharded DBMS work well, even Citus which allegedly does. There's always been a catch that seems worse than manually doing sharding at a higher level than your DB, not that that's easy either.
I'd argue that horizontally sharded databases can work well, but they do tend to have significant non obvious tradeoffs that can be pretty painful.
There's a handful of companies that have scaled Citus past 1PB for production usage, but the examples I'm aware of all had more engineering to avoid capability or architecture limitations than one might like. I'd love to see someone come back with a fresh approach that covered more use cases effectively.
Author here. Yeah, that's not a bad take away either. I've also been really vocal in Primsa issues for all sorts of things. We are about to embark on a big migration away from Prisma and onto Drizzle once the Drizzle team lands 1.0
We will absolutely share our findings when that migration happens!
That just sounds irresponsible. The correct choice for prod isn't "the cool new trendy thing that will solve all our problems once it hits 1.0", the correct choice is "the boring stable thing that has existed long enough for everyone to understand its shortcomings".
It's wild and hilarious, how often startups and companies go for distributed databases like CockroachDB/TiDB/Yugabyte before they actually need distribution, this trends sucks. 100 million rows is nothing that a well-tuned Postgres or MySQL instance (or even read-replicated setup) can't handle comfortably. Scale when you hit the wall.
Yes, there are multiple steps to consider before jumping to a distributed database and only when you actually hit bottlenecks, like read replication, CQRS, etc. But I guess it's often just about chasing fancy stuff.
HA is important. But Postgres and MySQL both support HA and replication setups without needing to jump straight into a distributed SQL (In this context of using cockroach). We use MySQL Innodb cluster + MySQL router with auto failover on single primary mode.
> If you start having replicas you are already in distributed territory.
But it’s not the same as a distributed database with quorum writes, global consensus, and cross-region latencies. Those systems are built for horizontal write scaling, that come with added complexity and cost, which most apps don’t need.
I can't help thinking more startups need greybeards around. (Of which, realistically, I'm now one.)
Largest table 100 million rows and they were paying 6 figures for database services annually? I have one now that sits happily enough on an 8yo laptop. I've worked on systems that had similar scale tables chugging along on very average for 20 years ago MSSQL 2000 boxes. There just isn't a need for cloud scale systems and cloud scale bills for that data volume.
The problems they're describing should never have got that far without an experienced hand pointing out they didn't make sense, and if they'd hired that greybeard they'd have spotted it long before.
The answer to the question, "what database should I use?" is "postgres". If you are in a situation where postgres actually won't work, then you already would know exactly why postgres won't work.
In other words: [Postgres -> exotic solution] is the path everyone should take (and 99% will just stay in postgres), and not [exotic solution -> postgres].
Yes, the nosql fad that swept the industry was nearly as insufferable as the SPA craze that followed alongside. Now everyone's back to tried and true. Most data once more sits in RDBMS and most html gets render on the server.
Us grizzled genX devs saw this coming a decade ago.
As a grizzled genX dev myself, we are in a different situation now - "nosql" (hate the term) has tremendous use cases, it's just that most people aren't creating something that requires it. It was a natural exploration of the tools, something that should be encouraged. "I knew it all along" isn't an attitude I find helpful or effective. My "grizzled genX dev" attitude is that nearly all people think they know what is going to happen or what is the best route, and they are almost always entirely wrong. We only find out by trying a bunch of things.
In other words, there are many companies currently worth $Billion+ that wouldn't have succeeded had they followed your advice. Today, with incredibly powerful infra of all types available, starting with Postgres is almost always the right step unless you know, know, better. That wasn't the case 10+ years ago.
Related: Oxide's podcast, "Whither CockroachDB," which reflects on experience with postgres at Joyent, then the choice to use cockroach in response to prior experiences with postgres.
I'm trying to avoid editorializing in my above summary, for fear of mischaracterizing their opinions or the current state of postgres. Their use of postgres was 10 years ago, they were using postgres for a high-availability use case -- so they (and I) don't think "postgres bad, cockroach good." But like Bryan Cantrill says, "No one cares about your workload like you do." So benchmark! Don't make technical decisions via "vibes!"
We also did this, using change data capture and kafka to stream data to clickhouse as it gets written to postgres.
Clickhouse is incredible tech. We’ve been very pleased with it for OLAP queries, and it’s taken a lot of load off the postgres instance, so it can more easily handle the very high write load it gets subjected to.
Not an article, and I have no direct knowledge of this either way, but I would strongly suspect that Instagram migrated off Postgres a while back. Probably to fb-mysql + myrocks, or some other RocksDB based solution.
The compression level is vastly superior to any available Postgres-based solution, and at Instagram’s scale it amounts to extremely compelling hardware cost savings.
Also if they were still primarily on pg, it would be one of the largest pg deployments in existence, and there would be obvious signs of the eng impact of that (conference talks, FOSS contributions, etc).
Bigger-picture: Postgres is an amazing database, and it’s often the right choice, but nothing in tech is always the best choice 100% of the time. There’s always trade-offs somewhere.
Probably a corollary of the fact that most usecases can be served by an RDBMS running on a decently specced machine, or on different machines by sharding intelligently. The number of usecases for actual distributed DBs and transactions is probably not that high.
It still makes me sad when half the queries I see are json_* - I know its far too late, but a big sad trombone in query performance is constantly left joining to planner queries that are going to give you 100 rows as an estimate forever.
Not sure why those are json_agg() instead of array_agg() in that example. Why would you use a JSON array instead of a native properly typed array? Yes, if you have some complex objects for some reason you can use JSON objects. But those where all just arrays of IDs. Also why was it json_agg() and not jsonb_agg()? Is there any reason on why to use JSON over JSONB in PostgreSQL?
If you, for whatever obscure reason, need to preserve whitespace and key ordering, that is you want something that is effectively just a text column, then you should use JSON over JSONB.
I can't think of any case at all, no matter how contrived, where you'd want to use the non-B versions of the JSON aggregate functions though.
Hoping for more easy columnar support in databases, which is one of the things that can lead you to storing json in database columns (if your data is truly columnar).
Currently the vendor lock-in or requirements for installing plugins make it hard to do with cloud sql providers. Especially hard since by the time it's a problem you're probably at enough scale to make switching db/vendors hard or impossible.
How does columnar = json? json isn't colunar at all... If you just want to have a schema in json instead of sql, use a no-sql db, postgres nosql features are strong, but the db features are actually much stronger.
For all the Prisma-haters: I salute you. But I want to reply to numerous comments with the following:
ORMs come in two main types, that I'm aware of: Active Record (named after the original Ruby one, I think) and Data Mapper (think Hibernate; SQLAlchemy).
Active Record ORMs are slightly more ergonomic at the cost of doing loads of work in application memory. Data Mapper looks slightly more like SQL in your code but are much more direct wrappers over things you can do in SQL.
Data Mapper also lets you keep various niceties such as generating migration code, that stem from having your table definition as objects.
Readit News
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.
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).
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.
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.
It all depends though, sometimes 1b is passe.
But 100m is a good point to consider what comes next.
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!
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.
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.
That sounds insane for a crud app with one million users.
What am I missing?
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.
Obv it depends on your query patterns
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.
I already knew about prisma from the infamous https://github.com/prisma/prisma/discussions/19748
- The query objects can become hard to read with anything more or less complex.
- If you need an unsupported Postgres extension you are out of luck.
- One large file in a schema, impossible to shard.
- We have many apps in a monorepo and they cannot have separate prisma connections cause the schema gets baked into "@prisma/client"
Basically the only thing useful about it are the TS types which is something SQL-builder libraries solve better. Long story short, use Kysely, Prisma provides no value that I see.
Wow, what the fuck.
"Also, this chapter about Query Performance Optimization from the High Performance MySQL book has some great insights. One of the techniques it mentions is JOIN decomposition:
This belief that they can write JavaScript that outperforms decades of bare-metal executed optimisations in mainstream database engines is just astonishing.In my first job fresh out of uni, I worked with a "senior" backend developer who believed this. He advocated for crusty, bug-ridden ORMs like Sequelize and Prisma (still very early stage back then, so lots more issues than now though I'd still steer well clear of it). Claiming they did "query optimizations". I knew it made zero sense, but also that I wasn't going to be able to convince him.
Also, this is an old quote. Databases didn't all support things like JSON at the time, so joins and subqueries presented an N+1 problem and could balloon data fetch requirements fairly easily. Being a GraphQL-focused ORM originally too, this made some sense.
The default is now being changed and correlated subqueries, JOINs, & JSON aggregation will replace the old approach unless explicitly toggled.
I've sworn off ORMs entirely. My application is a Postgres application first and foremost. I use PG-specific features extensively. Why would I sacrifice all the power that Postgres offers me just for some conveniences in Python, or Ruby, or whatever?
Nah. Just write the good code for your database.
I recently wanted to check it out and wrote a small app that had good use of pgvector for embeddings, custom queries with ctes for a few complex edge cases, and it was all quite smooth.
Now it might not be at the level of active record, ecto or sqlalchemy but it was quite decent.
If you know your sql at any point it gave me options to drop down a level of abstraction, but still keep the types so as not to break the abstraction too much for the rest of the code.
..........I'm sorry, what? That seems........absurd.
edit: Might as well throw in: I can't stand ORMs, I don't get why people use it, please just write the SQL.
Funny relevant story: we got an OOM from a query that we used Prisma for. I looked into it - it’s was a simple select distinct. Turns out (I believe it was changed like a year ago, but I’m not positive), event distincts were done in memory! I can’t fathom the decision making there…
I used to agree until I started using a good ORM. Entity Framework on .NET is amazing.
I removed a $lookup (the mongodb JOIN equivalent) and replaced it with, as Prisma does, two table lookups and an in-memory join
p90 response times dropped from 35 seconds to 1.2 seconds
But I've found with that you can get better performance in _few_ situations with application level joins than SQL joins when the SQL join is causing a table lock and therefore rather than slower parallel application joins you have sequential MySQL joins. (The lock also prevents other parallel DB queries which is generally the bigger deal than if this endpoint is faster or not).
Although I do reach for the SQL join first but if something is slow then metrics and optimization is necessary.
https://kysely.dev/https://github.com/valtyr/prisma-kysely
There's a handful of companies that have scaled Citus past 1PB for production usage, but the examples I'm aware of all had more engineering to avoid capability or architecture limitations than one might like. I'd love to see someone come back with a fresh approach that covered more use cases effectively.
Disclaimer: former Citus employee
As someone who has primarily worked with Postgres for relational concerns, I’ve envied the apparent robustness of the MySQL scaling solutions.
We will absolutely share our findings when that migration happens!
HA is important. But Postgres and MySQL both support HA and replication setups without needing to jump straight into a distributed SQL (In this context of using cockroach). We use MySQL Innodb cluster + MySQL router with auto failover on single primary mode.
> If you start having replicas you are already in distributed territory.
But it’s not the same as a distributed database with quorum writes, global consensus, and cross-region latencies. Those systems are built for horizontal write scaling, that come with added complexity and cost, which most apps don’t need.
Largest table 100 million rows and they were paying 6 figures for database services annually? I have one now that sits happily enough on an 8yo laptop. I've worked on systems that had similar scale tables chugging along on very average for 20 years ago MSSQL 2000 boxes. There just isn't a need for cloud scale systems and cloud scale bills for that data volume.
The problems they're describing should never have got that far without an experienced hand pointing out they didn't make sense, and if they'd hired that greybeard they'd have spotted it long before.
Might have been monthly.
100,000,000 rows is what I handled on a single Sun server in 2001 with Sybase, no problemo.
In other words: [Postgres -> exotic solution] is the path everyone should take (and 99% will just stay in postgres), and not [exotic solution -> postgres].
Us grizzled genX devs saw this coming a decade ago.
In other words, there are many companies currently worth $Billion+ that wouldn't have succeeded had they followed your advice. Today, with incredibly powerful infra of all types available, starting with Postgres is almost always the right step unless you know, know, better. That wasn't the case 10+ years ago.
I don't think I've once seen a migrating away from Postgres article.
https://www.youtube.com/watch?v=DNHMYp8M40k
I'm trying to avoid editorializing in my above summary, for fear of mischaracterizing their opinions or the current state of postgres. Their use of postgres was 10 years ago, they were using postgres for a high-availability use case -- so they (and I) don't think "postgres bad, cockroach good." But like Bryan Cantrill says, "No one cares about your workload like you do." So benchmark! Don't make technical decisions via "vibes!"
But yeah we did migrate our _analytics_ data to ClickHouse (while still keeping Postgres for more transactional stuff) back when I was at PostHog.
Writeup: https://posthog.com/blog/how-we-turned-clickhouse-into-our-e...
Clickhouse is incredible tech. We’ve been very pleased with it for OLAP queries, and it’s taken a lot of load off the postgres instance, so it can more easily handle the very high write load it gets subjected to.
The compression level is vastly superior to any available Postgres-based solution, and at Instagram’s scale it amounts to extremely compelling hardware cost savings.
Also if they were still primarily on pg, it would be one of the largest pg deployments in existence, and there would be obvious signs of the eng impact of that (conference talks, FOSS contributions, etc).
Bigger-picture: Postgres is an amazing database, and it’s often the right choice, but nothing in tech is always the best choice 100% of the time. There’s always trade-offs somewhere.
https://www.singlestore.com/made-on/heap/
Now we have lovely kql queries and pretty much start new with postgres again...
I can't think of any case at all, no matter how contrived, where you'd want to use the non-B versions of the JSON aggregate functions though.
https://www.postgresql.org/docs/current/indexes-expressional...
Currently the vendor lock-in or requirements for installing plugins make it hard to do with cloud sql providers. Especially hard since by the time it's a problem you're probably at enough scale to make switching db/vendors hard or impossible.
with pg_mooncake v0.2 (launching in ~couple weeks), you'll be able to get a columnar copy of your Postgres that's always synced (<s freshness).
Keep your write path unchanged, and keep your Postgres where it is. Deploy Mooncake as a replica for the columnar queries.
ORMs come in two main types, that I'm aware of: Active Record (named after the original Ruby one, I think) and Data Mapper (think Hibernate; SQLAlchemy).
Active Record ORMs are slightly more ergonomic at the cost of doing loads of work in application memory. Data Mapper looks slightly more like SQL in your code but are much more direct wrappers over things you can do in SQL.
Data Mapper also lets you keep various niceties such as generating migration code, that stem from having your table definition as objects.
Use Data Mapper ORMs if you want to use an ORM.
https://martinfowler.com/eaaCatalog/queryObject.html
https://www.martinfowler.com/eaaCatalog/activeRecord.html