How to Scale PostgreSQL: Lessons from Systems That Never Sleep

The Database Is Where Pressure Becomes Visible

At two in the morning, when an API that normally responds in 80 milliseconds starts taking 4 seconds, PostgreSQL is usually the first system blamed. The database dashboard is red, CPU utilisation is climbing, active connections are approaching the configured limit, and several queries are waiting behind locks. From the incident channel, the conclusion looks obvious: PostgreSQL cannot handle the traffic. Someone proposes a larger instance, another engineer suggests adding replicas, and a third begins researching how difficult it would be to shard the largest tables.

Sometimes the database genuinely has reached its limit. More often, it is absorbing the consequences of decisions made elsewhere: an endpoint performing twelve queries instead of two, an ORM loading relationships nobody uses, a worker retrying failed jobs without backoff, or a cache failure releasing thousands of identical requests at once. PostgreSQL is not necessarily the origin of the problem. It is simply the place where the accumulated pressure becomes measurable.

This distinction matters because scaling the wrong layer is expensive. A larger database can hide an inefficient access pattern for a while, but it also increases the amount of traffic the application can generate before the next failure. A read replica can reduce pressure on the primary, yet it does nothing for a transaction that updates the same contested row hundreds of times per second. Sharding can distribute data, but it can also convert a performance problem into a permanent architectural constraint.

Before scaling PostgreSQL, we need to understand what is actually being scaled.

A Database Does Not Receive “Traffic”

Teams often describe database growth in terms of application traffic: ten thousand users became one hundred thousand users, or API requests increased from five hundred to five thousand per second. Those numbers matter, but PostgreSQL never sees users or HTTP requests. It sees statements, transactions, locks, tuple versions, index lookups, sequential scans, temporary files, WAL records, connections, and network round trips.

Two applications receiving the same number of requests can create radically different database workloads. One may serve each request with a single indexed lookup and a cache hit. Another may execute a dozen queries, join several large tables, write an audit record, update a session timestamp, and retry the entire operation when the client times out. From the product dashboard, both systems processed one request. From PostgreSQL’s perspective, they are not remotely equivalent.

A useful approximation is:

Database demand
=
Request rate
× Queries per request
× Average query cost
× Amplification factors


The amplification factors are where production systems become interesting. They include retries, cache misses, replication, write amplification, lock contention, background jobs, backfills, analytics queries, and redundant application behaviour. A small increase in user traffic can produce a much larger increase in database demand when several of these factors move together.

Consider an API endpoint that receives 1,000 requests per second and executes four queries per request. Under normal conditions, 90 percent of requests are served from an application cache, leaving PostgreSQL with approximately 400 queries per second. If the cache becomes unavailable, the database does not receive a 10 percent increase in load. It receives roughly ten times its normal query volume, often while the application is also retrying requests that have started timing out.

The failure can develop like this:

This feedback loop explains why database incidents often accelerate instead of degrading gradually. Rising latency keeps transactions and connections open for longer. Longer connection occupancy reduces available capacity. Requests then wait for connections, hit application timeouts, and retry, producing more work for a database that is already behind. The database may have handled the original traffic comfortably, but it cannot handle the traffic plus the system’s reaction to its own slowdown.

Why PostgreSQL Is Blamed Too Early

PostgreSQL sits near the bottom of the request path, which makes it a natural collection point for upstream mistakes. When an application server is inefficient, horizontal scaling may add more instances and temporarily restore capacity. Those new instances eventually create more database connections and issue more queries. The application tier appears healthy because it has been scaled out, while the database becomes the shared resource every instance is competing to use.

This is especially common in systems built around stateless services. Stateless application servers are easy to replicate, so teams become accustomed to solving load problems by adding containers. PostgreSQL does not scale in the same way. Adding another database node requires deciding whether it will accept writes, serve stale reads, participate in synchronous replication, or own a specific partition of the data. Each choice introduces correctness and operational consequences that do not exist when adding another API container.

The result is a misleading comparison. The application tier appears elastic because much of its state and contention have been pushed into PostgreSQL. This is why building scalable web applications requires more than horizontally scaling the application layer. The database, cache, queues, background workers, and external integrations must all operate within explicit capacity boundaries. The database appears inflexible because it is coordinating the consistency guarantees the rest of the system depends on.

