Personal blog of Yzmir Ramirez

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.

Download Percona Distribution for PostgreSQL Today!

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.

Download Percona Distribution for PostgreSQL Today!

This blog was originally published in July 2017 and was updated in August 2023.

It’s a pretty common question around here, so let’s see what we can do about that.

So, What is MySQL Partitioning?

Partitioning is a way in which a database (MySQL in this case) splits its actual data down into separate tables but still gets treated as a single table by the SQL layer.

When partitioning in MySQL, it’s a good idea to find a natural partition key. You want to ensure that table lookups go to the correct partition or group of partitions. This means that all SELECT, UPDATE, and DELETE should include that column in the WHERE clause. Otherwise, the storage engine does a scatter-gather and queries ALL partitions in a UNION that is not concurrent.

Generally, you must add the partition key into the primary key along with the auto-increment, i.e., PRIMARY KEY (part_id, id). If you don’t have well-designed and small columns for this composite primary key, it could enlarge all of your secondary indexes.

Want to ensure a successful MySQL 5.7 to 8.0 Upgrade? Watch this on-demand webinar from Percona to learn how!

 

What are the Different Types of MySQL Partitions?

Horizontal and vertical MySQL partitions are techniques used to divide tables into smaller sections for improved performance and management. Horizontal partitioning splits a table into smaller tables with identical columns but distinct rows, while Vertical partitioning divides a table into separate tables with related columns. Various partition types, like RANGE, LIST, HASH, and KEY, are used for specific needs, from range-based data to custom criteria, to ensure efficient data handling and the optimization of queries. Note: Vertical partitioning is not supported in MySQL 8.0 — more on this later.

RANGE partitioning

RANGE partitioning in MySQL is a data partitioning technique where a large table is divided into smaller partitions based on a specified range of column values like dates or numeric intervals. Each partition holds data that falls within a specific range, optimizing data handling and query speed.

HASH partitioning

HASH partitioning in MySQL divides a table into partitions based on the hash value of a designated column’s contents. Unlike range or list partitioning, where you manually designate the partition for specific column values, hash partitioning automatically assigns values to partitions based on hashing. This method distributes data evenly across partitions to achieve balanced storage and optimal query performance.

LIST partitioning

LIST partitioning in MySQL shares similarities with range partitioning. As with range partitioning, each partition is explicitly defined, but in list partitioning, partitions are created and assigned based on including a column value in predefined value lists rather than the contiguous ranges of values used in range partitioning.

COLUMNS partitioning

COLUMNS partitioning in MySQL is a technique that involves dividing a table into partitions based on specific columns’ values. Unlike other partitioning methods focusing on the entire row, column partitioning separates columns into different partitions. This approach is helpful when working with tables with many columns or when specific columns are frequently updated.

KEY partitioning

KEY partitioning is similar to HASH partitioning, except that only one or more columns to be evaluated are specified, and the MySQL server provides its own hashing function. These columns can contain other than integer values since the hashing function supplied by MySQL guarantees an integer result regardless of the column data type.

MySQL Partitioning in Version 5.7

MySQL version 5.7 introduced various features and capabilities for partitioning, enhancing its utility in managing large datasets. It enabled dividing large tables into smaller, manageable segments based on defined criteria. This facilitates improved data organization, query optimization, and maintenance.

In version 5.7, MySQL partitioning supports multiple partitioning types, including RANGE, LIST, HASH, KEY, and COLUMNS. Each type caters to different data distribution needs. 

Using partitioning in a MySQL 5.7 environment offers several practical benefits. It significantly improves query performance by reducing the amount of data scanned during queries, which is especially helpful when dealing with large tables. Partition pruning, a feature in MySQL 5.7, ensures that only relevant partitions are accessed, further enhancing query efficiency. Additionally, partitioning aids in maintenance tasks like archiving and purging old data, as operations can be performed on individual partitions instead of the entire table.

Are you ready for MySQL 5.7 EOL? Percona can help. 

Learn More

MySQL Partitioning in MySQL 8.0

MySQL 8.0 brought substantial advancements and enhancements to partitioning, significantly elevating its capabilities. This version introduces key features and optimizations that address limitations from previous iterations.

One major enhancement is the support for subpartitioning. MySQL 8.0 allows you to create subpartitions within existing partitions, providing an additional level of data segmentation. This feature facilitates even more precise data organization and management, allowing for complex use cases involving multi-level data hierarchies.

Additionally, MySQL 8.0 introduces automated list partitioning, simplifying partition creation through by enabling the database to determine the partition based on the values inserted automatically.

This version also notably integrates native backing for range and list partitioning of spatial indexes, amplifying geospatial query speed for substantial datasets. Enhancements to the query optimizer improve partition pruning for both single-level and subpartitioned tables, leading to improved query performance.

