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

121–130 of 145 posts

Re: Scaling PostgreSQL to power 800M ChatGPT users

#121

Post by ninkendo »

tasn wrote: post #119
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…

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 lower the memory requirements.

Hopefully it’s something I can open source soon (I may upstream it to the sqlx project, as that is what we’re using for db connections.)

Re: Scaling PostgreSQL to power 800M ChatGPT users

#122

Post by mkleczek »

zozbot234 wrote: post #10
everfrustrated wrote: post #3

This is why I love Postgres. It can get you to being one of the largest websites before you need to reconsider your architecture just by throwing CPU and disk at it. At that point you can well afford to hire people who are deep experts at sharding etc.

PostgreSQL actually supports sharding out of the box, it's just a matter of setting up the right table partitioning and using Foreign Data Wrapper (FDW) to forward queries to remote databases. I'm not sure what the post is referencing when they say that sharding requires leaving Postgres altogether.

Shameless plug: https://github.com/mkleczek/pgwrh automates it quite a bit.

Re: Scaling PostgreSQL to power 800M ChatGPT users

#123

Post by maherbeg »

bostik wrote: post #60
tomnipotent wrote: post #30

Earlier quoted context omitted.

> would slow down the primary since it has to wait for the TCP acks Other than keeping around more WAL segments not sure why it would slow down the primary?

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 You could use asynchronous WAL shipping, where the WAL files are uploaded to an object store (S3 / Azure Blob) and the streaming connections are only u…

Yeah, you'll definitely want to set things like `max_standby_streaming_delay` and friends to ensure things are bound correctly.

Re: Scaling PostgreSQL to power 800M ChatGPT users

#124

Post by maherbeg »

csiegert wrote: post #100
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…

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

This is really tough in a large organization with features that cross across product domains.

Re: Scaling PostgreSQL to power 800M ChatGPT users

#126

Post by hu3 »

ants_a wrote: post #108
spullara wrote: post #61

Earlier quoted context omitted.

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…

Postgres setups are typically based on physical replication, which is not an option on MySQL. My testing shows the limit to be about 177k tps with each transaction consisting of 3 updates and 1 insert.

Be careful. During consulting I ran into similar magnitude of writes for a mostly CRUD workload.

They had huge problems with VACUUM at high tps. Basically the database never had space to breath and cleanup.

Re: Scaling PostgreSQL to power 800M ChatGPT users

#127

Post by bostik »

stemchar wrote: post #78
bostik wrote: post #60

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 You could use asynchronous WAL shipping, where the WAL files are uploaded to an object store (S3 / Azure Blob) and the streaming connections are only u…

> 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 the hood (because the read "holds the table open by point in time"). Something in this scenario leaked the state from replica to primary, because after several hours the primary would error out, and the logs showed that it failed to write because the old table was held in place in the replica and the two tables had deviated too far apart in time / versions.

It has seared to my memory because the thing just did not make any sense, and even figuring out WHY the writes had stopped at the primary took quite a bit of digging. I do remember that when the read at the replica was forcefully terminated, the primary was eventually released.

þ: The ballpark would have been tens of millions of rows.

Re: Scaling PostgreSQL to power 800M ChatGPT users

#128

Post by evanelias »

jll29 wrote: post #92
ramraj07 wrote: post #54

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.

The post tells you there is a single point of failure: if you wanted to DDOS OpenAI, you'd target write-heavy operations. For that reason, I find it actually bold that they disclosed it, and I appreciate it. The article reminded me of a similar post about MySQL use for Facebook from the Meta team, which had the same message: big database servers are powerful workhorses that scale and are very cost-effective (and simp…

What Facebook post are you referring to? Generally speaking, Facebook's MySQL infra has been heavily sharded for a very long time, and doesn't rely on abnormally-beefy servers. It's basically the complete opposite approach of what OpenAI is describing here.

Re: Scaling PostgreSQL to power 800M ChatGPT users

#129

Post by atonse »

tormeh wrote: post #48
Tostino wrote: post #29

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.

I love Postgres and use it for _everything_. I've also used SQL Server for a couple of years.

I've lost count the number of times I'll read about some new postgres or MySQL thing where you find out that Oracle or SQL server implemented it 20 years ago. Yes they always have it behind expensive SKUs. But they're hardly slouches in the technical competence departments.

I found Oracle to just be a lot more unwieldy from a tooling perspective than SQL Server (which IMO had excellent tools like SSMS and the query planner/profiler to do all your DB management).

But overall, these paid databases have been very technically sound and have been solving some of these problems many, many years ago. It's still nice to see the rest of us benefit from these features in free databases nowadays.

As others have said, the query planners I used 25 years ago with Oracle (cost based, rule based, etc) were amazing. The oracle one wasn't visual but the MSSQL one was totally visual that actually gave you a whole graph of how the query was assembled. And I last used the MSSQL one 15 years ago.

Maybe pgAdmin does that now (I haven't used pgAdmin), but I miss the polished tools that came with SQL Server.

Re: Scaling PostgreSQL to power 800M ChatGPT users

#130

Post by awesome_dude »

londons_explore wrote: post #95
zozbot234 wrote: post #84

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

Sorry, what am I missing here, this complaint is true for all architectures, because the readers are always going to be out of sync with the state in the database until they do another read.

The nanosecond that the system has the concept of readers and writers being different processes/people/whatever it has multiple copies, the one held by the database, and the copies held by the readers when they last read.

It does not matter if there is a single DB lock, or a multi shared distributed lock.

Post reply on HN