Practices to handle tile expiration

[cdauth]

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. 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:

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 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.

[joto]

It is quite unusual to have different sets of data for each different zoom level. Usually for something like highways you have two or three different tables, one with all data for higher zoom levels, one for middle zoom levels and one for low zoom levels if needed. The tables for mid zoom levels don't need a lot of the data (for instance you usually don't render oneway arrows at that level) so they might contain aggregated and simplified data anyway for faster rendering. So the tables are quite different anyway and it makes sense to do expire on the table level.

Because tables for lower zoom levels are so much smaller than the tables with complete data, updating and rendering those tables is much much faster in practice, having many tables isn't a problem.

You should do expire after each data update run, doing this every second doesn't improve anything, because you get new data at most once per minute.

[pnorman]

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.

This is correct. Any changes in a table will cause expiry of tiles for the objects changed. osm2pgsql doesn't know that some objects aren't used on some zoom ranges because of SQL in later stages of your processing.

• 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.

This would be fairly easy to do for insert but would break when objects are deleted.

I found out that I can configure a minzoom 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.

This is not correct. You'll generally have one table with roads shown on low zooms (e.g. motorway and trunk), one table with roads shown on medium zooms (e.g. motorway to secondary), and one table with roads shown on high zooms (e.g. everything). This is also a significant performance boost to low-zoom rendering. It's also easier to step from this into using osm2pgsql generalization.

Or you can go the way of the OSMF servers and just re-render low zooms nightly.