What are best practices for dealing with fragmentation in PostgreSQL?


I have a multi-tenant database whose workload includes on-the-fly aggregation for reports.

CREATE TABLE account (
    id serial PRIMARY KEY,
    name text NOT NULL

    id bigserial PRIMARY KEY,
    account_id int NOT NULL REFERENCES account (id),
    length numeric NOT NULL,
    weight numeric NOT NULL,
    color text NOT NULL,
    shape text NOT NULL

CREATE INDEX ON widget (account_id);

I want to run account-scoped queries on widget, e.g.

SELECT color, count(*), sum(weight) AS weight
FROM widget
WHERE account_id = 42094 AND 3 < length

Suppose there are 5,000 records in account and 100,000,000 records in widget.

Since PostgreSQL lacks clustered indices, an individual account's 20,000 widget records get scattered all over the entire table. With enough records in the table, PostgeSQL will have to read 20,000 pages for a query that under other circumstances would be very fast.

This would seem to be a common sort of situation...how do users deal with this?

2 Answers 2

CLUSTER widget USING widget_account_id_idx;

That will rewrite the table in index order, and your query will become faster.

There are down sides:

  • during CLUSTER, the table will be inaccessible

  • the order is not maintained, so you have to run CLUSTER everynow and then

  • For reference, because it sounded like OP wasn't aware that clustering exists in PostgreSQL.
    – J.D.
    Commented Dec 10, 2020 at 16:04
  • For 100m records that takes some time....is this normal to have to do? A 1+ hour rewrite seems burdensome. Commented Dec 10, 2020 at 16:49

Creating an index which includes all the columns your query needs could enable index-only scans, and the index should stay well-clustered even if the table itself does not. But this would require you vacuum the table aggressively to keep the visibility map tuned up. If the table turns over very quickly, this could be hard to do.

You could partition widget on account_id. It probably isn't feasible to give every account_id its own partition, but maybe using just 128 hash partitions or so would increase the density of a given account_id in its partition enough to speed things up. Or at least, make it easier to run CLUSTER on one partition at a time.

Depending on what kind of DML widgets undergoes, lowering fillfactor might cause the table to stay nicely clustered for a long time once CLUSTER is run once.

  • "this would require you vacuum the table aggressively to keep the visibility map tuned up" I assume that is necessary for good PostgreSQL performance anyway, so not a problem. Commented Dec 11, 2020 at 3:38
  • You always need to vacuum "enough", but the level that constitutes "enough" can be far higher if you depend on IOS for good performance, versus just needing to make space available for reuse and to prevent wrap around. The default autovac settings are likely not aggressive enough for good IOS performance on a very active table.
    – jjanes
    Commented Dec 11, 2020 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.