|
Hi Tim,
I've had good success with TimescaleDB for large timesries databases (40b readings).
You turn your timestamp table into a Timescale hypertable and it looks after the indexing and partitioning automatically, with the table accessed like a normal postgres table, but very quickly.
It also adds some SQL functions to add a bit of time based query functionality.
Cheers
Brent Wood
Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529 From: Tim Uckun <timuckun@xxxxxxxxx>
Sent: Tuesday, September 7, 2021 15:44 To: pgsql-general <pgsql-general@xxxxxxxxxxxxxx> Subject: Choosing an index on partitioned tables. I have a series of tables which are going to be queries mostly on two
columns. A timestamp table and a metric type column. My plan is to partition by date ranges which means the primary key has to include the timestamp column and the id column As far as I know there is no way to specify an index type for those columns. The metric type is a text column and will not be very selective. It will have somewhere around 200 types of metrics and they will all be short, less than ten characters. Given that there will be a lot of records I was wondering what type of index would be ideal for that column. Seems like hash indexes would be ideal because only comparison will be = and they are smaller than Btrees but for a while they were not recommended. Would hash be the best or would something work better?
|
