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:
- 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)?
- how would I best go about moving data from the master table to the relevant child tables?
- is this a sensible optimisation to make in the first place?
Hope someone can constrain my options somehow!