OpenAI’s PostgreSQL architecture provides a useful example of how far a carefully managed relational database can be pushed. In January 2026, OpenAI described operating a single PostgreSQL primary with nearly 50 geographically distributed read replicas, serving millions of queries per second for a predominantly read-heavy workload. Its PostgreSQL load had grown by more than ten times in the preceding year, yet the team continued using an unsharded primary by aggressively reducing writes, moving reads to replicas, optimising expensive queries, pooling connections, isolating workloads, and controlling overload.

The interesting lesson is not that every company should reproduce that architecture. Most should not. The lesson is that PostgreSQL’s practical ceiling is determined as much by workload design and operational discipline as by raw instance capacity. A database handling millions of carefully controlled indexed reads may be healthier than one processing a few thousand unpredictable joins, lock-heavy updates, and unbounded background jobs.

The Different Meanings of “The Database Is Slow”

When engineers say PostgreSQL is slow, they may be describing several unrelated conditions. CPU saturation is different from storage latency. Lock contention is different from connection exhaustion. Replica lag is different from an overloaded primary. A query that spends three seconds waiting for a connection has a different remedy from one that spends three seconds executing a sequential scan.

A production database usually encounters pressure through one or more of these paths:

Compute pressure Expensive joins, sorting, aggregation, expression evaluation
I/O pressure Large scans, poor cache locality, index churn, temporary files
Concurrency pressure Lock waits, hot rows, long transactions, conflicting updates
Connection pressure Too many clients, idle sessions, connection storms
Write pressure WAL generation, checkpoints, index maintenance, dead tuples
Maintenance pressure Vacuum lag, table bloat, stale statistics
Network pressure Cross-region queries, large results, replica WAL delivery


Treating all of them as a generic capacity problem leads to generic solutions, usually a larger machine. Vertical scaling is valuable and often sensible, especially early in a product’s life. It creates operational headroom without forcing the team to introduce distributed coordination. But it should be a deliberate capacity decision, not a substitute for identifying whether PostgreSQL is computing too much, waiting too long, or receiving work that should never have reached it.

PostgreSQL’s multiversion concurrency control model also means that write pressure behaves differently from read pressure. An update creates a new row version rather than modifying the existing tuple in place, which contributes to WAL generation, index maintenance, dead tuples, vacuum work, and eventually table or index bloat. OpenAI identified this write amplification as one reason to migrate shardable, write-heavy workloads away from its primary PostgreSQL deployment while retaining PostgreSQL for workloads that remained strongly read-oriented.

This does not make PostgreSQL unsuitable for writes. It means the shape of those writes matters. Updating ten million independent rows is not the same as repeatedly updating one account balance. Appending immutable events is not the same as rewriting large JSON documents. A steady stream of small transactions is not the same as a feature launch that creates a sudden write storm.

Start With the Shape of the Workload

The first serious scaling exercise is therefore not selecting a larger database instance. It is constructing an accurate model of the work PostgreSQL performs on behalf of the product.

For each important request path, we want to know how many queries it executes, which queries run inside transactions, how many rows they inspect, how many rows they return, and whether they acquire locks. We also need to know what happens when a dependency fails. Does the application retry? Does it bypass the cache? Does it open a new connection? Does a failed job return immediately to the queue?

This investigation often reveals that database demand is not evenly distributed across the product. A handful of query patterns usually account for a disproportionate share of CPU time, I/O, lock waits, or total execution time. PostgreSQL may be processing thousands of distinct query shapes, but only a small number determine whether the system remains stable during peak traffic.

That is where experienced teams begin. They do not ask, “How do we scale PostgreSQL?” as though scaling were a single mechanism. They ask which workload is consuming the limiting resource, why the application is generating that workload, and what must remain correct while they change it.

Only after those questions are answered does scaling become an architectural decision rather than an expensive guess.

Recovering Capacity Before Adding Infrastructure

Once a team has accepted that “PostgreSQL is slow” is not a diagnosis, the next step is to replace intuition with evidence. This sounds obvious, yet production incidents have a way of pushing teams toward whatever change can be made fastest. An index is added because a query looks suspicious. The connection limit is doubled because requests are waiting. A larger instance is provisioned because CPU is high. Each action may improve the graph temporarily, but without knowing which resource is constrained and which workload is consuming it, the team is changing the system without understanding it.

