These are the main options for PostgreSQL horizontal scaling:

1. Read replicas — streaming replication with PgBouncer read/write splitting
2. Analytical offload — CDC from PostgreSQL into ClickHouse® via Tinybird
3. Citus / horizontal sharding — distributed PostgreSQL across multiple nodes

PostgreSQL horizontal scaling means distributing load across multiple nodes rather than upgrading a single machine. When read throughput, analytical query volume, or write fan-out exceeds what one PostgreSQL instance can handle, horizontal approaches become necessary.

The right option depends on what is actually saturated. Read replicas solve read throughput. Analytical offload solves analytical query interference with OLTP. Citus addresses write throughput distribution — but adds significant operational complexity.

Before choosing, answer these questions:

• Is the bottleneck read throughput (queries per second from dashboards, reports, API calls), write throughput (inserts, updates overwhelming the primary), or analytical scan volume (long-running aggregations competing with OLTP)?
• Are the overloaded queries transactional (row lookups, writes) or analytical (aggregations, time-range scans)?
• Can your team operate distributed PostgreSQL infrastructure, or do you need a managed scaling path?

Three ways to implement PostgreSQL horizontal scaling

This section covers the three main approaches, with configuration and code for each.

Option 1: Read replicas — streaming replication with PgBouncer

The most common horizontal scaling path. PostgreSQL's built-in streaming replication streams WAL records from the primary to one or more standby replicas. Read traffic is routed to replicas; writes go to the primary.

How it works: enable WAL streaming on the primary, create a standby with pg_basebackup, and use PgBouncer in transaction mode to route read queries to replicas and writes to the primary.

Primary postgresql.conf settings for streaming replication:

# postgresql.conf (primary)
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
hot_standby = on

pg_hba.conf — allow replication connections:

# pg_hba.conf (primary)
host  replication  replicator  10.0.0.0/8  scram-sha-256

Create standby with pg_basebackup:

pg_basebackup -h primary-host -U replicator -D /var/lib/postgresql/data \
  --wal-method=stream --checkpoint=fast --progress

PgBouncer pgbouncer.ini for read/write splitting:

[databases]
mydb_write = host=primary-host port=5432 dbname=mydb
mydb_read  = host=replica-host port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

Route application read queries to mydb_read and writes to mydb_write. PgBouncer pools connections to each target; the application or driver is responsible for choosing the correct pool. Many ORMs and connection libraries support this routing natively.

When this fits:

• Your bottleneck is read throughput — dashboards, reports, and API queries saturating the primary
• Queries are point lookups or small scans well-suited to the Postgres execution model
• You do not need sub-second analytical aggregations over billions of rows

Trade-offs: replicas lag behind the primary by milliseconds to seconds depending on write volume. Queries on replicas may return stale data. Replicas do not reduce write pressure on the primary. Adding more replicas improves read throughput but does not help if analytical scans are the root cause.

Prerequisites: PostgreSQL 14+, PgBouncer 1.18+, network connectivity between primary and standby nodes.

Option 2: Analytical offload — CDC from PostgreSQL into ClickHouse® via Tinybird

The most impactful path when analytical queries are what's hurting PostgreSQL performance. Change Data Capture (CDC) streams row-level changes from PostgreSQL into a purpose-built analytical engine, removing the analytical scan load from the OLTP database entirely.

How it works: enable logical replication on PostgreSQL, configure a CDC tool (Debezium, Estuary Flow, or Sequin) to capture changes, stream them into Tinybird, and define SQL Pipes that serve analytical queries as low-latency REST APIs. PostgreSQL remains the OLTP source of truth; ClickHouse® handles analytics.

Enable logical replication on PostgreSQL:

-- postgresql.conf
-- wal_level = logical  (requires restart)

-- Create replication slot
SELECT pg_create_logical_replication_slot('tinybird_slot', 'pgoutput');

-- Create publication for the tables to replicate
CREATE PUBLICATION tinybird_pub FOR TABLE orders, events, users;

Debezium connector config (connects to Tinybird via Kafka):

{
  "name": "postgres-tinybird-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "postgres-host",
    "database.port": "5432",
    "database.user": "replicator",
    "database.dbname": "mydb",
    "database.server.name": "mydb",
    "plugin.name": "pgoutput",
    "slot.name": "tinybird_slot",
    "publication.name": "tinybird_pub",
    "table.include.list": "public.orders,public.events"
  }
}

