[Tile-serving] [osm2pgsql-dev/osm2pgsql] Some research on middle performance (Issue #2110)

[Paul Norman]

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/2569965484 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20250103/7517bb4c/attachment.htm>