I'm migrating a poorly planned table (in an Azure PostgreSQL db) that stores time history data, and I'm a bit overwhelmed. This history table has a composite primary key on 5 columns, 4 of which are a composite id and the 5th is a timestamp. The table itself is 66 GB on disk and the btree index on its pkey is 20 GB. The performance of this table has recently slowed down quite a noticeable bit, if you can believe it.

I've created a new table to migrate the data into and added a trigger on the old table to redirect new data into its replacement. The new table's pkey is made up of an integer id and timestamp column. I still need some guidance, though. As the data has been coming in, the ratio of the table's size to its primary key is about the same as before, ~3:1. It might shrink once the data is clustered, but it's worrying.

I've been looking for resources on how to make this table manageable. I've found a lot of advice here, but two things seem certain for better performance:

  1. Creating a BRIN index on the timestamp column
  2. Partitioning time history tables to improve performance by timestamp, then by id

The docs state that partitions "substitute for leading columns of indexes" and reduce the index size. I'm unsure how to proceed with this knowledge though. I have two questions as I'm confused on what's "best" to do.

  1. A good primary key would order the columns by integer id first and timestamp second, but the advice I've found is to partition by timestamp first (monthly/daily) and then sub-partition by id. Would the partitioning still be able to "substitute" the pkey in that case? If not, do I need to swap the pkey column order?

  2. If uniqueness isn't needed, is it better to just throw out the pkey and add a multi-column BRIN index on (time, id)? And then partition? (Is the BRIN even needed?)

Edit: Info about the table

It's largely a read-only table that has a few hundred thousand records appended to it each day. The most frequently run query is:

SELECT * FROM t_hist
    int_id IN
    (SELECT int_id FROM measurements
    WHERE location_id = 5
    AND timestamp/1000 > EXTRACT EPOCH FROM NOW() - 30*24*3600 -- last 30 days
ORDER BY int_id, timestamp

Thanks for any help/advice!

  • 1. Provide an example of a query that is written against the table 2. An integer is not primary key in and of itself, if there is a composite key present it was probably for good reason and you shouldn't throw it out on the supposition it will improve performance and not just take up more space.
    – bbaird
    Aug 9, 2021 at 19:10
  • The 4 columns matched to a dimension table, so all I did was add an auto-increment column to that table and map the generated ints to the history table where the old composite key matched. One of the key columns was a VARCHAR(32), I think I cut down the size. I'll add a query though.
    – Optimum
    Aug 9, 2021 at 19:13
  • Hi and welcome to dba.se! You should take a look at TimescaleDB - I've messed around with it - the project appears impressive as does the list of users (bottom of home page). You could also look at Citus (now owned by Microsoft - time series tab under elephant on home page). Client list slightly less impressive - have messed around with it also - user community appears friendly with the devs answering questions!
    – Vérace
    Aug 9, 2021 at 19:30
  • @Vérace It's an Azure managed instance, so no Timescale sadly. Citus is amazing but my management won't go for it right now ($$). Thanks for the kind welcome though!
    – Optimum
    Aug 9, 2021 at 19:34
  • There's a pricing section but no link - how do I find out how much it costs? I mean above and beyond what you're paying for Azure anyway?
    – Vérace
    Aug 9, 2021 at 19:40


Your Answer

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