Re: Should I use table partitions

On Tue, Oct 9, 2018 at 10:19 AM Campbell, Lance <lance@illinois.edu> wrote:

The big, main reason for partitioning in PostgreSQL is if you have data retention that has to be managed. It makes having to deal with bloat a lot easier since you don't have to do bulk deletes that may never be filled with new data again. If you have no data retention being done now, I wouldn't worry about it until you start getting closer to 1 billion rows or more. That's when vacuuming run times may start to be a concern and partitioning can help reduce those since the individual objects are smaller to manage. Also, if there's a lot of static data, and you're on 9.6+, you can greatly improve vacuum times by running a VACUUM FREEZE on the table. That added a feature to allow vacuum to skip pages that are fully frozen.

Query read performance is really a secondary concern for partitioning in PG, especially if you're not managing data retention. Btree lookups are FAR more efficient than constraint exclusion (partition pruning in PG11+) will ever be. And if you're only doing more narrow data lookups, you'll actually see a performance decrease in queries since removing partitions in the planner adds overhead. That overhead isn't as big of a concern if there's data retention to manage, though, since bloat will be a bigger impact than the planner overhead. If you're grabbing larger blocks of data at one time, that's when partitioning may start helping with reads more since it has to pull fewer pages from disk.