The most valuable scaling work usually happens before the architecture changes. It comes from reducing the amount of work performed for each product action, shortening the time resources remain occupied, and preventing abnormal traffic from overwhelming normal traffic. A database with disciplined workloads often has far more capacity than its current dashboards suggest.

Begin With the Queries That Consume the System

Application logs tend to highlight individual slow requests. Database scaling requires a broader view because a query does not need to be individually slow to become expensive. A query taking 20 milliseconds may look harmless until it runs 30,000 times per minute. Another query may take two seconds but execute only a few times each day. The first is often the more important optimization target.

PostgreSQL’s pg_stat_statements extension is useful because it aggregates planning and execution statistics by normalized query shape. Instead of inspecting isolated statements, we can rank queries by total execution time, call frequency, average latency, rows returned, temporary block usage, and WAL generation. PostgreSQL also exposes activity, table, index, replication, WAL, and I/O statistics through views such as pg_stat_activity, pg_stat_user_tables, pg_stat_user_indexes, and pg_stat_io. These views are most useful when combined with host-level CPU, memory, storage, and network metrics rather than interpreted alone.

A practical first query might look like this:

SELECT
queryid,
calls,
total_exec_time,
mean_exec_time,
rows,
shared_blks_hit,
shared_blks_read,
temp_blks_written,
wal_bytes,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;


This list rarely produces a complete explanation by itself, but it narrows the investigation. A query near the top may be expensive because each execution is slow, because it runs too frequently, or because both are true. Those cases require different changes. A slow reporting query might need a better access path or a separate analytical workflow. A fast query called millions of times may need caching, batching, or removal from a request path altogether.

The distinction between latency and cumulative cost is central:

Query A: 1,000 ms × 100 calls = 100 seconds of database time
Query B: 15 ms × 1,000,000 calls = 15,000 seconds of database time


Production optimization should follow total resource consumption and operational risk, not whichever query appears most dramatic in a trace.

Read the Execution Plan, Not the SQL’s Appearance

Once a costly query has been identified, the next question is how PostgreSQL actually executes it. SQL is declarative: it describes the result we want, not the physical work required to obtain it. A query that looks compact may scan millions of rows, spill a sort to disk, or repeatedly execute a nested operation. A long query may be efficient because every join is selective and supported by appropriate indexes.

EXPLAIN shows the planner’s chosen execution strategy, while EXPLAIN ANALYZE executes the statement and records what happened. The difference between estimated and actual row counts is particularly important. When PostgreSQL estimates that a condition will return 50 rows but it actually returns 500,000, the planner may choose a nested loop or join order that becomes extremely expensive at runtime. PostgreSQL’s documentation describes EXPLAIN as the primary way to inspect plan nodes, estimated costs, row estimates, and actual execution behavior.

For production investigation, a common form is:

EXPLAIN (
ANALYZE,
BUFFERS,
WAL,
VERBOSE,
FORMAT TEXT
)
SELECT
o.id,
o.status,
c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.store_id = 42
AND o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 100;


ANALYZE must be used carefully because the query really runs. Executing an expensive write statement or an unbounded analytical query against a busy production primary can make an existing incident worse. When the risk is unclear, begin with plain EXPLAIN, reproduce the workload against realistic data in a safe environment, or wrap writes in a transaction that can be rolled back.

While reading a plan, I usually look for a small set of signals: scans touching far more rows than they return, filters that discard most of the scanned data, large differences between estimated and actual rows, nested loops with high iteration counts, sorts or hash operations spilling to temporary storage, and repeated index lookups that could have been fetched together. None of these automatically proves the plan is wrong. They indicate where the database is doing more work than the product result appears to justify.

Indexes Must Match Access Patterns

The usual response to a sequential scan is to add an index. That is sometimes correct, but indexing every filtered column is not a scaling strategy. Each index consumes storage, increases cache pressure, and adds work to inserts, updates, deletes, vacuuming, replication, and schema maintenance. An index should exist because an important access pattern benefits from it enough to justify its ongoing write and operational cost.

