partitioning comments table based on id

Question

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.

Answer 1

You didn't tell us your PostgreSQL version, so I assume v13.

1. If you create an index on the partitioned table, it will create an index on each partition. You can easily verify that with \d comments_1.

   Support for partitioned indexes exists since v11.

2. That depends entirely on the query. The rule is that any query that does not have a WHERE condition that restricts the partitioning key will scan all partitions and become slower.

   Actually, most queries become slower through partitioning.

3. The index should be on (post_id, comment_id), because the comparison on comment_id uses an the inequality operator.

   Your query is missing an ORDER BY and so is useless for pagination. I doubt that the query with ORDER BY will perform well, because it will scan all partitions.

Overall, it seems like you are using partitioning the wrong way.

• A partition size of 1000000 is ridiculously small. You may end up with way too many partitions for efficiency.

• From your query, it would make much more sense to partition by post_id, so that only one partition has to be scanned.

• The main benefit of partitioning is not query speed, but discarding old data. So partitioning by id makes sense if you discard data by that criterion.