[OSM-dev] Nominatim Index bloat? Try pgindexrebuild, a production friendly index debloater

Rory McCann rory at technomancy.org
Fri Aug 19 11:30:23 UTC 2016


Hello all,

If you use/update Nominatim (or any PostgreSQL) server for a long time,
one problem you may come across is index bloat. Your database will grow
in size on your disk, your indexes getting larger on disk. It's no fun
when your database fills up the disk and won't start anymore.

There are several solutions to this, but I wanted a way to fix index
bloat while still being able to use the database as a production
nominatim (or tile) database, so I wrote pgindexrebuild:

    https://github.com/rory/pgindexrebuild

It uses PostgreSQL's CREATE INDEX CONCURRENTLY to create a new index and
replace the old bloated one afterwards. The CONCURRENTLY ensure that you
can still read and write from the table while it is creating a new
index. It has some nice things like lock files, bloat thresholds, and
logging, to allow you to put it in cron and forget about it. "Set up and
forget" is a design goal.

Although I'm using this often, I'm always open to suggestions about
braindead things I might be doing. ☺ Suggestions welcome!

This is inspired by [pgtoolkit](https://github.com/grayhemp/pgtoolkit)
which does the same thing for table bloat.

-- 

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 490 bytes
Desc: OpenPGP digital signature
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20160819/fa8950fc/attachment.sig>


More information about the dev mailing list