Suppose the application repeatedly retrieves the latest pending orders for a store:

SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE store_id = $1
AND status = 'pending'
ORDER BY created_at DESC
LIMIT 100;


Three independent indexes on store_id, status, and created_at may not be as useful as one index designed around the query:

CREATE INDEX CONCURRENTLY idx_orders_store_pending_created
ON orders (store_id, created_at DESC)
INCLUDE (customer_id, total_amount)
WHERE status = 'pending';


The partial predicate keeps completed and cancelled orders out of the index, while the column order supports filtering by store and reading rows in the desired sequence. Included columns may allow PostgreSQL to satisfy more of the request from the index when visibility conditions permit an index-only scan. PostgreSQL documents partial indexes as indexes built over a subset of table rows, which can reduce index size and avoid maintaining entries that are irrelevant to the targeted workload.

This index is not universally better. If pending orders represent most of the table, the partial index may provide little benefit. If queries also filter heavily by another field, the column order may need to change. If the table receives constant writes, the index’s maintenance cost must be measured. Index design is an empirical process tied to real query patterns and data distribution.

The same reasoning applies to ORM-generated SQL. A repository method that appears to retrieve one entity may generate multiple joins, fetch large text or JSON columns, and load several related collections. OpenAI reported that some of its expensive queries came from ORM-generated multi-table joins, including a 12-table join whose traffic spikes contributed to severe incidents. Its response included reviewing generated SQL and, where appropriate, breaking complex database joins into simpler operations handled by the application.

Moving joins into application code is not automatically an optimization. It can introduce extra network round trips, inconsistent reads, larger intermediate results, and N+1 query patterns. The useful principle is narrower: do not allow abstractions to hide the cost of database access. The SQL emitted by an ORM is part of the production system and should be reviewed with the same care as handwritten code.

Remove Work Before Making Work Faster

An optimised query is still more expensive than a query that no longer needs to run. Before tuning indexes or memory settings, ask why the request reaches PostgreSQL at all.

Managed platforms do not remove these responsibilities. Teams building with Supabase and Next.js still need to understand PostgreSQL access patterns, generated queries, connection usage, and the cost of placing too much business logic inside the request path.

Some database traffic exists only because an application writes too eagerly. A service updates last_seen_at on every request, rewrites a large JSON document when one field changes, persists transient progress every second, or stores duplicate representations that must remain synchronised. Other traffic comes from repeatedly reading values that change infrequently, such as feature configuration, product metadata, permissions, or organisation settings.

The first optimisation may be to change the product’s consistency requirement. A last_seen_at value rarely needs second-level precision. It could be updated only when the previous value is older than ten minutes:

UPDATE users
SET last_seen_at = now()
WHERE id = $1
AND last_seen_at < now() - interval '10 minutes';


Even here, the application can often avoid sending the statement if it already knows a recent update occurred. Similar techniques include coalescing counter updates, appending events instead of repeatedly rewriting aggregates, batching background writes, caching stable reads, and computing expensive summaries asynchronously.

OpenAI describes using redundant-write removal, lazy writes, rate-limited backfills, caching, and migration of shardable write-heavy workloads to reduce pressure on its single PostgreSQL writer. The important sequencing is that infrastructure expansion came alongside aggressive demand reduction, not instead of it.

Connection Pools Are Admission-Control Systems

Connection pooling is commonly introduced as a performance optimisation because opening database connections is relatively expensive. At scale, its more important role is controlling concurrency.

PostgreSQL uses a process-oriented architecture in which client sessions consume server resources. Allowing every application instance to open a large pool does not create database capacity. It allows more work to compete for the same CPU, memory, locks, and storage. When a deployment automatically scales from 20 application instances to 100, a pool of 30 connections per instance can increase potential database concurrency from 600 to 3,000 connections within minutes.

The pool should be sized from the database backward, not from each service forward:

Usable database connections
=
Database connection budget
− Administrative reserve
− Migration and worker reserve
− Replication and operational reserve

Per-service pool allocation
=
Usable connections
× Service workload share
÷ Maximum service instance count


This is a policy decision, not a formula that produces a perfect number. The objective is to maintain bounded concurrency and preserve enough reserved capacity to diagnose and recover the database during an incident.

