Create an index on a very large table to filter on date, or date + id

Question

I am creating a table where I will have a large volume of data and the search will always be based on a date, or a date + an ID. I want to create an index to support the queries, but I have no idea how to create this index for date.

My query:

create table if not exists example_table (
    id                  serial  primary key,
    original_created_at timestamp with time zone not null default now(),
    external_id         text not null,
    category            text NOT NULL,
    user_id             uuid not null,
    data                jsonb not null default '{}'::jsonb,

    constraint external_id_uq unique (external_id)
);

Example:
Most of the searches will be for the original_created_at or original_created_at + user_id field. I want to optimize performance for the search to only get values ​​from that date.

Answer 1

You speak of "date", but original_created_at is type timestamptz. I assume you do not actually want "values ​​from that date", but just for that timestamp.

A single multicolumn index can cover searches for either: original_created_at or for original_created_at + user_id. Just make sure to put original_created_at first:

CREATE INDEX ON example_table (original_created_at, user_id);

See:

• Is a composite index also good for queries on the first field?

Also, I would rearrange table columns to save a couple of bytes per row and make your searches a tiny bit faster yet, with the two filter columns first:

CREATE TABLE IF NOT EXISTS example_table (
  original_created_at timestamptz NOT NULL DEFAULT now()
, user_id             uuid NOT NULL
, id                  serial  PRIMARY KEY  -- do you actually have use for this?
, external_id         text NOT NULL
, category            text NOT NULL
, data                jsonb NOT NULL DEFAULT '{}'::jsonb
, CONSTRAINT external_id_uq UNIQUE (external_id)
);

See:

• Calculating and saving space in PostgreSQL

Since external_id_uq is UNIQUE NOT NULL anyway, you may be able to drop the surrogate PK column id.

bigint is more efficient for user_id than uuid. Not least, it makes above index smaller. Only use the type uuid if you actually need it. See:

• PostgreSQL using UUID vs Text as primary key

If there are lots of rows for the same timestamp (or timestamp + id combo), and rows are physically sorted, a BRIN index may be an alternative due its small size.