Table SharedResource { id bigint [pk] user uuid

Question

Table SharedResource { id bigint [pk] user uuid

resource_type enum [ note: "(file,folder,workspace, sharable...)"]
resource_object_id varchar(100) [ note: "file or folder ro workspace id"]
created_at timestamp

Indexes {
id [unique]
(user,resource_type,resource_object_id) [unique]
}
}

I am using (resource_type column as table ref) and (resource_object_id as row id for table). but concern is related to table partitioning. if I am doing a partition by (resource_type) then, what if one resource type has significantly more records than others? this is my concern. should I separate my tables for each type of resource? anyone can suggest me

#programming #russian

0

02.12.2023

1 ответов

30 просмотров

Yaroslav Schekin · Accepted Answer

Why didn't you try to test it yourself? By running something like: CREATE TABLE SharedResource ( id bigint PRIMARY KEY, "user" uuid, resource_type text, resource_object_id varchar(100), created_at timestamp, UNIQUE ("user", resource_type, resource_object_id) ) PARTITION BY RANGE (resource_type); You'd see this: ERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: PRIMARY KEY constraint on table "sharedresource" lacks column "resource_type" which is part of the partition key. I.e. this simply won't work, period. BTW, look at https://wiki.postgresql.org/wiki/Don%27t_Do_This and count how many of the things listed there you did.

169 похожих чатов

Table SharedResource { id bigint [pk] user uuid

1 ответов

Похожие вопросы