To sum it up, MySQL 8.0 significantly advances partitioning with features like subpartitioning, automatic list partitioning, and improved query optimization. These enhancements address limitations from previous versions, allowing for more complex data organization, streamlined management, and optimized query performance.

Upgrading MySQL to 8.0? Check out this blog to learn how to avoid disaster!

What are the Benefits of MySQL Partitions?

MySQL partitioning offers several advantages in terms of query performance and maintenance:

Enhanced Query Performance: Partitioning improves query performance by minimizing the amount of data scanned during queries. As the data is distributed into smaller partitions, the database engine only needs to scan relevant partitions, leading to faster query responses.

Optimized Resource Utilization: Partitioning enables parallelism in query execution across partitions. This means that multiple partitions can be processed simultaneously, making better use of available hardware resources and further enhancing query performance.

Data Retention and Deletion: Partitioning simplifies the archiving or deleting of old data by targeting specific partitions, and enhancing data retention policies.

Reduced Overhead: Partitioning can significantly reduce the overhead of managing large tables. For example, when inserting or deleting data, the database engine only needs to modify the relevant partitions, which can be much faster than performing these operations on the entire table.

Streamlined Maintenance: Partitioning simplifies maintenance operations. For example, you can perform maintenance tasks like index rebuilds, statistics updates, or data archiving on specific partitions rather than the entire table, minimizing downtime and optimizing resource utilization.

Data Lifecycle Management: Partitioning supports efficient data lifecycle management. Old or infrequently accessed data can be stored in separate partitions or even archived, allowing for better control over data retention and optimization of storage resources.

Enhanced Scalability: Partitioning enhances the database’s ability to scale, as data can be distributed across different storage devices.

In summary, MySQL partitioning brings substantial advantages to both query performance and maintenance. It improves data retrieval speed, enhances resource utilization, streamlines maintenance operations, optimizes storage management, and reduces overheads associated with large tables. These benefits collectively contribute to a more efficient database environment.

What are the Challenges and Limitations of MySQL Partitions?

While there are lots of positives about using MySQL partitioning, there can also be challenges and limitations that users should be aware of:

Query Optimization Complexity: Although partitioning can enhance query performance, it requires queries to be designed with partitioning key considerations in mind. Inappropriately designed queries may not fully utilize partitioning benefits, leading to poor performance.

Limited Key Choices: Not all columns are suitable for partitioning keys. Choosing a proper partitioning key is crucial, and inappropriate selections can result in uneven data distribution across partitions, impacting performance.

Suboptimal Partitioning Strategies: Choosing the wrong partitioning strategy or key can lead to performance degradation. For instance, using partitioning on a table with a small number of rows may not provide significant benefits and can even worsen performance due to increased complexity.

Limited Parallelism: While partitioning allows for parallel processing, there might be limitations on how many partitions can be processed concurrently based on hardware resources, potentially impacting query performance.

Data Skewing: In some scenarios, data might not be uniformly distributed across partitions, causing “data skew.” This can lead to uneven resource utilization and slower performance for certain partitions.

Replication and Backup Issues: MySQL partitioning might impact the way data replication and backups are performed. Special considerations are needed to ensure these processes still work seamlessly after partitioning.

So, while MySQL partitioning does offer advantages, it also brings challenges and limitations related to complexity, maintenance, query optimization, and performance. Careful planning and continuous monitoring are crucial to facing these challenges and achieving optimal performance.

Performance Optimization with MySQL Partitioning

MySQL partitioning enhances query performance by enabling the database to focus on relevant data partitions during queries. This reduces the amount of data that needs to be scanned, resulting in faster data retrieval. For example, when querying a large table for specific date ranges, partitioning allows the engine to scan only relevant partitions containing data within those ranges.

Query execution plans are positively impacted by partitioning. The query optimizer recognizes partitioning schemes and generates execution plans that use partition pruning. This means the optimizer can skip unnecessary partitions, resulting in optimized query plans that use fewer resources and execute more quickly.

Partitioning influences indexing strategies by narrowing the scope of indexing. Instead of indexing the entire table, partitioning allows for more focused indexing. This minimizes index size and boosts efficiency, leading to faster query performance.

In scenarios where partitioning aligns with natural data distribution, such as time-series data or geographical regions, query execution time is significantly reduced. Queries that involve specific partitions can bypass irrelevant data; for instance, when searching for transactions within a certain date range, partitioning enables the database to search only the relevant partition.

Best Practices for Implementing MySQL Partitioning

With these best practices, you can ensure that your MySQL partitioning setup is efficient, well-maintained, and improves database performance.