A saturated pool is often healthier than an unconstrained database. Requests waiting briefly in an application or pooler queue can be timed out, prioritized, rejected, or retried with backoff. Once too much work enters PostgreSQL, every query may slow down, including health checks, incident tooling, and the requests required for recovery.

Long Transactions Turn Small Problems Into System Problems

A transaction should remain open only for the period in which database consistency is required. It should not include calls to external APIs, user interaction, large file processing, or arbitrary application work. Long transactions retain snapshots, hold locks, occupy connections, interfere with cleanup, and increase the amount of work that must be discarded when a failure occurs.

One particularly dangerous state is idle in transaction: the client has opened a transaction and then stopped issuing queries without committing or rolling back. PostgreSQL exposes transaction and query start times through pg_stat_activity, making it possible to identify sessions that have remained open unexpectedly. PostgreSQL also provides settings such as statement_timeout, lock_timeout, and idle_in_transaction_session_timeout to bound execution, lock waiting, and inactive open transactions.

A useful operational query is:

SELECT
pid,
usename,
application_name,
state,
now() - xact_start AS transaction_age,
wait_event_type,
wait_event,
query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;


Timeout values should reflect workload classes rather than one global guess. A customer-facing API query may need a strict limit, while a controlled maintenance job may legitimately run longer. The mistake is allowing every query to run indefinitely and hoping that application timeouts will clean things up. An HTTP request can time out while its database query continues consuming resources unless cancellation is propagated correctly.

Lock contention requires similar precision. When many transactions update the same row, adding CPU does not necessarily improve throughput because the operations must still serialize. The right fix might be changing the data model, distributing updates across multiple rows, using append-only events, acquiring locks in a consistent order, or removing nonessential work from the transaction. Scaling hardware cannot parallelize a business invariant that requires all writers to wait for one another.

Vacuum Is Part of Write Capacity

PostgreSQL’s MVCC model allows readers and writers to proceed concurrently by maintaining multiple row versions. Those obsolete versions must eventually be reclaimed, and table statistics must remain accurate enough for the planner to choose sensible execution plans. Vacuuming and analyzing are therefore not background housekeeping that can be ignored until convenient. They are part of the database’s ability to sustain writes.

PostgreSQL’s routine vacuuming documentation explains that VACUUM reclaims or makes reusable the storage occupied by updated and deleted rows, while ANALYZE collects statistics used by the query planner. Autovacuum automates both processes, but its default behavior may need adjustment for tables with unusually high update rates, large row sizes, or strict latency requirements.

A table can accumulate dead tuples faster than autovacuum clears them, particularly during large backfills, bulk updates, or workloads that repeatedly modify the same records. The resulting bloat increases the amount of data that scans and indexes must traverse. Meanwhile, long-running transactions may prevent old row versions from being removed because they could still be visible to an older snapshot.

The correct response is not simply to run VACUUM FULL, which requires stronger locking and rewrites the table. Teams should first understand why cleanup is falling behind, whether transactions are preventing progress, and whether table-specific autovacuum thresholds or cost settings need adjustment. Backfills should also be rate-limited, divided into small batches, and designed to pause when database latency or replication lag rises.

The System Must Be Able to Refuse Work

A database operating close to full utilization has little ability to absorb abnormal events. The aim is not to maximize average utilization. It is to maintain enough headroom for failovers, traffic spikes, cache failures, deployment mistakes, and maintenance activity.

This requires overload controls outside PostgreSQL. Expensive endpoints may need independent concurrency limits. Background queues should use bounded workers rather than opening more database sessions whenever the backlog grows. Retries need exponential backoff and jitter. Nonessential work should be shed before core reads and writes become unavailable. In severe cases, a known harmful query pattern may need to be blocked temporarily while the underlying application bug is fixed.

OpenAI reports applying rate limits at several layers, including the application, connection pooler, proxy, and query level. It also added the ability to block specific query digests during overload. This is less about raw performance than preserving control: a system that can reject some work can continue serving its most important work and recover faster.

By this stage, many teams discover that PostgreSQL had substantial unused capacity hidden behind inefficient queries, unnecessary writes, excessive connections, long transactions, and uncontrolled retries. Removing those costs postpones architectural complexity and creates a clearer signal when the database truly approaches its structural limits.

