I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options".

I've already run pg_tune and bumped a few of the memory options, and that gave a good performance increase. The problematic table is >250m rows at the moment, essentially consists of a timestamp, value, fk id row and has indexes on the (timestamp, fk id) and (fk id, timestamp) as well as the row id.

The "problematic" part comes when requesting say, a year's worth of data over more than a few of the fk ids (for example, a year's worth over a range of 150 fk ids)... queries run to 10 minutes.

I've come up with a pretty reasonable (it seems) partition function that splits data into tables based on the id of a table to which they are foreign keyed (so basically, these are many many thousands of rows per fk id per day, or maybe only up to a hundred per day, but they all end up in their own child table).

Then, the check constraints are just "is the id of this fk = to xxx".

This seemed like a reasonably good idea, because we often select data based on the foreign key id.

Now - I realised also that old data will still be in the master table, and couldn't see anything obvious in the docs about how to solve that (i.e. insert all pre-existing rows into child partitions), so I have also added a check constraint that says "and timestamp is > timestamp-at-which-child-table-was-made".

And then also noticed the caveat:

Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions.

Besides this caveat being a bit vague "perhaps a hundred" and "not many thousands" - it seems like maybe my partitioning method is not ideal anyway since we may have thousands of rows in the foreign-keyed table, and hence many thousands of partitions (but then, how long does it really take to scan thousands of equality conditions?).

So it's starting to feel a little bit crazy, and I just wanted to sanity check.

The questions, then:

  1. is it really ok to have thousands of partition tables, given very simple equality constraints (I intend to get rid of the timestamp check, so would only be based on fk id)?
  2. how would I best go about moving data from the master table to the relevant child tables?
  3. is this a sensible optimisation to make in the first place?

Hope someone can constrain my options somehow!

  • did you ever figure this one out? I'm facing the same questions.
    – Sam
    Feb 13, 2015 at 0:05
  • Hi Sam, yeah we did end up partitioning the data but not using the master table for reads (everything locks), only writes. Used non-ORM generated SQL to read from the partition tables and UNION ALL them together. Still not sure it's as fast as it could be, but works pretty well for millions of rows!
    – Steve Pike
    Feb 13, 2015 at 7:23
  • So you have thousands of partitions with hundreds of millions rows and you insert a new row to main table in a reasonable time. How about select from main table? Is it really different to select from child table? Oct 27, 2017 at 6:11


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.