[HN Gopher] Scaling PostgreSQL to power 800M ChatGPT users
___________________________________________________________________
Scaling PostgreSQL to power 800M ChatGPT users
Author : mustaphah
Score : 291 points
Date : 2026-01-22 21:24 UTC (1 days ago)
(HTM) web link (openai.com)
(TXT) w3m dump (openai.com)
| hu3 wrote:
| From what I understand they basically couldn't scale writes in
| PostgreSQL to their needs and had to offload what they could to
| Azure's NoSQL database.
|
| I wonder, is there another popular OLTP database solution that
| does this better?
|
| > For write traffic, we've migrated shardable, write-heavy
| workloads to sharded systems such as Azure CosmosDB.
|
| > Although PostgreSQL scales well for our read-heavy workloads,
| we still encounter challenges during periods of high write
| traffic. This is largely due to PostgreSQL's multiversion
| concurrency control (MVCC) implementation, which makes it less
| efficient for write-heavy workloads. For example, when a query
| updates a tuple or even a single field, the entire row is copied
| to create a new version. Under heavy write loads, this results in
| significant write amplification. It also increases read
| amplification, since queries must scan through multiple tuple
| versions (dead tuples) to retrieve the latest one. MVCC
| introduces additional challenges such as table and index bloat,
| increased index maintenance overhead, and complex autovacuum
| tuning.
| 0xdeafbeef wrote:
| Tidb should handle it nice. I've wrote 200k inserts / sec for
| hour in peak. Underlying lsm works better for writes
| anonzzzies wrote:
| That would mean it improved somewhat. We always got better
| write performance from mysql vs postgres, however that is a
| while ago; we then tried tidb to go further but it was
| basically rather slow. Again, a while ago.
|
| When did you get your results, might be time to re-evaluate.
| 0xdeafbeef wrote:
| It was 1 year ago. Around 15 tikv serves, 32 cpu, 128 ram
| each, 4 tb nvme. In this case latency matters a lot. When i
| had load server in different region with ping of 3ms I got
| 70k inserts, when moved to the same region with sub ms ping
| it went to thousands
| menaerus wrote:
| I was thinking about the same paragraph because write-
| amplification is exactly the problem solved by LSM trees _and_
| they already have a solution for that in-house - one of the
| first acquisitions that OpenAI made is Rockset - a company that
| actually built the RocksDb at scale.
|
| So, this is the part that actually made me left wondering why.
| everfrustrated wrote:
| 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.
| zozbot234 wrote:
| 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.
| dmix wrote:
| This is specifically what they said about sharding
|
| > The primary rationale is that sharding existing application
| workloads would be highly complex and time-consuming,
| requiring changes to hundreds of application endpoints and
| potentially taking months or even years
| zozbot234 wrote:
| I know they said that, but in fact sharding is entirely a
| database-level concern. The application need not be aware
| of it at all.
| EB66 wrote:
| Sharding can be made mostly transparent, but it's not
| purely a DB-level concern in practice. Once data is split
| across nodes, join patterns, cross-shard transactions,
| global uniqueness, certain keys hit with a lot of
| traffic, etc matter a lot. Even if partitioning handles
| routing, the application's query patterns and its
| consistency/latency requirements can still force
| application-level changes.
| awesome_dude wrote:
| > Once data is split across nodes, join patterns, cross-
| shard transactions, global uniqueness, certain keys hit
| with a lot of traffic
|
| If you're having trouble there then a proxy "layer"
| between your application and the sharded database makes
| sense, meaning your application still keeps its naieve
| understanding of the data (as it should) and the
| proxy/database access layer handles that messiness...
| shirley
| zozbot234 wrote:
| > mostly transparent, but it's not purely a DB-level
| concern in practice ...
|
| But how would any of that change by going outside
| Postgres itself to begin with? That's the part that
| doesn't make much sense to me.
| londons_explore wrote:
| When sharded, anything crossing a shard boundary becomes
| non-transactional.
|
| Ie. if you shard by userId, then a "share" feature which
| allows a user to share data with another user by having a
| "SharedDocuments" table cannot be consistent.
|
| That in turn means you're probably going to have to
| rewrite the application to handle cases like a shared
| document having one or other user attached to it
| disappear or reappear. There are _loads_ of bugs that can
| happen with weak consistency like this, and at scale
| every very rare bug is going to happen and need dealing
| with.
| zozbot234 wrote:
| > 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.
| londons_explore wrote:
| 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"
| doesn't work with the naive approach.
| ants_a wrote:
| These are limitations in the current PostgreSQL
| implementation. It's quite possible to have consistent
| commits and snapshots across sharded databases. Hopefully
| some day in PostgreSQL too.
| simonw wrote:
| Genuinely sounds like the kind of challenge that could be
| solved with a swarm of Codex coding agents. I'm surprised
| they aren't treating this as an ideal use-case to show off
| their stack!
| csto12 wrote:
| I read your message, guessed the author, and I'm happy to
| announce I guessed correctly.
| aisuxmorethanhn wrote:
| It wouldn't work.
| Ozzie_osman wrote:
| Getting the sharing in-place, yes, but maintaining it
| operationally would still be a headache. Things like
| schema migrations across shards, resharding, and even
| observability.
| gloflo wrote:
| Oh snap! Maybe it's all a great deception for making
| money?
| manquer wrote:
| > potentially taking months or even years
|
| On one hand OAI sell coding agents and constantly hype how
| easy it will replace developers and most of the code
| written is by agents, on the other hand they claim it will
| take years to refactor
|
| Both cannot be true at the same time.
| mkleczek wrote:
| Shameless plug: https://github.com/mkleczek/pgwrh automates
| it quite a bit.
| 9rx wrote:
| _> At that point you can well afford to hire people who are
| deep experts at sharding etc._
|
| Can you, though? OpenAI is haemorrhaging money like it is going
| out of style and, according to the news cycle over the last
| couple of days, will likely to be bankrupt by 2027.
| londons_explore wrote:
| And typically the bigger the company gets, the harder it is
| to migrate to a new data model.
|
| You suddenly have literally thousands of internal users of a
| datastore, and "We want to shard by userId, nobody please
| don't do joins on user Id anymore" becomes an impossible ask.
| ed_mercer wrote:
| Why does the [Azure PostgreSQL flexible server instance] link
| point to Chinese Azure?
| Natfan wrote:
| Bohan Zhang, the article's author, is likely Chinese.
|
| e: and the link points to en-us at time of writing. I frankly
| don't see the value in your comment.
| noxs wrote:
| All names are Asian and mostly Chinese
|
| > Author Bohan Zhang
|
| > Acknowledgements Special thanks to Jon Lee, Sicheng Liu,
| Chaomin Yu, and Chenglong Hao, who contributed to this post,
| and to the entire team that helped scale PostgreSQL. We'd also
| like to thank the Azure PostgreSQL team for their strong
| partnership.
| bzmrgonz wrote:
| Someone ask Microsoft what does it feel to be bested by an open
| source project on their very own cloud platform!!! Lol.
| esjeon wrote:
| Azure offers Postgres "DBaaS", so I'm pretty sure they are no
| where near that stage. It's more likely that we should watch
| out for the Microsoft E-E-E strategy.
| bzmrgonz wrote:
| Amen to that.. those tripple-E bastards are likely to use
| that playbook again. Best advise is to seek fertile grounds
| where freedom grows. I can't wait for Europe's cloud
| offering, I believe they're gonna serve as the middle ground
| between greedy tech-bros and china's fake free as in free
| beer products. Pack up your bags IT HOBBITS, we're moving to
| middle earth.
| DLA wrote:
| And the same for Linux boxes on Azure - they dominate Windows
| servers by a huge margin.
| beoberha wrote:
| Are you saying this because OpenAI didnt choose SQL Server?
| csto12 wrote:
| In 2026 is SQL Server ever the answer?
| Tostino wrote:
| 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.
| tormeh wrote:
| 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.
| Tostino wrote:
| The SQL Server query planner is head and shoulders above
| what Postgres offers in the types of optimizations it
| will apply to your queries. It also properly caches query
| plans.
|
| 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.
| bzmrgonz wrote:
| TBF, there's a price to be paid for speed on threads...
| no isolation, lower tolerance to failures, complex
| synchronization, painful debugging.
| atonse wrote:
| 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.
| beoberha wrote:
| That's kind of my point. They're not really in competition.
| I bet they'd have an easier time with this scale if they
| were on SQL Server, but obviously that migration isn't
| happening and startups don't reach for it for many reasons.
| everfrustrated wrote:
| The software licencing of 50 read replicas alone would
| make sqlserver a non-starter
| bzmrgonz wrote:
| My sentiments exactly. Anyone at the low side of scale
| thinking about MS SQL, should seriously do a current survey
| of things in the dbms space.. there is absolutely no NEED
| to pay for dbms in 2026. Those old dinosours only still
| exist, because of the data hijacking nature of past db
| designs and coding. Everybody and their grandmother were
| obfuscating code and designs in order to bake in customer
| loyalty and repetitive patronage. Those old projects are
| keeping the lights on at proprietary DB Inc. AT the high
| end of things, you're gonna need db engineers, and if you
| get yourself Microsoftie hammersharks disguised as
| professional engineers, they gonna see everything as a
| nail.
| doodlesdev wrote:
| They don't care. Azure has a revenue higher than GCP, losing
| only to AWS. It's Microsoft's new baby, and they love it, no
| matter what you want to run there. Also, they're still the 4th
| largest company by market cap.
|
| Honestly, only us nerds in Hacker News care about this kind of
| stuff :) (and that's why I love it here).
|
| edit: also, the article cites OpenAI did adopt Azure Cosmos DB
| for new stuff they want to shard. Still shows how far you can
| take PostgreSQL though.
| bdcravens wrote:
| That ship sailed a long time ago, as Microsoft has offered
| Linux VMs in Azure for 14 years, and today, about 2/3 of VMs
| running there are Linux. In the public cloud era, owning the
| infrastructure and customer base is far more important than
| licenses.
| QuiCasseRien wrote:
| I like the way of thinking. Instead of migrating to another
| database, they keep that awesome one running and found smart
| workaround to push limits.
| hahahahhaah wrote:
| It is what mature engineering does. Migrations are not fun.
| dbuser99 wrote:
| I don't get it. This whole thing says single writer does not
| scale, so we stopped writing as much and removed reads away from
| it, so it works ok and we decided that's enough. I guess thats
| great.
| sbstp wrote:
| This article has very little useful information...
|
| There's nothing novel about optimizing queries, sharding and
| using read replicas.
| ramraj07 wrote:
| 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.
| spullara wrote:
| 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 switch before it is an emergency.
| ants_a wrote:
| 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.
| hu3 wrote:
| 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.
| jll29 wrote:
| 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 simpler to manage
| than distributed setups where writes need be to carefully
| orchestrated - a very hard task).
|
| The two core mesages of both articles combined could be
| read as: 1. big DB servers are your friend and 2. keep it
| simple, unless you can't avoid the extra complexity any
| more.
| evanelias wrote:
| 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.
| resdev wrote:
| They could've used mongodb which is web scale NoSQL database
| because SQL is 1990's era legacy technology.
|
| /s
| winterrx wrote:
| First OpenAI Engineering blog? I'm definitely interested in
| seeing more and how they handled the rapid growth.
| londons_explore wrote:
| There was a lot of downtime...
|
| I think they handled the massive growth by a lot of 2am
| emergencies and editing config files directly in production in
| the hope of fixing fires.
| ahmetozer wrote:
| ai written blog, its very generic and same context is repated
| many times
| n_u wrote:
| Cool! I'd love to know a bit more about the replication setup.
| I'm guessing they are doing async replication.
|
| > We added nearly 50 read replicas, while keeping replication lag
| near zero
|
| I wonder what those replication lag numbers are exactly and how
| they deal with stragglers. It seems likely that at any given
| moment at least one of the 50 read replicas may be lagging cuz
| CPU/mem usage spike. Then presumably that would slow down the
| primary since it has to wait for the TCP acks before sending more
| of the WAL.
| tomnipotent wrote:
| > 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?
| bostik wrote:
| 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 used to signal the
| position of WAL head to the replicas. The replicas will then
| fetch the WAL files from the object store and replay them
| independently. This is what wall-g does, for a real life
| example.
|
| The tradeoffs when using that mechanism are pretty funky,
| though. For one, the strategy imposes a hard lower bound to
| replication delay because even the happy path is now "primary
| writes WAL file; primary updates WAL head position; primary
| uploads WAL file to object store; replica downloads WAL file
| from object store; replica replays WAL file". In case of
| unhappy write bursts the delay can go up significantly. You
| are also subject to any object store and/or API rate limits.
| The setup makes replication delays slightly more complex to
| monitor for, but for a competent engineering team that
| shouldn't be an issue.
|
| But it is rather hilarious (in retrospect only) when an
| object store performance degdaration takes all your replicas
| effectively offline and the readers fail over to getting
| their up-to-date data from the single primary.
| stemchar wrote:
| > 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?
| bostik wrote:
| 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[th] 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.
|
| th: The ballpark would have been tens of millions of
| rows.
| ants_a wrote:
| There is no backpressure from replication and streaming
| replication is asynchronous by default. Replicas can ask
| the primary to hold back garbage collection (off by
| default), which will eventually cause a slow down, but not
| blocking. Lagging replicas can also ask the primary to hold
| onto WAL needed to catch up (again, off by default), which
| will eventually cause disk to fill up, which I guess is
| blocking if you squint hard enough. Both will take
| considerable amount of time and are easily averted by
| monitoring and kicking out unhealthy replicas.
| maherbeg wrote:
| Yeah, you'll definitely want to set things like
| `max_standby_streaming_delay` and friends to ensure things
| are bound correctly.
| mannyv wrote:
| Uh, they scaled PostgreSQL by offloading a lot of it to Azure
| CosmosDB.
|
| I'm not sure that's the answer people are looking for.
| ph4evers wrote:
| Nice write up! It is cool to see that PostgreSQL is still
| standing. Adyen has some nice blog posts about squeezing the max
| out of PostgreSQL https://medium.com/adyen/all?topic=postgres
| ggregoire wrote:
| > 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?
| jiggawatts wrote:
| 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 databases or similar
| ERP workloads.
|
| Azure Standard_M896ixds_24_v3 provides 896 cores, 32 TB of
| memory, and 185 Gbps Ethernet networking. This is generally
| available, but you have to allocate the quota through a support
| ticket and you may have to wait and/or get your finances
| "approved" by Microsoft. Something like this will set you back
| [edited] $175K per month[/edited]. (I suspect OpenAI is getting
| a huge effective discount.)
|
| Personally, I'm a fan of "off label" use of the High
| Performance Compute (HPC) sizes[2] for database servers.
|
| The Standard_HX176rs HPC VM size gives you 176 cores and 1.4 TB
| of memory. That's similar to the E-series VM above, but with a
| higher compute-to-memory ratio. The memory throughput is also
| _way_ better because it has some HBM chips for L3 (or L4?)
| cache. In my benchmarks it absolutely smoked the general-
| purpose VMs at a similar price point.
|
| [1] https://learn.microsoft.com/en-us/azure/virtual-
| machines/siz...
|
| [2] https://learn.microsoft.com/en-us/azure/virtual-
| machines/siz...
| manquer wrote:
| On the AWS side there are "HANA certified" instances that max
| out at 1920 cores and 32 TB RAM - u7inh-32tb.480xlarge
|
| https://docs.aws.amazon.com/sap/latest/general/sap-hana-
| aws-...
| jiggawatts wrote:
| I'm pretty sure both Azure and AWS are merely reselling the
| same HPE Compute Scale-up Server 3200 chassis with some
| variations. Azure seems to have only the 16-socket model,
| but AWS has the 32-socket model.
|
| That AWS instance uses these 60-core processors: https://ww
| w.intel.com/content/www/us/en/products/sku/231747/...
|
| To anyone wondering about these huge memory systems: avoid
| them if at all possible! Only ever use these if you
| absolutely must.
|
| For one, these systems have specialised parts that are more
| expensive per unit compute: $283 per CPU core instead of
| something like $85 for a current-gen AMD EPYC, which are
| also about 2x as fast as the older Intel Scalable Xeons
| that need to go into this chassis! So the cost efficiency
| ratio is something like 6:1 in favour of AMD processors.
| (The cost of the single large host system vs multiple
| smaller ones can get complicated.)
|
| The second effect is that 32-way systems have huge inter-
| processor cache synchronisation overheads. Only very
| carefully coded software can scale to use thousands of
| cores without absolutely drowning in cache line
| invalidations.
|
| At these scales you're almost always better off scaling out
| "medium" sized boxes. A single writer and multiple read-
| only secondary replicas will take you _very_ far, up to
| hundreds of gigabits of aggregate database traffic.
| antonkochubey wrote:
| > Something like this will set you back $30K-$60K per year
|
| lol, no, cloud is nowhere near that good value. It's $3.5M
| annually.
|
| > The Standard_HX176rs HPC VM size gives you 176 cores and
| 1.4 TB of memory
|
| This one is $124k per year.
| jiggawatts wrote:
| Thanks for the correction, fixed.
|
| I noticed that the M896i is so obscure and rarely used that
| there are typos associated with it everywhere including the
| official docs! In once place is says it has 23 TB of memory
| when it actually has 32 TB.
| cuu508 wrote:
| Are there any pictures around of these 8, 16, 32 socket
| boards? Just curious how they look like.
| jiggawatts wrote:
| The individual motherboards have only four sockets: https:/
| /assets.ext.hpe.com/is/image/hpedam/s00012647?$zoom$#...
|
| Multiple of these can be linked together with "NUMALink"
| cables, which carry the same protocol as the traces that go
| between sockets on the motherboard. You end up with a
| single kernel running across multiple chassis.
| AlisdairO wrote:
| Regarding schema changes and timeouts - while having timeouts in
| place is good advice, you can go further. While running the
| schema rollout, run a script alongside it that kills any workload
| conflicting with the aggressive locks the schema change is trying
| to take. This will greatly reduce the pain caused by lock
| contention, and prevent you from needing to repeatedly rerun
| statements on high-throughput tables.
|
| This would be a particularly nice-to-have feature for Postgres -
| the option to have heavyweight locks just proactively cancel any
| conflicting workload. For any case where you have a high-
| throughput table, the damage of the heavyweight lock sitting
| there waiting (and blocking all new traffic) is generally much
| larger than just cancelling some running transactions.
| zozbot234 wrote:
| Doesn't Postgres support transactional schema changes already?
| Why would you want to proactively kill work that's just going
| to complete after the schema change is done? Load balancing,
| throttling etc. is a different matter that has little to do
| with what you're proposing.
| AlisdairO wrote:
| It supports transactional schema changes, but that's not what
| I'm talking about. Most schema changes require heavyweight
| locks on the tables they're altering. The locks might be
| short lived (for example, just a catalogue update to add a
| column), but they are nevertheless heavyweight and both block
| and are blocked by other work.
|
| SELECT or DML operations take a lightweight lock on the table
| that doesn't block _most_ other work, but it does block these
| schema changes. While the schema change is waiting to acquire
| the table lock, all _new_ operations (like new SELECTs, for
| example) get blocked until the schema change completes.
|
| So the following scenario can be pretty disastrous:
|
| * Start a long-running SELECT operation on table
|
| * Attempt to apply schema change to the table
|
| * All new work on the table is blocked until the SELECT
| completes and the schema change can apply.
|
| * Production outage
|
| What the ChatGPT folks do is set a lock timeout when applying
| the schema change to make it 'give up' after a few seconds.
| This works to avoid truly excessive impact (in their case,
| they may have micro-outages of up to 5s while trying to apply
| schema), but has problems - firstly, they then need to retry,
| which may lead to more micro-outages, and secondly there's no
| guarantee on a system with mixed workload that they will be
| able to force the change through, and the schema change just
| ends up getting starved out.
|
| A better alternative for most workloads is to build a system
| that detects what workload is blocking your schema change and
| kills it, allowing the schema change to go through quickly
| and unblock all the work behind it. You'd still use a lock
| timeout with this to be on the safe side, but it shouldn't be
| necessary in most cases.
|
| Side note on transactional DDL - for Postgres systems with
| high throughput, most people just use autocommit. Table level
| locks that get taken to perform the schema change get held
| for the duration of the transaction, and you generally want
| to really minimize the amount of time you hold them for.
| huksley wrote:
| "... If a new feature requires additional tables, they must be in
| alternative sharded systems such as Azure CosmosDB rather than
| PostgreSQL...."
|
| So it is not really scaling too much now, rather maintaining
| current state of things and new features go to a different DB?
| CodeCompost wrote:
| Azure CosmosDB is insanely expensive. I can't imagine anybody
| using it unless you have OpenAI money.
| freakynit wrote:
| *Microsoft's money
| londons_explore wrote:
| We don't know the profit margins on it... Might not be very
| expensive if you're an internal user as openAI effectively is
| for microsoft.
| qaq wrote:
| for people not burning billions of VC $ sharding Postgres is not
| a bad option.
| killingtime74 wrote:
| Why a single postgres? Why not shard by users?
| ramraj07 wrote:
| They literally answer that in the post. They started with a
| single instance and realized sharding the existing tables will
| be too much work (they'll slowly migrate to new tables
| instead).
| killingtime74 wrote:
| Right, should have read it
| trhway wrote:
| "However, some read queries must remain on the primary because
| they're part of write transactions. "
|
| if there is a read replica that has reached required snapshot -
| it is usually enough (depends on your task of course) for it to
| be the snapshot that was at the start of your transaction - and
| if the read query doesn't need to read your transaction
| uncommitted data, then that replica can serve the read query.
| KellyCriterion wrote:
| I would be super curious about:
|
| How do they store all the other stuff related to operating the
| service? This must be a combination of several components? (yes,
| including some massdata storage, Id guess?)
|
| This would be cool to understand, as Ive absolutely no idea how
| this is done (and could be done :-)
| kachapopopow wrote:
| Why does everyone make a "how we scaled PostgreSQL" article.
| mrweasel wrote:
| Running this on Azure Postgresql, even migrating to CosmosDB,
| cannot be cheap. I know that OpenAI have to deal/relationship
| with Microsoft, but still, this has to be expensive.
|
| This is however the most down to earth: How we scale Postgresql
| I've read in a long time. No weird hacker, no messing around with
| the source code or tweaking the Linux kernel. Running on Azure
| Postgresql it's not like OpenAI have those options anyway, but
| still it seems a lot more relatable than: We wrote our own
| drive/filesystem/database-hack in Javascript.
| neya wrote:
| Out of pure boredom and tired of all these Chat websites selling
| my data and with ChatGPT's new update on ads - I decided enough
| was enough and created my own Chat application for privacy. Like
| every other architect, I searched for a good database and
| eventually gave up on specialized ones for chat because they were
| either too expensive to host or too complex to deal with. So, I
| simply just used PostgreSQL. My chat app has basic RAG, not
| ground breaking or anything - but the most important feature I
| made was ability to add different chat models into one group
| chat. So, when you ask for opinions on something - you are not
| relying on just a single model and you can get a multi-model view
| of all the possible answers. Each model can have its own unique
| prompt within the group chat. So basically, a join table.
|
| Months passed by since this application was developed (a simple
| Phoenix/Elixir backend), and yesterday I was casually checking my
| database to see how many rows it had - about 500,000+ roughly. I
| didn't notice a single hint of the volume the Postgres was
| handling, granted - I'm the only user, but there's always a lot
| going on - RAG, mostly that requires searching of the database
| for context before multiple agents send you a response (and
| respond amongst themselves). Absolutely zero performance
| degradation.
|
| I'm convinced that Postgres is a killer database that doesn't get
| the attention it deserves over the others (for chat). Already
| managing some high traffic websites (with over 500M+ requests)
| with no issues, so I am extremely unsurprised that it works
| really well for chat apps at scale too.
| szastamasta wrote:
| Article has so much fluff and only some very coarse information
| like (we sharded writes, yay!). Almost no detail just keywords
| for SEO, or whatever they're aiming for.
|
| There's also a lot of repetition. Maybe it was AI generated...?
| PUSH_AX wrote:
| Could even be seen as a disguised ad for their infrastructure
| partner too.
| jll29 wrote:
| Yeah, right - when PostgreSQL starts to struggle, Microsoft
| Azure CosmoDB[tm] comes to the rescue (mentioned 3x).
| embedding-shape wrote:
| Those sort of articles suck big time.
|
| I remember coming across an article from NYCMesh which looked
| interesting ("Administrating the Mesh" -
| https://www.nycmesh.net/blog/datadog/) which made sense all
| the way until they put Datadog on top of everything, and I
| asked myself:
|
| > What the hell, how is using a centralized service for
| managing a decentralized mesh a suitable solution? Did the
| author get employed by Datadog or what happened?
|
| Then I got curious and lo and behold; the author was indeed
| hired by Datadog (and still works there AFAIK), effectively
| compromising the entire article and the project itself,
| because of their new employer.
| lenkite wrote:
| Lol, yes, it was very vague. Very generic paragraphs on
| Caching, Connection pooling, Query Optimization wrt Joins, etc.
| DeathArrow wrote:
| "This effort demonstrates that with the right design and
| optimizations, Azure PostgreSQL can be scaled to handle the
| largest production workloads."
|
| Sure, but choosing from the start a DB that can scale with ease
| would have taken far less time and effort.
|
| You can bend any software into doing anything, but is it worth
| it?
| PunchyHamster wrote:
| They could've just sharded it; their users are not
| interconnected, it would be easy to just have 128 shards and
| then assign user to one by org/user hash
| PunchyHamster wrote:
| Weird, I'd imagine for kind of use they are getting it would be
| easy to shard the infrastructure to entirely separate instances
|
| .
| nasretdinov wrote:
| 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 primary (with 50(!)
| read replicas), so could you, especially before you've reached
| your first 100M users.
|
| Any non-distributed database or setup is orders of magnitude
| easier to design for, and it's also typically much more cost
| efficient too, both in terms of hardware and software too.
|
| There are some curious details, e.g.:
|
| - you can ship WAL to 50 read replicas simultaneously from a
| single primary and be fine - you can even be using an ORM and
| still get decent performance - schema changes are possible, and
| you can just cancel a slow ALTER to prevent production impact -
| pgbouncer is ok even for OpenAI scale
|
| There are so many things that contradict current "conventional
| wisdom" based on the experience from what was possible with the
| hardware 10+ (or even 20+) years ago. Times finally changed and I
| really welcome articles like these that show how you can greatly
| simplify your production setup by leveraging the modern hardware.
| _ink_ wrote:
| 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.
| easton wrote:
| Might've had something to do with explaining behavior to their
| app teams. "Use this new DB product where it's sharded" might
| be easier than "here's a new postgres endpoint like the old one
| but now if you join on user ID it's inconsistent".
|
| (not that that's an excuse, but i've seen similar things
| before)
| lighthouse1212 wrote:
| 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.
| Etheryte wrote:
| When people spend their entire careers in AWS land, it's easy
| to forget just how much power a single beefy bare metal server
| brings to bear. You can scale far and wide simply by getting a
| bigger server.
| oofbey wrote:
| True. But "big beef" is complicated and difficult to make
| reliable. Horizontal scaling of unreliable servers is dirt
| simple to stay up through almost anything except sudden load
| spikes. And then it's largely a matter of configuring your
| auto scaling and retries.
|
| That said big beef is so simple to start with. And this story
| is a strong example that YAGNI is a practical reality for
| almost everybody wrt "distributed everything".
| fbotelho wrote:
| I like your point, but it also says that this isn't easy:
|
| > It may sound surprising that a single-primary architecture
| can meet the demands of OpenAI's scale; however, making this
| work in practice isn't simple.
|
| And it also says that this approach has cornered them into a
| solution that isn't trivial to change. They now use different
| database deployments (the single primary one that is the focus
| of the post and *multiple* other systems, such as Azure
| CosmosDB, to which some of the write traffic is being
| directed).
|
| > To mitigate these limitations and reduce write pressure,
| we've migrated, and continue to migrate, shardable (i.e.
| workloads that can be horizontally partitioned), write-heavy
| workloads to sharded systems such as Azure Cosmos DB,
| optimising application logic to minimise unnecessary writes. We
| also no longer allow adding new tables to the current
| PostgreSQL deployment. New workloads default to the sharded
| systems.
|
| I wonder how easy it is for developers to maintain and evolve
| this solution of miscellaneous database systems.
|
| So yes, you can go far with a single primary, but you can also
| potentially never easily get away from it.
| kevincox wrote:
| But they didn't really stay single primary. They moved a lot of
| load off to alternate database systems. So they did effectively
| shard, but to different databases rather than postgres.
|
| Quite possibly they would have been better off staying purely
| postgres but with sharing. But impossible to know.
| poemxo wrote:
| Counterpoint: if they had reached for sharding early, they
| would have avoided the technical debt of having to refactor
| their existing database. I don't think sharding is necessarily
| that complex either, especially for a SaaS style app like
| ChatGPT where users are mostly siloed in.
| msp26 wrote:
| This account's comment history is pure slop. 90% sure its all
| AI generated. The structure is too blatant.
| iamlintaoz wrote:
| If you need so many tricks to support the infra, it will
| eventually come back to bite you. I am pretty sure that Google
| in year 2000 could have supported their workloads with existing
| technologies (Yahoo could, and it was a much larger company).
| But they did GFS and Bigtable, and the rest is history. Other
| companies struggled to catch up due to inferior infrastructure.
| A visionary company needs to be prepared and should not be
| hindered by infrastructure. Can you scale the single primary
| system another 10x or more? Because their CEO said that they
| will scale their revenue by that much within just a couple of
| years.
| LudwigNagasena wrote:
| TL;DR There is no secret sauce, it's the same set of techniques
| you've seen in most PostgreSQL scaling guides. Those techniques
| do work.
| kuon wrote:
| The article is basically "we use PostgreSQL, it works, but we had
| to do some optimization to make it scale".
|
| I don't really get the point here. What is novel and great? It
| feels they followed the first " how to scale pg" article.
| ninkendo wrote:
| > 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 database, and not
| releasing it until you're done. We had to bump the Postgres
| connection limits by an order of magnitude, multiple times, and
| before you know it Postgres takes up more RAM than anything else
| just to support the number of connections we need.
|
| The problem permeated enough of our (rust) codebase that I had to
| come up with a compile time check that makes sure you're not
| awaiting any async functions while a Postgres connection is in
| your scope. Using the .await keyword on an async function call,
| but _not_ passing the pg connection to that function, ends up
| being a nearly perfect proxy for "doing unrelated work while not
| releasing a connection". It worked extremely well, the compiler
| now just straight up tells us where we're doing it wrong (in 100+
| places in fact.)
|
| Actually getting away from that pattern has been the hard part,
| but we're almost rid of every place we're doing it, and I can now
| run with a 32-connection pool in load testing instead of a 10,000
| connection pool and there's no real slowdowns. (Not that we'd go
| that low in production but it's nice to know we can!)
|
| Just decreasing the timeout for idle transactions would have
| probably been the backup option, but some of the code that holds
| long transactions is very rarely hit, and it would have taken a
| lot of testing to eliminate all of it if we didn't have the
| static check.
| csiegert wrote:
| 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"?
| ninkendo wrote:
| 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 etc.
|
| 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.
| yobbo wrote:
| "Work" might require a transaction which reads with lock,
| computes, (launch missiles), and then updates.
| maherbeg wrote:
| This is really tough in a large organization with features
| that cross across product domains.
| tasn wrote:
| How did you implement this runtime check? Is it a lint rule, or
| using the type system?
| ninkendo wrote:
| 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.)
| MarginalGainz wrote:
| This scale is only possible because of what they don't ask
| Postgres to do.
|
| If you treat Postgres strictly as a Key-Value store for
| transactional integrity, it flies. But if you try to run complex
| discovery or tsvector search on that same primary, the
| locking/CPU load kills it instantly.
|
| The real hero here is aggressive segregation: keep Postgres for
| 'Truth', and offload all discovery to specialized indices that
| don't block the write-head.
| bhouston wrote:
| Ah yes, OpenAI is sharding now. Surprise surprise.
|
| 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:
|
| https://news.ycombinator.com/item?id=44074702
| HenriTEL wrote:
| Yeah that's an ad for Azure Cosmos DB
| ASalazarMX wrote:
| I was confused when I saw elsewhere a "goose with a knife" meme
| asking someone if they had 800M users, and then if they had
| donated to PostgreSQL. Now I understand.
___________________________________________________________________
(page generated 2026-01-23 23:01 UTC)