Eventually, however, optimization stops being enough. The primary may still face more writes than one machine can sustain, global users may need lower read latency, analytical workloads may compete with transactional traffic, or the blast radius of one shared cluster may become unacceptable. At that point, the question changes from how to make one PostgreSQL deployment work harder to how the surrounding architecture should distribute responsibility.

How to Scale PostgreSQL: Lessons From Building Systems That Never Sleep

When One Database Is No Longer Enough

After query optimization, write reduction, connection control, and overload protection, the database should behave differently. Latency becomes more predictable, incidents become easier to diagnose, and capacity planning is based on measurable resource limits rather than anxiety. Only then can a team see whether the primary is approaching a genuine architectural boundary.

That boundary is rarely expressed as a single number. It may appear as sustained CPU pressure during ordinary traffic, insufficient I/O capacity, unacceptable write latency, replica lag that grows faster than it can recover, or maintenance operations that no longer fit within safe operational windows. It may also be organizational: too many unrelated products depend on one cluster, and a mistake in a low-priority service can affect the company’s most important transaction path.

At this stage, scaling PostgreSQL becomes less about making individual queries faster and more about deciding where different kinds of work should run.

Vertical Scaling Is Usually the First Architectural Move

For many growing products, the most sensible database scaling decision is still a larger machine. Increasing CPU, memory, storage throughput, or network capacity preserves the existing transactional model and avoids introducing distributed consistency into the application.

Vertical scaling is sometimes dismissed as unsophisticated, but simplicity has operational value. The same restraint applies to application architecture. Moving from a monolith to distributed services does not automatically reduce database pressure and may increase it through duplicated reads, retries, network calls, and independently scaled connection pools. We explored this wider trade-off in our guide to monoliths versus microservices for MVPs.
A single primary preserves one authoritative write order, supports transactions across the entire dataset, and keeps foreign keys, unique constraints, and joins within one database. Engineers can reason about failures without also reasoning about data placement.

The limitation is that vertical capacity is finite. Larger instances also become more expensive, maintenance can take longer, and recovery may involve moving a larger volume of data. A team should therefore use vertical scaling to buy time for disciplined engineering, not as permission to ignore workload growth.

The useful question is not whether scaling up is elegant. It is whether the additional capacity costs less than the engineering and operational burden of distributing the database. For most startups, the answer remains yes for much longer than expected.

Read Replicas Scale Reads, but They Change Semantics

When reads dominate the workload, replicas provide the most natural path beyond one database server. PostgreSQL physical streaming replication sends write-ahead log records from the primary to standby servers, which replay those changes and can serve read-only queries when configured as hot standbys. Streaming replication is asynchronous by default, so there is normally a delay between a transaction committing on the primary and becoming visible on a replica.

That delay may be tiny under normal conditions, but application correctness cannot depend on it always being tiny. Network problems, bursts of write activity, slow storage, long-running replica queries, or insufficient replica capacity can increase lag. Once reads are routed to replicas, the application must define which operations tolerate stale data.

A product catalogue can usually tolerate a short delay. An account balance shown immediately after a transfer may not. A user who changes a password and then attempts to log in again should not be rejected because the authentication read reached a replica that has not replayed the update. The same issue appears after creating an order, changing a permission, publishing content, or updating inventory.

This means replica routing should be based on consistency requirements rather than HTTP methods or repository conventions. “All reads go to replicas” is simple, but it is rarely correct.

A more realistic routing model looks like this:

Some systems implement temporary read affinity after a write, routing subsequent requests from the same user or workflow to the primary for a short period. Others pass a commit position through the request context and wait until a replica has replayed at least that position. Many products use a simpler policy: transaction-sensitive reads remain on the primary, while explicitly identified stale-tolerant queries use replicas.

The important part is that consistency is intentional. Replica lag should not quietly become a user-facing bug.

Replicas Should Isolate Workloads, Not Merely Add Capacity

A common replica architecture places every read replica behind one load balancer and distributes queries evenly. This increases aggregate capacity, but it leaves different workloads competing with each other. A large internal report can consume memory and I/O on the same replica serving customer-facing requests. A backfill can create long queries that conflict with WAL replay. An experimental feature can destabilize the shared read fleet.

