Help, my table is getting too big!?
In PostgreSQL, since version 10, you can use partitioning.
Partitioning has multiple use cases:
- You can use it if you notice some tables are becoming too big.
The limit for a table in PostgreSQL is 32TB.
- You can use it to increase the performance of your database.
This happens because queries can skip over the partitions not needed for the calculations causing them to analyze less data.
- You can use it to have an easier maintenance.
Whenever some data is not needed anymore (older data for instance partitioned on a date), you can drop the partition completely.
It can also speed up the vacuum maintenance process as it will perform it for each partition, causing the main table to not have too much bloat or having its rows frozen by a vacuum that never end.
Lastly, you can also detach a partition when not needed anymore without removing the data:
<dbname>=$ ALTER TABLE <table> DETACH PARTITION <partition>
This will make it, so the partition becomes its own table instead.
Important remarks about partitioning:
- The upper range is exclusive. This is especially important when used with dates.