Re: Choosing an index on partitioned tables.

On Tue, 2021-09-07 at 04:06 +0000, Brent Wood wrote:
> From: Tim Uckun <timuckun(at)gmail(dot)com>
> > 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