Can PostgreSQL Handle Billions of Rows?

Can PostgreSQL Handle Billions of Rows?

I've had this conversation maybe forty times in the last two years. A founder pings me on Slack. They've got a Postgres database creaking at 400 million rows. Someone on their team — usually a well-meaning backend engineer who just read a Medium post about ClickHouse — is pushing hard to migrate. They want to know if they're crazy.

They're not crazy. They're asking the right question, badly framed.

So let me answer it straight: yes, PostgreSQL can absolutely handle billions of rows. I've run it. I've run it at 40 billion rows on a single primary node, and I've seen clients run it higher. But "can handle" is doing a lot of work in that sentence, and the honest answer depends on what you mean by "handle" and what you mean by "billions."

This piece is a definition and a how-to. I'll explain what PostgreSQL's actual scaling limits are, where it breaks, what you have to do to keep it fast past a billion rows, and when you should stop pretending it's the right tool. Because there's a point where Postgres stops being your database and starts being your problem. Knowing where that point is saves you a nine-month migration you didn't need.

What "can PostgreSQL handle billions of rows" actually asks

Most people asking "can PostgreSQL handle big data analytics 2026" are really asking three separate questions and don't know it.

First: will Postgres store a billion rows without dying? Trivially yes. A billion rows in a table of modest width runs maybe 100-200 GB on disk. Postgres has been storing terabyte tables since the early 2000s. The storage layer isn't the concern.

Second: will queries over those billion rows stay fast? This is the real question. And here the answer is "yes, if you do specific things, and no, if you don't."

Third: will it handle a workload that's genuinely analytical — the kind of dashboards and aggregation queries that people historically bought ClickHouse or Snowflake for? This is where 2026 looks very different from 2019. Postgres has caught up meaningfully. More on that shortly.

The mistake is collapsing these three into one. Storing is easy. Querying is where you earn your salary.

The number that actually matters isn't row count

I want to kill a framing I see everywhere. "Postgres handles up to X rows" is meaningless. Postgres doesn't index tables — it indexes pages. Your performance ceiling has almost nothing to do with how many rows you have and almost everything to do with:

• Table width (how many bytes per row)
• Index selectivity (how many distinct values, and how well they cluster)
• Working set size versus RAM
• Whether your queries hit indexes or sequential scans
• Write/read ratio and autovacuum behavior

A 5-billion-row table of narrow event rows with a good (entity_id, created_at) index can be faster than a 50-million-row table of bloated JSONB blobs. I've seen both. I've fixed both.

So when someone tells me they're worried about their table hitting a billion rows, I ask them what their p99 query latency is and what their pg_stat_statements top offenders look like. Row count is a red herring. Latency is the whole story.

Where Postgres genuinely struggles past a billion rows

Let me be honest about the failure modes. I've hit every one of these.

Bloat. Postgres uses MVCC, which means dead tuples accumulate and storage grows until autovacuum reclaims. On a high-write billion-row table, if your autovacuum isn't tuned, you can watch a 300 GB table become 900 GB over a few months while read latency climbs. This is the single most common reason I see Postgres deployments degrade. It's not row count. It's that nobody tuned autovacuum for the actual write rate.

Sequence and index write amplification. If you're inserting 20K rows/second into a table with a B-tree primary key on a bigserial, you're competing for the rightmost index page and fighting page splits. This is where things get ugly fast. The fix is usually periodically-rebuilt clustering keys — either a UUIDv7, or a (bucket, id) pattern, or switching to a partitioned design where recent data lives in its own partition.

Cross-partition queries. Partitioning at the billion+ level isn't optional — it's the baseline. But a query that doesn't filter on the partition key can fan out to hundreds of partitions and destroy performance. I've watched a single unpruned query touch 400 partitions and run for 90 seconds when the same query properly pruned runs in 30 milliseconds.

Cold storage cost. Postgres wants everything on premium local SSD. Once your hot table exceeds a few TB, you're paying NVMe prices for cold data that ClickHouse or Parquet-on-S3 would store for a tenth of the cost. This is real money. At SIVARO we've kept clients on Postgres where it made sense and moved them off where the storage economics stopped working.

The 2026 reality: PG's analytical story changed

