I am confused with PostgresQL/Timescale indexing

Question

I have created a table like this

CREATE TABLE IF NOT EXISTS public.table_name(
    id SERIAL PRIMARY KEY,
    user_id int4 NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
);

SELECT create_hypertable('table_name', 'created_at', if_not_exists => TRUE);

CREATE INDEX IF NOT EXISTS ix_table_name_user_id ON public.table_name USING btree (user_id, created_at desc);

This is causing error and I am unable to create the index and the error says

CREATE INDEX IF NOT EXISTS ix_table_name_user_id ON public.table_name USING btree (user_id, created_at desc);
       - cannot create a unique index without the column "created_at" (used in partitioning)

But then I changed the PRIMARY KEY to a composite one

CREATE TABLE IF NOT EXISTS public.table_name(
    id SERIAL NOT NULL,
    user_id int4 NOT NULL,
    CONSTRAINT wind_surfer_pkey PRIMARY KEY (created_at, id)
);

And the index is working now. Can anyone help me understand this with references/documentations that I can read.

I tried ChatGPT but cannot understand the explanation

Answer 1

Look at the TimescaleDB example : https://docs.timescale.com/getting-started/latest/tables-hypertables/

CREATE TABLE stocks_real_time (
  time TIMESTAMPTZ NOT NULL,
  symbol TEXT NOT NULL,
  price DOUBLE PRECISION NULL,
  day_volume INT NULL
);

SELECT create_hypertable('stocks_real_time','time');

You can see that their example table doesn't contain a primary key before creating the hypertable.

Now, if you look at the create_hypertable documentation, you'll see that:

The PostgreSQL table cannot be an already partitioned table (declarative partitioning or inheritance)

Which mean you can't have an already existing primary key on your table.

Try dropping that id !

Answer 2

TimescaleDB partitions your data into chunks (our term for partition). For that reason, to find data we need to know what chunks to look at. Additionally, to make sure of the uniqueness of the key, we need to understand what chunks may see collisions (otherwise, we'd have to look through all chunks for every single insert).

That said, any kind of unique key (PK or unique index) in TimescaleDB needs to include the timestamp dimension column (and if you'd use secondary dimension, those too).

See the documentation: https://docs.timescale.com/use-timescale/latest/schema-management/indexing/#best-practices-for-indexing