This latest and greatest version of PostgreSQL, version 13, has been here since last summer and continues the tradition of gains in performance, administration, application development, and security enhancements.
Of the many new features and enhancements that have been made, some of the more notable ones include:
- Space savings and performance gains from de-duplication of B-tree index entries
- Improved performance for queries that use aggregates or partitioned tables
- Better query planning when using extended statistics
- Parallelized vacuuming of indexes
- Incremental sorting
TIP: More detailed information can be found in the Release Notes here.
I learned long ago that it can be something of a challenge keeping up to date with all this new stuff. Therefore, I’d like to cover a small subset of these new advances by demonstrating simple use cases which I hope you will find constructive and are related to pgbench, logical replication, streaming replication fail-over, and re-provisioning a deprecated PRIMARY as a STANDBY using pg_rewind.
About pgbench
As you may recall, pgbench is a simple program for benchmark tests on PostgreSQL by running the same sequence of SQL commands over and over in multiple concurrent database sessions. Pgbench finds its most common use, as the name implies, as a benchmarking tool testing hardware and configuration runtime parameters for the purposes of performance tuning.
The latest iteration of pgbench has a number of new capabilities and includes:
- A new set of options chooses data generation either from the client or the server side i.e. ‘g’, and ‘G’. Whereas previous versions generated data on the pgbench client and then sent it to the server, one now has the ability to test data generation exclusively on the server itself without being affected by network latency.
- The ability to create a partitioned “accounts” table using either range or hash partitioning although the default is range partitioning.
- A new option ‘–show-script’ which echoes the actual code of any built-in script name on stderr.
Example 1: Hash and Range Partitioning
The first step is to of course initialize the benchmarking, in this case, we start out using hash partitioning:
# hash partitioning table public.pgbench_accounts
pgbench -i --partition-method=hash --partitions=5
...
creating tables...
creating 5 partitions...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.04 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.79 s (drop tables 0.02 s, create tables 0.17 s, client-side generate 0.13 s, vacuum 0.26 s, primary keys 0.22 s).
Here’s what it should look like:
Schema | Name | Type | Owner | Persistence | Size
--------+--------------------+-------------------+----------+-------------+---------+
public | pgbench_accounts | partitioned table | postgres | permanent | 0 bytes
public | pgbench_accounts_1 | table | postgres | permanent | 2656 kB
public | pgbench_accounts_2 | table | postgres | permanent | 2656 kB
public | pgbench_accounts_3 | table | postgres | permanent | 2656 kB
public | pgbench_accounts_4 | table | postgres | permanent | 2656 kB
public | pgbench_accounts_5 | table | postgres | permanent | 2656 kB
public | pgbench_branches | table | postgres | permanent | 40 kB
public | pgbench_history | table | postgres | permanent | 0 bytes
public | pgbench_tellers | table | postgres | permanent | 40 kB
And here’s the partitioned pgbench_accounts table definition:
Partitioned table "public.pgbench_accounts"
Column | Type | Collation | Nullable | Default | Storage | Stats target Description
----------+---------------+-----------+----------+---------+----------+--------------
aid | integer | | not null | | plain |
bid | integer | | | | plain |
abalance | integer | | | | plain |
filler | character(84) | | | | extended |
Partition key: HASH (aid)
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Foreign-key constraints:
"pgbench_accounts_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
Referenced by:
TABLE "pgbench_history" CONSTRAINT "pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid)
Partitions: pgbench_accounts_1 FOR VALUES WITH (modulus 5, remainder 0),
pgbench_accounts_2 FOR VALUES WITH (modulus 5, remainder 1),
pgbench_accounts_3 FOR VALUES WITH (modulus 5, remainder 2),
pgbench_accounts_4 FOR VALUES WITH (modulus 5, remainder 3),
pgbench_accounts_5 FOR VALUES WITH (modulus 5, remainder 4)
Alternatively, we can just as easily partition using range partitioning:
# range partitioning public.pgbench_accounts
pgbench -i --partition-method=range --partitions=5
Partitioned table "public.pgbench_accounts"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------------+-----------+----------+---------+----------+--------------+
aid | integer | | not null | | plain | |
bid | integer | | | | plain | |
abalance | integer | | | | plain | |
filler | character(84) | | | | extended | |
Partition key: RANGE (aid)
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Partitions: pgbench_accounts_1 FOR VALUES FROM (MINVALUE) TO (20001),
pgbench_accounts_2 FOR VALUES FROM (20001) TO (40001),
pgbench_accounts_3 FOR VALUES FROM (40001) TO (60001),
pgbench_accounts_4 FOR VALUES FROM (60001) TO (80001),
pgbench_accounts_5 FOR VALUES FROM (80001) TO (MAXVALUE)
For those people curious to see the actual commands creating the partition(s) just update the runtime parameter and look in your postgres log:
postgres=# alter system set log_statement = 'ddl';
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
postgres=# show log_statement;
log_statement
---------------
ddl
LOG: statement: drop table if exists pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers
LOG: statement: create table pgbench_history(tid int,bid int,aid int,delta int,mtime timestamp,filler char(22))
LOG: statement: create table pgbench_tellers(tid int not null,bid int,tbalance int,filler char(84)) with (fillfactor=100)
LOG: statement: create table pgbench_accounts(aid int not null,bid int,abalance int,filler char(84)) partition by range (aid)
LOG: statement: create table pgbench_branches(bid int not null,bbalance int,filler char(88)) with (fillfactor=100)
LOG: statement: create table pgbench_accounts_1
partition of pgbench_accounts
for values from (minvalue) to (20001) with (fillfactor=100)
LOG: statement: create table pgbench_accounts_2
partition of pgbench_accounts
for values from (20001) to (40001) with (fillfactor=100)
LOG: statement: create table pgbench_accounts_3
partition of pgbench_accounts
for values from (40001) to (60001) with (fillfactor=100)
LOG: statement: create table pgbench_accounts_4
partition of pgbench_accounts
for values from (60001) to (80001) with (fillfactor=100)
LOG: statement: create table pgbench_accounts_5
partition of pgbench_accounts
for values from (80001) to (maxvalue) with (fillfactor=100)
LOG: statement: alter table pgbench_branches add primary key (bid)
LOG: statement: alter table pgbench_tellers add primary key (tid)
LOG: statement: alter table pgbench_accounts add primary key (aid)
Example 2: Benchmarking
Part I
Let’s initialize a database with the following conditions:
- install pg_stat_statements to see our results
- initialize benchmarking
- generate data solely on the server-side
- create primary keys
- create foreign key constraints
- range partition table “accounts” creating five child tables
Open file postgresql.conf? and edit the following runtime parameters. Restart the server and add extension pg_stat_statement to any database using the command create pg_stat_statement.
# pg_conftool 13 main postgresql.conf edit
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
systemctl restart postgresql@13-main
Initialize an empty database:
export PGHOST=pg1-POC13 PGPORT=5432 PGDATABASE=db01 PGUSER=postgres PGPASSWORD=mypassword
createdb db01
pgbench -i -s 10 -I dtGvpf -F 90 --partition-method=hash --partitions=5
This query should return a nice summary of the commands thus far executed:
SELECT query, calls, total_exec_time::int, rows, (100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0))::int AS hit_percent
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
-[ RECORD 1 ] ----------------+------------------------------------------------------------------------------------
query | SELECT abalance FROM pgbench_accounts WHERE aid = $1
calls | 1560162
total_exec_time | 73958
rows | 1560162
hit_percent | 94
-[ RECORD 2 ]
----------------+------------------------------------------------------------------------------------
query | insert into pgbench_accounts(aid,bid,abalance,filler) select aid, (aid - $1) / $2 + $3, $4, $5 from generate_series($7, $8) as aid
calls | 1
total_exec_time | 2250
rows | 1000000
hit_percent | 100
-[ RECORD 3 ]
----------------+------------------------------------------------------------------------------------
query | create database db03
calls | 1
total_exec_time | 2092
rows | 0
hit_percent | 90
-[ RECORD 4 ]
----------------+------------------------------------------------------------------------------------
query | vacuum analyze pgbench_accounts
calls | 1
total_exec_time | 1591
rows | 0
hit_percent | 92
-[ RECORD 5 ]
----------------+------------------------------------------------------------------------------------
query | alter table pgbench_accounts add primary key (aid)
calls | 1
total_exec_time | 1086
rows | 0
hit_percent | 59
Part II
Execute the benchmarking for a duration of five minutes. Just to make things clearer, you can reset the stats in view pg_stat_statements.
# clear the stats before starting the benchmarking
# SELECT pg_stat_statements_reset();
pgbench -c 40 -j 7 -T 300 -b tpcb-like db01 -P 60
New SQL statements representing the DML operations are now listed:
SELECT query, calls, total_exec_time::int, rows, (100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0))::int AS hit_percent
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
-[ RECORD 1 ]
---+-----------------------------------------------------------------------------------------------------------
query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
calls | 42767
total_exec_time | 6203809
rows | 42767
hit_percent | 100
-[ RECORD 2 ]
---+-----------------------------------------------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls | 42767
total_exec_time | 3146175
rows | 42767
hit_percent | 100
-[ RECORD 3 ]
---+-----------------------------------------------------------------------------------------------------------
query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls | 42767
total_exec_time | 28281
rows | 42767
hit_percent | 95
-[ RECORD 4 ]
---+-----------------------------------------------------------------------------------------------------------
query | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)
calls | 42767
total_exec_time | 22797
rows | 42767
hit_percent | 100
-[ RECORD 5 ]
---+-----------------------------------------------------------------------------------------------------------
query | SELECT $2 FROM ONLY "public"."pgbench_branches" x WHERE "bid" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
calls | 42767
total_exec_time | 2347
rows | 42767
hit_percent | 100
Notice how the child tables have grown in size:
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+--------------------+-------------------+----------+-------------+---------+
public | pgbench_accounts | partitioned table | postgres | permanent | 0 bytes |
public | pgbench_accounts_1 | table | postgres | permanent | 28 MB |
public | pgbench_accounts_2 | table | postgres | permanent | 29 MB |
public | pgbench_accounts_3 | table | postgres | permanent | 28 MB |
public | pgbench_accounts_4 | table | postgres | permanent | 28 MB |
public | pgbench_accounts_5 | table | postgres | permanent | 28 MB |
public | pgbench_branches | table | postgres | permanent | 168 kB |
public | pgbench_history | table | postgres | permanent | 2384 kB |
public | pgbench_tellers | table | postgres | permanent | 272 kB |
About Logical Replication
We now explore another very useful addition to pg version 13 which is the transparent replication of partitioned tables via logical decoding. No longer is it necessary to expend valuable time manually adding the triggers and supplementary instruction replicating the child tables.
Example
Using the aforementioned pgbench example, execute the following. Note that one must update table history, by adding a primary key, otherwise, replication is not possible with this table:
# UPDATE TABLE, ADD PK
alter table public.pgbench_history add primary key (tid,bid,aid,mtime);
# CREATE DATABASE ON SUBSCRIBER NODE
createdb -h pg4-POC13 -U postgres db
# COPY DATABASE SCHEMA
pg_dump -h pg1-POC13 -U postgres -s db01 | psql 'host=pg4-POC13 user=postgres dbname=db01'
#
# PROVIDER pg1-POC13: DB01
#
psql 'host=pg1-POC13 user=postgres password=mypassword dbname=db01' <<_eof_
\set ON_ERROR_STOP
create publication publication1 for all tables;
_eof_
#
# SUBSCRIBER pg4-POC13: DB01
#
psql 'host=pg4-POC13 user=postgres password=mypassword dbname=db01' <<_eof_
\set ON_ERROR_STOP
create subscription subscript_set1
connection 'host=pg1-POC13 dbname=db01 user=postgres password=mypassword'
publication publication1
with (copy_data=true, create_slot=true, enabled=true, slot_name=myslot1);
_eof_
And here we see the child accounts tables have been correctly replicated:
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+--------------------+-------------------+----------+-------------+------------+
public | pgbench_accounts | partitioned table | postgres | permanent | 0 bytes |
public | pgbench_accounts_1 | table | postgres | permanent | 28 MB |
public | pgbench_accounts_2 | table | postgres | permanent | 29 MB |
public | pgbench_accounts_3 | table | postgres | permanent | 28 MB |
public | pgbench_accounts_4 | table | postgres | permanent | 28 MB |
public | pgbench_accounts_5 | table | postgres | permanent | 28 MB |
public | pgbench_branches | table | postgres | permanent | 8192 bytes |
public | pgbench_history | table | postgres | permanent | 2352 kB |
public | pgbench_tellers | table | postgres | permanent | 8192 bytes |
About Streaming Replication
Because of the large number of new features added to streaming replication, I’m limiting the focus on two enhancements:
- Restarting a STANDBY in order to point it to a newly promoted PRIMARY, after updating primary_conninfo and primary_slot_name, is no longer necessary as a reload will suffice.
- pg_rewind has a new option, –write-recovery-conf, that simplifies the steps of reprovisioning a failed PRIMARY as a viable STANDBY, similarly to the one found in pg_basebackup.
Recall the three-node streaming replication cluster we’ve been using:
pg1-POC13: PRIMARY (read-write)
pg2-POC13: REPLICA (read-only, streaming)
pg3-POC13: REPLICA (read-only, streaming)
Example 1: STANDBY, pg3, Points to New PRIMARY pg2
Suppose a failover promotes pg2. This new feature adds robustness to the entire database cluster. REPLICA pg3 continues service without interruption as it is redirected from pg1 to pg2. Previous versions of postgres required host pg3 to be restarted in order to effect new primary_conninfo parameters.
Step 1
pg3-POC13; run a benchmark of SELECTS on host pg3
pgbench -h pg3-POC13 -U postgres -c 40 -j 7 -T 300 -b select-only db01 -P 5
Step 2
pg2-POC13; promote the host while the bench-marking on pg3 is active
-- create a new slot for pg3
select * from pg_create_physical_replication_slot('pg3');
-- confirm slot is inactive
select * from pg_get_replication_slots();
-- promote host
select pg_promote():
-- confirm read-write state
select pg_is_in_recovery();
TIP: it’s understood that runtime parameter wal_keep_size is a non-zero value and is set sufficiently retaining WALs as pg3 is redirected away from pg1 towards pg2.
Step 3
pg3-POC13; point to pg2-POC13
-- make the updates
alter system set primary_conninfo = 'user=postgres host=10.231.38.112';
alter system set primary_slot_name = 'pg3';
select pg_reload_conf();
-- confirm replication is active
select * from pg_stat_wal_receiver;
Step 4
pg2-POC13; validate replication from pg2->pg3
-- confirm slot is active
select * from pg_get_replication_slots();
-- confirm replication is active
select * from pg_stat_replication;
Example 2
When re-provisioning a failed PRIMARY, ie pg1, as a new STANDBY, it used to be necessary to edit the requisite recovery runtime configuration parameters after executing pg_rewind but before starting the host.
PostgreSQL version 13 now simplifies the exercise by providing the switch –write-recovery-conf and updating postgresql.auto.conf with the correct runtime parameters.
TIP: pg_rewind requires runtime parameter wal_log_hints=on before re-provisioning takes place on the failed PRIMARY
Step 1
pg2-POC13; add a slot on PRIMARY pg2 for the new STANDBY pg1
-- as postgres create a new slot
select * from pg_create_physical_replication_slot('pg1');
select * from pg_get_replication_slots();
Step 2
pg1-POC13; re-provision pg1
# as postgres, perform the rewind
/usr/lib/postgresql/13/bin/pg_rewind \
--target-pgdata=/var/lib/postgresql/13/main \
--source-server="host=pg2-POC13 port=5432 user=postgres dbname=postgres " \
--write-recovery-conf \
--progress
You should get messaging similar to the following:
pg_rewind: connected to server
pg_rewind: servers diverged at WAL location 0/6CDCEA88 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/6CDCE9D8 on timeline 1
pg_rewind: reading source file list
pg_rewind: reading target file list
pg_rewind: reading WAL in target
pg_rewind: need to copy 206 MB (total source directory size is 422 MB)
211594/211594 kB (100%) copied
pg_rewind: creating backup label and updating control file
pg_rewind: syncing target data directory
pg_rewind: Done!
Now you can restart host pg1-POC13 and bring it back into service:
# as root, restart the server
systemctl start postgresql@13-main
Login pg1 and confirm replication:
# confirm replication is active
select * from pg_stat_wal_receiver;
Caveat
In the case of failure, check the following:
- pg2-POC13: As the PRIMARY you will want to check that you have a slot installed and ready before starting up the reprovisioned pg1 as a new standby
- pg1-POC13:
- check to see if he server started up i.e. ‘netstat -tlnp’
- check postgresql.auto.conf if you get a FATAL server is starting up message
- check the slot name
- check connectivity info is correct
Conclusion
Although not germane to features and capabilities, I was a little curious to see the differences between PostgreSQL 13 and earlier versions. Here’s a table with metrics creating a data cluster, database, and time required initializing pgbench. It’s interesting to see how performance times have improved over the previous versions:
initdb datacluster binaries createdb pgbench -i
ver time size(bytes) size(bytes) time time
9.6 0m0.889s 38,877,134 38,844,934 0m0.311s 0m0.236s
10 0m0.729s 39,598,542 42,054,339 0m0.725s 0m0.240s
11 0m0.759s 40,844,747 41,336,566 0m0.683s 0m0.212s
12 0m0.592s 41,560,196 43,853,282 0m0.179s 0m0.213s
13 0m0.502s 41,266,877 65,652,665 0m0.188s 0m0.168s
Frankly, I find it amazing how the size of both binaries and the data cluster has remained so compact over the years. I remember, way back in 2000, I was working with a proprietary RDBMS that upon initialization created an empty instance of 1GB in size – I wonder how big it gets these days??
Happy Hacking!
References:
PostgreSQL 13 Released!
PostgreSQL 13 Documentation
PostgreSQL Versioning Policy
Our white paper “Why Choose PostgreSQL?” looks at the features and benefits of PostgreSQL and presents some practical usage examples. We also examine how PostgreSQL can be useful for companies looking to migrate from Oracle.
Download PDF
The “SHOW PROCESSLIST” command is very famous and very useful for MySQL DBAs. It will help you to understand the ongoing thread activities and their current states. By default, the “show processlist” output details will be collected from the thread manager, and it needs the global mutex. From MySQL 8.0.22, we have an alternative way to get the process details from the PERFORMANCE_SCHEMA. It doesn’t need the global mutex. ?
I was once helping one of our customers on how to load MySQL audit logs into a MySQL database and analyze them. But immediately I thought: “Hey, this is not the most efficient solution! MySQL or typical RDBMS, in general, were not really meant to store logs after all.”
Are you thinking about deploying MongoDB? Is it the right choice for you?
What is Edge
In this blog, we will review all the backup and restore strategies for MySQL, the cornerstones of any application. There are a few options, depending on your topology, MySQL versions, etc. And based on that, there are some questions we need to ask ourselves to make sure we make the right choices.
Today we are going to present you with another way to make a database copy between servers using our 
It has been some time since I have posted updates to my plugins. After the initial version, I decided to split the plugins into categories of metrics. This will allow users to choose whether they want to install all of the plugins or only select ones they care about.
In previous TAM Enterprise Experiences posts, we have outlined typical aspects of utilizing MySQL in an Enterprise environment. One thing we have not yet covered is the topic of database encryption, both from the standpoint of business requirements as well as some of the more technical aspects of encryption.