Once data lands in Tinybird, define a Pipe for real-time analytics:

-- Tinybird Pipe: daily_order_summary
SELECT
  toDate(created_at) AS order_date,
  status,
  count() AS total_orders,
  sum(amount) AS total_revenue
FROM orders_ds
WHERE created_at >= today() - INTERVAL 30 DAY
GROUP BY order_date, status
ORDER BY order_date DESC

Publish the Pipe as a REST API endpoint. Application dashboards call https://api.tinybird.co/v0/pipes/daily_order_summary.json instead of querying PostgreSQL directly. PostgreSQL is freed from analytical scan load.

When this fits:

• Analytical queries (aggregations, time-range scans, reporting) are what's slowing PostgreSQL down
• You need sub-second response times on analytical queries under concurrent load
• You want the same data accessible via APIs for both BI tools and application features — user-facing analytics

Trade-offs: adds ingestion infrastructure (CDC tool + Tinybird). Data in Tinybird reflects changes with a short lag (typically seconds). Does not help with write throughput pressure on the primary. Requires defining Pipes to replicate analytical query logic.

Prerequisites: PostgreSQL 13+ with wal_level = logical, a CDC tool (Debezium + Kafka, Estuary, or Sequin), Tinybird account.

Option 3: Citus — horizontal sharding

Citus distributes PostgreSQL tables across multiple worker nodes by a distribution column. Each node owns a subset of shards. Queries hitting the distribution key are routed to the right shard; queries across all shards run in parallel across workers.

How it works: install Citus as a PostgreSQL extension, designate a coordinator node, add worker nodes, and convert large tables to distributed tables with a suitable distribution key.

Citus setup:

-- On coordinator node
CREATE EXTENSION citus;

-- Add worker nodes
SELECT citus_add_node('worker-1', 5432);
SELECT citus_add_node('worker-2', 5432);

-- Distribute a table by tenant/user ID
SELECT create_distributed_table('orders', 'tenant_id');
SELECT create_distributed_table('events', 'tenant_id');

-- Reference tables (replicated to all workers)
SELECT create_reference_table('product_catalog');

Query behavior on distributed tables:

-- Routed to a single shard (fast — uses distribution key)
SELECT count(*) FROM orders WHERE tenant_id = 42 AND created_at > now() - INTERVAL '7 days';

-- Cross-shard (parallel across all workers)
SELECT date_trunc('day', created_at) AS day, sum(amount)
FROM orders
WHERE created_at > now() - INTERVAL '30 days'
GROUP BY day ORDER BY day;

When this fits:

• Your bottleneck is write throughput and you need to distribute writes across multiple nodes
• Your data has a clear tenant or partition key that maps to Citus's distribution model
• Your team can operate distributed PostgreSQL infrastructure (shard rebalancing, distributed transactions)

Trade-offs: Citus adds significant operational complexity. Cross-shard joins and queries without the distribution key fan out to all workers and can be slower than a well-tuned single node. Distributed transactions (cross-shard writes) are constrained. Schema changes require coordination across all shards. Shard rebalancing is disruptive.

Prerequisites: Citus extension (open source or Citus Cloud / Azure Cosmos DB for PostgreSQL), PostgreSQL 14+, at least 2 worker nodes, a suitable distribution key with high cardinality.

Summary: picking the right option

Decision framework: what to choose for PostgreSQL horizontal scaling

Pick based on what is actually saturated:

• Read replicas if your primary is overloaded with read queries from dashboards, reports, or APIs — and those queries are well-indexed point lookups or small scans. Simplest path with the least operational overhead.
• Analytical offload via Tinybird if long-running analytical queries — aggregations, time-range scans, reporting — are competing with OLTP on the primary. This is the most common hidden cause of PostgreSQL slowness, and the highest-leverage fix. Tinybird provides real-time change data capture with sub-second query serving.
• Citus sharding if write throughput is the bottleneck and your data model supports a clean distribution key (e.g., tenant_id). Citus is the right choice for multi-tenant SaaS with high write fan-out per tenant. Expect significant operational investment.

Bottom line: most teams discover that analytical query interference — not write throughput — is the primary cause of PostgreSQL saturation. Offloading analytics to ClickHouse® via Tinybird unblocks the OLTP database without the complexity of distributed sharding.

What is PostgreSQL horizontal scaling and when does it matter?

