我对PostgreSQL/Timescale的索引感到困惑。

huangapple go评论93阅读模式
英文:

I am confused with PostgresQL/Timescale indexing

问题

我已经创建了一个类似这样的表格

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);

这导致了错误,我无法创建索引,错误信息如下

CREATE INDEX IF NOT EXISTS ix_table_name_user_id ON public.table_name USING btree (user_id, created_at desc);
       - 无法创建唯一索引,缺少列 "created_at"(用于分区)

但后来我将主键更改为复合主键

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)
);

现在索引可以正常工作。
有人可以帮我理解这个问题,并提供我可以阅读的参考文档吗?

我尝试了 ChatGPT 但无法理解解释。

英文:

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

答案1

得分: 1

TimescaleDB将您的数据分成块(我们称之为分区)。因此,要找到数据,我们需要知道要查看哪些块。此外,为了确保键的唯一性,我们需要了解哪些块可能会发生碰撞(否则,我们将不得不查看每个单独插入的所有块)。

也就是说,TimescaleDB中的任何类型的唯一键(PK或唯一索引)都需要包括时间戳维度列(如果您使用了辅助维度,也需要包括)。

请参阅文档:https://docs.timescale.com/use-timescale/latest/schema-management/indexing/#best-practices-for-indexing

英文:

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

答案2

得分: 0

查看TimescaleDB示例: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');

您可以看到他们的示例表在创建超级表之前不包含主键。

现在,如果您查看create_hypertable文档,您会看到:

PostgreSQL表不能是已经分区的表(声明式分区或继承)

这意味着您不能在表上具有已经存在的主键。

尝试删除那个id

英文:

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 !

huangapple
  • 本文由 发表于 2023年7月13日 17:25:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/76677840.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定