Zabbix PostgreSQL version 11 Native Partitioning

history - table that stores all numeric (float) values
history_uint - table that stores all integer values
history_log - table that stores all log values
history_text - table that store all text values
history_str - table that stores all string values
trends - table that stores all numeric (float) values
trends_uint - table that stores all numeric (unsigned integers)

History and trends are the two ways of storing collected data in Zabbix.

Whereas history keeps each collected value, trends keep averaged information on hourly basis and therefore are less resource-hungry.

The general strong advice is to keep history for the smallest possible number of days and that way not to overload the database with lots of historical values.

Instead of keeping a long history, you can keep longer data of trends. For example, you could keep history for 14 days and trends for 5 years.

While keeping shorter history, you will still be able to review older data in graphs, as graphs will use trend values for displaying older data.

Trends is a built-in historical data reduction mechanism which stores minimum, maximum, average and the total number of values per every hour for numeric data types.

Trends usually can be kept for much longer than history. Any older data will be removed by the housekeeper.

When server flushes trend cache and there are already trends in the database for this hour (for example, server has been restarted mid-hour), server needs to use update statements instead of simple inserts. Therefore on a bigger installation if restart is needed it is desirable to stop server in the end of one hour and start in the beginning of the next hour to avoid trend data overlap.

Zabbix updates trends immediately after receipt of new value. Therefore, all information stored in trends is always valid and up-to-date (updated in realtime).

Zabbix generates all graphs from detailed history if period is less than 24 hours, and the trends are used for graphs having period longer than 24 hours.

Zabbix Partitioning Considerations

Before performing partitioning in Zabbix, several aspects must be considered:

Time partitioning will be used for table partitioning.
Housekeeper will not be needed for some data types anymore. This Zabbix functionality for clearing old history and trend data from the database can be controlled in Administration | General Housekeeper.
The values of History storage period (in days) and Trend storage period (in days) fields in item configuration will not be used anymore as old data will be cleared by the range i.e. the whole partition. They can (and should be) overridden in Administration | General Housekeeper - the period should match the period for which we are expecting to keep the partitions which is monthly
Even with the housekeeping for items disabled, Zabbix server and web interface will keep writing housekeeping information for future use into the housekeeper table. To avoid this, you can add trigger for this table after you add the data there:

CREATE TRIGGER housekeeper_blackhole
    BEFORE INSERT ON housekeeper
    FOR EACH ROW
    EXECUTE PROCEDURE housekeeper_blackhole();

With the following procedure:

CREATE OR REPLACE FUNCTION housekeeper_blackhole()
    RETURNS trigger AS
$func$
BEGIN
    RETURN NULL;
END
$func$ LANGUAGE plpgsql;

Prepare PostgreSQL Database

Download and install the PostgreSQL Core Distribution that supports Native Partitioning. As of this writing it is PostgreSQL v11.1.
Tune postgresql.conf to ensure enable_partition_pruning = on. The default should be on. This enables or disables the query planner's ability to eliminate a partitioned table's partitions from query plans, thus improving performance.
Copy the pg_hba.conf from the old database to the new database and tune the database appropriately. You can use the following tools:

pgtune
postgresqltuner.

Turn of Zabbix Housekeeper in the Frontend as mentioned in the Zabbix Partitioning Considerations.
Shut down (or stop) the Zabbix Server and Zabbix Frontend from writing to the database.
Back up the original database!!!

Install PostgreSQL v11

wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt install postgresql-11
sudo passwd postgres
sudo su - postgres
ssh-keygen # passwordless

Prepare Zabbix Database

sudo -i -u postgres
postgres@zbxdatabase:~$ createuser -P -s -e zabbix
postgres@zbxdatabase:~$ psql
postgres=# create database zabbix;
postgres=# GRANT ALL PRIVILEGES ON DATABASE zabbix to zabbix;
postgres=# \q

Time Series Partitioning

Partitioning syntax was introduced in PostgreSQL 10. It is very effective for INSERTs and large/slow SELECT queries, which makes it suitable for time series logging.

PostgreSQL Default Partition

With PostgreSQL version 11 it is possible to create a "default" partition. This stores rows that do not fall into any existing partition's range. This is ideal since the partitioned range might not include specific data which the default will then pick up. This is automatically done with pg_partman. From there one can delete all data from the table via the following example:

