PostgreSQL partitioning — 4 strategies for managing large tables

Tables grow. At some point a table that used to respond in milliseconds starts taking seconds. Queries that scan a few thousand rows are now scanning tens of millions. Indexes help, but they can only do so much when the underlying table is massive. Partitioning splits a large table into smaller physical pieces while keeping the logical interface unchanged. Your application still queries one table, but PostgreSQL routes reads and writes to the relevant partition. This reduces the amount of data scanned per query, makes maintenance operations faster and keeps indexes smaller and more efficient. PostgreSQL supports declarative partitioning natively since version 10. There are 4 strategies to choose from, and picking the right one depends on your data and how you query it. How partitioning actually works Before diving into strategies, it helps to understand the mechanics. A partitioned table in PostgreSQL is a virtual parent table. It stores no data itself. Data lives in partitions — regul