Choose the Correct Partition Key: Select a partition key that aligns with your data distribution and query patterns. Common choices include time-based or range-based values.

Monitor Query Performance: Continuously monitor query performance after partitioning. Use tools like EXPLAIN to assess query execution plans.

Watch for Bloat: Over time, partitions can accumulate large amounts of data, leading to slow queries.

Proper Indexing: Partitioned tables benefit from proper indexing. Ensure that the chosen partition key is part of the primary or unique key. Additionally, consider indexing frequently queried columns to improve performance further.

Regular Maintenance: Perform routine maintenance tasks, such as purging old data from partitions, optimizing indexes, and rebuilding partitions.

Backup and Restore: As we mentioned earlier, partitioning can impact backup and restore strategies. Ensure your backup and restore procedures account for partitioned data to prevent data loss and ensure reliable recovery.

Test, Test, and Test Again: Before implementing partitioning in production, thoroughly test it in a controlled environment. This helps identify potential issues and fine-tune the partitioning strategy.

Documentation: Always be documenting! Be sure to include your partitioning strategy, why certain partition keys are used, and your maintenance procedures.

Talk to experts: If you’re new to partitioning or dealing with complex scenarios, consider consulting with experts.

Choosing the Right Partitioning Strategy

Selecting the appropriate partitioning strategy in MySQL involves carefully considering various factors, including:

Understanding your data’s nature and distribution. For range-based data, consider range partitioning, while list partitioning is suitable for discrete values. Hash partitioning evenly distributes data.

Analyzing query patterns to align the strategy with how data is accessed. Time-based queries benefit from range partitioning, while hash partitioning suits equally accessed values.

Matching the strategy to your database requirements. For archiving historical data, consider range-based on time. High-write workloads might favor hash or key partitioning for load balancing.

Watching for changes in data patterns. As data grows, a previously effective strategy might become less optimal. Periodically review and adjust.

Any partitioning strategy should improve query performance, not lead to suboptimal queries — test and benchmark strategies before implementation.

Ensuring the strategy aligns with maintenance operations. For example, rebuilding large partitions might often impact uptime. Select a strategy that minimizes disruptions.

Continuously monitoring query performance after implementation. Be ready to adjust your strategy as needed.

Evaluating how your chosen strategy accommodates future growth, as some strategies scale better with larger datasets.

Choosing the right partitioning strategy is pivotal to database performance. By aligning the strategy with your data’s characteristics and specific requirements, you ensure that your MySQL partitioning delivers the desired results.

Elevate your MySQL database management: Get started with Percona Distribution for MySQL.

Migrating to MySQL 8.0 and 5.7 EOL Support with Percona

Proper MySQL partitioning optimizes databases by splitting large tables into smaller parts, enhancing query speed and data management while reducing overhead and making maintenance easier. But, users need to understand that careful planning, monitoring, and testing are vital to avoid any potential performance declines due to improper setup.

Looking to upgrade to MySQL 8.0 or stay on 5.7? Percona will support you either way.

Move to MySQL 8.0  Get Post-EOL Support For MySQL 5.7

I have been working for a customer benchmarking insert performance on Amazon EC2, and I have some interesting results that I wanted to share. I used a nice and effective tool iiBench which has been developed by Tokutek. Though the “1 billion row insert challenge” for which this tool was originally built is long over, but still the tool serves well for benchmark purposes.

OK, let’s start off with the configuration details.

Configuration

First of all let me describe the EC2 instance type that I used.

EC2 Configuration

I chose m2.4xlarge instance as that’s the instance type with highest memory available, and memory is what really really matters.

High-Memory Quadruple Extra Large Instance
68.4 GB of memory
26 EC2 Compute Units (8 virtual cores with 3.25 EC2 Compute Units each)
1690 GB of instance storage
64-bit platform
I/O Performance: High
API name: m2.4xlarge

As for the IO configuration I chose 8 x 200G EBS volumes in software RAID 10.

Now let’s come to the MySQL configuration.

MySQL Configuration

I used Percona Server 5.5.22-55 for the tests. Following is the configuration that I used:

## InnoDB options
innodb_buffer_pool_size         = 55G
innodb_log_file_size            = 1G
innodb_log_files_in_group       = 4
innodb_buffer_pool_instances    = 4
innodb_adaptive_flushing        = 1
innodb_adaptive_flushing_method = estimate
innodb_flush_log_at_trx_commit  = 2
innodb_flush_method             = O_DIRECT
innodb_max_dirty_pages_pct      = 50
innodb_io_capacity              = 800
innodb_read_io_threads          = 8
innodb_write_io_threads         = 4
innodb_file_per_table           = 1

