Re: Choosing an index on partitioned tables.

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(dot)Wood(at)niwa(dot)co(dot)nz> 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(at)gmail(dot)com>
> *Sent:* Tuesday, September 7, 2021 15:44
> *To:* pgsql-general <pgsql-general(at)postgresql(dot)org>
> *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?
>
>
> <https://www.niwa.co.nz>
> 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 <https://www.niwa.co.nz> Facebook
> <https://www.facebook.com/nzniwa> LinkedIn
> <https://www.linkedin.com/company/niwa> Twitter
> <https://twitter.com/niwa_nz> Instagram
> <https://www.instagram.com/niwa_science> 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
>