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:
- Creating a BRIN index on the timestamp column
- 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.
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?
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 WHERE 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!