[Tile-serving] [osm2pgsql-dev/osm2pgsql] Practices to handle tile expiration (Discussion #2495)

[Candid Dauth]

After finding that most docker images and manuals seem to use quite outdated software versions and methods to import OSM data and render tiles from it, I decided my own image: [FacilMap/openstreetmap-tile-server](https://github.com/FacilMap/openstreetmap-tile-server). One thing that I still need to improve is the expiration of tiles. I wasn’t able to find any real-world examples using the new expire table feature (Gemini even insisted that such a feature does not exist), so my work on this is entirely based on the osm2pgsql user manual and my own thoughts.

So far I have defined a single expire table for my map style. I run the following bash script to expire the tiles from this table:
```bash
EXPIRE_MAP=mymap
EXPIRE_TABLE=mymap_expire
EXPIRE_WAIT=60
EXPIRE_DELETE_FROM=13

while true; do
	date="$(date -u +'%Y-%m-%dT%H:%M:%SZ')"
	if psql -Aqtc "select zoom || '/' || x || '/' || y from \"${EXPIRE_TABLE}\" where last < '${date}';" | render_expired -m "${EXPIRE_MAP}" -c /etc/renderd.conf -d "${EXPIRE_DELETE_FROM}"; then
		if ! psql -Aqtc "delete from \"${EXPIRE_TABLE}\" where last < '${date}';"; then
			echo "Cleaning up expiration table failed." >&2
		fi
	else
		echo "Expiring tiles failed." >&2
	fi

	sleep "$EXPIRE_WAIT"
done
```

So I generate a list of tiles whose `last` time is at least one second before the current time (because more tiles might become expired in the current second after reading out the list). Then I expire this list of tiles (which can take several minutes depending on the configuration). If expiring the tiles succeeds, I delete all rows from the expire table whose `last` time is still at least one second before the time when I queried the table the first time. Any tiles that were expired (again) while the tile expiration was running would have a later `last` date now, so this way they will be handled (again) during the next iteration.

Firstly, I would like some feedback on my script. Is this how expiration is commonly done? One thing that I know I could still improve there is calling the expiration each time right after `osm2pgsql-replication` instead of in a separate infinite loop.

-----

Now to my actual question. I have one map where I render a large variety of way types at different zoom levels. On the one end of the spectrum, all motorways globally are rendered starting at zoom level 6. On the other end of the spectrum, most footways globally are rendered starting at zoom level 14. This is equivalent to the OSM Carto style, but I only render a small subset of it, which mostly consists of motorways, footpaths and some other roads.

Since rendering tiles takes quite long for this map on my server, especially at low zoom levels, I prerendered all tiles up to zoom level 9 and configured the expiration script to rerender expired tiles up to zoom level 9 as well and delete expired tiles for zoom levels above. To my surprise, this caused my server to basically be busy rerendering expired tiles all the time, causing significant CPU and memory/swap usage on my server. At first I was confused, since at zoom level 9 I only render motorways and a few major roads and I didn’t expect those to change so often, but now I think I understand what’s going on. Since I have only a single expire table that does not distinguish between zoom levels, a simple change to a footpath anywhere causes tiles containing it to be expired, even if those tiles are at a zoom level so low that they don’t actually contain that footpath. And I imagine small things like footpaths change in many places all the time, which is why so many tiles at zoom levels <= 9 keep getting expired that my server can barely keep up with it. So what I need is a more sophisticated mechanism for tile expiration where the minimum zoom level of a feature is considered when determining what tiles to expire.

I found out that I can configure a [`minzoom`](https://osm2pgsql.org/doc/manual.html#defining-and-using-expire-outputs) option for an expire table. Because I can only define it for the whole table, it means that I basically need one expire table per minimum zoom level. As a consequence, I also need one feature table per minimum zoom level that is connected to the expire table. Right now I have a single table for ways with a `highway` column to determine the way type. Now I will need separate `ways_minzoom6`, `ways_minzoom8`, `ways_minzoom9`, … tables with corresponding expire tables where each way type is put into the table corresponding to the minimum zoom level where it is rendered. I have several questions about this:
* Is there a technical reason why I cannot define the expire minzoom in the `:insert()` call instead of defining a fixed minzoom for the expire table? That would make my setup much easier, since I would be able to keep using a single way table and a single expire table.
* Might splitting up my ways into about 10 different tables have a negative impact on the rendering of my tiles? I don’t know enough details about how geospatial indexes work in PostGIS, but I could imagine that querying features from 10 different tables to render a tile would be slower than querying them from a single table?
* Are there examples of how to query and clear multiple expire tables in my expiration script? I imagine I need to adapt my postgres query to query all expire tables at the same time, but still return the tiles uniquely, since the same tile might appear in multiple tables. After the expiration I need to clear the tiles separately for each table, since I assume postgres does not allow deleting from multiple tables in one query.

-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2495
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/repo-discussions/2495 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20260708/10e7ae90/attachment.htm>