Here's the thing most "you need ClickHouse" articles haven't updated. Between 2021 and 2026, Postgres's analytical capabilities went from "tolerable for small aggregates" to "genuinely competitive for a lot of workloads."

The big shifts:

Columnar extensions matured. Citus Columnar and the newer Hypercore work lets you store some tables or partitions in columnar format within Postgres itself. On an append-heavy events table, this drops scan times by 10-50x versus heap storage and compresses 5-10x. I've benchmarked this directly on a client's 8-billion-row event table and it changed the conversation entirely.

Vectorized execution arrived. Postgres 17 and 18 (both shipped by now, with 18 landing earlier in 2026) improved JIT and parallel query planning significantly. Large aggregations parallelize across cores in ways that just weren't practical in 2019.

pgvector plus pgvectorscale. AI workloads. If you're doing RAG over a billion embeddings, you can do it in Postgres now — Timescale's pgvectorscale handles billions of vectors with DiskANN-based indexes. A lot of teams running "AI-native" databases in 2026 could have just used Postgres.

DuckDB as a companion. The play isn't "replace Postgres with DuckDB." It's "use DuckDB as a read accelerator over Postgres data" for analytic queries. A pattern I've deployed for several clients.

So yes — for can PostgreSQL handle big data analytics 2026, the answer is more affirmative than it was five years ago. Postgres isn't just a transactional database pretending to be analytics-capable. It's genuinely earning its analytical stripes on the workloads where you'd have reflexively reached for a specialized system.

How to make Postgres handle billions of rows (the practical playbook)

Here's the actual playbook. This is what I'd do if you handed me a Postgres instance and told me it needed to hit 5 billion rows and stay fast.

Partition early, partition by time

If a table is going to pass 100 million rows, partition it. Almost always by range on a time column, sometimes composite-partitioned by a tenant or entity key.

sql
CREATE TABLE events (
    id           bigint GENERATED ALWAYS AS IDENTITY,
    tenant_id    uuid NOT NULL,
    event_type   text NOT NULL,
    payload      jsonb,
    created_at   timestamptz NOT NULL,
    PRIMARY KEY (tenant_id, created_at, id)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

The composite primary key matters. (tenant_id, created_at, id) beats a naked bigserial because it lets queries filter to a tenant and a time window without a full index scan, and the appending rows distribute across the B-tree better.

Time partitioning means queries with a created_at filter prune to one or two partitions. It means you can DETACH old partitions instead of deleting rows. And it means autovacuum works on smaller units.

Get the indexes right — and only the right ones

Every index costs you write throughput and disk. On a billion-row table, "let's just add an index" is how you turn a 5K QPS system into a 2K QPS system overnight.

Rules I follow:

• One good composite index on (high_cardinality_filter, time) beats five single-column indexes.
• Partial indexes are criminally underused. CREATE INDEX ... WHERE status = 'active' on a table where 2% of rows are active shrinks the index 50x.
• BRIN indexes on append-only time-series columns. A BRIN index on created_at for a 5-billion-row table is often a few megabytes and still gives you range pruning.

sql
CREATE INDEX brin_events_created_at ON events USING brin (created_at)
    WITH (pages_per_range = 32);

CREATE INDEX partial_active_events ON events (tenant_id, created_at)
    WHERE event_type = 'signup';

Tune autovacuum for reality, not defaults

Defaults assume a small OLTP database. They will destroy you at scale. Here's what I set on any node running a billion-row+ table:

sql
ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_insert_scale_factor = 0.02,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_limit = 2000
);

Default scale factors are 0.2 — meaning autovacuum waits for 20% of the table to change. On a billion rows, that's 200 million dead tuples before it kicks in. You're dead long before then. 2% is aggressive but correct for a hot table.

Offload analytics to columnar storage

For the big aggregations, use columnar. Citus Columnar, Hydra, or DuckDB over Parquet exports. A query SELECT date_trunc('day', created_at), count(*) FROM events GROUP BY 1 over 5 billion rows takes minutes on heap storage. On columnar, it takes seconds.

sql
CREATE TABLE events_archive (LIKE events) USING columnar;

INSERT INTO events_archive SELECT * FROM events
    WHERE created_at < '2026-06-01';

This is the biggest single lever, honestly. Row-store for hot transactional data, column-store for historical analytics. Same Postgres. Same connection.

