Ideas for speeding up remote Postgres/PostGIS data access in TileMill

ideas.md

Pulling data from remote Postgresql can be really slow. Many people try this, once.

That said, here is a mini guide of things to think about if you are pulling remote data.

Big ideas

• If network between you and the remote data is slow, try running TileMill on a remote machine. For example, if your data is on Amazon's network, short of running TileMill on the same machine, at least running TileMill on another AWS machine would be faster. This is because network access within Amazon's cloud is going to be reliably faster than between AWS and the outside. Here is a guide for how to run TileMill from the command line on an Ubuntu Server (with no desktop) that works with AWS: http://www.mapbox.com/tilemill/docs/guides/ubuntu-service/. For details on setting up an AWS instance see https://gist.github.com/springmeyer/b0bbcd976378cf3e4e44 and for details for how to automate this see https://github.com/miccolis/aws-tilemill.

• Debug locally to make sure you know how fast data should show up in a normal situation. It's a very good bet that data that is remote is going to be much slower to load than data that is local, but a minor difference in the SQL query that fetches data might mean hundreds of thousands more rows of data are fetches. Make sure you have a baseline.

Project Settings

• Before trying to view the data set your project min zoom level high - something like 10, or even 16 - as high as possible since the lower zooms are likely going to pull more data and TileMill will be stuck waiting for it to some over http. This way you will not as likely end up stalling your machine when you first open a project and the map defaults to the lowest zoom.

• Choose metatile 1 in your project settings. This will mean that only the data that is needed for a single tile is fetched at once (rather than larger blocks of data). This can help make the map view more responsive when previewing data than higher metatile values. See http://www.mapbox.com/tilemill/docs/guides/metatiles/#choosing_your_metatile_size for more details

CartoCSS settings

• Set the Map buffer-size to 0 in your CartoCSS; This will likely lead to (more) cut off labels, but it will also result in less data being fetched and faster rendering so it can help when previewing the map (unless of course you are trying to preview label rendering!)

Layer Settings

• TileMill layers support "Advanced options" a hidden feature that you usually should not care about, but which allows the expert user to pass arbitrary options down from TileMill to Mapnik.

  • If you data is so big that it takes forever to load you can set the row_limit=N option, which will truncate the query at N number of rows. This will lead to some parts of your map missing data, but it can still be useful for seeing how rendering looks where data does show up. Just remember to remove the option before exporting!
  • Also potentially useful is cursor_size. cursor_size=1000 tells Mapnik to fetch data in batches of 1000. This can slow down overall query times, but can also safe on memory and so in cases where you are low on memory to hold large resultsets it can increase query times.

Also in the case of PostGIS data access there are a number of details about the table that TileMill/Mapnik figures out automatically, but these require issueing queries. In the case that your connection to Postgres is slow these queries can slow down access. They are queries to detect the projection of the data, the field name of the geometry type, and the type of the geometry type. You can pass the correct values to the Advanced Options to allow TileMill to skip these queries and speed up layer initialization. Relevant options are:

• geometry_field. If using OSM data this is the field way and if you imported data using shp2pgsql this field is usually called geom. Pass geometry_field=way to the Advanced options.
• srid. If you data is in spherical mercator pass either srid=900913 or srid=3857.