Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Scaling PostgreSQL to power 800M ChatGPT users

openai.com

Post reply on HN
Print view View original

131–140 of 145 posts

Re: Scaling PostgreSQL to power 800M ChatGPT users

#131

Post by ahachete »

Postgres can really scale well vertically (and horizontally for read-only workloads) as the post shows.

However, I'm still surprised about the reasons for not sharding. They have been mentioned before, but I haven't seen a substantial rationale.

Sharding is almost only analyzed from the perspective of write scaling. But sharding may not only be about write scaling, but a path to reducing blast radius. And this is something that triggers much earlier than write scaling needs (especially given how well Postgres scales vertically and reads).

When you shard your database, you end up having N clusters (for HA purposes, each "shard" must be a primary-replica(s) cluster itself), each holding 1/Nth of the data.

There are certain scenarios which, while unlikely, may hit you: data corruption in the WAL replication stream, a problem during a major upgrade, a situation that requires a whole cluster restore from a backup, you name it. For those cases, the whole cluster may experience notable downtime.

If you have a single cluster, 100% of your users experience downtime. If you sharded into N clusters, only 1/Nth of your users experience downtime. For a company servicing 800M users the difference from both scenarios is dramatically different. Even for much much smaller companies.

I'm puzzled why this is not perceived as a risk, and if it is not, how it is mitigated.

While I wouldn't advocate to shard "too early", given that it comes with notable caveats, I believe more and more in sharding your workloads when possible more earlier than later. Way before truly needing it from a write scaling perspective. Because apart from reducing the blast radius, it applies implicitly the principle of "divide-and-conquer", and your problems become much more manageable (your number of connections per cluster decreases at will, backup restore times can be a fraction of the time, logical replication can be considered as a true option for replication/upgrades/etc if you keep shards relatively small and many other operational procedures are greatly simplified if now you have much smaller databases, even if you have many more of them).

Re: Scaling PostgreSQL to power 800M ChatGPT users

#132

Post by [deleted] »

ninkendo wrote: post #103
csiegert wrote: post #100

Earlier quoted context omitted.

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”?

That’s what we should do, yes. The problem is that we were just sorta careless with interleaving database calls in with the “work” we were doing. So that function that calls that slow external service, also takes a &PgConnection as an argument, because it wants to bump a timestamp in a table somewhere after the call is complete. Which means you need to already have a connection open to even call that function, etc et…

[deleted]

Re: Scaling PostgreSQL to power 800M ChatGPT users

#133

Post by ants_a »

bostik wrote: post #127
stemchar wrote: post #78

Earlier quoted context omitted.

> If you use streaming replication (ie. WAL shipping over the replication connection), a single replica getting really far behind can eventually cause the primary to block writes. Some time back I commented on the behaviour: https://news.ycombinator.com/item?id=45758543 I'd like to know more, since I don't understand how this could happen. When you say "block", what do you mean exactly?

I have to run part of this by guesswork, because it's based on what I could observe at the time. Never had the courage to dive in to the actual postgres source code, but my educated guess is that it's a side effect of the MVCC model. Combination of: streaming replication; long-running reads on a replica; lots[þ] of writes to the primary. While the read in the replica is going it will generate a temporary table under…

What you are describing here does not match how postgres works. A read on the replica does not generate temporary tables, nor can anything on the replica create locks on the primary. The only two things a replica can do is hold back transcation log removal and vacuum cleanup horizon. I think you may have misdiagnosed your problem.

Re: Scaling PostgreSQL to power 800M ChatGPT users

#135

Post by MaxGabriel »

ninkendo wrote: post #98

> 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…

We have a similar check in our Haskell codebase, after running into two issues:

1. Nested database transactions could exhaust the transaction pool and deadlock 2. Same as you described with doing eg HTTP during transactions

We now have a compile time guarantee that no IO can be done outside of whitelisted things, like logging or getting the current time. It’s worked great! Definitely a good amount of work though.

Re: Scaling PostgreSQL to power 800M ChatGPT users

#136

Post by tetha »

jiggawatts wrote: post #36
ggregoire wrote: post #35

> scaled up by increasing the instance size I always wondered what kind of instance companies at that level of scalability are using. Anyone here have some ideas? How much cpu/ram? Do they use the same instance types available to everyone, or does AWS and co offer custom hardware for these big customers?

The major hyperscalers all offer a plethora of virtual machines SKUs that are essentially one entire two-socket box with many-core CPUs. For example, Azure Standard_E192ibds_v6 is 96 cores with 1.8 TB of memory and 10 TB of local SSD storage with 3 million IOPS. Past those "general purpose" VMs you get the enormous machines with 8, 16, or even 32 sockets.[1] These are almost exclusively used for SAP HANA in-memory da…

