Scaling PostgreSQL for Multi‑Terabyte Databases: Indexes, Partitioning, Tablespaces, Parallelism, and Replication

After a recent conversation with a DBA who began migrating a large Oracle database to PostgreSQL, the author notes a lack of comprehensive articles covering PostgreSQL's features for squeezing the last bits of performance out of hardware while safely handling several terabytes of data.

Standard PostgreSQL Features

PostgreSQL offers out‑of‑the‑box capabilities that can handle multi‑terabyte workloads when used correctly.

Lightweight / Specialized Indexes

Using partial, BRIN, GIN, and experimental BLOOM indexes can dramatically reduce index size for large OLTP systems, avoiding the common mistake of relying solely on default B‑tree indexes.

Partial indexes store only a subset of rows, e.g., indexing only orders with status “FINISHED”.

GIN excels at columns with many repeated values, storing each unique value once.

BRIN provides lossy compression by indexing only the minimum and maximum values of block ranges, ideal for ordered time‑series data.

BLOOM can offer up to 20× higher efficiency for suitable bitmap‑style searches.

Table Partitioning

Declarative partitioning introduced in PostgreSQL 10 (improved in 11) allows clean separation of hot and cold data, reducing vacuum and buffer usage without application changes.

Tablespaces

Tablespaces let you place tables and indexes on different storage media, enabling cost‑effective placement of cold data on slower disks while keeping hot data on fast storage.

Maximizing Parallelism

Since PostgreSQL 9.6, many operations can run in parallel; the default max_parallel_workers_per_gather is 2, and newer releases add parallel hash joins, index scans, and UNIONs.

Query Load Balancing with Replicas

Using streaming replicas (e.g., with Patroni) you can scale read‑only workloads across multiple nodes, while writes remain on a single primary.

Hybrid / Foreign Tables

Foreign Data Wrappers (FDW) let you query external data sources—files, logs, Twitter, S3, or even other RDBMS—as if they were regular tables, often with compression that reduces data size 10‑20×.

Extensions like c_store provide columnar storage for faster analytical queries.

Foreign Table Inheritance (Sharding)

Combining partitioning with foreign tables enables sharding across remote PostgreSQL instances or other databases, allowing data locality while keeping a unified query interface.