Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.