DELETE FROM public.history_p2018_11

Optional - BRIN versus Btree INDEXES

With PostgreSQL 9.5 a new type of index, BRIN (Block Range INdex) was introduced. These indexes work best when the data on disk is sorted. Brin only stores min/max values for a range of blocks on disk, which allows them to be small, but which raises the cost for any lookup against the index. Each lookup that hits the index must read a range of pages from disk, so each hit becomes more expensive.

Huge tables benefit from the BRIN index. Adding a BRIN index is fast and very easy and works well for the use case of time series data logging, though less well under intensive update. An INSERTs into BRIN indexes are specifically designed to not slow down as the table get bigger, so they perform much better than btree indexes.

In PostgreSQL v11, partitioning offers automatic index creation. You simply create an index on the parent table, and Postgres will automatically create indexes on all child tables. This thus makes partition maintenance much easier!

CREATE TABLE

On an empty database (you can create multiple database on the same server if you'd like or want to upgrade from version 9.x to 10/11) create the following tables for history* and trends*.

sudo -su postgres psql -d zabbix

Then run the following commands

-- history
CREATE TABLE public.history
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value numeric(16,4) NOT NULL DEFAULT 0.0000,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_1 ON public.history USING btree (itemid, clock);

-- history_log
CREATE TABLE public.history_log
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    "timestamp" integer NOT NULL DEFAULT 0,
    source character varying(64) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::character varying,
    severity integer NOT NULL DEFAULT 0,
    value text COLLATE pg_catalog."default" NOT NULL DEFAULT ''::text,
    logeventid integer NOT NULL DEFAULT 0,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_log_1 ON public.history_log USING btree (itemid, clock);

-- history_str
CREATE TABLE public.history_str
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value character varying(255) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::character varying,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_str_1 ON public.history_str USING btree (itemid, clock);

-- history_text
CREATE TABLE public.history_text
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value text COLLATE pg_catalog."default" NOT NULL DEFAULT ''::text,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_text_1 ON public.history_text USING btree (itemid, clock);

-- history_uint
CREATE TABLE public.history_uint
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value numeric(20,0) NOT NULL DEFAULT (0)::numeric,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_uint_1 ON public.history_uint USING btree (itemid, clock);

-- trends
CREATE TABLE public.trends
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    num integer NOT NULL DEFAULT 0,
    value_min numeric(16,4) NOT NULL DEFAULT 0.0000,
    value_avg numeric(16,4) NOT NULL DEFAULT 0.0000,
    value_max numeric(16,4) NOT NULL DEFAULT 0.0000,
    CONSTRAINT trends_pkey PRIMARY KEY (itemid, clock)
) PARTITION BY RANGE (clock);

-- trends_uint
CREATE TABLE public.trends_uint
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    num integer NOT NULL DEFAULT 0,
    value_min numeric(20,0) NOT NULL DEFAULT (0)::numeric,
    value_avg numeric(20,0) NOT NULL DEFAULT (0)::numeric,
    value_max numeric(20,0) NOT NULL DEFAULT (0)::numeric,
    CONSTRAINT trends_uint_pkey PRIMARY KEY (itemid, clock)
) PARTITION BY RANGE (clock);

Optionally depending on the role you used run

ALTER TABLE public.history OWNER TO zabbix;
ALTER TABLE public.history_log OWNER TO zabbix;
ALTER TABLE public.history_str OWNER TO zabbix;
ALTER TABLE public.history_text OWNER TO zabbix;
ALTER TABLE public.history_uint OWNER TO zabbix;
ALTER TABLE public.trends OWNER TO zabbix;
ALTER TABLE public.trends_uint OWNER TO zabbix;

PostgreSQL Partition Manager Extension (pg_partman)

pg_partman is an extensions to create and manage both time-based and serial-based table partition sets. Native partitioning in PostgreSQL 10 is supported as of pg_partman v3.0.1 and PostgreSQL 11 as of pg_partman v4.0.0.

pg_partman works as an extension and it can be installed directly on top of PostgreSQL.

Installing pg_partman

Debian apt:

sudo apt install postgresql-11-partman

postgresql.conf

/etc/postgresql/11/main/conf.d/pgpartman.conf

### General
shared_preload_libraries = 'pg_stat_statements, pg_partman_bgw' # (change requires restart)

