Re: Choosing an index on partitioned tables.

[Tim Uckun <timuckun@xxxxxxxxx>]

Hi Brent. 

I looked at timescaledb. It does make partitioning on date ranges automatic  which is awesome and as you said it does add a couple of extra features on top of postgres but their cloud offering are much more expensive than buying a generic postgres instance from AWS. A generic t3.medium on amazon costs ~90 dollars per month in singapore and ~140 per month from timescale.

Is it really worth the extra expense? 

On Tue, Sep 7, 2021 at 4:06 PM Brent Wood <Brent.Wood@xxxxxxxxxx> wrote:

Hi Tim,

I've had good success with TimescaleDB for large timesries databases (40b readings).

https://www.timescale.com/

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?

Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 National Institute of Water & Atmospheric Research Ltd (NIWA) 301 Evans Bay Parade Hataitai Wellington New Zealand Connect with NIWA: niwa.co.nz Facebook LinkedIn Twitter Instagram

To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems