pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex bloat? Try pgindexrebuild, a production friendly index debloater
2+ messages / 2 participants
[nested] [flat]
* Index bloat? Try pgindexrebuild, a production friendly index debloater
@ 2016-09-06 07:16 Rory McCann <rory@technomancy.org>
0 siblings, 1 reply; 2+ messages in thread
From: Rory McCann @ 2016-09-06 07:16 UTC (permalink / raw)
To: pgsql-admin
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. ☺ Suggestions welcome!
This is inspired by [pgtoolkit](https://github.com/grayhemp/pgtoolkit)
which does the same thing for table bloat.
--
Attachments:
signature.asc (application/pgp-signature, 489B) download
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Index bloat? Try pgindexrebuild, a production friendly index debloater
@ 2016-09-11 19:58 Gunnar "Nick" Bluth <gunnar.bluth@pro-open.de>
parent: Rory McCann <rory@technomancy.org>
0 siblings, 0 replies; 2+ messages in thread
From: Gunnar \"Nick\" Bluth @ 2016-09-11 19:58 UTC (permalink / raw)
To: pgsql-admin
Am 06.09.2016 um 09:16 schrieb Rory McCann:
> Hello all,
Hi Rory,
>
> 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
Sounds promising.
You might want to post that to -announce as well?
Cheers,
--
Gunnar "Nick" Bluth
RHCE/SCLA
Mobil +49 172 8853339
Email: gunnar.bluth@pro-open.de
_____________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.
Ten years later they are choosing Windows over UNIX.
What part of that message aren't you getting? - Tom Payne
Attachments:
0x3289338C.asc (application/pgp-keys, 1.7K) download
signature.asc (application/pgp-signature, 472B) download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2016-09-11 19:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-09-06 07:16 Index bloat? Try pgindexrebuild, a production friendly index debloater Rory McCann <rory@technomancy.org>
2016-09-11 19:58 ` Gunnar "Nick" Bluth <gunnar.bluth@pro-open.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox