To: pgsql-admin@postgresql.org
From: Rory McCann <rory@technomancy.org>
Subject: Index bloat? Try pgindexrebuild, a production friendly index
 debloater
Date: Tue, 6 Sep 2016 09:16:09 +0200
Lines: 56
Message-ID: <nqlqfn$f78$1@blaine.gmane.org>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="wOjFWM5M42xgCKeLSX7aP2UKenBChQcuI"
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.2.0
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--wOjFWM5M42xgCKeLSX7aP2UKenBChQcuI
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hello all,

If you use/update a 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. I've had
this problem when using Nominatim, an open source address geocoder for
OpenStreetMap/

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
database, so I wrote pgindexrebuild:

    https://github.com/rory/pgindexrebuild

It uses 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. =E2=98=BA Suggestions welcome!

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

--=20


--wOjFWM5M42xgCKeLSX7aP2UKenBChQcuI
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.22 (GNU/Linux)

iQEcBAEBAgAGBQJXzm05AAoJEOrWdmeZivv2ar4H/0vlw2hXT4asqLN6VjmsvBlq
e5H4RNWOkOHOifYDpW8+OtyofniWRSR1xqAuumFuw8v+5WESAkQPux10CnWGSdc3
9CuJa3/Pfz7Kj+05/6I6Xe/37dlHQFeNra5QC8tcN3/X+s+fQUR5tFC0NBDlTLJ0
xA56OzhPuznDoeqpVQb+xLuETh5g1+4a8fVZez0fHCVTd+gsl3HFX3t0mpbmaQTe
ENxG9UeSOgvdRFXW3EXYZGaJ+rgJIEIcDMMRYnE6eFbxcho4YYWl1sW6inNty0p2
h+D9hC/bBFrCzF4pC2xjnxFKLU5I3JQzxcV2RdjxWUFOmvvXvaUyWZ28Q6aBzGY=
=AY+R
-----END PGP SIGNATURE-----

--wOjFWM5M42xgCKeLSX7aP2UKenBChQcuI--