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 ); 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
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
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?
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:
CLUSTER, the table will be inaccessible
the order is not maintained, so you have to run
CLUSTEReverynow 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.