Open mjtrangoni opened 4 years ago
Are "btree" indexes still relevant ? or is it better to use "BRIN" ?
@mjtrangoni ,
Great to hear that you are taking the initiative of upgrading to Zabbix 5.0! I attended some of their online webinars and it looks fantastic. I have not tried to upgrade yet.
My opinion is that you are making a good choice. It is feasible to upgrade BUT always test the upgrade first! Test at least for a few weeks and ensure all your custom scripts and integrations work correctly. Otherwise you risk spending more time fixing issues and AFAIK there is no way to downgrade. Take a pg_dump
of the production instance, pg_restore
it on your dev instance and run through some common scenarios. Also check that pg_partman
and other postgres modules work correctly.
Regarding SCHEMA changes in Zabbix 5.0 (create.sql.gz), it appears you are right:
CREATE TABLE history (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
value DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
ns integer DEFAULT '0' NOT NULL
);
CREATE INDEX history_1 ON history (itemid,clock);
CREATE TABLE trends (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
num integer DEFAULT '0' NOT NULL,
value_min DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
value_avg DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
value_max DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
PRIMARY KEY (itemid,clock)
);
Thus what you mentioned should be correct. Partitioning on the clock column should work well.
I will look to upgrade my development instance to version 5.0 and PostgreSQL version 12 in the next few weeks and update the notes here.
@rustequal , regarding BRIN
indexes that is purely subjective. The only reason I would suggest BRIN
indexes would be if the partition table is very big. PostgreSQL index defaults to B-tree indexes.
Let me know if you have any more questions.
Thanks & Best Regards, Andreas
I just upgraded PostgreSQL in my 4.4 installation from 11 to 12 and there was a small issue that i overlooked that might be worth mentioning.
You need to make sure you have pg_partman >= 4.2.0 and that you upgrade the extension in pg:
SELECT * FROM pg_available_extensions WHERE name = 'pg_partman';
name | default_version | installed_version | comment
pg_partman | 4.4.0 | 4.0.0 | Extension to manage partitioned tables by time or ID
ALTER EXTENSION pg_partman UPDATE TO '4.4.0';
Otherwise you will get errors like:
postgres@zabbix ERROR: column "relhasoids" does not exist
@Jonybat ,
Thanks for mentioning that. I will make sure to include that in the notes.
Regards, Andreas
PSQL 12.3 delivers 4.4.0 already
zabbix=> select * from pg_available_extensions WHERE name = 'pg_partman';
name | default_version | installed_version | comment
------------+-----------------+-------------------+------------------------------------------------------
pg_partman | 4.4.0 | 4.4.0 | Extension to manage partitioned tables by time or ID
(1 row)
Yes, as i said above, in my case it was an upgrade from PSQL 11, and i had to upgrade the extension manually in psql.
I also ran into the issue described here in https://github.com/pgpartman/pg_partman/issues/91
Scheduled partman maintenances were failing with deadlocks, but running maintenances manually from psql would work. I changed the constraint_valid
to false for all partitions, as described in the last comment of that issue, and it started working. This never happened before the upgrade to PSQL 12, so i guess its also worth mentioning.
This is little off/topic, but I am really curious about the upgrade as there are few interesting changes in v12, performance and size of the tables. Still had no time to check in my test instance.
https://www.cybertec-postgresql.com/en/b-tree-index-improvements-in-postgresql-v12/
And for real bleeding edge - anyone bold enough to check v13? https://www.cybertec-postgresql.com/en/b-tree-index-deduplication/
I still haven't had the time either to test out the upgrade process. PostgreSQL v13 also comes with some interesting native logical replication support for partitioned tables; reference.
I haven't looked at this due to my priorities but hope to revisit this soon.
In particular I would like to migrate the Zabbix infrastructure to cloud (Azure) in order for a more reliable environment.
Right now Azure supports Single Server PostgreSQL version 11 with extensions pg_partman and TimescaleDB (single server extensions). That is awesome. BUT I wish it were PostgreSQL 12 supported too and available in the Flexible Server option; it isn't as of this writing (flexible server extensions).
Additionally writing the terraform templates for this will make migration easier.
IMHO using timescaleDB is a better option than pg_partman purely because:
Again the only thing holding me back is PostgreSQL 11 only support for Azure in Single Server instance (not preferred) but I'll see where I can go with it.
Hi @Doctorbal,
While trying to prepare the upgrade to Zabbix 5.0, I faced the issue of the new double precision.
This requires PSQL >= 12, as said here. See the patch here.
What is your opinion about this? Would you mind making a paragraph for this case here.
I have also to test this well before the final move. Is this correct?