Can Postgres table partitioning by hash boost query performance?

Question

I have an OLTP table on a Postgres 14.2 database that looks something like this:

     Column     |            Type             | Nullable |
----------------+-----------------------------+-----------
 id             | character varying(32)       | not null |
 user_id        | character varying(255)      | not null |
 slug           | character varying(255)      | not null |
 created_at     | timestamp without time zone | not null |
 updated_at     | timestamp without time zone | not null |
Indexes:
    "playground_pkey" PRIMARY KEY, btree (id)
    "playground_user_id_idx" btree (user_id)

The database host has 8GB of RAM and 2 CPUs.

I have roughly 500M records in the table which adds up to about 80GB in size.

The table gets about 10K INSERT/h, 30K SELECT/h, and 5K DELETE/h.

The main query run against the table is:

SELECT * FROM playground WHERE user_id = '12345678' and slug = 'some-slug' limit 1;

Users have anywhere between 1 record to a few hundred records.

Thanks to the index on the user_id I generally get decent performance (double-digit milliseconds), but 5%-10% of the queries will take a few hundred milliseconds to maybe a second or two at worst.

My question is this: would partitioning the table by hash(user_id) help me boost lookup performance by taking advantage of partition pruning?

score 2 · Answer 1 · answered Apr 11 '22 at 06:01

No, that wouldn't improve the speed of the query at all, since there is an index on that attribute. If anything, the increased planning time will slow down the query.

If you want to speed up that query as much as possible, create an index that supports both conditions:

CREATE INDEX ON playground (user_id, slug);

If slug is large, it may be preferable to index a hash:

CREATE INDEX ON playground (user_id, hashtext(slug));

and query like this:

SELECT *
FROM playground
WHERE user_id = '12345678'
  AND slug = 'some-slug'
  AND hashtext(slug) = hashtext('some-slug');
LIMIT 1;

Of course, partitioning could be a good idea for other reasons, for example to speed up autovacuum or CREATE INDEX.

Can Postgres table partitioning by hash boost query performance?

1 Answers1