The advice I've had from PostgreSQL experts is that for optimal performance you
should run VACUUM on a freshly-loaded table. It does maintenance work other
than dead tuple cleanup. The visibility map, fsm are, and xid information are
some to the things updated.
However, although vacuum does important work, I'm not sure any of these really
matter for the typical osm2pgsql workload. The [docs
state](https://www.postgresql.org/docs/17/routine-vacuuming.html#VACUUM-BASICS)
> PostgreSQL's [VACUUM](https://www.postgresql.org/docs/17/sql-vacuum.html)
> command has to process each table on a regular basis for several reasons:
>
> 1. To recover or reuse disk space occupied by updated or deleted rows.
> 2. To update data statistics used by the PostgreSQL query planner.
> 3. To update the visibility map, which speeds up [index-only
> scans](https://www.postgresql.org/docs/17/indexes-index-only-scans.html).
> 4. To protect against loss of very old data due to transaction ID wraparound
> or multixact ID wraparound.
1 does not matter because there are no updated or deleted rows at this point in
time. 2 is taken care of by a separate ANALYZE. The queries we run against the
middle (or rendering) tables don't work for index-only scans. 4 is a valid
concern, regular auto-vacuums will take care of that, and osm2pgsql DBs don't
normally have enough transactions/second for wraparound to matter.
I still can't figure out if the FSM matters or not for us
--
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/issues/2110#issuecomment-2569965484
You are receiving this because you are subscribed to this thread.
Message ID: <osm2pgsql-dev/osm2pgsql/issues/2110/[email protected]>
_______________________________________________
Tile-serving mailing list
[email protected]
https://lists.openstreetmap.org/listinfo/tile-serving