PostgreSQL horizontal scaling distributes workload across multiple nodes rather than upgrading a single server. It matters when OLTP vs OLAP workloads compete for the same resources and vertical tuning no longer helps.

The signals: primary CPU above 80% during business hours, pg_stat_activity showing long-running analytical queries blocking OLTP transactions, autovacuum unable to keep up, and connection limits maxed even with PgBouncer.

Understanding which workload is the culprit determines which horizontal path to take. A database that looks slow across both transactional and analytical queries is usually suffering from one root cause — fixing it at the wrong layer adds infrastructure without solving the problem.

Schema and pipeline design

Practical schema rules for horizontal PostgreSQL scaling

Designing schemas with horizontal scaling in mind avoids costly migrations later.

Rule 1: choose a high-cardinality distribution key for Citus. The distribution column should have high cardinality (many distinct values) and appear in the WHERE clause of most queries. Low-cardinality keys create hot shards. tenant_id, user_id, or org_id are typical choices.

Rule 2: use time-based partitioning on large tables. PostgreSQL declarative partitioning (PARTITION BY RANGE) on created_at enables partition pruning and reduces the scan surface for both replicas and Citus.

Rule 3: denormalize analytical tables before CDC export. When streaming to ClickHouse® via Tinybird, pre-join related tables in a PostgreSQL materialized view or application layer before ingestion. ClickHouse® handles denormalized wide tables better than multi-table joins.

Example: CDC-ready PostgreSQL schema + ClickHouse® landing table

-- PostgreSQL: source table (OLTP) — partitioned parent
CREATE TABLE orders (
  order_id    BIGSERIAL,
  tenant_id   BIGINT NOT NULL,
  user_id     BIGINT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending',
  amount      NUMERIC(12,2) NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (order_id, created_at)
) PARTITION BY RANGE (created_at);

-- Monthly child partition
CREATE TABLE orders_2026_04 PARTITION OF orders
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- Index for OLTP point lookups (on the parent; inherited by partitions)
CREATE INDEX ON orders (tenant_id, created_at DESC);

ClickHouse® landing table in Tinybird (receives CDC events):

CREATE TABLE orders_raw (
  order_id   UInt64,
  tenant_id  UInt64,
  user_id    UInt64,
  status     LowCardinality(String),
  amount     Float64,
  created_at DateTime,
  updated_at DateTime,
  _op        LowCardinality(String)  -- 'c' create, 'u' update, 'd' delete
)
ENGINE = ReplacingMergeTree(updated_at)
PARTITION BY toYYYYMM(created_at)
ORDER BY (tenant_id, order_id)

Analytical materialized view for reporting queries:

CREATE MATERIALIZED VIEW orders_daily_mv
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(order_date)
ORDER BY (tenant_id, status, order_date)
AS SELECT
  toDate(created_at) AS order_date,
  tenant_id,
  status,
  countState() AS total_orders_state,
  sumState(amount) AS revenue_state,
  uniqState(user_id) AS unique_users_state
FROM orders_raw
GROUP BY order_date, tenant_id, status

Query with: SELECT order_date, countMerge(total_orders_state) AS orders, sumMerge(revenue_state) AS revenue, uniqMerge(unique_users_state) AS users FROM orders_daily_mv GROUP BY order_date ORDER BY order_date DESC.

Failure modes

1. Replica lag causing stale reads. Under high write load, replicas fall behind the primary by seconds or more. Applications that read their own writes may see outdated state. Mitigation: route writes and their immediate reads to the primary, or use synchronous replication for critical cases. Monitor pg_stat_replication.replay_lag.

2. Citus cross-shard query fan-out. Queries without the distribution key fan out to all workers, amplifying resource usage under load. Mitigation: review EXPLAIN output for dashboard queries and ensure they include the distribution key. Pre-aggregate with materialized views for cross-shard queries that cannot be avoided.

3. CDC slot accumulation blocking WAL cleanup. Logical replication slots hold WAL until the consumer acknowledges. If the CDC consumer falls behind or goes offline, WAL fills disk. Set max_slot_wal_keep_size (PostgreSQL 13+) and monitor pg_replication_slots.confirmed_flush_lsn.

4. Schema changes breaking CDC pipelines. Adding a column to PostgreSQL does not propagate to ClickHouse® automatically. The CDC event arrives with the new column and ClickHouse® rejects it if the schema is mismatched. Treat the ClickHouse® schema as a versioned contract and add nullable columns on both sides before deploying.

