The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →A single PostgreSQL server can be extended well beyond its first deployment without moving to a different database. The difficulty is that “outgrown” describes several different problems, and each one has a remedy that works well for one problem and does little for another. Name the constraint first, then choose the PostgreSQL feature that addresses it. Partitioning, replication, logical replication, parallel query, and distributed PostgreSQL such as Citus are not interchangeable substitutes for one another.
Name the bottleneck before choosing an architecture
Most teams that feel they have outgrown Postgres are describing a symptom. The fix depends on which resource is actually saturated. Work through these questions in order, using measurements rather than impressions.
- Slow specific queries. A few statements dominate CPU or I/O. Run
EXPLAIN (ANALYZE, BUFFERS)on them and look for sequential scans over large tables, misestimated row counts, and sorts or hash operations spilling to disk. Missing or unsuitable indexes and query shape are the most common causes, and they are the cheapest to fix. - A very large table with time- or key-bounded access. Most reads target recent rows, a tenant, or a date range, while the table keeps growing. This points toward declarative partitioning.
- Too many concurrent connections. Latency rises as sessions compete for CPU and memory. This is a connection-management problem before it is a data-volume problem.
- Read-heavy traffic. The primary spends its capacity serving reports or dashboards that could run against a copy of the data.
- Availability requirements. A single node failure causes unacceptable downtime. This calls for standby servers and a failover design, which is a different goal from scaling.
- Write throughput or storage beyond one machine. Sustained write volume or data size exceeds what one node can absorb even after tuning. Only this case points toward distributing data across nodes.
Measure the constraint over a representative period, including peak load and maintenance windows, before committing to a topology. A snapshot taken on a quiet afternoon rarely shows what fails on the last business day of the month.
Partitioning divides one table; it does not add a second writer
Declarative partitioning in PostgreSQL 18 splits one logical table into smaller physical tables that live in the same database cluster. The partitioned parent holds no rows itself. Each partition is an ordinary table with its own bounds, and inserts are routed to the matching partition automatically.
#1 Best Overall
When partitioning helps
Partitioning helps when queries touch a small subset of partitions, because the planner can prune the rest, and when operations such as bulk deletion are easier to perform one partition at a time. A common pattern is monthly range partitions on an event timestamp, where expired months are removed by detaching or dropping a partition rather than running a large DELETE.
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
created_at timestamptz NOT NULL,
payload jsonb
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_10 PARTITION OF events
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');Where partitioning does not help
Partitioning does not distribute writes across machines. Every partition still runs on the same server, so it cannot relieve a node that is out of CPU, memory, or disk. It also adds planning cost: when many partitions remain relevant to a query, planning time and memory use can rise, and the PostgreSQL documentation explicitly cautions against assuming that more partitions are always better. The partition key should match how the data is queried and retired, and it should be chosen before the table is large, because converting a populated table later is expensive.
Replicas add availability and read capacity, not automatic write scaling
PostgreSQL’s high-availability documentation describes two broad goals. One is letting a second server take over if the primary fails. The other is letting several servers serve the same data. Both are useful, and neither is the same as sharding. A standby that replays the primary’s write-ahead log does not take on writes for the cluster as a whole.
Rank #2
Streaming standbys for failover and read traffic
A physical standby receives the primary’s changes and can serve read-only queries when it is configured as a hot standby. Read-only reporting, search, and dashboard traffic can move there, which frees primary capacity for writes. All writes still go to the primary.
Recommended Free Tools
Choosing a synchronization mode
Synchronization mode is the central trade-off. With asynchronous replication, the primary does not wait for standbys, so commits are fast but a failover can lose the most recent transactions that had not yet reached the standby. With synchronous replication, configured through synchronous_standby_names, the primary waits for confirmation from the named standbys, which protects data at the cost of commit latency and of availability when those standbys are unreachable. Replication lag also means that a read on a standby may not reflect the latest commit. Decide which of these behaviors your application can tolerate before adding a replica for reads.
Logical replication copies selected data and changes
Logical replication works from publications and subscriptions rather than from the whole cluster’s write-ahead log. A subscription first takes a snapshot of the existing table data, then applies subsequent changes, and changes within a single subscription are applied in the publisher’s commit order. The PostgreSQL documentation lists uses such as replicating a subset of tables, consolidating data from several databases for analytics, replicating between major versions, and sharing data between databases.
Basic setup
- On the publisher, set
wal_leveltological. This requires a server restart. - Create a publication for the tables you want, for example
CREATE PUBLICATION orders_pub FOR TABLE orders;. - On the subscriber, create the matching tables, then run
CREATE SUBSCRIPTION orders_sub CONNECTION 'host=publisher dbname=shop' PUBLICATION orders_pub;. - Check that the publisher has enough replication slots and that the subscriber has enough logical replication worker capacity, as set by
max_replication_slotsandmax_logical_replication_workers.
What logical replication is not
Logical replication is a data-movement and downstream-copy mechanism. It is not a multi-writer cluster that spreads a single table’s writes across nodes automatically. Replication slots also hold back WAL cleanup on the publisher while a subscriber is behind or disconnected, so monitor slot lag to avoid filling the publisher’s disk.
Parallel query speeds some reads and consumes more resources
Parallel query lets the planner split eligible work among worker processes. It can shorten long scans and aggregations, but it is conditional. The planner does not generate parallel plans for statements that perform writes or row locking, and operations classified as parallel-unsafe disable parallel execution for that statement.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteEach worker is a separate process. The PostgreSQL resource-consumption documentation illustrates the cost: a query using four workers may use up to five times the CPU, memory, and I/O of the same query run without workers. On a server already handling many concurrent sessions, raising parallelism can slow everything else down. Treat worker settings as a concurrency parameter to tune against measured load, not as a switch that multiplies throughput.
Distributed PostgreSQL spreads tables across nodes
Citus is an extension that turns a cluster of PostgreSQL nodes into one distributed database. Its project documentation describes distributed tables that are sharded across worker nodes, reference tables replicated to every node, and a distributed query engine that routes or parallelizes queries across the cluster. Microsoft’s Learn documentation for Citus, including its FAQ labelled for Citus 14, covers the same model for managed deployments.
What must be true for distribution to pay off
- The main tables share a distribution column, and most joins and filters include it. Queries that cannot be routed to one shard must touch many of them.
- Primary keys, unique constraints, and foreign keys are checked against the distribution column. Designs that assume a single global unique index often need changes.
- Reference data that every node joins against is small enough to replicate.
- Cross-node transactions and joins are acceptable in latency and complexity terms.
Confirm that the PostgreSQL major version and the Citus release you plan to run are a supported pair. Version-specific behavior and feature availability should be checked against the release you deploy, not assumed from general descriptions. Distribution is an architectural commitment: moving a schema onto it typically requires application changes, and it is much harder to reverse than adding an index or a partition.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Choosing between the options
| Measured constraint | First option to evaluate | Application or schema change | Main trade-off |
|---|---|---|---|
| Expensive or poorly planned queries | Indexes, query and schema changes, eligible parallel query | Usually limited to the affected queries | Gains are query-specific; parallel workers add concurrent load |
| Large table with time- or key-bounded access and retention | Declarative partitioning | Partition key chosen into the schema; queries should filter on it | Does not add write capacity; too many relevant partitions raise planning cost |
| Failover or read capacity | Streaming standbys with a failover design | Read-only routing for reporting traffic; handling of replica lag | Synchronous mode adds commit latency; asynchronous mode can lose recent commits on failover |
| A subset of data or a downstream copy | Logical replication | Subscriber schema must match published tables | Slot and worker configuration; not a general write-scaling layer |
| Writes or storage beyond one node, with distributable data and queries | Distributed PostgreSQL such as Citus | Distribution column, key and constraint redesign, query routing | Cross-node operations and a harder-to-reverse architecture |
| Operations burden rather than an engine limit | A managed PostgreSQL service | Depends on the provider’s tooling and extension support | Provider limits, features, and costs must be checked directly; not assessed here |
When comparing real options, evaluate five things: which bottleneck each one addresses, whether it changes application or schema assumptions, how it behaves for consistency, lag, and failover, how much operational complexity it adds, and whether the extensions and PostgreSQL features your application depends on still work. Ranking products without a workload test tends to produce the wrong answer.
Free tools Windows power users keep installed
One-click scans. No signup required.
Managed services change operations, not the design questions
A managed PostgreSQL service can remove work such as patching, backups, and failover orchestration. It does not remove the need to identify the bottleneck, and it does not guarantee that a distributed extension or a particular scaling feature is available. Verify the provider’s supported PostgreSQL versions, extensions, limits, and pricing directly with the provider before making it part of the plan.
Read the hard limits as ceilings, not targets
The PostgreSQL 18 limits reference states that database size is unlimited as a hard limit, while warning that practical performance and available disk can become constraints much earlier. The maximum size of a single relation is 32 TB with the default 8 KB block size. These are upper bounds that the engine enforces, not recommended operating points.
No universal row count or request rate justifies leaving a single node. The point at which one server stops being sufficient depends on schema, hardware, query mix, concurrency, and recovery requirements. Build a test that replays representative queries and write volume at expected peak, then measure latency, replication lag, and recovery time against your own targets.
When evaluating any option, check the version. Partitioning, replication, parallel query, and extension packaging have changed across PostgreSQL releases, and the behavior described here is taken from PostgreSQL 18 documentation unless noted otherwise.
The Bottom Line
You can grow well past a single PostgreSQL node without leaving PostgreSQL, but the remedy must match the measured bottleneck. Tune queries and indexes first, use partitioning to manage large tables, add standbys for availability and read traffic, use logical replication to copy selected data, and reserve distributed PostgreSQL such as Citus for workloads whose writes and queries can actually be distributed.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
