101–110 of 145 posts
I honestly don't understand such negative response tone from the comments. Yes, it does promote Azure, but that's to be expected from a company with is part owned by Microsoft :). The main point of the article is that it's actually not that hard to live with a single primary Postgres for your transactional workloads (emphasis on _transactional_), and if OpenAI with their 800M+ users can still survive on a single prim…
> It’s also common to find long-running idle queries in PostgreSQL. Configuring timeouts like idle_in_transaction_session_timeout is essential to prevent them from blocking autovacuum. Idle transactions have been a huge footgun at $DAYJOB… our code base is full of “connect, start a transaction, do work, if successful, commit.” It means you’re consuming a connection slot for all work, even while you’re not using the d…
Why don’t you change the order to “do work, if successful, grab a connection from the Postgres connection pool, start a transaction, commit, release the connection to the connection pool”?
If the codebase is large, and full of that kind of pattern (interleaving db writes with other work), the compiler plugin is nice for (a) giving you a TODO list of all the places you’re doing it wrong, and (b) preventing any new code from doing this while you’re fixing all the existing cases.
One idea was to bulk-replace everything so that we pass a reference to the pool itself around, instead of a checked-out connection/transaction, and then we would only use a connection for each query on-demand, but that’s dangerous… some of these functions are doing writes, and you may be relying on transaction rollback behavior if something fails. So if you were doing 3 pieces of “work” with a single db transaction before, and the third one failed, the transaction was getting rolled back for all 3. But if you split that into 3 different short-lived connections, now only the last of the 3 db operations is rolled back. So you can’t just find/replace, you need to go through and consider how to re-order the code so that the database calls happen “logically last”, but are still grouped together into a single transaction as before, to avoid subtle consistency bugs.
The 'single primary with read replicas' pattern scaling to 800M users is the real insight here. Most startups reach for sharding or distributed databases way too early, adding complexity for scale they don't have. If OpenAI can serve hundreds of millions from one Postgres primary by offloading reads and pushing new write-heavy features elsewhere, that's a strong argument for simplicity.
I mentioned that as a right solution to the problem last time they posted about Postgres performance issues:
https://news.ycombinator.com/item?id=44072645
But the response from an OpenaI engineer (who is the author of this article) was that sharding isn't the solution:
Earlier quoted context omitted.
It really is a good database. Give it lots of room. If you can distribute your workload on multiple machines though, you can't beat Postgres' licencing terms vs SQL Server.
Why is it a good database? Integration with Entra? I've heard arguments in favor of Oracle DB, but I've never heard anything good about MSSQL besides integration with the MS ecosystem.
It offers heap tables, as well as index organized tables depending on what you need.
The protocol supports running multiple queries and getting multiple resultsets back at once saving some round-trips and resources.
Also supports things like global temp tables, and in memory tables, which are helpful for some use cases.
The parallelism story for a single query is still stronger with SQL Server.
I'm sure I could think of more, but it's been a few years since I've used it myself and I've forgotten a bit.
It is a good database. I just wouldn't use it for my startup. I could never justify that license cost, and how it restricts how you design your infrastructure due to the cost and license terms.
Did I miss it, or did they not say why they picked CosmoDB? Postgres has also sharding, so instead of moving to a different DB they could have added a new postgres instance with sharding for the new requests.
(not that that's an excuse, but i've seen similar things before)
Earlier quoted context omitted.
It has one piece of useful info: their main data store even for 800M users is a single instance of postgres (for writes) without sharding.
when I joined twitter in 2011 there was a single mysql master user (not tweets) database and a few dozen read replicas. it was writing about 7000 updates per second and during bursts it would go too high for the single-threaded replication in mysql at the time to keep up with the master which would cause replication lag and all kinds of annoying things in the app. you just have to pick the right time to make the swit…
Earlier quoted context omitted.
> When sharded, anything crossing a shard boundary becomes non-transactional. Not necessarily? You can have two-phase commit for cross-shard writes, which ought to be rare anyway.
Two-phase commit provides an eventual consistency guarantee only.... Other clients (readers) have to be able to deal with inconsistencies in the meantime. Also, 2PC in postgres is incompatible with temporary tables, which rules out use with longrunning batch analysis jobs which might use temporary tables for intermediate work and then save results. Eg. "We want to send this marketing campaign to the top 10% of users"…
> It’s also common to find long-running idle queries in PostgreSQL. Configuring timeouts like idle_in_transaction_session_timeout is essential to prevent them from blocking autovacuum. Idle transactions have been a huge footgun at $DAYJOB… our code base is full of “connect, start a transaction, do work, if successful, commit.” It means you’re consuming a connection slot for all work, even while you’re not using the d…
Why don’t you change the order to “do work, if successful, grab a connection from the Postgres connection pool, start a transaction, commit, release the connection to the connection pool”?