Partitioning is a way to split one large table into several smaller pieces (partitions) so the database can work more easily with a large number of records.
Imagine we are building a banking application that stores all user transactions in the transactions table.
Hundreds of thousands of new transactions appear every day, and after a few years the table already contains several million records.
At the same time, users most often view only the transactions for the last month.
How does Partitioning help here?
Instead of storing all transactions in one huge table, we can split it into parts by month:
transactions_january— transactions for January.transactions_february— transactions for February.transactions_march— transactions for March.
Now, when a user wants to view the transactions for February, the database can access only the corresponding part of the table without scanning the rest.
This mechanism is called Partition Pruning.
Meanwhile, to the developer it all looks like one regular transactions table. The database itself decides which partition to write new data to and where to read it from.
What types of Partitioning are there?
PostgreSQL has three main types:
- Range — splitting by ranges. For example, transactions by month or year.
- List — splitting by specific values. For example, orders by country: Ukraine, Poland, Romania.
- Hash — distributing records across partitions based on the hash of a particular column, for example
user_id.
Does Partitioning always speed up queries?
No. If a query doesn't allow the database to determine which part of the table is needed, the database may still have to scan all partitions.
In addition, Partitioning makes the database structure more complex, so it shouldn't be used unnecessarily (like any tool in programming).
So, Partitioning is a way to organize the storage of large amounts of data that can speed up certain queries and simplify the maintenance of large tables.