### Partitioning & pg_partman settings
enable_partition_pruning = on
pg_partman_bgw.interval = 3600
pg_partman_bgw.role = 'zabbix'
pg_partman_bgw.dbname = 'zabbix'
pg_partman_bgw.analyze = off
pg_partman_bgw.jobmon = on

Restart postgresql and in the logs you should see pg_partman master background worker master process initialized with role zabbix.

Connect as postgres user to Zabbix database and create the extensions as part of the public schema on the Zabbix database.

sudo -su postgres psql -d zabbix

Then create the SCHEMA and EXTENSION:

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman schema partman;

Optionally depending on the user you used for this operation you need to set the right access right to your zabbix role, eg.

GRANT USAGE ON SCHEMA partman TO zabbix;
GRANT SELECT ON ALL TABLES IN SCHEMA partman TO zabbix;
GRANT DELETE ON ALL TABLES IN SCHEMA partman TO zabbix;

Create Partitioned Tables

Perform the SQL queries on the Zabbix database (otherwise it won't find the functions in another database where the extension was added) which will target the pg_partman functions uploaded

select partman.create_parent('public.history', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
select partman.create_parent('public.history_uint', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
select partman.create_parent('public.history_str', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
select partman.create_parent('public.history_text', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
select partman.create_parent('public.history_log', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
select partman.create_parent('public.trends', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');
select partman.create_parent('public.trends_uint', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');

This can be changed by using the UPDATE command on the partman.part_config table; for e.g.:

UPDATE partman.part_config SET premake = 7 WHERE parent_table = 'public.history_log';

Deleting designated partitions

It is impossible to manually remove partitions, thus use partman.part_config table config:

UPDATE partman.part_config set retention = '30 day', retention_keep_table = false, retention_keep_index = false WHERE parent_table = 'public.history';

Then execute maintenance procedure:

SELECT partman.run_maintenance('public.history');

Partition maintenance: Creating future partitions

pg_partman has a function run_maintenance that allows one to automate the table maintenance.

SELECT run_maintenance(p_analyze := false);

 -- note: disabling analyze is recommended for native partitioning due to aggressive locks

Native partitioning can result in heavy locking and therefore it is recommended to set p_analyze to FALSE which will effectively disable analyze.

Partition maintenance - Dropping/expiring old partitions

To configure pg_partman to drop old partitions, update the partman.part_config tables:

UPDATE partman.part_config SET retention_keep_table = false, retention = '7 day'
WHERE parent_table = 'public.history';
UPDATE partman.part_config SET retention_keep_table = false, retention = '7 day'
WHERE parent_table = 'public.history_uint';
UPDATE partman.part_config SET retention_keep_table = false, retention = '7 day'
WHERE parent_table = 'public.history_str';
UPDATE partman.part_config SET retention_keep_table = false, retention = '7 day'
WHERE parent_table = 'public.history_text';
UPDATE partman.part_config SET retention_keep_table = false, retention = '7 day'
WHERE parent_table = 'public.history_log';
UPDATE partman.part_config SET retention_keep_table = false, retention = '12 month'
WHERE parent_table = 'public.trends';
UPDATE partman.part_config SET retention_keep_table = false, retention = '12 month'
WHERE parent_table = 'public.trends_uint';

Following this change, whenever:

SELECT partman.run_maintenance('public.history');
SELECT partman.run_maintenance('public.history_uint');
SELECT partman.run_maintenance('public.history_str');
SELECT partman.run_maintenance('public.history_text');
SELECT partman.run_maintenance('public.history_log');
SELECT partman.run_maintenance('public.trends');
SELECT partman.run_maintenance('public.trends_uint');

Delete designated partitions

UPDATE partman.part_config SET retention = '7 day', retention_keep_table = false, retention_keep_index = false WHERE parent_table = 'public.history';
select partman.run_maintenance('public.history');

Change Zabbix history tables from monthly to daily with pg_partman

Originally we agreed to perform monthly partitions on the history* tables. But there is so much data being sent to Zabbix it increases the Zabbix database quickly. This will make scaling a problem in the future.
Although we can keep attaching disks the simple, scalable and permanent solution is to implement "Daily" partitions on all history* tables.
Currently (20190212) the partitioned tables for history* are based on monthly partitions.
The Zabbix Consultants recommend using daily partitions no longer than 7 days previously. Additionally the Zabbix Official Documentation clarifies this in detail - https://www.zabbix.com/documentation/current/manual/config/items/history_and_trends
The Zabbix Housekeeper, based on the rule set in Administration | General | Housekeeping to 7 days and 365 days respectively for history* and trends* data. Thus there is NO POINT IN RETAINING LONGER THAN 7 DAYS OF HISTORY IF ALREADY SPECIFIED IN THE FRONTEND.
pgpartman/pg_partman#248

PRIOR

Set 5 hour maintenance window. Take a snapshot of the database. pg_dump remotely the database and have backups upon backups...
Stop NGINX GUI, then Zabbix Server.
Kill all connections to the database.

-- view connections
SELECT sum(numbackends) FROM pg_stat_database;

-- kill all connections
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'zabbix'
  AND pid <> pg_backend_pid();

PROCEDURE

Objective: changing the following example table (plus 4 other tables ~10GGB each) from monthly to daily partitions while minimizing downtime and data loss.

zabbix=# \d+ history
                                     Table "public.history"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 itemid | bigint        |           | not null |         | plain   |              |
 clock  | integer       |           | not null | 0       | plain   |              |
 value  | numeric(16,4) |           | not null | 0.0000  | main    |              |
 ns     | integer       |           | not null | 0       | plain   |              |
Partition key: RANGE (clock)
Indexes:
    "history_1" btree (itemid, clock)
Partitions: history_p2019_01 FOR VALUES FROM (1546300800) TO (1548979200),
            history_p2019_02 FOR VALUES FROM (1548979200) TO (1551398400),
            history_p2019_03 FOR VALUES FROM (1551398400) TO (1554076800),
            history_p2019_04 FOR VALUES FROM (1554076800) TO (1556668800),
            history_p2019_05 FOR VALUES FROM (1556668800) TO (1559347200),
            history_p2019_06 FOR VALUES FROM (1559347200) TO (1561939200),
            history_p2019_07 FOR VALUES FROM (1561939200) TO (1564617600),
            history_default DEFAULT

Procedure: 00. Delete data older than 7 days. This will simplify and speed up the moving process.

/* The following is just an example of an epoch timestamp 7 days out from initiall creating this procedure - https://www.epochconverter.com/ ...
delete FROM history where age(to_timestamp(history.clock)) > interval '7 days';
delete FROM history_uint where age(to_timestamp(history_uint.clock)) > interval '7 days' ;
delete FROM history_str where age(to_timestamp(history_str.clock)) > interval '7 days' ;
delete FROM history_log where age(to_timestamp(history_log.clock)) > interval '7 days' ;
delete FROM history_text where age(to_timestamp(history_text.clock)) > interval '7 days' ;

Stop pg_partman from running the dynamic background worker to perform table maintenance on the history* tables in the partman.part_config column used by the maintenance

UPDATE partman.part_config SET automatic_maintenance = 'off' WHERE parent_table = 'public.history';
UPDATE partman.part_config SET automatic_maintenance = 'off' WHERE parent_table = 'public.history_uint';
UPDATE partman.part_config SET automatic_maintenance = 'off' WHERE parent_table = 'public.history_str';
UPDATE partman.part_config SET automatic_maintenance = 'off' WHERE parent_table = 'public.history_log';
UPDATE partman.part_config SET automatic_maintenance = 'off' WHERE parent_table = 'public.history_text';

Create a table similar to the one being unpartitioned. For e.g.:

-- history_moved
CREATE TABLE public.history_moved
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value numeric(16,4) NOT NULL DEFAULT 0.0000,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_moved_1 ON public.history_moved USING BRIN (itemid, clock);

-- history_uint_moved
CREATE TABLE public.history_uint_moved
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value numeric(20,0) NOT NULL DEFAULT (0)::numeric,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_uint_moved_1 ON public.history_uint_moved USING BRIN (itemid, clock);

-- history_str_moved
CREATE TABLE public.history_str_moved
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value character varying(255) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::character varying,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_str_moved_1 ON public.history_str_moved USING BRIN (itemid, clock);

-- history_log_moved
CREATE TABLE public.history_log_moved
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    "timestamp" integer NOT NULL DEFAULT 0,
    source character varying(64) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::character varying,
    severity integer NOT NULL DEFAULT 0,
    value text COLLATE pg_catalog."default" NOT NULL DEFAULT ''::text,
    logeventid integer NOT NULL DEFAULT 0,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_log_moved_1 ON public.history_log_moved USING BRIN (itemid, clock);

-- history_text_moved
CREATE TABLE public.history_text_moved
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value text COLLATE pg_catalog."default" NOT NULL DEFAULT ''::text,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_text_moved_1 ON public.history_text_moved USING BRIN (itemid, clock);

Then run

select partman.create_parent('public.history_moved', 'clock', 'native', 'monthly', null, 1, 'on', null, true, 'seconds');
select partman.create_parent('public.history_uint_moved', 'clock', 'native', 'monthly', null, 1, 'on', null, true, 'seconds');
select partman.create_parent('public.history_str_moved', 'clock', 'native', 'monthly', null, 1, 'on', null, true, 'seconds');
select partman.create_parent('public.history_log_moved', 'clock', 'native', 'monthly', null, 1, 'on', null, true, 'seconds');
select partman.create_parent('public.history_text_moved', 'clock', 'native', 'monthly', null, 1, 'on', null, true, 'seconds');

Call the partman.undo_partition_proc() function on the table wanting to be unpartitioned. This seems to lock the table and you can't view any information in the frontend:

CALL partman.undo_partition_proc('public.history', '1 day', null, 1, 'public.history_moved', false, 0, 10, false);
CALL partman.undo_partition_proc('public.history_uint', '1 day', null, 3, 'public.history_uint_moved', false, 0, 10, false);
CALL partman.undo_partition_proc('public.history_str', '1 day', null, 3, 'public.history_str_moved', false, 0, 10, false);
CALL partman.undo_partition_proc('public.history_log', '1 day', null, 3, 'public.history_log_moved', false, 0, 10, false);
CALL partman.undo_partition_proc('public.history_text', '1 day', null, 3, 'public.history_text_moved', false, 0, 10, false);

VACUUM ANALYZE history;
VACUUM ANALYZE history_uint;
VACUUM ANALYZE history_str;
VACUUM ANALYZE history_log;
VACUUM ANALYZE history_text;

Create the partitioned tables on the original history* tables wanting daily partitions:

select partman.create_parent('public.history', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
select partman.create_parent('public.history_uint', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
select partman.create_parent('public.history_str', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
select partman.create_parent('public.history_log', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
select partman.create_parent('public.history_text', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');

INSERT the data back into the newly partitioned tables. Use https://www.epochconverter.com/ to find epoch timestamp <= '7 days'. Below is just an example:

INSERT INTO public.history SELECT * FROM public.history_moved WHERE clock > 1549168074;
INSERT INTO public.history_uint SELECT * FROM public.history_uint_moved WHERE clock > 1549168074;
INSERT INTO public.history_str SELECT * FROM public.history_str_moved WHERE clock > 1549168074;
INSERT INTO public.history_log SELECT * FROM public.history_log_moved WHERE clock > 1549168074;
INSERT INTO public.history_text SELECT * FROM public.history_text_moved WHERE clock > 1549168074;

Drop the old table and remove the partman.part_config column

DROP TABLE history_moved;
DELETE FROM partman.part_config WHERE parent_table = 'public.history_moved';
DROP TABLE history_uint_moved;
DELETE FROM partman.part_config WHERE parent_table = 'public.history_uint_moved';
DROP TABLE history_str_moved;
DELETE FROM partman.part_config WHERE parent_table = 'public.history_str_moved';
DROP TABLE history_log_moved;
DELETE FROM partman.part_config WHERE parent_table = 'public.history_log_moved';
DROP TABLE history_text_moved;
DELETE FROM partman.part_config WHERE parent_table = 'public.history_text_moved';

UPDATE the partman.part_config for public.history:

UPDATE partman.part_config SET automatic_maintenance = 'on', retention_keep_table = false, retention = '8 day' WHERE parent_table = 'public.history';
UPDATE partman.part_config SET automatic_maintenance = 'on', retention_keep_table = false, retention = '8 day' WHERE parent_table = 'public.history_uint';
UPDATE partman.part_config SET automatic_maintenance = 'on', retention_keep_table = false, retention = '8 day' WHERE parent_table = 'public.history_str';
UPDATE partman.part_config SET automatic_maintenance = 'on', retention_keep_table = false, retention = '8 day' WHERE parent_table = 'public.history_log';
UPDATE partman.part_config SET automatic_maintenance = 'on', retention_keep_table = false, retention = '8 day' WHERE parent_table = 'public.history_text';

Run the maintenance:

SELECT partman.run_maintenance('public.history');
SELECT partman.run_maintenance('public.history_uint');
SELECT partman.run_maintenance('public.history_str');
SELECT partman.run_maintenance('public.history_log');
SELECT partman.run_maintenance('public.history_text');

Verify partman.part_config:

SELECT * FROM partman.part_config;

Run VACUUM ANALYZE:

VACUUM ANALYZE history;
VACUUM ANALYZE history_uint;
VACUUM ANALYZE history_str;
VACUUM ANALYZE history_log;
VACUUM ANALYZE history_text;

Ensure pg_partman_bgw is set in postgresql.conf file.

References

Change already implemented PSQL 11 Native Partitioning from Monthly to Daily
PostgreSQL Partition Manager Extension (pg_partman)

Zabbix Data Dump To New Database

pgdump/pgrestore Manual Mechanism

On the old database, ensure pg_hba.conf file is set to allow connections from the new database.

root# chown postgres:postgres /var/lib/backups/postgresql
postgres$ time pg_dump -Fc --file=/var/backups/postgresql/zabbix.dump -d zabbix -h <hostname>
postgres# time pg_restore -Fc -j 8 -d zabbix /var/backups/postgresql/zabbix.dump

Upgrade Zabbix 3.4 to 4.2 and move from PostgreSQL 9.6 to PostgreSQL 11

In my case I was at the situation to optimize DB and also considering, that some of the new features of Zabbix 4.2 are too tempting to stay with "just" 3.4 (Grafana like behaviour). This meant activity to set up a new DB server, migrate data to the new DB machine, as well as start new Zabbix server. Here is a short HOWTO on the SQL side how I did it.

Prepare the new Zabbix server, new virtual machine with new IP address (luckily I do have 2 IPs, one for live monitoring and second for the test Zabbix environment). Prepare the new SQL server, yet again new virtual machine (as already mentioned PostgreSQL 11). Install the pg_partman extension as described above.

On the new SQL machine just create the user and database as needed for the Zabbix data migration.

createuser --pwprompt zabbix
createdb -O zabbix -E Unicode -T template0 zabbix

SQL Config Zabbix 3.4 migration

On the new SQL server create a new directory and dump the config data from the old SQL. In fact it is not just config, but the command will exclude tables, which are usually the largest. This will keep only the "small" ones to make first step much faster. As I did not want to keep the disk trashing I used the dump compression via -j 4. Also since I want to restore data in parallel later on, I choose the directory format (-Fd).

pg_dump -v -Fd -j 4 -d zabbix -h <OLD_ZABBIX_SQL_IP> -U zabbix --inserts -Z 4 -f sql_dump-SMALL_TABLES-for-restore --exclude-table=history* --exclude-table=trends*

Now restore the tables to the new SQL machine. I have chosen to use the user zabbix for the data import to be on the safe side.

pg_restore -v -Fd -j 4 -v -d zabbix -h 127.0.0.1 -U zabbix sql_dump-SMALL_TABLES-for-restore

At this point you have all tables but history* and trends* tables created in the new SQL. Prepare missing history* and trends* tables to the new SQL as specified at Create Partitioned Tables, adjust parameters of those tables according to your needs (retention etc).

Once finished, if you want and your environment enables that you can already start you new Zabbix server pointing to this new SQL DB. Database has all the needed tables and beside that config data as well as some of the production data. All what is missing are just data from history* and trends* tables. So if you decide to start the zabbix server, you should see in the zabbix_server.log migration procedure taking place, lines like this (as already mentioned I was migrating from the Zabbix 3.4 to 4.2, hence the database versions in the logs).

  6330:20190512:020451.749 using configuration file: /etc/zabbix/zabbix_server.conf
  6330:20190512:020451.862 current database version (mandatory/optional): 03040000/03040007
  6330:20190512:020451.862 required mandatory version: 04020000
  6330:20190512:020451.862 starting automatic database upgrade
  6330:20190512:020451.880 completed 0% of database upgrade
  6330:20190512:020451.897 completed 1% of database upgrade
  6330:20190512:020451.932 completed 2% of database upgrade
  ...
  6364:20190512:021416.145 completed 99% of event name update
  6364:20190512:021416.322 completed 100% of event name update
  6364:20190512:021416.555 event name update completed
  6364:20190512:021416.567 server #0 started [main process]
  ...

This is the automatic procedure of DB schema upgrade from the old zabbix to the new version.

SQL data from Zabbix 3.4 migration

We probably do not want to lose our history* and trends* data from the old database. My goal was also to have as short downtime as possible. So here come the more intricate and time consuming part about the whole data migration process.

Procedure for migration of the history* tables is quite straigth forward.

pg_dump -v -Fd -j 4 -d zabbix -h <OLD_ZABBIX_SQL_IP> -U zabbix -Z 4 -f sql_dump-LARGE_TABLES-HISTORY-for-restore --table=history*
pg_restore -v -Fd -j 4 -d zabbix sql_dump-LARGE_TABLES-HISTORY-for-restore

You can use the default COPY procedure, as there are no constrain on the tables. So eventually it is allowed to insert all the data, even the duplicate ones, to the history* tables. Those will live there only few days and expire eventually (14 in my case).

For the trends* tables this is more difficult. Since there is a unique constrain, it is not possible to use COPY approach because it will most likely fail and no data will be inserted (COPY is just one big transaction). Alternate approach is needed then.

pg_dump -v -Fd -j 4 -d zabbix -h zabbix1 -U zabbix --inserts -f sql_dump-LARGE_TABLES-TRENDS-INSERTS-for-restore --table=trends*

Note --insert option, which creates dump files, where each table row is extra INSERT. Now you are set and you may start the pg_restore. If you do be prepared, that it takes ages. To speed things a lot I turned off the fsync in /etc/postgresql/11/main/postgresql.conf (please note, that my zabbix server was already running and collecting data).

⚠️ What I did was a little bit on the edge as turning fsync=off might have some unwanted results in case of the database failure!!! So use this with some extra caution.

fsync = off                             # flush data to disk for crash safety

Just reload the PostgreSQL. There is no need to restart, this option takes effect just with reload. Now you can start the import procedure.

pg_restore -v -Fd -j 4 -d zabbix sql_dump-LARGE_TABLES-TRENDS-INSERTS-for-restore

I was importing about 49 million records for about 3 hours. The import may finish with some error reports, but you can ignore those, as majority of the data will be inserted and only those considered as duplicates will fail.

Turn on the fsync in /etc/postgresql/11/main/postgresql.conf, or just delete this config line in /etc/postgresql/11/main/postgresql.conf as fsync=on is the default, so you are on the safe side once again.

fsync = on                             # flush data to disk for crash safety

Reload PostgreSQL and you should be ready for the full production.

Clean up, change of the IPs on newly provisioned Zabbix server, decommission the old one etc. as needed.

[SideNote]

You may come up with deviations of the migration strategy, such as, before starting the new zabbix server create a dump the LARGE_TABLES-TRENDS using COPY, wait some time (2 hours might be enough), so there should be no issue with data constraints and just then start new zabbix server. Again, first start of Zabbix server with this new database will convert the database schema, tables and trends as well as starts INSERTing new data. As there should be no duplicate keys on trends* values, it may be possible to restore tables with success and not having to turn fsync=off, but ... if not it will roll back all the data inserted so far.

pg_dump -v -Fd -j 4 -d zabbix -h zabbix1 -U zabbix -f sql_dump-LARGE_TABLES-TRENDS-for-restore --table=trends*

Performance Testing

pgbench

pgbench help understand Transaction Processing Performance Council (TPC) on a database to see performance.

Ensure you generate enough load.
Options
- -i = initialize
- -s = scaling
- -j = threads
- -c = clients
- -t = transactions; based on clients; if 10 clients then 100,000 transactions

Example:

$ pgbench -i -s 50 zabbix
$ pgbench -c 10 -j 2 -t 10000 zabbix

EXPLAIN ANALYZE

To help understand what is being done under the hood with partitioning we can use the EXPLAIN command. The EXPLAIN command shows the execution plan of a statement and how the tables i scanned.

$ psql -d zabbix -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT count(*) FROM public.history;"

$ psql -d zabbix -c "EXPLAIN ANALYZE SELECT * FROM public.history;"

Zabbix Databases Performance Results

PSQL 11 Non-Partitioned Database VM

8vCPU
16GB RAM
SSD

$ pgbench -i -s 50 zabbix
$ pgbench -c 10 -j 2 -t 10000 zabbix

Result:

$ pgbench -c 10 -j 2 -t 10000 zabbix
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 6.900 ms
tps = 1449.297191 (including connections establishing)
tps = 1449.363391 (excluding connections establishing)

PSQL 11 Non-Partitioned Database Baremetal

24 CPUs
126GB RAM
HDD

$ pgbench -c 10 -j 2 -t 10000 zabbix
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 8.235 ms
tps = 1214.341276 (including connections establishing)
tps = 1214.421294 (excluding connections establishing)

PSQL 11 Partitioned Database VM

8vCPU
16GB RAM
SSD

$ pgbench -i -s 50 zabbix
$ pgbench -c 10 -j 2 -t 10000 zabbix

$ pgbench -c 10 -j 2 -t 10000 zabbix
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 5.605 ms
tps = 1783.971153 (including connections establishing)
tps = 1784.074779 (excluding connections establishing)

Partitioning Advantages, Mistakes and Problems

Partitioning Advantages

Average number of index blocks to navigate in order to find a row goes down.
Having smaller blocks of data will improve performance.
You can DROP an individual partition to erase all of the data from that range.
The REINDEX operations will happen in a fraction of a time it would take for a single giant index to build.

Common Partitioning Mistakes

Not turning on enable_partition_pruning = on.
Failing to add all the same indexes or constraints to each partition that existed in the parent.
Forgetting to assign the same permissions to each child table as the parent.
Writing queries that don't filter on the partitioned key field. The WHERE clause needs to filter on constants. In general, keep the WHERE clauses as simple as possible, to improve the odds the optimizer will construct the exclusion proof you're looking for.
Query overhead for partitioning is proportional to the number of partitions. Keep the number of partitions to the two digit range for best performance.
When you manually VACUUM/ANALYZE, these will not cascade from the parent. You need to specifically target each partition with those operations.
Fail to account for out of range dates in the INSERT trigger. Expect the bad data will show up one day with a timestamp either far in the past or in the future, relative to what you have partitioned right now. Instead of throwing an error, some prefer to redirect inserted rows from outside of the partitioned range into a holding pen partition dedicated to suspicious data.
Regarding logical replication note that the logical replication restrictions from postgres documentation Replication is only possible from base tables to base tables. That is, the tables on the publication and on the subscription side must be normal tables, not views, materialized views, partition root tables, or foreign tables. In the case of partitions, you can therefore replicate a partition hierarchy one-to-one, but you cannot currently replicate to a differently partitioned setup. Attempts to replicate tables other than base tables will result in an error.

Reference - PostgreSQL 10 High Performance; 2018; pg 426-427

Ansible Role

I have written an ansible role that I used to configure pg_partman. I used the role in conjunction with the excellent ANXS postgresql role.

For details please view ansible|zabbix.pgpartman.

References

Ciolli, G., & Riggs, S. (2018). PostgreSQL 10 Administration Cookbook. Burmingham, UK: Packt Publishing Ltd.
Ahmed, I., Pirozzi, E., Smith, G. (2018). PostgreSQL High Performance. Burmingham, UK: Packt Publishing Ltd.
Juba, S., Volkov, A. (2017). Learning PostgreSQL 10. Burmingham, UK: Packt Publishing Ltd.
PostgreSQL Official Documentation | Table Partitioning
PostgreSQL Official Documentation | CREATE TABLE
PostgreSQL Official Documentation | Logical Replication | Restrictions
pgBarman
pgbench
pgBouncer
PgCookbook - a PostgreSQL documentation project
pglogical
pg_partman
Upgrading to postgreSQL 11 with Logical Replication
Zabbix Documentation | History and Trends
Tuning PostgreSQL with pgbench
PostgreSQL Logical Replication Gotchas

Files

README.md

Latest commit

History

README.md

File metadata and controls

Table of Contents

[SideNote]