## Disabling query cache
query_cache_size                = 0
query_cache_type                = 0

You can see that the buffer pool is sized at 55G and I am using 4 buffer pool instances to reduce the contention caused by buffer pool mutexes. Another important configuration that I am using is that I am using “estimate” flushing method available only on Percona Server. The “estimate” method reduces the impact of traditional InnoDB log flushing, which can cause downward spikes in performance. Other then that, I have also disabled query cache to avoid contention caused by query cache on write heavy workload.

OK, so that was all about the configuration of the EC2 instance and MySQL.

Now as far as the benchmark itself is concerned, I made no code changes to iiBench, and used the version available here. But I changed the table to use range partitioning. I defined a partitioning scheme such that every partition would hold 100 million rows.

Table Structure

The table structure of the table with no secondary indexes is as follows:

CREATE TABLE `purchases_noindex` (
  `transactionid` int(11) NOT NULL AUTO_INCREMENT,
  `dateandtime` datetime DEFAULT NULL,
  `cashregisterid` int(11) NOT NULL,
  `customerid` int(11) NOT NULL,
  `productid` int(11) NOT NULL,
  `price` float NOT NULL,
  PRIMARY KEY (`transactionid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (transactionid)
(PARTITION p0 VALUES LESS THAN (100000000) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200000000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (300000000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (400000000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (500000000) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (600000000) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (700000000) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (800000000) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (900000000) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (1000000000) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

While the structure of the table with secondary indexes is as follows:

CREATE TABLE `purchases_index` (
  `transactionid` int(11) NOT NULL AUTO_INCREMENT,
  `dateandtime` datetime DEFAULT NULL,
  `cashregisterid` int(11) NOT NULL,
  `customerid` int(11) NOT NULL,
  `productid` int(11) NOT NULL,
  `price` float NOT NULL,
  PRIMARY KEY (`transactionid`),
  KEY `marketsegment` (`price`,`customerid`),
  KEY `registersegment` (`cashregisterid`,`price`,`customerid`),
  KEY `pdc` (`price`,`dateandtime`,`customerid`)
) ENGINE=InnoDB AUTO_INCREMENT=11073789 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (transactionid)
(PARTITION p0 VALUES LESS THAN (100000000) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200000000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (300000000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (400000000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (500000000) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (600000000) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (700000000) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (800000000) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (900000000) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (1000000000) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Also, I ran 5 instances of iiBench simultaneously to simulate 5 concurrent connections writing to the table, with each instance of iiBench writing 200 million single row inserts, for a total of 1 billion rows. I ran the test both with the table purchases_noindex which has no secondary index and only a primary index, and against the table purchases_index which has 3 secondary indexes. Another thing I would like to share is that, the size of the table without secondary indexes is 56G while the size of the table with secondary indexes is 181G.

Now let’s come down to the interesting part.

Results

With the table purchases_noindex, that has no secondary indexes, I was able to achieve an avg. insert rate of ~25k INSERTs Per Second, while with the table purchases_index, the avg. insert rate reduced to ~9k INSERTs Per Second. Let’s take a look at the graphs have a better view of the whole picture.

Note, in the above graph, we have “millions of rows” on the x-axis and “INSERTs Per Second” on the y-axis.
The reason why I have chosen to show “millions of rows” on the x-axis so that we can see the impact of growth in data-set on the insert rate.

We can see that adding the secondary indexes to the table has decreased the insert rate by 3x, and its not even consistent. While with the table having no secondary indexes, you can see that the insert rate is pretty much constant remaining between ~25k to ~26k INSERTs Per Second. But on the other hand, with the table having secondary indexes, we can see that there are regular spikes in the insert rate, and the variation in the rate can be classified as large, because it varies between ~6.5k to ~12.5k INSERTs per second, with noticeable spikes after every 100 million rows inserted.

I noticed that the insert rate drop was mainly caused by IO pressure caused by increase in flushing and checkpointing activity. This caused spikes in write activity to the point that the insert rate was decreased.

Conclusion

As we all now there are pros and cons to using secondary indexes. While secondary indexes cause read performance to improve, but they have an impact on the write performance. Well most of the apps rely on read performance and hence having secondary indexes is an obvious choice. But for those applications that are write mostly or that rely a lot on write performance, reducing the no. of secondary indexes or even going away with secondary indexes causes a write throughput increase of 2x to 3x. In this particular case, since I was mostly concerned with write performance, so I went ahead to choose a table structure with no secondary indexes. Other important things to consider when you are concerned with write performance is using partitioning to reduce the size of the B+tree, having multiple buffer pool instances to reduce contention problems caused by buffer pool mutexes, using “estimate” checkpoint method to reduce chances of log flush storms and disabling the query cache.