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.

[cdauth]

Thanks for your answers. So the common practice seems to be to not have one feature/expire table per minimum zoom level, but to create a balance between performance and simplicity, group the minimum zoom levels together into (usually 3) groups and so have 3 feature tables and 3 expire tables. While having multiple tables certainly adds a bit of complexity to the Lua script and the CartoCSS project, this complexity is far outweighed by the possibility to add the feature tables as separate layers in the CartoCSS project and set the minimum zoom level for those layers as well. This way at low zoom levels, Mapnik will need to query only the low zoom level tables. I found this to cause a great performance improvement at low zoom levels, and on the other hand I didn’t notice a performance penalty at high zoom levels where Mapnik now has to query several tables at once.

In my case, it was sensible to use 4 tables for minzoom levels 0, 10, 13 and 16. I created these through 4 identical osm2pgsql.define_way_table() calls, with their only difference being that each of them references a separate expiration table. For the expiration tables, I made sure to specify the minzoom property:

local cycling_restrictions_expire2 = osm2pgsql.define_expire_output({
	minzoom = 10,
	maxzoom = maxzoom,
	table = 'cycling_restrictions_expire2'
})

In my project.mml file, I added the 4 feature tables as separate layers with a minzoom. I found out that the minzoom property has to be under a properties property. This was not clear from the documentation, and having minzoom directly on the layer did not have an effect but also did not raise any errors.

    -
        id: "cycling_restrictions_lines2"
        name: "cycling_restrictions_lines2"
        srs: "+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over"
        geometry: "linestring"
        properties:
            minzoom: 10
        Datasource:
            ...

In my style.mss, I did not have to change much. Where I previously had a #cycling_restrictions_lines selector in which all the styles were grouped, I simply changed that selector to #cycling_restrictions_lines1, #cycling_restrictions_lines2, #cycling_restrictions_lines3, #cycling_restrictions_lines4. This led to a quadrupling of the resulting mapnik.xml file, since CartoCSS seems to resolve all nested rules and create a separate entry in the XML file for them, but I’m not aware of any negative consequences of that.

Now to the tile expiration. In my specific setup, it was beneficial to specify one pattern for the table name and then look up the expiration tables automatically, rather than having to specify the names of all the expiration tables. I used the following code to look up the tables:

expire_table_pattern="cycling_restrictions_expire%"
mapfile -t expire_tables < <(psql -Aqtc "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name SIMILAR TO '${expire_table_pattern}';")

expire_tables is now a bash array of the table names. I then used the following code to actually expire the tiles:

EXPIRE_MAP=mymap
EXPIRE_WAIT=60
EXPIRE_DELETE_FROM=13

join() {
	echo "$2${3+$(printf "$1%s" "${@:3}")}"
}

while true; do
	date="$(date -u +'%Y-%m-%dT%H:%M:%SZ')"
	mapfile -t queries < <(printf "SELECT zoom || '/' || x || '/' || y AS tile_path FROM %s WHERE last < '${date}'\n" "${expire_tables[@]}")
	if psql -Aqtc "$(join " UNION " "${queries[@]}")" | render_expired -m "$EXPIRE_MAP" -c /etc/renderd.conf -d "$EXPIRE_DELETE_FROM"; then
		for table in "${tables_ref[@]}"; do
			if ! psql -Aqtc "delete from \"${table}\" where last < '${date}';"; then
				echo "Cleaning up expiration table ${table} failed." >&2
			fi
		done
	else
		echo "Expiring tiles failed." >&2
	fi

	sleep "$EXPIRE_WAIT"
done

Like in my original code, I look up the expired tiles with a last date before the current date, but now through the UNION call I select them from multiple tables at once, and tiles that appear in multiple tables are only returned once. After the successful expiration, I delete those tiles from each table.