PostgreSQL 10 High Performance - Third Edition

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 ...