In this blog, we will see the concepts for analyzing WAL files, such as pg_waldump (supported versions 12 and above) and pg_walinspect (supported versions 15 and above). Write-Ahead Logging (WAL) is a standard approach to transaction logging and one of the database’s functional components. WAL is a standard method for ensuring data durability. It records […]
Jul
02
2024
02
2024
--
See What’s Inside a WAL File Using pg_waldump and pg_walinspect
Dec
01
2023
01
2023
--
Partitioning in PostgreSQL With pg_partman (Serial-Based & Trigger-Based)
The PostgreSQL partition manager pg_partman is an open source extension widely supported and actively maintained by the PostgreSQL community.
- pg_partman is an extension that streamlines the creation and management of table partition sets, supporting both time-based and serial-based partitioning approaches.
- You can use pg_partman to automate and create partitions by breaking large tables into smaller partitions, thereby enhancing performance.
In my previous blog post, PostgreSQL Partitioning Made Easy Using pg_partman (TimeBased), we discussed:
- Installation of pg_partman
- Native partitioning implementation using pg_partman (time-based)
- Automatic maintenance of partitions
In this blog, we will discuss the partitioning of newly created and already existing tables via serial-based.
Please read the previous blog post for the installation of pg_partman before continuing with this blog.
In setting up partitioning without a template table using pg_partman, you actively configure the extension to create partitions based on specific criteria.
Using pg_partman, only the range partitioning (more about types of partition) can be implemented, either time-based or serial-based, but list partitioning cannot be implemented, as it can be only predicted when a new partition has to be created with range partitioning on dates or timestamps or IDs.
Partitioning by range without template
You can create partitions based on a serial-based column using pg_partman. For example:
Step 1: Create a table with native partitioning type by range using serial-typed column.
partman=# CREATE TABLE students (id INT PRIMARY KEY , fname VARCHAR(20) , lname VARCHAR (20) , dob DATE NOT NULL ,joined DATE ) PARTITION BY RANGE (id); CREATE TABLE
Step 2: Create parent to create initial child partitioned tables without template table.
partman=# SELECT partman.create_parent('public.students', p_control := 'id',p_type := 'native',p_interval := '100000',p_premake := 3, p_start_partition := '0');
create_parent
---------------
t
(1 row)
The structure of the parent table with its partitions created is as follows.
partman=# d+ students
Partitioned table "public.students"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+-------------+---
id | integer | | not null | | plain |
fname | character varying(20) | | | | extended |
lname | character varying(20) | | | | extended |
dob | date | | not null | | plain |
joined | date | | | | plain |
Partition key: RANGE (id)
Indexes:
"students_pkey" PRIMARY KEY, btree (id)
Partitions: students_p100000 FOR VALUES FROM (100000) TO (200000),
students_p200000 FOR VALUES FROM (200000) TO (300000),
students_p300000 FOR VALUES FROM (300000) TO (400000),
students_default DEFAULT
Here, we insert 1,000,000 rows to view data moving into partitioned tables.
partman=# INSERT INTO students (id ,fname,lname,dob ,joined) VALUES ( generate_series(1, 1000000) ,(array['Oswald', 'Henry', 'Bob', 'Vennie'])[floor(random() * 4 + 1)],
(array['Leo', 'Jack', 'Den', 'Daisy' ,'Woody'])[floor(random() * 5 + 1)], '1995-01-01'::date + trunc(random() * 366 * 3)::int,
generate_series('1/1/2022'::date, '08/31/2023'::date, '1 day'));
INSERT 0 1000000
partman=# SELECT students_p100000 , students_p200000 ,students_p300000, students_default,students_totalcnt from ( SELECT COUNT(*) FROM students_p100000 ) AS students_p100000, ( SELECT COUNT(*) FROM students_p200000 ) AS students_p200000,( SELECT COUNT(*) FROM students_p300000) AS students_p300000, ( SELECT COUNT(*) FROM students_default) AS students_default , ( SELECT COUNT(*) FROM students ) AS students_totalcnt ; students_p100000 | students_p200000 | students_p300000 | students_default | students_totalcnt ------------------+------------------+------------------+------------------+------------------- (100000) | (100000) | (100000) | (700000) | (1000000) (1 row)
Using functions, we can create child partitions and move the data from default to child tables.
partman.run_maintenance_proc → Create child partitions
partman=# CALL partman.run_maintenance_proc();
ERROR: updated partition constraint for default partition "students_default" would be violated by some row
CONTEXT: SQL statement "ALTER TABLE public.students ATTACH PARTITION public.students_p400000 FOR VALUES FROM ('400000') TO ('500000')"
Here, we see that, while performing run_maintenance_proc to create needed partitions for existing data in default tables, it’s a violation of rows as it helps to create new partitions needed as per the data but not to insert data to new partitions. So, we can use the function partition_data_proc to move the data.
Using partman.partition_data_proc we can move data batch-wise to partitioned tables.
partman.partition_data_proc → Move data to partitioned tables
partman=# CALL partman.partition_data_proc ('public.students');'
NOTICE: Batch: 1, Rows moved: 99999 NOTICE: Batch: 2, Rows moved: 100000
NOTICE: Batch: 3, Rows moved: 100000 NOTICE: Batch: 4, Rows moved: 100000
NOTICE: Batch: 5, Rows moved: 100000 NOTICE: Batch: 6, Rows moved: 100000
NOTICE: Batch: 7, Rows moved: 100000 NOTICE: Batch: 8, Rows moved: 1
<strong>NOTICE: Total rows moved: 700000</strong>
NOTICE: Ensure to VACUUM ANALYZE the parent (and source table if used) after partitioning data
CALL
partman=# VACUUM ANALYZE public.students;
VACUUM
Here, we actively move the data from the default to the newly created partitioned table.
partman=# SELECT students_p100000 , students_p200000 ,students_p300000, students_p400000 , students_p500000 ,students_p600000, students_p700000 , students_p800000 ,students_p900000,students_default, students_totalcnt from ( SELECT COUNT(*) FROM students_p100000 ) AS students_p100000, ( SELECT COUNT(*) FROM students_p200000 ) AS students_p200000, ( SELECT COUNT(*) FROM students_p300000) AS students_p300000, ( SELECT COUNT(*) FROM students_p400000 ) AS students_p400000, ( SELECT COUNT(*) FROM students_p500000 ) AS students_p500000, ( SELECT COUNT(*) FROM students_p600000) AS students_p600000, ( SELECT COUNT(*) FROM students_p700000 ) AS students_p700000, ( SELECT COUNT(*) FROM students_p800000 ) AS students_p800000, ( SELECT COUNT(*) FROM students_p900000) AS students_p900000, ( SELECT COUNT(*) FROM students_default) AS students_default , (SELECT COUNT(*) FROM students ) AS students_totalcnt ; students_p100000 | students_p200000 | students_p300000 | students_p400000 | students_p500000 | students_p600000 | students_p700000 | students_p800000 | students_p900000 | <strong>students_default</strong> | students_totalcnt ------------------+------------------+------------------+------------------+------------------ (100000) | (100000) | (100000) | (100000) | (100000) | (100000) | (100000) | (100000) | (100000) | <strong>(0)</strong> | (1000000) (1 row)
Partman type partitioning
Creating the parent table with the partman option (more about parent table creation options) allows us to create trigger-based partitions using pg_partman’s method of partitioning, which has less read-write performance compared to native/declarative partitioning.
Below, let’s see the implementation steps for trigger-based partitioning.
Partition creations using pg_partman for a non-declarative table: (trigger-based)
Step 1: Create a table with partman-type range partitioning using serial-typed columns and insert random data of 5,000 rows.
partman=# CREATE TABLE donors (id INT PRIMARY KEY , name VARCHAR(20) , bloodgroup VARCHAR (15) , last_donated DATE NOT NULL, contact_num VARCHAR(10)); CREATE TABLE partman=# INSERT INTO donors (id , name , bloodgroup , last_donated , contact_num) VALUES (generate_series(1, 5000) ,'user_' || trunc(random()*100) , (array['A+ group', 'A- group', 'O- group', 'O+ group','AB+ group','AB- group','B+ group','B- group'])[floor(random() * 8 + 1)] , '2022-01-01'::date + trunc(random() * 366 * 1)::int, CAST(1000000000 + floor(random() * 9000000000) AS bigint)); INSERT 0 5000
Step 2: Create parent to create initial child partitioned tables without template table.
partman=# SELECT partman.create_parent('public.donors', p_control := 'id',p_type := 'partman',p_interval := '1000');
create_parent
---------------
t
(1 row)
The table structure has been created with child tables based on the default premake value of four on partman.part_config and a trigger named donors_part_trig.
partman=# d+ donors
Table "public.donors"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+-----------------------+-----------+----------+---------+----------+-----------
id | integer | | not null | | plain |
name | character varying(20) | | | | extended |
bloodgroup | character varying(15) | | | | extended |
last_donated | date | | not null | | plain |
contact_num | character varying(10) | | | | extended |
Indexes:
"donors_pkey" PRIMARY KEY, btree (id)
Triggers:
donors_part_trig BEFORE INSERT ON donors FOR EACH ROW EXECUTE FUNCTION donors_part_trig_func()
Child tables: donors_p1000,donors_p2000,donors_p3000,donors_p4000, donors_p5000,
donors_p6000,donors_p7000, donors_p8000,donors_p9000
Access method: heap
Using partman.partition_data_proc, we can move data batch-wise to partitioned tables.
partman=# CALL partman.partition_data_proc ('public.donors');
NOTICE: Batch: 1, Rows moved: 999 NOTICE: Batch: 2, Rows moved: 1000
NOTICE: Batch: 3, Rows moved: 1000 NOTICE: Batch: 4, Rows moved: 1000
NOTICE: Batch: 5, Rows moved: 1000 NOTICE: Batch: 6, Rows moved: 1
NOTICE: Total rows moved: 5000
NOTICE: Ensure to VACUUM ANALYZE the parent (and source table if used) after partitioning data
CALL
Consequently, upon inserting new data into the table, we notice that if the partition exists, the data seamlessly moves into the respective partitions; however, if the partition doesn’t exist, the data remains within the main table.
partman=# INSERT INTO donors (id , name , bloodgroup , last_donated , contact_num) VALUES (generate_series(5001, 7000) ,'user_' || trunc(random()*100) , (array['A+ group', 'A- group', 'O- group', 'O+ group','AB+ group','AB- group','B+ group','B- group'])[floor(random() * 8 + 1)] , '2022-01-01'::date + trunc(random() * 366 * 1)::int, CAST(1000000000 + floor(random() * 9000000000) AS bigint)); INSERT 0 0
In this scenario, inserting data where the partition doesn’t exist results in directing the data to the main table. Subsequently, manual movement of this data occurs in batches to the newly created partitions.
SELECT donors_p1000,donors_p2000,donors_p3000,donors_p4000,donors_p5000, donors_p6000,donors_p7000,donors_p8000,donors_p9000,donors_totalcnt from ( SELECT COUNT(*) FROM donors_p1000 ) AS donors_p1000, ( SELECT COUNT(*) FROM donors_p2000) AS donors_p2000, ( SELECT COUNT(*) FROM donors_p3000 ) AS donors_p3000, ( SELECT COUNT(*) FROM donors_p4000 ) AS donors_p4000,( SELECT COUNT(*) FROM donors_p5000) AS donors_p5000,( SELECT COUNT(*) FROM donors_p6000 ) AS donors_p6000, ( SELECT COUNT(*) FROM donors_p7000 ) AS donors_p7000,( SELECT COUNT(*) FROM donors_p8000) AS donors_p8000, ( SELECT COUNT(*) FROM donors_p9000) AS donors_p9000, ( SELECT COUNT(*) FROM donors ) AS donors_totalcnt ; donors_p1000 | donors_p2000 | donors_p3000 | donors_p4000 | donors_p5000 | donors_p6000 | donors_p7000 | donors_p8000 | donors_p9000 | donors_totalcnt --------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+----------------- (1000) | (1000) | (1000) | (1000) | (1000) | (1000) | (0) | (0) | (0) | (6000) (1 row)
partman=# INSERT INTO donors (id , name , bloodgroup , last_donated , contact_num) VALUES (generate_series(14001, 15000) ,'user_' || trunc(random()*100) ,
(array['A+ group', 'A- group', 'O- group', 'O+ group','AB+ group','AB- group','B+ group','B- group'])[floor(random() * 8 + 1)] , '2022-01-01'::date + trunc(random() * 366 * 1)::int,
CAST(1000000000 + floor(random() * 9000000000) AS bigint));
INSERT 0 1000
partman=# CALL partman.partition_data_proc('public.donors');
NOTICE: Batch: 1, Rows moved: 999
NOTICE: Batch: 2, Rows moved: 1
NOTICE: Total rows moved: 1000
NOTICE: Ensure to VACUUM ANALYZE the parent (and source table if used) after partitioning data
CALL
partman=# VACUUM ANALYZE donors;
VACUUM
Employing functions to proactively create partitioned tables and migrate data enables the proactive planning of partitions ahead of inserts, preventing data accumulation in default partitions or the main table.
The blog I mentioned earlier contains details about the steps for automatic partitioning.
For more details on pg_partman and its latest updates, check out https://www.postgresql.org/about/news/pg_partman-470-released-2495/.
References:
Performing ETL Using Inheritance in PostgreSQL
PostgreSQL Partitioning Using Traditional Methods
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.
Sep
27
2023
27
2023
--
PostgreSQL Partitioning Made Easy Using pg_partman (TimeBased)
PostgreSQL Partition Manager — ‘pg_partman’ — is an open source extension freely available, widely supported, and actively maintained by the PostgreSQL community.
pg_partman creates and manages both time-based and serial-based table partition sets. Users use it to automate and create partitions by breaking large tables into smaller partitions for better performance.
In this post, initially, we will discuss the creation of the pg_partman extension following partitioning for newly created tables. First, we will need to install the OS with the necessary control files for the appropriate PostgreSQL version for installing pg_partman on the database.
Installation of pg_partman on the database
I recommend creating the schema before creating the extension to install the extension’s objects. This step is not mandatory, but after installation, you cannot change it. By default, the extension creates objects in the ‘public’ schema.
Establishing a dedicated schema for an extension serves the purpose of storing its objects, including tables, functions, procedures, and more. This approach facilitates smoother management and simplifies tasks like handling backups, upgrades, and migrations.
Below, I present the steps and failures encountered when attempting to change the schema after creating the extension.
Step one: As an example, let us try to create an extension without Qualifying SCHEMA.
CREATE EXTENSION pg_partman;
postgres=# dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+------------------------------------------------------
pg_partman | 4.6.0 | public | Extension to manage partitioned tables by time or ID
Step two: Next, we demonstrate a failure to set up the schema for an already created extension.
CREATE schema testpartman;
ALTER EXTENSION pg_partman set schema testpartman ; ERROR: extension "pg_partman" does not support SET SCHEMA
Eventually, here are the steps for Installation of pg_partman along with schema.
Step 1: Create schema for installation of the extension
partman=# CREATE SCHEMA partman; CREATE SCHEMA
Step 2: Create extension pg_partman in schema partman
partman=# CREATE EXTENSION pg_partman SCHEMA partman; CREATE EXTENSION
partman=# dx List of installed extensions Name | Version | Schema | Description ------------+---------+------------+-------------------------------------------------- pg_partman | 4.6.0 | partman | Extension to manage partitioned tables by time or ID
Implementation of native partitioning
Native partitioning is also known as declarative partitioning. PostgreSQL allows you to declare that a table is divided into partitions with a list of columns or expressions to be used as the partition key.
However, you can only implement range partitioning using pg_partman, either with time-based or serial-based methods. This is because you can only predict when a new partition needs to be created in the case of range partitioning on dates or timestamps.
Time-based partitioning by range with template
Unique constraints on the partitioned table must include all partitioning columns. Extension pg_partman helps to manage this by using a template table to manage properties that currently are not supported by native partitioning.
Properties like INDEX and unique constraint creations on the template table are created on all partitions.
partman=# CREATE TABLE employees (id INT PRIMARY KEY, fname VARCHAR(20) , lname VARCHAR (20) , dob DATE NOT NULL ,joined DATE) PARTITION BY RANGE (joined); ERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: PRIMARY KEY constraint on table "employees" lacks column "joined" which is part of the partition key.
1. First, create a table with native partitioning type by range using DATE typed column
<span style="font-family: 'Courier 10 Pitch', Courier, monospace; font-size: 11.2px; background-color: #cce4f5; color: #666666;">partman=# CREATE TABLE employees (id INT, fname VARCHAR(20) , lname VARCHAR (20) , dob DATE NOT NULL ,joined DATE NOT NULL) PARTITION BY RANGE (joined); </span>CREATE TABLE
2. Next, create a template table
partman=# CREATE TABLE partman.employees_template (LIKE public.employees); CREATE TABLE
3. Add constraints to the template table needed for all partitioned tables
partman=# ALTER TABLE partman.employees_template ADD PRIMARY KEY (ID); ALTER TABLE
4. Create parent to create initial child partitioned tables
partman=# SELECT partman.create_parent('public.employees', p_control := 'joined',p_type := 'native',p_interval := 'yearly',p_template_table := 'partman.employees_template',p_premake := 2, p_start_partition := (CURRENT_TIMESTAMP + '1 hour'::interval)::text);
create_parent
---------------
t
(1 row)
Additionally, I have listed below the various options that can be used with the ‘create_parent’ function and their respective values:
| Argument | Data Type | Definition | Values |
| p_parent_table | text | Parent Partitioned table ( Original table to have existed before creation & MUST be schema-qualified even if it is a public schema.) | schema.tablename |
| p_control | text | You should partition the column, either integer or time-based. | Column name |
| p_type | text | You can use the Partman type based on your performance improvement and flexibility requirements. | native,partman |
| p_interval | text | Integer range or time interval for each partition. (The generic intervals of “yearly” to “quarter-hour”) (yearly, quarterly, monthly, weekly, daily, hourly, half-hour, quarter-hour ) | Any other interval that validates time.For ID based integer ranges. |
| p_template_table | text | Template table name, if not provided, will create one in the schema the extension is installed. | schema.tablename |
| p_premake | integer | Additional partitions to stay ahead of the current partition. | We create a default of 4 (resulting in the creation of 5 partitions, including the current one). |
| p_start_partition | text | This allows specifying the first partition of a set instead of it being automatically determined. It must be a valid timestamp (for time-based) or a positive integer (for id-based) value. | CURRENT_TIMESTAMP /(CURRENT_TIMESTAMP + ‘1 hour’::interval)::text) |
| p_automatic_maintenance | text | The parameter determines whether maintenance is automatically managed when run_maintenance() is called without a table parameter or by a background worker process. | DEFAULT ‘on’::text |
Because we specified ‘2’ for premake in the create_parent function, you can observe that two partitions ahead have been created, as shown in the table definition below.
partman=# d+ employees
Partitioned table "public.employees"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain |
fname | character varying(20) | | | | extended |
lname | character varying(20) | | | | extended |
dob | date | | not null | | plain |
joined | date | | not null | | plain |
Partition key: RANGE (joined)
Partitions: employees_p2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'),
employees_p2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'),
employees_p2025 FOR VALUES FROM ('2025-01-01') TO ('2026-01-01'),
employees_default DEFAULT
To illustrate, we insert around 10,000 rows to see the data flush to its appropriate partitions if it exists or moves to the default partition.
partman=# INSERT INTO employees (id ,fname,lname,dob ,joined) VALUES ( generate_series(1,10000) ,(array['Oswald', 'Henry', 'Bob', 'Vennie'])[floor(random() * 4 + 1)], (array['Leo', 'Jack', 'Den', 'Daisy' ,'Woody'])[floor(random() * 5 + 1)], '1995-01-01'::date + trunc(random() * 366 * 3)::int, '2023-01-01'::date + trunc(random() * 366 * 3)::int); INSERT 0 10000
partman=# SELECT employees_p2023 , employees_p2024 ,employees_p2025,employees_default,employees_totalcnt from ( SELECT COUNT(*) FROM employees_p2023 ) AS employees_p2023,( SELECT COUNT(*) FROM employees_p2024 ) AS employees_p2024, ( SELECT COUNT(*) FROM employees_p2025) AS employees_p2025 ,( SELECT COUNT(*) FROM employees_default) AS employees_default , ( SELECT COUNT(*) FROM employees ) AS employees_totalcnt ; employees_p2023 | employees_p2024 | employees_p2025 | employees_default | employees_totalcnt -----------------+-----------------+-----------------+-------------------+------------ (3294) | (3397) | (3293) | (16) | (10000) (1 row)
By utilizing functions, we can seamlessly create child partitions and efficiently transfer data from the default table to these child tables.
partman=# CALL partman.run_maintenance_proc();
ERROR: updated partition constraint for default partition "employees_default" would be violated by some row
CONTEXT: SQL statement "ALTER TABLE public.employees ATTACH PARTITION public.employees_p2026 FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2027-01-01 00:00:00+00')"
As an illustration, the usage of partman.partition_data_proc shown below, which can move data batch-wise to partitioned tables.
partman=# CALL partman.partition_data_proc ('public.employees');
NOTICE: Batch: 1, Rows moved: 16
NOTICE: Total rows moved: 16
NOTICE: Ensure to VACUUM ANALYZE the parent (and source table if used) after partitioning data
CALL
partman=# VACUUM ANALYZE employees; VACUUM
As a result, we can see below the newly created table structure along with the new child partition.
partman=# d+ employees
Partitioned table "public.employees"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | |
fname | character varying(20) | | | | extended | |
lname | character varying(20) | | | | extended | |
dob | date | | not null | | plain | |
joined | date | | not null | | plain | |
Partition key: RANGE (joined)
Partitions: employees_p2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'),
employees_p2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'),
employees_p2025 FOR VALUES FROM ('2025-01-01') TO ('2026-01-01'),
employees_p2026 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01'),
employees_default DEFAULT
Consequently, after using the function, you will notice that the data from the default table has been moved to the newly created partitioned table.
partman=# SELECT employees_p2023 , employees_p2024 ,employees_p2025, employees_p2026, employees_default,employees_totalcnt from ( SELECT COUNT(*) FROM employees_p2023 ) AS employees_p2023,( SELECT COUNT(*) FROM employees_p2024 ) AS employees_p2024, ( SELECT COUNT(*) FROM employees_p2025) AS employees_p2025,( SELECT COUNT(*) FROM employees_p2026) AS employees_p2026, ( SELECT COUNT(*) FROM employees_default) AS employees_default , ( SELECT COUNT(*) FROM employees ) AS employees_totalcnt ; employees_p2023 | employees_p2024 | employees_p2025 | employees_p2026 | employees_default | employees_totalcnt -----------------+-----------------+-----------------+-----------------+-------------------+-------------------- (3294) | (3397) | (3293) | (16) | (0) | (10000) (1 row)
Creating the partitioned child tables ahead
Subsequently, we can modify the table partman.part_config settings to make changes by modifying the premake, which controls the partitions ahead.
partman=# update partman.part_config set premake = '4' where parent_table ='public.employees'; UPDATE 1
partman=# CALL partman.run_maintenance_proc(); CALL
partman=# d+ employees
Partitioned table "public.employees"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------
id | integer | | | | plain |
fname | character varying(20) | | | | extended |
lname | character varying(20) | | | | extended |
dob | date | | not null | | plain |
joined | date | | not null | | plain |
Partition key: RANGE (joined)
Partitions: employees_p2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'),
employees_p2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'),
employees_p2025 FOR VALUES FROM ('2025-01-01') TO ('2026-01-01'),
employees_p2026 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01'),
employees_p2027 FOR VALUES FROM ('2027-01-01') TO ('2028-01-01'),
employees_p2028 FOR VALUES FROM ('2028-01-01') TO ('2029-01-01'),
employees_p2029 FOR VALUES FROM ('2029-01-01') TO ('2030-01-01'),
employees_p2030 FOR VALUES FROM ('2030-01-01') TO ('2031-01-01'),
employees_default DEFAULT
Automatic maintenance of partitions
You can plan to create partitions ahead of the INSERTS to prevent data from moving into the default_partition or the main table. You can achieve this by using functions to create newly partitioned tables and move the data.
For instance, we can use the function run_maintenance() to create new partitions in advance, and there are two ways to do it.
1. Using background worker
2. Cron Scheduler
1. Setting up background worker
To start with, we can set “shared_preload_libraries” for partition maintenance operations to run automatically. You can set the parameter either by using ALTER SYSTEM or by manually modifying the config file.
partman=# ALTER SYSTEM set shared_preload_libraries = 'pg_partman_bgw'; ALTER SYSTEM
Afterward, perform a restart of the cluster either using systemctl or pg_ctl.
ubuntu@ip-172-31-47-134:~$ sudo systemctl restart postgresql@14-main.service
shared_preload_libraries -------------------------- pg_partman_bgw (1 row)
Configuration options background worker
1. pg_partman_bgw.interval: Number of seconds between calls to run_maintenance(). The default is 3600 (1 hour).
– For time-based partitions, intervals can be chosen based on the partition interval at least daily or twice daily.
– For serial-based partitions, you can choose the partition interval based on the data inserted on a daily basis.
2. pg_partman_bgw.dbname: Required. The database(s) that run_maintenance() will run on. If multiple, use a comma-separated list. If not set, BGW will do nothing.
3. pg_partman_bgw.role: The role that run_maintenance() will run as. The default is “postgres”. You are allowed to use only a single role name.
4. pg_partman_bgw.analyze: Optional argument, by default whenever a new child table is created, an analyze is run on the parent table of the partition set to ensure constraint exclusion works. This analyze can be skipped by setting this to false and help increase the speed of moving large amounts of data. If you set this to false, we strongly recommend that you perform a manual analyze of the partition upon completion to ensure the statistics are updated properly.
2. Cron scheduler
00 01,12 * * * psql -c “SELECT pg_partman.run_maintenance()”
Advantages of pg_partman
- Using pg_partman, you can define data retention to automatically move data into archived partitions or drop the partition.
- Query Performance will improve when dealing with partitioned data specifically.
- Vacuum efficiency will improve as data resides in smaller partitions, reducing the time required for table maintenance compared to dealing with large sets of data.
- You can attach or detach partitions without downtime concurrently, and it will not impact ongoing database operations.
Limitations of pg_partman
- pg_partman only supports partitioning of data types that are integer or date/timestamp.
- Generally, when opting for native partitioning, the parent table should already be created as range partitioned but not list partitioned.
- Unique constraints on partitioned tables must include all partitioning columns.
Conclusion
To summarize, we discussed creating and managing partitions effectively using PostgreSQL Partition Manager(pg_partman) along with the creation of the partition ahead of Inserting data using time-based. I will provide a more detailed usage of pg_partman in upcoming blog posts.
Please review the blogs below for reference:
Performing ETL Using Inheritance in PostgreSQL
PostgreSQL Partitioning Using Traditional Methods
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.
Jul
03
2023
03
2023
--
PostgreSQL Partitioning Using Traditional Methods
Partitioning is the concept of splitting large tables logically into smaller pieces for better performance of the database.
Methods of built-in PostgreSQL partition techniques
- Range partitioning
- List partitioning
- Hash partitioning
When to use partitioning
- Bulk operations like data loads and deletes can be performed using the partition feature of ATTACH and DETACH effectively.
- The exact point at which a table will benefit from partitioning depends on the application. However, a rule of thumb is that the size of the table should exceed the physical memory of the database server.
- As data is growing, sub-partitions can be created, which enhances the performance, and also old partitions can be deleted either by making them standalone or dropping them entirely.
Benefits of partitioning
- Query performance for DDL and DML operations can be improved in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions which is explained below.
- When queries or updates access a large percentage of a single partition, performance can be improved by using a sequential scan of that partition instead of using an index, which would require random-access reads scattered across the whole table.
- Dropping the partition table or truncating the partition table can be done using DROP TABLE and TRUNCATE TABLE, respectively, reducing the load through DELETE operations.
Range partitioning
Database partition that is based on a specific range of columns with data like dates and Numeric values.
Here, as an example, I created a table with range partitioning and partition tables for each quarter on a Date column.
CREATE TABLE employees (id INT NOt NULL , fname VARCHAR(20) , lname VARCHAR (20) , dob DATE NOT NULL , joined DATE NOT NULL) PARTITION BY RANGE (joined);
CREATE TABLE employees_q1 PARTITION of employees for VALUES FROM ('2022-01-01') to ('2022-04-01');
CREATE TABLE employees_q2 PARTITION of employees for VALUES FROM ('2022-04-01') to ('2022-07-01');
CREATE TABLE employees_q3 PARTITION of employees for VALUES FROM ('2022-07-01') to ('2022-10-01');
CREATE TABLE employees_q4 PARTITION of employees for VALUES FROM ('2022-10-01') to ('2023-01-01');
Range partitions are seen below in the table structure.
d+ employees
Partitioned table "public.employees"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
fname | character varying(20) | | | | extended | | |
lname | character varying(20) | | | | extended | | |
dob | date | | not null | | plain | | |
joined | date | | not null | | plain | | |
Partition key: RANGE (joined)
Partitions: employees_q1 FOR VALUES FROM ('2022-01-01') TO ('2022-04-01'),
employees_q2 FOR VALUES FROM ('2022-04-01') TO ('2022-07-01'),
employees_q3 FOR VALUES FROM ('2022-07-01') TO ('2022-10-01'),
employees_q4 FOR VALUES FROM ('2022-10-01') TO ('2023-01-01')
Inserted some random data for entries with 365 days a year.
INSERT INTO employees (id ,fname,lname,dob ,joined) VALUES ( generate_series(1, 365) ,(array['Oswald', 'Henry', 'Bob', 'Vennie'])[floor(random() * 4 + 1)],
(array['Leo', 'Jack', 'Den', 'Daisy' ,'Woody'])[floor(random() * 5 + 1)], '1995-01-01'::date + trunc(random() * 366 * 3)::int,
generate_series('1/1/2022'::date, '12/31/2022'::date, '1 day'));
Range partitioned data is seen as below distributed among its partitions.
SELECT employees_q1 , employees_q2 , employees_q3 , employees_q4 , employees_totalcnt from ( SELECT COUNT(*) FROM employees_q1 ) AS employees_q1, ( SELECT COUNT(*) FROM employees_q2 ) AS employees_q2, ( SELECT COUNT(*) FROM employees_q3 ) AS employees_q3, ( SELECT COUNT(*) FROM employees_q3 ) AS employees_q4 , ( SELECT COUNT(*) FROM employees ) AS employees_totalcnt ; employees_q1 | employees_q2 | employees_q3 | employees_q4 | employees_totalcnt --------------+--------------+--------------+--------------+-------------------- (90) | (91) | (92) | (92) | (365) (1 row)
Performance of DDL operations
Here, I created a table without a partition and inserted the same data, similar to the partitioned table.
A query plan is seen better for DDL operations when performed on data with a single partition or fewer partitions.
CREATE TABLE employees_nopartition (id INT NOt NULL , fname VARCHAR(20) , lname VARCHAR (20) , dob DATE NOT NULL , joined DATE NOT NULL) ;
INSERT INTO employees_nopartition (id ,fname,lname,dob ,joined) VALUES ( generate_series(1, 365) ,(array['Oswald', 'Henry', 'Bob', 'Vennie'])[floor(random() * 4 + 1)],
(array['Leo', 'Jack', 'Den', 'Daisy' ,'Woody'])[floor(random() * 5 + 1)], '1995-01-01'::date + trunc(random() * 366 * 3)::int,
generate_series('1/1/2022'::date, '12/31/2022'::date, '1 day'));
EXPLAIN select * from employees_nopartition where joined >= '2022-05-12' and joined < '2022-06-10';
QUERY PLAN
------------------------------------------------------------------------------
Seq Scan on employees_nopartition (cost=0.00..8.47 rows=29 width=22)
Filter: ((joined >= '2022-05-12'::date) AND (joined < '2022-06-10'::date))
(2 rows)
Here we can see a better query plan when data is fetched from the partitioned table than data fetched from the non-partitioned table.
EXPLAIN select * from employees where joined >= '2022-05-12' and joined < '2022-06-10';
QUERY PLAN
------------------------------------------------------------------------------
Seq Scan on employees_q2 (cost=0.00..2.37 rows=29 width=22)
Filter: ((joined >= '2022-05-12'::date) AND (joined < '2022-06-10'::date))
(2 rows)
List partitioning
Database partition that is based on key value(s) or discrete values and partition can also be done with the expression of the column like (RANGE BY LIST(expression)), which is explained below:
For example, I created a table with a list partition and a few list-partitioned tables and inserted some random data with 1,000 rows.
CREATE TABLE sales (id INT NOT NULL , branch VARCHAR(3),type text, Amount int ) PARTITION BY LIST (branch);
CREATE TABLE HYD_sales PARTITION of sales for VALUES IN ('HYD');
CREATE TABLE BLR_sales PARTITION of sales for VALUES IN ('BLR');
CREATE TABLE DEL_sales PARTITION of sales for VALUES IN ('DEL');
CREATE TABLE TPT_sales PARTITION of sales for VALUES IN ('TPT');
INSERT into sales (id , branch ,type , amount ) VALUES ( generate_series(1, 1000) , (array['HYD', 'BLR', 'DEL', 'TPT'])[floor(random() * 4 + 1)] , (array['Laptops', 'Printers', 'Hardisks', 'Desktops' ,'Monitors'])[floor(random() * 5 + 1)], (random()*200000)::int );
List partitions are seen in the table definition as below:
d+ sales
Partitioned table "public.sales"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | not null | | plain | |
branch | character varying(3) | | | | extended | |
type | text | | | | extended | |
amount | integer | | | | plain | |
Partition key: LIST (branch)
Partitions: blr_sales FOR VALUES IN ('BLR'),
del_sales FOR VALUES IN ('DEL'),
hyd_sales FOR VALUES IN ('HYD'),
tpt_sales FOR VALUES IN ('TPT')
Partitioned data distributed among its partitions is seen below:
SELECT blr_sales , del_sales , hyd_sales,tpt_sales, total_cnt from ( SELECT COUNT(*) FROM blr_sales ) AS blr_sales, ( SELECT COUNT(*) FROM del_sales ) AS del_sales, ( SELECT COUNT(*) FROM hyd_sales ) AS hyd_sales, ( SELECT COUNT(*) FROM tpt_sales ) AS tpt_sales , ( SELECT COUNT(*) FROM sales ) AS total_cnt; blr_sales | del_sales | hyd_sales | tpt_sales | total_cnt -----------+-----------+-----------+-----------+----------- (262) | (258) | (228) | (252) | (1001) (1 row)
List partitioning using expression
For example, I created a table with list partitioning using the expression of a column.
CREATE TABLE donors (id INT NOt NULL , name VARCHAR(20) , bloodgroup VARCHAR (15) , last_donated DATE , contact_num VARCHAR(10)) PARTITION BY LIST (left(upper(bloodgroup),3));
CREATE TABLE A_positive PARTITION of donors for VALUES IN ('A+ ');
CREATE TABLE A_negative PARTITION of donors for VALUES IN ('A- ');
CREATE TABLE B_positive PARTITION of donors for VALUES IN ('B+ ');
CREATE TABLE B_negative PARTITION of donors for VALUES IN ('B- ');
CREATE TABLE AB_positive PARTITION of donors for VALUES IN ('AB+');
CREATE TABLE AB_negative PARTITION of donors for VALUES IN ('AB-');
CREATE TABLE O_positive PARTITION of donors for VALUES IN ('O+ ');
CREATE TABLE O_negative PARTITION of donors for VALUES IN ('O- ');
List partitions are seen in the table definition below:
d+ donors
Partitioned table "public.donors"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
name | character varying(20) | | | | extended | | |
bloodgroup | character varying(15) | | | | extended | | |
last_donated | date | | | | plain | | |
contact_num | character varying(10) | | | | extended | | |
Partition key: LIST ("left"(upper((bloodgroup)::text), 3))
Partitions: a_negative FOR VALUES IN ('A- '),
a_positive FOR VALUES IN ('A+ '),
ab_negative FOR VALUES IN ('AB-'),
ab_positive FOR VALUES IN ('AB+'),
b_negative FOR VALUES IN ('B- '),
b_positive FOR VALUES IN ('B+ '),
o_negative FOR VALUES IN ('O- '),
o_positive FOR VALUES IN ('O+ ')
Here, I inserted some random 100 rows.
INSERT INTO donors (id , name , bloodgroup , last_donated , contact_num) VALUES (generate_series(1, 100) ,'user_' || trunc(random()*100) , (array['A+ group', 'A- group', 'O- group', 'O+ group','AB+ group','AB- group','B+ group','B- group'])[floor(random() * 8 + 1)] , '2022-01-01'::date + trunc(random() * 366 * 1)::int, CAST(1000000000 + floor(random() * 9000000000) AS bigint));
List partitioned data with expression distributed among its partitions is seen below:
SELECT a_negative , a_positive , ab_negative , ab_positive , b_negative ,b_positive ,o_negative , o_positive , total_cnt from ( SELECT COUNT(*) FROM a_negative ) AS a_negative, ( SELECT COUNT(*) FROM a_positive ) AS a_positive, ( SELECT COUNT(*) FROM ab_negative ) AS ab_negative, ( SELECT COUNT(*) FROM ab_positive ) AS ab_positive , ( SELECT COUNT(*) FROM b_negative ) AS b_negative, ( SELECT COUNT(*) FROM b_positive ) AS b_positive , ( SELECT COUNT(*) FROM o_positive ) AS o_positive , ( SELECT COUNT(*) FROM o_negative ) AS o_negative, ( SELECT COUNT(*) FROM donors ) AS total_cnt; a_negative | a_positive | ab_negative | ab_positive | b_negative | b_positive | o_negative | o_positive | total_cnt ------------+------------+-------------+-------------+------------+------------+------------+------------+----------- (9) | (19) | (10) | (12) | (12) | (10) | (18) | (10) | (100) (1 row)
Performance of DML operations
Here is an example shown with the table, which is created without partitions and inserted the same data similar to that of the partitioned table.
Below I created a table without a partition and inserted some random data with 1,000 rows to show query performance.
CREATE TABLE sales_nopartition (id INT NOT NULL , branch VARCHAR(3),type text, Amount int );
INSERT into sales_nopartition (id , branch ,type , amount ) VALUES ( generate_series(1, 1000) , (array['HYD', 'BLR', 'DEL', 'TPT'])[floor(random() * 4 + 1)] , (array['Laptops', 'Printers', 'Hardisks', 'Desktops' ,'Monitors'])[floor(random() * 5 + 1)], (random()*200000)::int );
UPDATE Query Performance
EXPLAIN update sales_nopartition set type = 'Smart Watches' where branch = 'HYD';
QUERY PLAN
---------------------------------------------------------------------------
Update on sales_nopartition (cost=0.00..19.50 rows=229 width=50)
-> Seq Scan on sales_nopartition (cost=0.00..19.50 rows=229 width=50)
Filter: ((branch)::text = 'HYD'::text)
(3 rows)
EXPLAIN update sales set type = 'Smart Watches' where branch = 'HYD';
QUERY PLAN
------------------------------------------------------------------
Update on sales (cost=0.00..5.10 rows=248 width=50)
Update on hyd_sales
-> Seq Scan on hyd_sales (cost=0.00..5.10 rows=248 width=50)
Filter: ((branch)::text = 'HYD'::text)
(4 rows)
DELETE Query Performance
EXPLAIN DELETE from sales_nopartition where branch='HYD';
QUERY PLAN
--------------------------------------------------------------------------
Delete on sales_nopartition (cost=0.00..19.50 rows=229 width=6)
-> Seq Scan on sales_nopartition (cost=0.00..19.50 rows=229 width=6)
Filter: ((branch)::text = 'HYD'::text)
(3 rows)
EXPLAIN DELETE from sales where branch='HYD';
QUERY PLAN
-----------------------------------------------------------------
Delete on sales (cost=0.00..5.10 rows=248 width=6)
Delete on hyd_sales
-> Seq Scan on hyd_sales (cost=0.00..5.10 rows=248 width=6)
Filter: ((branch)::text = 'HYD'::text)
(4 rows)
The above examples show the performance of DELETE and UPDATE operations with data fetched from a single partitioned table having a better query plan than the one with no partitions.
Hash partitioning
Hash partitioning table is defined as the table partitioned by specifying a modulus and a remainder for each partition.
- Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder.
- Hash partitioning is best used when each partition is on different table spaces residing on separate physical disks, so the IO is equally divided by more devices.
For example, I created a table with hash partitioning and a few partitioned tables with modulus five.
CREATE TABLE students ( id int NOT NULL, name varchar(30) NOT NULL , course varchar(100) ,joined date ) PARTITION BY hash(id);
CREATE TABLE student_0 PARTITION OF students FOR VALUES WITH (MODULUS 5,REMAINDER 0); CREATE TABLE student_1 PARTITION OF students FOR VALUES WITH (MODULUS 5,REMAINDER 1); CREATE TABLE student_2 PARTITION OF students FOR VALUES WITH (MODULUS 5,REMAINDER 2); CREATE TABLE student_3 PARTITION OF students FOR VALUES WITH (MODULUS 5,REMAINDER 3); CREATE TABLE student_4 PARTITION OF students FOR VALUES WITH (MODULUS 5,REMAINDER 4);
The table structure looks like the one below with five created partitions:
d+ students
Partitioned table "public.students"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | not null | | plain | |
name | character varying(30) | | not null | | extended | |
course | character varying(100) | | | | extended | |
joined | date | | | | plain | |
Partition key: HASH (id)
Partitions: student_0 FOR VALUES WITH (modulus 5, remainder 0),
student_1 FOR VALUES WITH (modulus 5, remainder 1),
student_2 FOR VALUES WITH (modulus 5, remainder 2),
student_3 FOR VALUES WITH (modulus 5, remainder 3),
student_4 FOR VALUES WITH (modulus 5, remainder 4)
Here, I Inserted some random data with 100,000 rows.
INSERT into students (id , name , course ,joined ) VALUES (generate_series(1, 100000) , 'student_' || trunc(random()*1000) , (array['Finance & Accounts', 'Business Statistics', 'Environmental Science'])[floor(random() * 3 + 1)],'2019-01-01'::date + trunc(random() * 366 * 3)::int);
We see below the hash partitioned data among its partitioned tables.
SELECT relname,reltuples as rows FROM pg_class WHERE relname IN ('student_0','student_1','student_2','student_3','student_4') ORDER BY relname;
relname | rows
-----------+-------
student_0 | 19851
student_1 | 20223
student_2 | 19969
student_3 | 19952
student_4 | 20005
(5 rows)
Benefits of hash partitioning
- The primary benefit is to ensure an even distribution of data among a predetermined number of partitions.
- Hash keys are used effectively and efficiently in cases where ranges are not applicable, like employee number, product ID, etc.
What if the data is out of range or list?
For this purpose, we use default partitions on range and list partitioned tables.
For both range and list partitions, data can be stored temporarily, which is out-of-range, by creating a default partition and later creating an appropriate partition.
Hash-partitioned tables may not have a default partition, as the creation of a default partition for hash partitioning does not make any sense and is not needed.
We see here what happens when I try to insert data for which a partition doesn’t exist and how the default partition helps in this case.
INSERT into sales VALUES ( 1001 , 'MYS' , 'Scanners' , 190000); ERROR: no partition of relation "sales" found for row DETAIL: Partition key of the failing row contains (branch) = (MYS).
CREATE TABLE sales_default PARTITION of sales DEFAULT; CREATE TABLE INSERT into sales VALUES ( 1001 , 'MYS' , 'Scanners' , 190000); INSERT 0 1
select * from sales_default ; id | branch | type | amount ------+--------+----------+-------- 1001 | MYS | Scanners | 190000 (1 row)
So the data we inserted is sent to the default partition, and partitions can be created later based on the data in the default table and available partitions.
Conclusion
Here we discussed default partitioning techniques in PostgreSQL using single columns, and we can also create multi-column partitioning. PostgreSQL Partition Manager(pg_partman) can also be used for creating and managing partitions effectively. Further details will be explained in upcoming blogs.
Also, please find below the related blogs for reference:
PostgreSQL Sharding: An Overview and MongoDB Comparison
Performing ETL Using Inheritance in PostgreSQL
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together.