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.w...@niwa.co.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 <timuc...@gmail.com> > *Sent:* Tuesday, September 7, 2021 15:44 > *To:* pgsql-general <pgsql-gene...@postgresql.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 >
