To be fair Timescale also adds some other features which might be useful. For example they add some SQL enhancements like last value and auto maintaining materialized views and such. The automatic management of partitions is also pretty big plus in my opinion. You can get some of the equivalent functionality by writing stored procs and deploying pg_cron but it's nice to have those things built in. It's open source so you can just deploy their docker image which I did for development but in the end I wanted to try and do the same thing in plain jane postgres. On Tue, Sep 7, 2021 at 7:24 PM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote: > > On Tue, 2021-09-07 at 04:06 +0000, Brent Wood wrote: > > From: Tim Uckun <timuckun@xxxxxxxxx> > > > 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? > > > > I've had good success with TimescaleDB for large timesries databases (40b readings). > > That has nothing to do with indexing, and I would think twice to install > an invasive extension like that and add a dependency on third-party code, > just because I want to partition a table. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com >
