Scaling Postgres to Millions of Rows

Scaling Postgres to millions of rows

Postgres does not usually fail because it “runs out of rows.” It slows down when the data model, indexes, and queries stop matching the workload. In 2026, that is still the core lesson for teams building SaaS products, internal platforms, and enterprise systems in Jakarta, Indonesia, and beyond: scale the design first, not just the hardware.

If your application is moving from hundreds of thousands to millions of records, you do not need to panic or jump straight to sharding. In many cases, a well-tuned single Postgres instance can serve a large production workload reliably. The goal is to keep reads predictable, writes efficient, and maintenance manageable.

What actually changes at millions of rows?

At small scale, inefficient queries can hide behind fast storage and low traffic. At millions of rows, the same mistakes become visible:

• Full table scans start to dominate response time.
• Poorly chosen indexes become expensive to maintain.
• Autovacuum falls behind and table bloat grows.
• Long-running analytics queries interfere with OLTP traffic.
• Backup, restore, and migration windows get longer.

The database is rarely the only problem. Application code, ORM behavior, and reporting patterns often create the bottleneck. For funded startups and enterprises, the real challenge is making sure operational growth does not turn into database entropy.

Start with the workload, not the database

Before changing architecture, answer a few practical questions:

• Which queries are slow most often?
• Are they read-heavy, write-heavy, or mixed?
• Do they filter by tenant, date, status, or region?
• Are there a few hot tables or many evenly used tables?
• Is the pain in latency, throughput, or maintenance?

This matters because the right fix depends on the shape of the workload. A billing system in Indonesia might need fast lookups by customer and invoice status, while an engagement platform may need efficient writes and time-based reporting. A single architecture pattern rarely solves both.

Which indexing strategies matter most?

Indexes are still the first major lever for scaling Postgres. The mistake is not “using indexes too little.” It is using the wrong ones.

Use indexes that match real predicates

If queries filter by tenant_id and created_at, a composite index on those columns may outperform separate single-column indexes. If the query sorts by a timestamp and limits results, the index should support that order.

A practical example:

SELECT id, status, created_at
FROM orders
WHERE tenant_id = $1
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

For this pattern, an index like this is often useful:

CREATE INDEX idx_orders_tenant_created_at_desc
ON orders (tenant_id, created_at DESC);

Keep indexes selective

Adding every possible index slows inserts and updates. At scale, write cost matters. If a column has low cardinality, such as a boolean status flag, it may not be a good standalone index. In many cases, it becomes useful only as part of a composite index.

Use partial indexes where they fit

If a query only targets active records, a partial index can reduce index size and improve performance:

CREATE INDEX idx_users_active_tenant
ON users (tenant_id, updated_at DESC)
WHERE is_active = true;

This is especially useful when a large portion of the table is archived, inactive, or soft-deleted.

When should you partition tables?

Partitioning is not a default requirement for millions of rows. It becomes useful when the workload has a clear access pattern that can be isolated, usually by time or tenant.

Good reasons to partition

• You query recent data far more often than historical data.
• You need fast retention management, such as dropping old data by month.
• You can benefit from partition pruning.
• A single table is becoming hard to vacuum or maintain.

Common partitioning patterns

Time-based partitioning works well for logs, events, billing records, and audit trails. Tenant-based partitioning can help when a small number of customers generate most of the load, but it adds operational complexity.

In practice, many teams in Jakarta and other Indonesian markets do better with monthly partitions for event-heavy tables than with premature sharding. Partitioning should reduce work, not create a new source of operational risk.

How do you keep queries fast in production?

The best scaling work is often query work.

Use EXPLAIN ANALYZE early

Do not guess. Check whether Postgres is using the index you expected, how many rows it actually scans, and where time is spent. A query that looks harmless in code may be doing a sequential scan across millions of rows.

Avoid ORM-generated surprises

ORMs can create inefficient joins, N+1 queries, or unnecessary SELECT * patterns. For high-traffic paths, inspect the generated SQL and optimize the exact query that runs in production.

Select only what you need

Fetching fewer columns reduces I/O and memory pressure. This matters more as rows widen with JSON fields, audit metadata, or denormalized attributes.

Paginate carefully

Offset pagination gets slower as offsets grow. For large datasets, keyset pagination is usually better:

SELECT id, created_at, status
FROM orders
WHERE tenant_id = $1
  AND created_at < $2
ORDER BY created_at DESC
LIMIT 50;

This avoids scanning and skipping large numbers of rows.

What about maintenance, vacuum, and bloat?

At scale, Postgres is not just about query speed. It is also about keeping the storage engine healthy.

Autovacuum must keep up with updates and deletes. If it falls behind, dead tuples accumulate and performance degrades. Monitor table bloat, vacuum frequency, and transaction age. For write-heavy systems, tune autovacuum settings rather than assuming the defaults will be enough forever.

Backups and restores also become part of scaling. A database that is easy to back up at 50 GB may be painful at 500 GB if you have not tested restore time, replication lag, and failover procedures.

Do you need read replicas or sharding?

Not necessarily.

Read replicas help when read traffic is the main issue and the workload can tolerate replication lag. They are useful for reporting, dashboards, and read-heavy APIs. But they do not fix poor queries or write contention.

Sharding is a bigger step. It increases application complexity, operational overhead, and failure modes. Most teams should only consider sharding after they have clear evidence that a single-node primary plus replicas and partitioning cannot meet the requirements.

A good rule in 2026: if you have not yet measured query plans, index efficiency, vacuum health, and replication behavior, you are probably not ready for sharding.

A practical scaling path for 2026

For most products, the path looks like this:

1. Measure slow queries and top tables.
2. Fix missing or poorly shaped indexes.
3. Reduce unnecessary columns, joins, and ORM overhead.
4. Add keyset pagination where needed.
5. Partition only where the access pattern is obvious.
6. Add read replicas for read-heavy workloads.
7. Consider sharding only when the business case is proven.

This sequence keeps complexity aligned with actual demand. It is also easier to explain to product and finance stakeholders, which matters in both startups and enterprises.

Key takeaways

• Millions of rows are not a problem by themselves; bad query patterns are.
• Indexes should match real filters, sorts, and join paths.
• Partitioning helps only when the workload has a clear pruning pattern.
• Autovacuum, bloat, and restore testing matter as much as query tuning.
• Sharding is a last-mile scaling option, not the first one.

Conclusion

Scaling Postgres to millions of rows is mostly about discipline: measuring what is slow, indexing what is actually used, and keeping maintenance under control. For teams in Jakarta, Indonesia, and global markets, that usually means getting more out of a single well-designed Postgres deployment before adding distributed complexity.

If your workload is growing fast, the safest move is to treat database scaling as an architecture problem, not just an infrastructure problem. That approach keeps your system simpler, your costs more predictable, and your future options open.