Interestingly enough,

> For example, Azure Standard_E192ibds_v6 is 96 cores with 1.8 TB of memory and 10 TB of local SSD storage with 3 million IOPS.

Is a well-stocked Dell Server going for ~50 - 60K capex without storage before the RAM prices exploded. I"m wondering a bit about the CPU in there, but the Storage + RAM is fairly normal and nothing crazy. I'm pretty sure you could have that in a rack for 100k hardware pricing.

Re: Scaling PostgreSQL to power 800M ChatGPT users

#137

Post by deepbluev7 »

ninkendo wrote: post #121
tasn wrote: post #119

Earlier quoted context omitted.

How did you implement this runtime check? Is it a lint rule, or using the type system?

It’s a compile-time check, and yeah it’s a lint rule. In fact it goes a little deeper than a lint can go, because it uses data from earlier compiler phases (in order to get access to what the borrow checker knows.) The correct terminology is a “rustc driver” from what I’ve heard. Lints like clippy run as a “LateLintPass”, which doesn’t have access to certain mir data that is intentionally deleted in earlier phases to…

I would love to see how you implemented that (and also the lint itself). I so far haven't found a solid way to implement custom lints for Rust, so if you have any resources to share at some point, I would love to see them!

Re: Scaling PostgreSQL to power 800M ChatGPT users

#138

Post by ninkendo »

MaxGabriel wrote: post #135
ninkendo wrote: post #98

> 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…

We have a similar check in our Haskell codebase, after running into two issues: 1. Nested database transactions could exhaust the transaction pool and deadlock 2. Same as you described with doing eg HTTP during transactions We now have a compile time guarantee that no IO can be done outside of whitelisted things, like logging or getting the current time. It’s worked great! Definitely a good amount of work though.

I figured it’d be Haskell that is able to do this sort of thing really well. :-D

I had this realization while writing the rustc plugin that this is basically another shade of “function coloring”, but done intentionally. Now I wish I could have a language that lets me intentionally “color” my functions such that certain functions can only be called from certain blessed contexts… not unlike how async functions can only be awaited by other async functions, but for arbitrary domain-specific abstractions, in particular database connections in this case. I want to make it so HTTP calls are “purple”, and any function that gets a database connection is “pink”, and make it so purple can call pink but not vice-versa.

The rule I ended up with in the lint, is basically “if you have a connection in scope, you can only .await a function if you’re passing said connection to that function” (either by reference or by moving it.) It works with rust’s knowledge of lifetimes and drop semantics, so that if you call txn.commit() (which moves the connection out of scope, marking the storage as dead) you’re now free to do unrelated async calls after that line of code. It’s not perfect though… if you wrap the connection in a struct and hold that in your scope, the lint can’t see that you’re holding a connection. Luckily we’re not really doing that anywhere: connections are always passed around explicitly. But even if we did, you can also configure the lint with a list of “connection types” that will trigger the lint.

Re: Scaling PostgreSQL to power 800M ChatGPT users

#139

Post by junto »

ahachete wrote: post #131

Postgres can really scale well vertically (and horizontally for read-only workloads) as the post shows. However, I'm still surprised about the reasons for not sharding. They have been mentioned before, but I haven't seen a substantial rationale. Sharding is almost only analyzed from the perspective of write scaling. But sharding may not only be about write scaling, but a path to reducing blast radius. And this is som…

Microsoft originally bought CitusData and rebadged it as Azure CosmosDb for Postgres Cluster. Microsoft have been recommending partners to now avoid that product. It does not and will not support Entra federated workload identities (passwordless).

The replacement will be Azure Database for Postgres with Elastic Clusters. I think it is still in preview.

Again it’s Citus based, but without the CosmosDb badge and it will support federated workload identities.

https://techcommunity.microsoft.com/blog/adforpostgresql/pos...

https://learn.microsoft.com/en-us/azure/postgresql/elastic-c...

Re: Scaling PostgreSQL to power 800M ChatGPT users

#140

Post by junto »

_ink_ wrote: post #74

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.

At a guess CosmosDb NoSql was a good choice for dumping user contexts sharded rather than use Postgres schema or JSONB. Citus is the obvious choice for this with Postgres but Azure had poor support until recently 2024 they have preview Azure Database for Postgres with Elastic Clusters, which is basically Azure Database for Postgres Flexible Server with Citus extension installed. In the end they aren’t paying Microsoft what usual customer are, so even though CosmosDb NoSql is expensive (RU based) and the SDK is horrible, it probably served as a good stopgap until elastic clusters is fully out of preview. That’s my guess anyway.
Post reply on HN