Scaling Postgres to 100 Million Rows: Partitioning, Indexing, and Read Replica Architectures

PostgreSQL is a robust relational database engine that handles heavy workloads easily. However, as tables grow past 100 million rows, queries that previously took milliseconds can begin to slow down. If query execution times exceed database transaction thresholds, they can block write operations and impact database performance. In this guide, we analyze partitioning, indexing, and replica configurations to keep queries fast at scale.

Declarative Table Partitioning

Table partitioning divides a single large table into smaller physical tables (partitions) while presenting a single interface to the application. This allows the database engine to search only the relevant partition for a query, bypassing millions of unrelated rows.

For a detailed breakdown of how we architect these systems for scale, visit our data engineering solutions page.

Implementing Partitioning by Date Range

Consider a high-volume transactional table. We can partition it by date range, creating separate partition tables for each month:

CREATE TABLE measurements (
    id UUID NOT NULL,
    metric_time TIMESTAMPTZ NOT NULL,
    value DOUBLE PRECISION,
    PRIMARY KEY (id, metric_time)
) PARTITION BY RANGE (metric_time);

CREATE TABLE measurements_y2026m01 PARTITION OF measurements
    FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2026-02-01 00:00:00+00');

CREATE TABLE measurements_y2026m02 PARTITION OF measurements
    FOR VALUES FROM ('2026-02-01 00:00:00+00') TO ('2026-03-01 00:00:00+00');

Optimizing Index Configurations

Failing to optimize index structures is a common cause of database performance issues. Review these core indexing strategies to keep query execution times low:

• Avoid Over-indexing: Every index on a table requires the database engine to update the index file during write operations, slowing down write queries. Remove unused or duplicate indexes.
• Use Partial Indexes: If your application queries only a subset of table records, create a partial index to save disk space and improve search speed:

  CREATE INDEX idx_active_users ON users(last_login) 
  WHERE status = 'ACTIVE';

• Implement Composite Indexes: If your query filters on multiple columns, create a composite index that covers those columns in order from left to right:

  CREATE INDEX idx_tenant_created ON transactions(tenant_id, created_at);

Read Replica Architectures

For read-heavy workloads, you can scale database performance by deploying read replicas. This configuration routes write queries to the primary database while directing read traffic to the replica instances, reducing CPU load on the primary server.

Implementing a Query Router in Node.js

Below is a connection pool wrapper that automatically routes query requests based on the operation type:

import { Pool } from 'pg';

export class ReplicaQueryRouter {
  private primaryPool = new Pool({ connectionString: process.env.PRIMARY_DATABASE_URL });
  private replicaPool = new Pool({ connectionString: process.env.REPLICA_DATABASE_URL });

  async executeQuery(sql: string, params: any[]): Promise<any> {
    const isReadQuery = sql.trim().toLowerCase().startsWith('select');
    const selectedPool = isReadQuery ? this.replicaPool : this.primaryPool;
    
    const start = performance.now();
    const result = await selectedPool.query(sql, params);
    const end = performance.now();
    
    console.log(`✓ Query executed on ${isReadQuery ? 'Replica' : 'Primary'} in ${(end - start).toFixed(2)}ms`);
    return result.rows;
  }
}

Step-by-Step PostgreSQL Scaling Checklist

Scale your high-volume PostgreSQL databases using these steps:

1. Analyze Query Bottlenecks: Use tools (e.g., pg_stat_statements) to identify slow, resource-heavy queries.
2. Define Partition Schema: Select range-based (date) or list-based partitioning based on query filters.
3. Implement Partition Tables: Create primary tables and month-based partitions using migration scripts.
4. Remove Unused Indexes: Identify and remove duplicate indexes to speed up write operations.
5. Create Partial Indexes: Index filtered record subsets to optimize index disk space.
6. Implement Query Router: Build a connection wrapper to direct read traffic to replica pools.
7. Configure Poolers: Configure pgBouncer to manage database connections efficiently under load.
8. Adjust Auto-vacuum Parameters: Tune auto-vacuum parameters to clean up deleted records and prevent index bloat.
9. Establish Backups: Configure automated backup scripts to secure database files.
10. Deploy Performance Monitors: Monitor CPU, memory, and query execution times to scale database resources proactively.

Summary of Recommendations

Scaling PostgreSQL databases past 100 million rows requires combining table partitioning, index optimization, and read replica configurations. Enforcing these architectural guardrails keeps queries fast and ensures database availability as your dataset scales.

High-Performance Indexes & Auto-Vacuum Tuning (Deep-Dive Analysis #1): Architectural Strategy

As tables scale past 100 million rows, keeping index sizes smaller than available RAM is key to maintaining query performance. If indexes grow too large, the database engine must read them from disk instead of memory, slowing down searches. Developers can prevent index bloat by tuning PostgreSQL auto-vacuum settings to clean up deleted records and update system stats regularly. This configuration keeps index structures clean and ensures queries run fast.

High-Performance Indexes & Auto-Vacuum Tuning (Deep-Dive Analysis #2): Operational Guidelines

Additionally, developers should analyze the impact of high write volumes on read replica lag. When a primary database processes thousands of updates per second, replicating these changes to replica servers can introduce replication lag. This means read replicas may serve slightly stale data. We manage this in application code by checking transaction timestamps, routing time-sensitive queries to the primary database when needed, and directing other queries to replicas. This configuration balances database performance and data consistency.

Mathematical Modeling Analysis

We model PostgreSQL B-Tree indexes as balanced multi-way search trees. The cost of searching a B-Tree index depends on the height of the tree. When a table grows past 100 million rows, index height can increase, requiring the database to perform additional disk operations for each query. We keep B-Tree depth low by using shorter index keys (like integer identifiers instead of long UUID strings) and indexing only critical columns in composite indexes.