timescale.info#161 · Vacuum and analyse the large tables after 1-2% of them change
- timeline-app
- pull request
The last change for #135, as proposed in [the issue comment](https://github.com/openchronology/timeline-app/issues/135#issuecomment-6185695578), with no partitioning. ## What changes PostgreSQL's autovacuum defaults scale with table size: - it vacuums after 20% of a table's rows change, or after 20% more rows are added; - it refreshes the planner's statistics after 10% change. At a billion rows, that's 200 million rows before a vacuum. `server/schema.sql` now sets these on 18 large tables: the six index tables, their `_retired` versions, and the history version and override tables. | Setting | Default | Now | | --- | ---: | ---: | | `autovacuum_vacuum_scale_factor` | 0.2 | 0.02 | | `autovacuum_vacuum_insert_scale_factor` | 0.2 | 0.02 | | `autovacuum_analyze_scale_factor` | 0.1 | 0.01 | The migration sets each option only on a table that has no value of its own for it: - An administrator's own tuning survives upgrades. - When nothing needs setting, the migration runs no `ALTER TABLE`, so it takes no lock that would conflict with autovacuum. ## Testing - `test/postgres.mjs` checks that all 18 tables carry the settings. It also checks that re-running the schema keeps an administrator's value. - I ran `openchronology-migrate` twice against the local test database: - the second run left these tables' `pg_class` rows untouched (same `xmin`); - a table whose options were reset got them back; - a value set by hand was kept. - Against the 1M-moment benchmark database, the migration took 3.5 s in total. - The full database test suite passes locally. ## Docs `docs/administration.md` explains the settings and that values set by hand survive upgrades. Closes #135.
Sources
Open #161 · Vacuum and analyse the large tables after 1-2% of them change in the timeline