Zabbix + Postgresql = Do you reindex? How?

[Linwood]

Using Zabbix on Ubuntu with about 650 hosts, 10k items and 140 values per second (+/-).

It's been running almost a year, and seems pretty stable. Disk space has grown to about 50G of data, I have not partitioned it. Performance is adequate, and house keeping is keeping up nicely (with some tweaking). I'm keeping mostly about 7 days history (sometimes 30 days) and a year of trends.

Auto-vacuum seems to be doing a credible job, though I am thinking of making it a bit more aggressive, but that's a detail.

My question is about indexes. The reindex in Postgresql (out of the box) cannot be used while the database is active. I'm used to databases that allow more online maintenance. I could shut down Zabbix for a few hours I guess, but that's not all that desirable.

How do people handle this running Postgresql with Zabbix?

Do you shut down periodically for maintenance?

Ignore the issue so long as it is running?

You partition and trust that to be adequate, and other tables not to need it?

There's also a third party tool, pg_repack, that seems well respected. With the recognition that all of Postgresl is an open source project, it does give me a bit of pause that this is external to the main system and not incorporated. Are people using it? Any issues running concurrent with Zabbix up?

[Linwood]

So, not a lot of interest? Or everyone as puzzled?

I did some reading and found this:

https://github.com/ioguix/pgsql-bloat-estimation

There are some plain SQL queries in there which give various bloat estimate numbers (size, factor, etc.).

What's less clear is what they mean, how much is too much, how much is just pending auto-vacuum, etc. My tables had been un-maintained (other than auto-vacuum) for about a year.

Out of curiosity I downloaded pg_repack and experimented. I first tried running it live, with zabbix active. It worked great on small tables, but I hit trends_uint, which is one of the larger ones, and things ground to a halt, zabbix started complaining the database was down. I stopped zabbix, let it finish (was a bit worried about halting mid-way though in retrospect looking at the code it looks pretty interrupt-safe).

Then I just decided, since zabbix was down, to repack everything, and did. A couple hours later it was all done, and I let zabbix go back to work.

Insert anti-climax here. Nothing obvious different. No obvious quicker response. I'm waiting for a couple days of linux and zabbix data to accumulate, but mostly nothing big.

There does seem to be a slight decrease in overall, steady-state CPU. Maybe. A few percent out of 25% or so average. Maybe.

I did gain a bit of space but that's the vacuum-full aspect of the tool, not the reindex. Could have done that anytime I wanted to shut down, and it will just get taken away again over time.

In other words, pg_repack seems a reasonable tool, perhaps one I could run online if I ran it more often or with less concurrency (I used four processes). But also it's not at all obvious I needed to do so.

Once I get a couple weeks statistics if I see something significant different will report back. Note I'm just looking at gross measures of system performance, I am not digging into individual query IO's or such; to me this was about routine system level maintenance, not trying to fix a specific slowdown.

And short version: No sign it did any real good, but it did no harm either, but I do like pg_repack, lots of flexibility offered by it, seems well done.