By Bohan Zhang, Member of the Technical Staff
Overview
OpenAI details how they scaled PostgreSQL to support 800 million ChatGPT users, achieving millions of queries per second through a single-primary architecture with nearly 50 read replicas across multiple global regions. The article covers the challenges of MVCC-related write amplification, connection pooling with PgBouncer, workload isolation, caching strategies, and rate limiting that enabled them to maintain low double-digit millisecond p99 latency and five-nines availability.
What You'll Learn
1
How to scale PostgreSQL for read-heavy workloads using a single-primary architecture with dozens of read replicas
2
How to use PgBouncer connection pooling to reduce connection setup latency from 50ms to 5ms
3
Why PostgreSQL's MVCC implementation creates write amplification and how to mitigate it by offloading write-heavy workloads
4
How to implement cache locking mechanisms to prevent cache-miss storms from overwhelming the database
5
When to use workload isolation and tiered routing to prevent noisy neighbor problems in shared database infrastructure
Prerequisites & Requirements
- Understanding of PostgreSQL architecture including replication, WAL (Write Ahead Log), and MVCC (multiversion concurrency control)
- Familiarity with database scaling concepts such as sharding, read replicas, and connection pooling
- Experience operating databases at scale in production environments
- Understanding of caching layers and cache invalidation patterns(optional)
Key Questions Answered
How does OpenAI scale PostgreSQL to handle millions of queries per second for ChatGPT?
OpenAI uses a single Azure PostgreSQL flexible server primary instance with nearly 50 read replicas spread across multiple global regions. They offload most read traffic to replicas, use PgBouncer for connection pooling, implement caching with lock mechanisms to prevent miss storms, isolate workloads by priority tier, and migrate write-heavy workloads to sharded systems like Azure Cosmos DB.
Why doesn't OpenAI shard their PostgreSQL database?
Sharding existing application workloads would require changes to hundreds of application endpoints and could take months or years. Since their workloads are primarily read-heavy and they've implemented extensive optimizations, the single-primary architecture with read replicas provides sufficient headroom for current and future growth. They instead shard only write-heavy workloads to systems like Azure Cosmos DB.
What problems does PostgreSQL MVCC cause at scale?
PostgreSQL's MVCC implementation copies the entire row when updating even a single field, causing significant write amplification. Under heavy write loads, this also increases read amplification as queries must scan through multiple dead tuple versions. Additional challenges include table and index bloat, increased index maintenance overhead, and complex autovacuum tuning requirements.
How does PgBouncer improve PostgreSQL connection management at scale?
PgBouncer acts as a proxy layer that pools database connections. Running in statement or transaction pooling mode efficiently reuses connections, reducing active client connections. At OpenAI, average connection time dropped from 50ms to 5ms. They co-locate the proxy, clients, and replicas in the same region to minimize network overhead, with each read replica having its own Kubernetes deployment running multiple PgBouncer pods.
How do you prevent cache miss storms from overwhelming PostgreSQL?
OpenAI implements a cache locking and leasing mechanism where only a single reader that misses on a particular cache key fetches data from PostgreSQL. When multiple requests miss the same key, only one acquires the lock to retrieve data and repopulate the cache. All other requests wait for the cache update rather than all hitting PostgreSQL simultaneously, preventing cascading load spikes.
What is cascading replication in PostgreSQL and why is it needed?
Cascading replication allows intermediate replicas to relay WAL data to downstream replicas instead of having the primary stream WAL to every replica directly. As replica count grows, the primary faces increasing network bandwidth and CPU pressure from WAL shipping. Cascading replication could allow scaling to over 100 replicas without overwhelming the primary, though it introduces additional failover management complexity.
How does OpenAI handle the single point of failure risk with one PostgreSQL primary?
They run the primary in High-Availability mode with a hot standby that's continuously synchronized and ready to take over. Critical read-only requests are offloaded to replicas so they continue serving even if the primary fails, reducing the blast radius. Multiple replicas per region with capacity headroom ensure single replica failures don't cause regional outages. The Azure PostgreSQL team ensures failovers remain safe under high load.
What schema management practices does OpenAI follow for PostgreSQL at scale?
Only lightweight schema changes are permitted that don't trigger full table rewrites, with a strict 5-second timeout enforced. Creating and dropping indexes concurrently is allowed. New tables must go in alternative sharded systems like Azure Cosmos DB rather than PostgreSQL. When backfilling table fields, strict rate limits are applied even if the process takes over a week to complete.
Key Statistics & Figures
ChatGPT user base
800 million users
Total users served by PostgreSQL-backed infrastructure
PostgreSQL load growth
More than 10x over the past year
Year-over-year increase in database load
Read replicas deployed
Nearly 50
Spread across multiple geographic regions globally
Query throughput
Millions of queries per second
QPS
Connection time with PgBouncer
5ms
down from 50ms
Client-side p99 latency
Low double-digit milliseconds
Consistent production read latency
Production availability
Five-nines
99.999%
SEV-0 incidents in 12 months
1
Occurred during ChatGPT ImageGen viral launch when write traffic surged over 10x with 100 million new signups in a week
Azure PostgreSQL max connection limit
5,000 per instance
Maximum connection limit that can be exhausted during connection storms
Schema change timeout
5 seconds
Strict timeout enforced on schema changes to prevent production impact
Technologies & Tools
Some links below are affiliate links. We may earn a commission if you make a purchase.
Database
Postgresql
Primary database powering ChatGPT and OpenAI API with single-primary architecture and read replicas
Managed Database
Azure Postgresql Flexible Server
Cloud-managed PostgreSQL instance serving as the single primary writer
Database
Azure Cosmos Db
Sharded database system for write-heavy workloads migrated from PostgreSQL
Connection Pooler
Pgbouncer
Connection pooling proxy layer running in Kubernetes to reduce connection overhead and setup latency
Orchestration
Kubernetes
Running PgBouncer deployments with load-balanced Kubernetes Services for each read replica
Replication
Wal (write Ahead Log)
PostgreSQL replication mechanism streaming data from primary to read replicas
Key Actionable Insights
1Implement cache locking mechanisms to prevent thundering herd problems during cache miss storms. When multiple requests miss the same cache key, only one should fetch from the database while others wait for the cache to be populated. This prevents cascading database overload from sudden cache failures.OpenAI experienced SEVs caused by widespread cache misses from caching-layer failures that saturated PostgreSQL CPU and triggered retry storms.
2Deploy PgBouncer in statement or transaction pooling mode as a proxy layer between your application and PostgreSQL to dramatically reduce connection overhead and prevent connection exhaustion. Co-locate the proxy, clients, and replicas in the same region to minimize network overhead.OpenAI reduced average connection time from 50ms to 5ms using PgBouncer. Azure PostgreSQL has a maximum connection limit of 5,000, and they previously experienced incidents from connection storms.
3Isolate database workloads by priority tier onto dedicated instances to prevent the noisy neighbor problem. Split requests into low-priority and high-priority tiers routed to separate database instances so resource-intensive workloads don't degrade critical requests.New feature launches at OpenAI introduced inefficient queries that consumed disproportionate PostgreSQL CPU, slowing down other critical features running on the same instances.
4Carefully review SQL generated by ORM frameworks rather than trusting their output. Complex multi-table joins generated by ORMs can become extremely costly at scale—consider breaking them down and moving complex join logic to the application layer instead.OpenAI identified an extremely costly ORM-generated query joining 12 tables whose volume spikes were responsible for past high-severity SEVs.
5Implement multi-layer rate limiting across application, connection pooler, proxy, and query layers to prevent traffic spikes from cascading into database failures. Enhance the ORM layer to support rate limiting and the ability to fully block specific query digests for targeted load shedding.Sudden traffic spikes and retry storms with overly short retry intervals can exhaust CPU, I/O, and connections, causing widespread service degradation.
6Enforce strict constraints on schema changes in production—only allow lightweight operations that don't trigger full table rewrites, set aggressive timeouts (e.g., 5 seconds), and rate-limit backfills even if they take weeks. Avoid adding new tables to existing high-traffic PostgreSQL deployments.At OpenAI's scale, even a small schema change like altering a column type can trigger a full table rewrite, causing production instability.
Common Pitfalls
1
Using complex multi-table joins in OLTP workloads, especially those generated by ORM frameworks. A single query joining 12 tables can consume massive CPU resources during volume spikes and bring down the entire service. ORM-generated queries often contain unexpected join patterns that aren't apparent without reviewing the raw SQL.
Always review ORM-generated SQL carefully. Consider breaking complex joins into multiple simpler queries and performing join logic at the application layer.
2
Not implementing cache locking, allowing all concurrent cache misses on the same key to simultaneously hit the database. During a caching-layer failure, this creates a thundering herd effect where burst cache misses push massive read volume directly to PostgreSQL, consuming CPU and causing cascading failures.
Implement a cache locking/leasing mechanism so only one reader per cache key fetches from the database while others wait for the cache to be repopulated.
3
Using overly short retry intervals in application code, which amplifies load during database degradation. When query latency rises and requests begin timing out, retries with aggressive intervals create a vicious cycle that further overwhelms the database and can cascade into complete service degradation.
Configure appropriate retry backoff strategies and implement rate limiting at multiple layers to prevent retry storms from compounding database overload.
4
Allowing long-running idle queries to accumulate in PostgreSQL without configuring proper timeouts. Idle-in-transaction sessions can block autovacuum, leading to table bloat and degraded performance over time as dead tuples accumulate.
Configure idle_in_transaction_session_timeout and other timeout parameters to prevent idle queries from blocking critical PostgreSQL maintenance operations.
5
Performing schema changes that trigger full table rewrites on production databases at scale, such as altering column types. Even seemingly small changes can lock tables and rewrite all data, causing significant production impact at high traffic volumes.
Restrict schema changes to lightweight operations only, enforce strict timeouts on DDL statements, and route new tables to alternative sharded systems instead of adding them to high-traffic PostgreSQL instances.
6
Adding read replicas indefinitely without considering the replication overhead on the primary. Each replica requires the primary to stream WAL data, increasing network bandwidth and CPU pressure, eventually causing higher and more unstable replica lag.
Consider cascading replication where intermediate replicas relay WAL to downstream replicas, though this introduces additional failover complexity.
Related Concepts
Postgresql Mvcc
Write Ahead Log (wal) Replication
Cascading Replication
Connection Pooling
Pgbouncer
Database Sharding
Cache-aside Pattern
Cache Locking And Leasing
Thundering Herd Problem
Noisy Neighbor Problem
Workload Isolation
High Availability Failover
Orm Query Optimization
Autovacuum Tuning
Rate Limiting
Load Shedding
Read Replica Scaling
Azure Cosmos Db
Oltp Anti-patterns
Write Amplification