Scaling PostgreSQL Databases: Table Partitioning and Index Maintenance

As transactional tables grow to millions of rows, query performance degrades even with proper indexing. Disk I/O bottlenecks and index memory footprints can slow down your entire application.

This guide explains how to scale PostgreSQL databases using table partitioning and active index maintenance.

When to Partition?

As a rule of thumb, partition a table when its size exceeds the available RAM of your database server, typically around 50GB to 100GB.

Range Partitioning by Time

For transactional logs or audit tables, range partitioning by date is the most effective strategy. This splits your main table into child tables representing specific time frames (e.g. monthly).

-- Create partitioned parent table
CREATE TABLE audit_logs (
  id UUID DEFAULT gen_random_uuid(),
  action TEXT,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create child partition for January 2026
CREATE TABLE audit_logs_y2026m01 PARTITION OF audit_logs
  FOR VALUES FROM ('2026-01-01 00:00:00') TO ('2026-02-01 00:00:00');

Pruning Queries

PostgreSQL's query planner uses partition pruning to read only the partitions containing the queried data, bypassing all other child tables. This minimizes disk I/O and keeps queries fast.