[ZBXNEXT-3550] Use BRIN index and partial indexing on history tables Created: 2016 Nov 15  Updated: 2024 Dec 03  Resolved: 2022 Jul 10

Status: Closed
Project: ZABBIX FEATURE REQUESTS
Component/s: Server (S)
Affects Version/s: 2.2.15, 3.0.5, 3.2.1
Fix Version/s: None

Type: New Feature Request Priority: Trivial
Reporter: D.Spindel Ljungmark Assignee: Unassigned
Resolution: Won't Do Votes: 4
Labels: index, performance, postgres
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

PostgreSQL 9.5+



 Description   

With PostgreSQL 9.5 a new kind of index was introduced, block range index.

BRIN indexes are a fraction (2-5% ) of normal BTree indexes, and only store min/max range data for a set of data/blocks.

On a naturally time-ordered table ( history, history_uint, etc) these indexes are very efficient, and can serve almost all queries.

Except!

Unbounded Lastvalue (limit=0) or sorted such (Select itemid, clock from history where itemid=1, order by clock desc, limit 1 offset 0) , will require parsing the entire table to get the data.

WIth the default ZBX_HISTORY_PERIOD=86400, an partial index on history* tables that covers "clock > now()-86400". can be created, which will efficiently cover lookups that request data with a limit.

More writeup on this approach is done here:
https://www.modio.se/optimizing-disk-usage-of-zabbix-and-postgresql.html

 Comments   
Comment by Oleksii Zagorskyi [ 2016 Nov 15 ]

I've read the article, thanks.
One point I wanted to share is - history older than one day maybe requested by different zabbix_server processes for different cases.
Not having corresponding indexes for those periods may cause performance issues.

Also, as for insertion values in the proper order - it's possible that server gets history from proxies collected in the past.
Keep that in mind please.

Comment by D.Spindel Ljungmark [ 2016 Nov 16 ]

So far we haven't seen that as a problem, and it's generally not a problem when it happens for single use requests, (while the response is slower than before, it's still not outrageously slow), but when you're doing that kind of lookup for 1500-6000 items, the sum becomes slow.

Proxies in the past are not a problem, as the data will be chronologically (mostly) ordered for those meters

The ordering is not a requirement Globally, only that individual items are (mostly) chronologically ordered.

Comment by D.Spindel Ljungmark [ 2016 Nov 17 ]

I've updated the article with some details and now done this change on a few more of our production databases.

We got a reduction in size by totally 24% ( total disk usage of postgres) from changing to BRIN indexes for history* tables.

Comment by Alexei Vladishev [ 2022 Jul 10 ]

We did some experiments with PostgreSQL BRIN indexes a year or two ago and actually got much worser performance comparing to traditional indexes. You may want to use TimescaleDB for more efficient storage and automated housekeeping instead.

I am closing this ticket.

Comment by LivreAcesso.Pro [ 2024 Dec 03 ]

Can we combine BRIN indexing with TimescaleDB?

Comment by LivreAcesso.Pro [ 2024 Dec 03 ]

Do we have some changes on BRIN performance until PostgreSQL 17?

Generated at Sat Aug 02 13:27:53 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.