How to deal with fragmentation in PostgreSQL?

Posted on

Question :

What are best practices for dealing with fragmentation in PostgreSQL?


Example:

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
);

CREATE TABLE widget (
    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
GROUP BY 1

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?

Answer :

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

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.

Leave a Reply

Your email address will not be published.