Watch the working set, not the disk usage

The OS page cache is your real performance multiplier. If your hot working set fits in RAM, you're fast. If it doesn't, every query is disk-bound. On a 5-billion-row table where you query the last 30 days, that hot slice might be 40 GB — easily cached on a 128 GB box. If you're querying randomly across 5 billion rows, no RAM helps.

Design queries to touch recent data. Archive old data to columnar and treat it as cold. Your p99 will thank you.

When Postgres is the wrong answer

Contrarian position time. The Postgres fanbase can be as dogmatic as the NoSQL crowd was in 2012. Postgres is not always right.

Move off Postgres when:

• You're ingesting >100K writes/sec sustained. At some point you want a purpose-built time-series or log store. Timescale is still Postgres, but plain Postgres with 100K+ inserts/sec of row-level writes is pushing it.
• Your hot data is genuinely >5 TB. Storage economics start hurting. Columnar-on-S3 becomes compelling.
• Your queries are massive analytical joins across a billion-row fact table and 50 dimension tables. Dedicated columnar systems (ClickHouse, Snowflake, BigQuery) still win here, and by a lot.
• You need multi-region active-active writes. Postgres's logical replication isn't a great fit for global write distribution. That's a real limitation.

But "we have more than a billion rows" is not one of those reasons. Not in 2026.

FAQ

Can PostgreSQL handle billions of rows in a single table?
Yes, but don't do it in a single unpartitioned table. A properly partitioned Postgres table at billions of rows performs well. A single monolithic billion-row table works for reads but starts degrading on writes and vacuums.

How many rows can Postgres realistically handle?
I've personally run 40 billion rows in production Postgres on a beefy single node. The theoretical limit is far higher. The practical limit is set by your working set, your query patterns, and your operational discipline — not by a hard cap.

Is Postgres fast enough for big data analytics?
In 2026, yes for most workloads under a few terabytes. Columnar extensions and vectorized execution closed much of the gap with dedicated OLAP systems. For petabyte-scale or heavy multi-join analytics, you still want a specialized system.

Does partitioning actually help at a billion rows?
It's not optional. Autovacuum works on one partition at a time, queries prune to relevant partitions, and detaching old data is instant. Unpartitioned billion-row tables fight you on all three fronts.

What's the biggest performance killer at scale?
Bloat from untuned autovacuum. I've solved more "Postgres is slow" tickets with an autovacuum tuning change and a one-time VACUUM FULL than with anything else. It's boring and it's true.

Should I use UUID or bigint for a primary key on a billion-row table?
Bigint if you can. If you need UUIDs, use UUIDv7 — the time-ordered version — not UUIDv4. UUIDv4 destroys B-tree locality on writes and you'll pay for it.

Can Postgres replace ClickHouse?
Sometimes. For workloads under a few hundred GB of hot analytical data with modern columnar extensions, absolutely. For hardcore OLAP with hundreds of billions of rows and complex joins, no. Pick per-workload, not per-org.

What about pgvector for AI workloads at scale?
pgvector plus pgvectorscale handles billions of embeddings. We run this in production. It's real. It's not the fastest option in every benchmark, but the operational simplicity of not running a separate vector database is usually worth more than raw QPS.

The bottom line on can PostgreSQL handle billions of rows

Yes. If you've heard "you outgrew Postgres" from someone who hasn't actually run it at scale, get a second opinion.

Can PostgreSQL handle billions of rows? Yes — with partitioning, tuned autovacuum, disciplined indexing, and columnar storage for the analytical slices. I've run it at 40 billion rows on one node. I've watched clients on 8 billion rows at sub-100ms p99 for their dashboard queries. The 2026 version of Postgres is a very different beast from the 2019 version.

But the honest answer is also: yes, until you hit one of the walls. Those walls are real, they're specific, and they're not "billion rows." They're "5 TB hot working set" or "100K sustained writes/sec" or "petabyte multi-way joins." Know where your walls are before you migrate to a system that has its own, different walls.

Most teams migrating off Postgres at 500 million rows for "scale" are solving a query-tuning problem with a platform change. That's expensive therapy.

If you're sitting on a Postgres instance that's creaking and you're not sure whether the answer is tuning, columnar, or a real migration — that's exactly the conversation I have for a living. Hit me up.

Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.