5. Citus shard rebalancing under load. Moving shards between workers causes I/O spikes and query slowdowns on affected shards. Schedule rebalance_table_shards() during low-traffic windows.

Why ClickHouse® for PostgreSQL horizontal scaling

When the bottleneck is analytical query load, ClickHouse® removes the root cause rather than distributing it. A columnar engine purpose-built for analytical workloads processes aggregations, time-range scans, and GROUP BY queries orders of magnitude faster than PostgreSQL's row-oriented storage.

ClickHouse®'s MergeTree engine family handles billions of rows with partition pruning, vectorized execution, and columnar compression. Queries that take 10–30 seconds on PostgreSQL return in milliseconds on ClickHouse®. This is the fastest database for analytics profile that analytical offload leverages.

For teams building real-time data processing pipelines, ClickHouse® also handles streaming data continuously via CDC — so the analytical engine stays current without polling PostgreSQL.

Why Tinybird is a strong fit for PostgreSQL horizontal scaling

PostgreSQL horizontal scaling via analytical offload requires three pieces: a way to stream changes out of PostgreSQL, a fast analytical engine to receive them, and a query serving layer to make results accessible to dashboards and applications.

Tinybird provides all three in one managed service. Stream PostgreSQL changes via CDC into Tinybird, define SQL Pipes over the incoming data, and publish REST API endpoints immediately. The same Pipe that powers your internal dashboard also serves your product's user-facing analytics — no separate API layer needed.

This is the real-time data ingestion pattern that removes analytical pressure from PostgreSQL without requiring distributed sharding or replica topology changes. Your PostgreSQL stays focused on OLTP; Tinybird handles analytical scale.

Next step: run EXPLAIN (ANALYZE, BUFFERS) on your three most expensive PostgreSQL queries, identify which are analytical scans rather than OLTP lookups, and model them as Tinybird Pipes against a CDC replica of those tables. Validate query latency improvement before migrating dashboard traffic.

Frequently Asked Questions (FAQs)

What is the difference between PostgreSQL horizontal scaling and vertical scaling?

Horizontal scaling distributes load across multiple nodes — read replicas, Citus workers, or external analytical engines. Vertical scaling adds more CPU, RAM, or faster storage to a single node. Horizontal scaling is necessary when vertical resources are exhausted or when distributing specific workload types (reads, analytics, writes) offers more leverage than raw hardware improvements.

Do read replicas help with write throughput in PostgreSQL?

No. Read replicas reduce read throughput pressure on the primary, but writes only go to the primary. Replicas do not reduce write load. If your primary is saturated by writes, replicas will not help. For write distribution, Citus sharding is the PostgreSQL-native option, or offloading analytical writes to ClickHouse® via CDC removes the analytical write amplification.

When should I use Citus instead of read replicas for PostgreSQL horizontal scaling?

Use Citus when write throughput — not read throughput — is the bottleneck, and when your data model has a clear distribution key (tenant_id, org_id). Citus distributes both reads and writes across workers. Use read replicas when the primary is overloaded with read queries and your write volume is manageable. Citus adds significantly more operational complexity.

How does CDC to ClickHouse® help PostgreSQL horizontal scaling?

Analytical queries — aggregations, reporting scans, dashboard queries — often generate the most CPU and I/O pressure on a PostgreSQL primary. CDC (Change Data Capture) streams row changes from PostgreSQL into ClickHouse®, which handles those queries natively. Once analytics traffic is routed to ClickHouse®, PostgreSQL's CPU and I/O are freed for OLTP. This is horizontal scaling by workload separation, not by node distribution.

What is replication lag and why does it matter for read replicas?

Replication lag is the delay between a write committed on the primary and its appearance on a replica. Under high write load, replicas can fall seconds or minutes behind. Applications reading from replicas may see stale data. For dashboards and reports, seconds of lag is acceptable. For applications that read their own writes, lag causes correctness issues. Monitor pg_stat_replication.replay_lag and route critical reads to the primary.

Can Tinybird replace a read replica for PostgreSQL horizontal scaling?

Tinybird does not replace read replicas for OLTP queries. It handles analytical workloads that replicas are not optimized for — aggregations over billions of rows, time-range scans, dashboard queries. Route transactional reads to replicas; route analytical queries to Tinybird.