1. 6
  1. 2

    I’m sure you all know this already, but: partitioning relational database tables is a double-edged sword. You almost always never need to do so.

    The reasoning being that the instant you have a query that doesn’t have a constraint that matches up with the partitioning constraint, you know have to check n different “tables” and n different indexes, with the potential concomitant I/O overhead.

    The only times it really makes sense is when queries are easily constrained by whatever partitioning predicate you chose; when the vast majority of data will be at rest and accessed only occasionally; when you want to spread the same table across multiple disks for performance or space reasons; or when you have a dataset where you often want to delete huge chunks and those chunks naturally fall on partition boundaries.

    IMHO, of course.

    1. 1

      I believe there are a few other cases where table partitioning is useful. Since sequential scans are much faster than index scans, you get a large speedup if you can partition your data so you get sequential scans over some of the partitions. As an example, if you are running a query that touches all data over several days, partitioning by each day can help since you will get sequential scans over all of the tables the query touches except the boundary ones (alternatively, if you can keep your table clustered, you can use a BRIN index to accomplish the same thing). The only other case I can think of is for inheritance (as mentioned in the article).