Workload-specific replicas create stronger boundaries. Customer-facing reads can use replicas optimized for low latency. Administrative tools can use a separate pool with stricter concurrency limits. Data exports, asynchronous jobs, and expensive support queries can run elsewhere. Analytical workloads should often be moved into a dedicated warehouse or analytical database rather than allowed to evolve indefinitely on transactional replicas.

OpenAI described combining geographically distributed read replicas with workload isolation, connection pooling, caching, and layered rate limiting. As of January 2026, its architecture used one Azure PostgreSQL primary and nearly 50 read replicas across multiple regions, serving millions of queries per second for a heavily read-oriented workload. The scale is unusual, but the underlying pattern is broadly useful: protect the primary, separate workload classes, and avoid allowing one query category to consume the capacity intended for another.

Geographic replicas can also reduce read latency for globally distributed users, although writes must still travel to the primary region in a single-writer architecture. Moving replicas closer to users improves only the portion of the request that can safely be served from them. It does not make a globally distributed application automatically local.

High Availability and Read Scaling Are Different Problems

A replica used for read traffic is not automatically a complete high-availability strategy. Failover requires detecting that the primary has become unavailable, promoting an appropriate standby, redirecting clients, preventing the old primary from accepting writes, and restoring redundancy afterward. PostgreSQL can promote a standby, but a reliable production process also needs orchestration, fencing, tested recovery procedures, and clear recovery objectives. PostgreSQL’s documentation notes that after failover, the system temporarily operates with only the promoted server until another standby is created or the former primary is safely reintroduced.

The choice between asynchronous and synchronous replication also reflects a business tradeoff. Asynchronous replication allows the primary to commit without waiting for a standby, which generally provides lower write latency but leaves a possible window of data loss during catastrophic failure. Synchronous replication can reduce that window by requiring confirmation from a standby, but it adds latency and can affect write availability when the required standby is unreachable.

There is no universal configuration that simultaneously maximizes durability, availability, and latency. The correct design comes from explicit recovery objectives:

RPO: How much committed data can the business lose?

RTO: How long can the database remain unavailable?

Write latency: How much replication delay can each transaction absorb?

Failure scope: Which regional or infrastructure failures must the system survive?


A payment ledger and a social activity feed may reasonably produce different answers. Treating every table as though it has identical durability requirements either makes the system unnecessarily expensive or leaves important data insufficiently protected.

Partitioning Is Not Sharding

Large tables often lead teams to discuss partitioning and sharding as though they are interchangeable. They solve different problems.

PostgreSQL table partitioning divides one logical table into child tables according to a range, list, or hash key. Queries can avoid irrelevant partitions when the planner can determine which partitions contain the requested rows. Partitions can also simplify retention by allowing old data to be detached or dropped without deleting rows individually. The database is still one PostgreSQL cluster, however, and writes still pass through the same primary.

Partitioning can improve manageability and certain query patterns, especially for time-oriented data such as events, audit logs, metrics, and historical orders. It does not automatically make every query faster. A poorly chosen partition key can create too many partitions, prevent effective pruning, complicate uniqueness constraints, and increase planning or maintenance overhead.

Sharding distributes data across independent database nodes. A tenant, account, user, or another routing key determines which shard owns a record. Unlike table partitioning, sharding can increase total write capacity because different primaries accept writes for different subsets of the data.

It also changes the application fundamentally.

Cross-shard joins become application workflows or offline computations. Globally unique constraints become harder to enforce. Transactions spanning shards require redesign, coordination, or acceptance of weaker guarantees. Rebalancing data can become a long-running production operation. Operational tooling must understand shard ownership, and incident response becomes more complex because the same logical product now depends on multiple database primaries.

For these reasons, sharding should solve a demonstrated constraint. It should not be introduced merely because the product might become large one day.

The Shard Key Becomes Part of the Product Architecture

A good shard key places data that is frequently read or modified together on the same shard. In a business-to-business SaaS product, organization_id may provide a natural boundary. In a consumer system, user_id may work if most workflows are user-scoped. Geographic sharding may help when data residency and regional latency are more important than global transactions.

