In addition to what tilerator requires, most styles will benefit from custom partial indexes which should be specified by the style.
You want to consider the need to issue statements to
- Create indexes with fillfactor 100 for imports that are not updated, e.g.
CREATE INDEX IF NOT EXISTS planet_osm_roads_admin ON planet_osm_roads USING GIST (way) WITH (fillfactor=100) WHERE boundary = 'administrative';
- Create indexes concurrently e.g.
CREATE INDEX CONCURRENTLY planet_osm_roads_admin ON planet_osm_roads USING GIST (way) WHERE boundary = 'administrative';
- Reindex e.g.
REINDEX planet_osm_roads_admin;
- Reindex concurrently e.g.
ALTER INDEX planet_osm_roads_admin RENAME TO planet_osm_roads_admin_old; CREATE INDEX CONCURRENTLY planet_osm_roads_admin ON planet_osm_roads USING GIST (way) WHERE boundary = 'administrative'; DROP INDEX planet_osm_roads_admin_old;
All of these are tasks which will need to be done by developers, when setting up new databases, or periodically as part of normal maintenance.
On 6/22/2016 12:38 AM, Pine W wrote:
Forwarding
Pine
---------- Forwarded message ---------- From: "Guillaume Lederrey" <glederrey@wikimedia.org mailto:glederrey@wikimedia.org> Date: Jun 21, 2016 12:03 Subject: [discovery] Index management for Maps To: "Operations Engineers" <ops@lists.wikimedia.org mailto:ops@lists.wikimedia.org>, "A public mailing list about Wikimedia Search and Discovery projects" <discovery@lists.wikimedia.org mailto:discovery@lists.wikimedia.org> Cc:
Hello!
I need some feedback from my fellow Ops on how to manage indexes on Maps.
Context:
Maps imports OpenStreetMap data in a Postgresql database. This import is done with osm2pgsql, which takes care of creating the schema, populating tables and creating a few indexes.
Some additional indexes are required to support the specific functionalities of Tilerator. So far, those indexes have been created manually and have not been tracked.
The enhancements to the schema created by osm2pgsql are minor (a few index and functions), so we probably need a lightweight solution.
Proposal:
A few idempotent scripts are versionned in osm-bright.tm2source [1]. Those scripts are executed after review by Ops, at the request of the project. We don't use a full schema migration process, because at this point there isn't really a need for it on this project.
Does this look reasonable to you? Feedback welcomed, shoot me down if you have to...
Thanks for your help!
Guillaume
[1] https://github.com/kartotherian/osm-bright.tm2source/tree/master/sql
-- Guillaume Lederrey Operations Engineer, Discovery Wikimedia Foundation
discovery mailing list discovery@lists.wikimedia.org mailto:discovery@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/discovery
Maps-l mailing list Maps-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/maps-l