Partitioning advantages
There are quite a few performance related improvements that partitioning this way provides. The average number of index blocks you'll have to navigate in order to find a row goes down, because the first level split there is pushed towards being the query planner's job instead. And as mentioned already, having smaller blocks of your data might alter when the database can consider a sequential scan of a range a useful technique.
There are some maintenance advantages too. You can DROP
an individual partition, to erase all of the data from that range. This is a common technique for pruning historical data out of a partitioned table, one that avoids the VACUUM
cleanup work that DELETE
leaves behind. If you have monthly data, and you only keep a certain number of months at a time, once a new month starts you just DROP
the oldest partition.
Another advantage is that REINDEX
operations will happen in a fraction of the time it would take for a single giant...