No shard key is perfect. An organization-based design struggles when one customer becomes much larger than the others. User-based sharding complicates shared resources and social relationships. Geographic sharding becomes difficult when users collaborate across regions. Time-based sharding is useful for immutable historical data but awkward for entities that remain active indefinitely.

The shard key should therefore be selected from observed access patterns, not from whichever identifier appears in every table. Before committing to it, teams should model their most important reads, writes, transactions, constraints, migrations, and support operations. They should also consider how a large tenant will be split, how shards will be rebalanced, and how requests behave when the routing layer has incomplete or stale metadata.

OpenAI chose not to shard its existing PostgreSQL deployment in the architecture it described, partly because doing so would require changes across hundreds of application endpoints and could take months or years. Instead, it migrated shardable, write-heavy workloads to other sharded systems, prevented new tables from being added to the existing deployment, and retained PostgreSQL for workloads that continued to fit its read-heavy architecture.

That is a useful alternative to sharding everything. A system can be decomposed by workload rather than forcing one database technology to serve every access pattern.

Production Architectures Usually Evolve by Extraction

Database architectures rarely move directly from one PostgreSQL instance to a perfectly sharded platform. They evolve by removing specific pressures from the original database. A practical example is a marketplace that keeps commerce responsibilities inside Shopify while moving vendor operations, permissions, synchronisation, and platform-specific workflows into a custom Node.js and PostgreSQL backend. Our breakdown of a hybrid marketplace architecture shows how clear system boundaries can delay unnecessary infrastructure complexity.

Search may move to a search engine because relevance queries no longer belong in transactional SQL. Event streams may move to an append-oriented platform. Analytics may move to a warehouse. Large objects may move to object storage. A high-volume notification system may receive its own datastore. A naturally tenant-scoped domain may be assigned to a separate PostgreSQL cluster.

Logical replication can support some of these transitions by publishing changes from selected tables to subscribers. PostgreSQL’s logical replication uses a publish-and-subscribe model and can replicate subsets of a database, which makes it useful for migrations, version upgrades, analytical copies, and selective data distribution. It still requires careful handling of schema changes, replication slots, conflicts, and initial synchronization.

This extraction model reduces risk because each change addresses an identifiable workload. It also preserves PostgreSQL where its transactional guarantees and relational model continue to provide value.

What Startups Should Take From Large-Scale Architectures

The wrong lesson from an architecture serving hundreds of millions of users is to copy its final shape. For an early-stage product, the objective is not to reproduce the architecture of a company serving hundreds of millions of users. It is to select an architecture that can validate the product while preserving a reasonable path forward, which is also central to our practical guide on how to build an MVP. Large systems contain solutions to constraints that most products do not have, along with operational machinery that smaller teams cannot justify.

The useful lesson is the order in which mature teams make decisions.

Begin with one well-operated primary. Measure query behaviour before introducing topology. Fix access patterns before adding hardware. Bound connections and retries before traffic spikes force the database to perform admission control through failure. Add replicas only after identifying which reads tolerate staleness. Isolate workloads before they can interfere with one another. Extract analytical, search, or write-heavy domains when their behaviour no longer matches the transactional core. Shard only when a measurable write or data-placement limit remains after those steps.

I've seen teams underestimate PostgreSQL because their first serious load test was also the first time they inspected generated SQL, configured pooling, or placed limits on background workers. I have also seen teams keep a database alive through increasingly fragile tuning when the workload had clearly outgrown a single-writer design. Both mistakes come from treating scaling as ideology rather than diagnosis.

PostgreSQL can carry a company surprisingly far, but only when the surrounding application respects that the database is a finite concurrency system. Every query consumes a resource. Every index creates a maintenance obligation. Every retry changes the workload. Every replica introduces a consistency decision. Every shard creates a permanent boundary in the application.

The strongest database architectures are not the ones with the most nodes. They are the ones in which the team understands why each node exists, which work it is allowed to perform, how it fails, and what the application must do when it does.

That is how systems that never sleep are built: not by assuming the database will remain infinitely available, but by designing the entire system so that PostgreSQL is never asked to carry more uncertainty than it can safely coordinate.

At Beitroot, we build and modernise production platforms where database design, application architecture, and infrastructure evolve together. Explore our engineering case studies to see how these decisions are applied in real products.