I have a website where people can leave a comment on blog posts. The comments are growing fast, and I want to partition the table 'comments'. I'm pretty new with postgresql and I'm trying to learn.
My code so far:
--- create sequence
CREATE SEQUENCE comments_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
--- create table
CREATE TABLE comments (
comment_id bigint DEFAULT nextval('comments_seq') NOT NULL,
blog_id bigint NOT NULL,
user_id bigint NOT NULL,
comment text NOT NULL,
timestamp timestamp with time zone DEFAULT NOW() NOT NULL
) PARTITION BY RANGE (comment_id);
--- add primary key and foreign keys
ALTER TABLE comments ADD CONSTRAINT comments_pk PRIMARY KEY (comment_id);
ALTER TABLE comments ADD CONSTRAINT comments_to_blogs_fk FOREIGN KEY (blog_id) REFERENCES blogs (blog_id) ON DELETE CASCADE;
ALTER TABLE comments ADD CONSTRAINT comments_to_users_fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE;
--- create index on post_id (because I want to fetch all comments based on post_id and comment_id DESC (for pagination)
CREATE INDEX comments_idx_1 ON comments USING btree (comment_id DESC, post_id);
--- create partition tables
CREATE TABLE comments_1 PARTITION OF comments FOR VALUES (1) TO (1000000);
CREATE TABLE comments_2 PARTITION OF comments FOR VALUES (1000000) TO (2000000);
I want to store 1,000,000 comments in each partition table. This is just an example!
I have a few question about this setup:
1) I've created an index on the comments-table. Will this comment also be created on the partition tables whenever I create one, or should I create an index on each partition table individually. On this page, I found the following:
Partitions may themselves be defined as partitioned tables, using what is called sub-partitioning. Partitions may have their own indexes, constraints and default values, distinct from those of other partitions. Indexes must be created separately for each partition.
But when I describe a newly created partition table like 'comments_1', I see this:
postgres=# \d+ comments_1
Table "comments_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------+--------------------------+-----------+----------+---------------------------------------+----------+--------------+-------------
comment_id | bigint | | not null | nextval('comments_seq'::regclass) | plain | |
blog_id | bigint | | not null | | plain | |
user_id | bigint | | not null | | plain | |
comment | text | | not null | | extended | |
timestamp | timestamp with time zone | | not null | now() | plain | |
Partition of: comments FOR VALUES FROM ('1') TO ('1000000')
Partition constraint: ((comment_id IS NOT NULL) AND (comment_id >= '1'::bigint) AND (comment_id < '1000000'::bigint))
Indexes:
"comments_2_pkey" PRIMARY KEY, btree (comment_id)
"comments_2_comment_id_post_id_idx" btree ("comment_id" DESC, post_id)
Foreign-key constraints:
TABLE "blogs" CONSTRAINT "comments_to_blogs_fk" FOREIGN KEY (blog_id) REFERENCES blogs(blog_id) ON DELETE CASCADE
TABLE "users" CONSTRAINT "comments_to_users_fk" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
Access method: heap
I can see that the index I defined on the "main" (comments_idx_1) table is also defined on the partitioned table (comments_2_comment_id_post_id_idx).
2) How will the performance be when comments for one blogpost are separated in 2 (or more) partitioned tables? For example, comment with ID #999999 will be in comments_1, but comment with ID #1000002 will be in comments_2, even though they are for the same blogpost. I know this is a difficult question to ask, I'm not asking for real numbers but I'm just asking for the fact if it really matters if comments are not in the same partitioned table (or not). Otherwise, I should consider a different partition approach I guess.
3) My SQL to fetch the comments for a blogpost look like this:
SELECT
comment_id,
user_id,
comment,
timestamp
FROM
comments
WHERE
post_id = $1
AND
comment_id < $2
LIMIT
50
--- $1: post_id (is always the same)
--- $2: latest fetched comment_id (varies over time) -> keyset pagination
When I start fetching the comments, I don't have a last fetched comment ID yet, so I select the MAX(comment_id) for that blogpost, add 1 and run the previous query. I want to show the comments in the order: newest first.
Is the index I've created (comment_id DESC, post_id) sufficient for this?
Excuse me for the long question, I hope someone can answer (a part) of it. As I said, I'm new to postgresql and I'm still learning. My database already is postgresql and I know the basics, but I'm feeling that the performance of the database is going down when fetching the comments (already with indexes). I think this might be a good approach.
