If you're asking about scaling Postgres to billions of rows or following stories like OpenAI scaling PostgreSQL at production scale, you're dealing with the same engineering problem: how do you keep analytical workloads fast when your data volume grows beyond what a single OLTP engine can handle without tradeoffs.
This post focuses on the concrete workflow behind scaling Postgres to billions of rows: identify where Postgres breaks down, choose the right relief valve, and offload analytical queries to a purpose-built engine while keeping your OLTP intact.
You'll see what Postgres can handle with optimizations, where its limits are hard to work around, and how to route analytics traffic through ClickHouse® so your transactional database stays healthy.
At a billion rows, autovacuum, table bloat, and lock contention stop being theoretical concerns and start becoming operational incidents.
How to scale Postgres to billions of rows (step-by-step)
Follow this sequence to keep Postgres healthy at scale while serving analytical workloads predictably.
Step 1: identify where the pressure comes from
Before changing anything, distinguish between OLTP slowness and analytical query pressure.
At billion-row scale, analytical queries and write-side maintenance compete for the same resources.
Autovacuum pressure, WAL growth, and checkpoint I/O are all write-driven — but analytical scans amplify the problem by competing for the same buffer pool and I/O bandwidth.
Queries that scan large time ranges, join multiple large tables, or produce aggregations for dashboards are the primary culprits at billion-row scale.
Step 2: apply Postgres-internal relief valves
Before adding new infrastructure, apply what Postgres gives you natively.
Partition large tables by time range (monthly or yearly) so analytical queries scan only the relevant partition.
Create targeted indexes for your most expensive analytical queries and use connection pooling (PgBouncer) to protect the write path from read spikes.
Step 3: pick your integration path for offloading analytics
Beyond Postgres-internal optimizations, choose where to route analytical workloads.
Integration path: Tinybird — CDC from Postgres → SQL APIs on ClickHouse®
How it works: stream changes from PostgreSQL into Tinybird via CDC (using Kafka + Debezium, or Estuary Flow, or Sequin), then publish SQL as high-concurrency, low latency APIs via Pipes.
Your Postgres stays as the OLTP source of truth. Tinybird handles the analytical query serving.
When this fits:
- You want scaling Postgres to billions of rows without changing your OLTP design.
- Your analytical queries need sub-second response times under concurrency.
- You want a unified ingestion + transformation + API serving workflow without assembling the stack yourself.
Prerequisites: a streaming CDC path from Postgres (Kafka connector, Estuary, or Sequin) and deployed Pipes endpoints in Tinybird.
Example: events table tuned for analytical reads (SQL):
CREATE TABLE IF NOT EXISTS pg_events
(
event_time DateTime,
user_id UInt64,
entity_id UInt64,
event_type LowCardinality(String),
metric_value Float64,
updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, entity_id, event_time);
SELECT
toStartOfHour(event_time) AS hour,
user_id,
sum(metric_value) AS total
FROM pg_events
WHERE event_time >= now() - INTERVAL 24 HOUR
GROUP BY hour, user_id
ORDER BY hour;
Note: ClickHouse® ReplacingMergeTree merges are asynchronous — duplicates may be visible until background merge completes. Use FINAL when exactness matters, or accept eventual convergence when freshness is the priority.
Integration path: ClickHouse® Cloud + ClickPipes — managed ingestion
How it works: ingest Postgres data into ClickHouse® Cloud via ClickPipes, then build your analytical serving layer on top with SQL.
This is a good fit if you want managed ingestion into ClickHouse® Cloud and you're comfortable owning the serving and API layer yourself.
When this fits:
- You already have a Kafka or S3 export path from Postgres.
- You want managed ClickHouse® ingestion without operating the ingestion stack.
- You own the API or BI layer that queries the analytical destination.
Prerequisites: ClickHouse® Cloud set up and a ClickPipes-compatible export source (Kafka topic fed by Debezium, or S3 exports).
Example: destination table for Postgres analytical data:
CREATE TABLE pg_analytics
(
record_time DateTime,
entity_id UInt64,
status LowCardinality(String),
value Float64,
updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
PARTITION BY toYYYYMM(record_time)
ORDER BY (entity_id, record_time);
Integration path: Self-managed — own CDC semantics end-to-end
How it works: operate your own CDC pipeline (Debezium + Kafka + Kafka Connect sink into ClickHouse®) and manage the analytical ClickHouse® cluster yourself.
This is the option when you need full control over deduplication semantics, schema evolution, or compliance requirements.
When this fits:
- You have compliance requirements that mandate self-hosted infrastructure.
- Your team runs infrastructure and already operates Kafka + ClickHouse®.
- You need custom deduplication or ordering semantics that managed paths don't expose.
Prerequisites: Debezium configured for your Postgres WAL, Kafka cluster, ClickHouse® deployment, and a Kafka Connect sink or custom consumer.
Step 3 recap: choosing your integration path
If you want scaling Postgres to billions of rows solved as an API-serving problem, start with Tinybird.
If you want managed ingestion and own the serving layer, use ClickPipes.
If you need full end-to-end control, go self-managed.
Step 4: shape analytical queries around ClickHouse® strengths
Build endpoints around bounded time windows and precompute repeated aggregations.
ClickHouse® excels at columnar scans with time-window filters — match your ORDER BY to your most common filter columns.
Step 5: validate freshness and latency SLOs
Define what "fresh enough" means for your analytics before going to production.
For most Postgres CDC paths, you can achieve seconds-to-minutes lag depending on replication configuration.
Step 6: monitor the pipeline end-to-end
Track CDC lag, ingestion freshness, and endpoint latency (p95/p99) as first-class metrics.
Alert on replication lag spikes before they cause user-visible staleness.
Step 7: load test and harden endpoints before scaling traffic
Validate endpoint latency under realistic concurrency before routing production analytical traffic away from Postgres.
Decision framework: what to choose (search intent resolved)
- Need analytical APIs with minimal ops overhead → Tinybird.
- Need managed ClickHouse® ingestion and own the serving layer → ClickPipes.
- Need full end-to-end control over CDC semantics and infrastructure → self-managed.
Bottom line: scaling Postgres to billions of rows analytically means finding the smallest integration surface that routes analytical queries away from Postgres while keeping the OLTP path clean.
What does scaling Postgres to billions of rows mean (and when should you care)?
PostgreSQL is an OLTP engine optimized for short transactions, frequent small reads, and write consistency.
At billion-row scale, analytical queries — scans, joins, aggregations — compete for the same memory, CPU, and I/O that your write path needs to stay responsive.
You should care when:
- Autovacuum runs longer and more frequently as dead tuples accumulate faster.
- Analytical queries take minutes instead of seconds and users start noticing.
- Dashboard refresh latency spikes during peak write hours.
- EXPLAIN ANALYZE shows sequential scans that partition indexes can't fix.
The problem is not Postgres. It is using Postgres for a workload contract it was not designed for.
Schema and pipeline design
When offloading to ClickHouse®, shape the analytical schema around your query patterns — not around the normalized Postgres relational model.
For event-style analytical data, that typically means denormalizing into wide rows and building ordering around your most common filter columns.
Practical schema rules for scaling Postgres analytics
- Denormalize commonly joined tables into the analytical schema so ClickHouse® doesn't need to perform expensive JOINs at query time.
- Put the most selective filter column first in ORDER BY (usually an entity key like
user_idplus a time column). - Partition by time grain (monthly) to limit scan scope for time-bounded queries.
Example: denormalized CDC-ready schema (SQL)
CREATE TABLE IF NOT EXISTS orders_analytical
(
order_time DateTime,
order_id UInt64,
user_id UInt64,
status LowCardinality(String),
amount Float64,
country LowCardinality(String),
updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
PARTITION BY toYYYYMM(order_time)
ORDER BY (user_id, order_id, order_time);
Failure modes (and mitigations)
CDC lag spike — Postgres write burst causes WAL lag to grow, analytical data becomes stale.
- Mitigation: monitor replication lag continuously; define a freshness SLA per analytical endpoint.
- Alert before lag crosses the threshold where user-visible staleness occurs.
Deduplication failures — Kafka at-least-once delivery sends duplicate row updates; analytics double-counts.
- Mitigation: use
ReplacingMergeTree(updated_at)and validate reconciliation counts. - Note: ClickHouse® merges are asynchronous — use
FINALwhen exact deduplication is critical.
- Mitigation: use
Schema drift — Postgres column adds or type changes break the CDC sink.
- Mitigation: treat the ClickHouse® destination schema as a versioned contract; add columns additively and map types explicitly.
Analytical query bleeding back into Postgres — ad-hoc BI queries run directly against Postgres replicas, competing with OLTP.
- Mitigation: enforce routing — analytical traffic goes to the ClickHouse® path, not to Postgres replicas.
Why ClickHouse® for scaling Postgres analytics
ClickHouse® is built for the analytical workload contract that Postgres struggles with at scale.
Its columnar storage and vectorized execution keep aggregation queries fast even as data grows to billions of rows.
For time-bounded analytical queries (last 24 hours, last 30 days), ClickHouse® typically returns results in milliseconds — without competing with your Postgres write path.
The MergeTree engine family provides partitioning, ordering, and merge-time deduplication, which aligns naturally with CDC streams from Postgres.
Security and operational monitoring
A Postgres CDC pipeline into ClickHouse® has several new failure surfaces compared to a single-database setup.
Make it observable:
- CDC lag and delivery delays — per-table lag metric with alerting thresholds.
- Endpoint error rates — auth failures, query timeouts, and schema mismatches.
- Reconciliation checks — periodic row-count and distribution comparisons between Postgres and ClickHouse® for critical tables.
For a broader view of cloud computing infrastructure patterns for distributed data systems, see the IBM reference.
For streaming data concepts that underlie CDC architectures, see the IBM streaming data guide.
Latency, caching, and freshness considerations
The end-to-end latency for a CDC-based architecture has three components:
- WAL capture latency — how quickly Postgres changes appear in the CDC stream.
- Ingestion latency — how quickly Tinybird or ClickHouse® makes ingested rows queryable.
- Serving latency — how fast the analytical endpoint responds under concurrency.
For most CDC paths, you can achieve seconds-to-low-minutes freshness.
For serving, ClickHouse® endpoints shaped around bounded time windows consistently return results in the milliseconds-to-low-seconds range under high concurrency.
Scaling Postgres to billions of rows: integration checklist (production-ready)
Before routing production analytical traffic to ClickHouse®:
- Define freshness SLAs per analytical endpoint and measure lag end-to-end.
- Enforce time windows and limits in every endpoint — never allow unbounded analytical queries.
- Choose deduplication semantics explicitly:
ReplacingMergeTree(updated_at)for CDC sources. - Validate schema mapping between Postgres and ClickHouse® — test column type conversions.
- Add monitoring: CDC lag, endpoint latency (p50/p95/p99), error rates, reconciliation counts.
- Load test with realistic concurrency before switching analytical dashboards or APIs to the new path.
- Document the rollback path if CDC falls behind or the ClickHouse® destination needs schema migration.
Why Tinybird is a strong fit for scaling Postgres to billions of rows
Tinybird reduces the integration surface between your Postgres OLTP stack and ClickHouse® analytics.
Instead of building and operating a CDC consumer, transformation layer, and API service separately, you ingest via CDC connectors and publish SQL as Pipes.
That's the difference when your team needs real-time data ingestion from Postgres without assembling and operating the full CDC-to-API stack.
The best database for real-time analytics is often the one that fits your operational constraints — and Tinybird keeps those constraints bounded.
If your goal is user-facing analytics served as product APIs from Postgres data at scale, Tinybird fits that path directly.
Next step: identify your most expensive analytical query on Postgres, model it as a bounded ClickHouse® endpoint in Tinybird, and validate latency against your SLA in staging before switching production traffic.
Frequently Asked Questions (FAQs)
What are the first signs that Postgres is struggling at billion-row scale?
Autovacuum running more frequently and for longer is usually the earliest signal.
Query latency percentiles (p95/p99) rising for analytical endpoints — even when OLTP looks fine — is the next indicator.
Can I keep using Postgres for OLTP while offloading analytics?
Yes, and this is the recommended approach.
Keep Postgres as your OLTP source of truth; route analytical queries to ClickHouse® via CDC. Your transactional write path remains untouched.
How does scaling Postgres to billions of rows work with CDC?
Postgres writes changes to its WAL (write-ahead log). A CDC tool like Debezium or Estuary reads the WAL and streams row-level changes to ClickHouse®.
ClickHouse® applies changes and makes them queryable, typically within seconds to low minutes depending on your replication configuration.
What ClickHouse® schema patterns work best for Postgres CDC data?
Use ReplacingMergeTree(updated_at) for tables that receive updates and deletes from Postgres.
Put your most selective filter column first in ORDER BY, partition by time grain, and denormalize commonly joined Postgres tables into wide rows.
How do I handle Postgres table schema changes in the CDC pipeline?
Treat the ClickHouse® destination schema as a versioned contract.
Add columns additively in ClickHouse® before they become required in Postgres, and map types explicitly — don't rely on auto-inference from CDC schema evolution.
Where does scaling Postgres to billions of rows break even for pure Postgres solutions?
Postgres with partitioning, connection pooling, and read replicas can handle analytical workloads into the hundreds of millions of rows for many use cases.
Beyond that — especially with concurrent analytical users — the contention between OLTP and OLAP workloads becomes an operational tax that a dedicated analytical engine (ClickHouse®) is designed to absorb.