1. Enhanced Monitoring feature

pg_stat_io:

pg_stat_io is the most useful piece of information added in the Postgres stats views from Postgres 16. It allows you to understand the I/O usage.

pg_stat_io is like a library’s circulation desk, keeping track of all the I/O operations in our PostgreSQL database.

By monitoring pg_stat_io, you can identify which objects (tables, indexes) are causing the most I/O, which backend processes (client queries, autovacuum) are generating the most I/O, and how efficiently your database is handling I/O operations.

By analyzing pg_stat_io data, you can identify performance bottlenecks, optimize queries, and ensure your database is handling I/O efficiently to maintain optimal performance.

1. mafiree=# SELECT * FROM pg_stat_io WHERE backend_type='client backend';
2. -[ RECORD 1 ]--+------------------------------
3. backend_type   | client backend
4. object         | relation
5. context        | bulkread
6. reads          | 9237884
7. read_time      | 0
8. writes         | 1229711
9. write_time     | 0
10. writebacks     | 0
11. writeback_time | 0
12. extends        | 
13. extend_time    | 
14. op_bytes       | 8192
15. hits           | 148612
16. evictions      | 720027
17. reuses         | 8501505
18. fsyncs         | 
19. fsync_time     | 
20. stats_reset    | 2023-11-20 04:09:06.185412+00

In the above example we can view the I/O operations done on a client backend process.

2. Advanced Logical Replication from standby server

PostgreSQL 16’s enhancement allowing logical replication from standby instances marks a significant advancement in replication strategies.

Here, we can conduct logical replication from a standby server that is already acting as a replica for a primary server.

3. Developer Experience Improvement:

Aesthetic improvements in numeric literals have been introduced in postgres 16.

Now, developers can use underscores as thousands separators for better readability (e.g., 10_000_000). Which are not supported in the earlier versions.

We can use 1_000_000 instead of writing as 1000000, which can be easier to understand.

1. //Example which postgres 16 allows underscore values//
2. mafiree=# select 10_000_000 As Thousands_underscore;
3.  thousands_underscore 
4. ----------------------
5.              10000000
6. (1 row)

Additionally, non-decimal integer literals, such as 0x1538, 0o12470, and 0b1010100111000, are now supported, allowing for more diverse and expressive ways to represent numeric values.

Which uses 0x, 0o and 0b as prefixes to identify the values.

1. //Example which the non decimal values are supported and converted to decimal values // 
2. mafiree=# SELECT 0x1538 as hexa, 0o12450 as octal, 0b1010100111001 as binary;
3.  hexa | octal | binary 
4. ------+------+--------
5.  5432 | 5416 |   5433
6. (1 row)

4. New JSON Functions in Postgres 16

In PostgreSQL 16, there are new features that follow the SQL/JSON standard. These include functions like JSON_ARRAY(), JSON_ARRAYAGG(), and IS_JSON, which brings new ways to work with JSON data.

IS JSON:

IS_JSON is a new feature which is supported in postgres 16 which identifies whether the expression is a json or not.

Note: It results in “t” (true) if it is a json object as it shows below.

1. //Example for IS JSON which results as ‘t’//
2. mafiree=# SELECT '{"newyork":"CITY"}' IS JSON;
3.  ?column? 
4. ----------
5.  t
6. (1 row)

Note: Else it results with “f” (false) as shown below.

1. //Example for IS JSON which results as ‘f’//
2. mafiree=# SELECT 'HELLO' IS JSON;
3.  ?column? 
4. ----------
5.  f
6. (1 row)

JSON_ARRAY():

JSON_ARRAY() is a recent addition in the release. It helps format values into an array in JSON. This function is handy for creating JSON arrays from different types of data.

1. //Example in which the output is in array format//
2. QUERY:
3. mafiree=# SELECT json_array('MAFIREE','DBA');
4.      json_array     
5. --------------------
6.  ["MAFIREE", "DBA"]
7. (1 row)

JSON_ARRAYAGG() :

JSON_ARRAYAGG() is a new function which aggregates all the values of the expressions in a JSON array.

For example consider employee table with values as below,

1. mafiree=# SELECT * FROM employee;
2. +----+-------+-------------+
3. | id | name  | designation |
4. +----+-------+-------------+
5. |  1 | ram   | DBA         |
6. |  2 | kavin | DEVELOPER   |
7. |  3 | kumar | TESTER      |
8. +----+-------+-------------+
9. (3 rows)

Now perform JSON_ARRAYAGG() operation in the employee table.

1. mafiree=# SELECT JSON_ARRAYAGG(designation) FROM employee;
2. +--------------------------------+
3. |         json_arrayagg          |
4. +--------------------------------+
5. | ["DBA", "DEVELOPER", "TESTER"] |
6. +--------------------------------+
7. (1 row)

Here we can see the designation column of the employee table is aggregated into array values.

5.Enhanced Security Features

sslrootcert=”system” is a new crucial parameter, designed to augment SSL/TLS security. When set, PostgreSQL uses the trusted certificate authority (CA) stored by the client’s operating system. This feature ensures a more robust and standardized approach to certificate validation, enhancing the overall security posture of database connections.

The require_auth parameter which serves as a gatekeeper, enabling clients to specify their willingness to accept particular authentication parameters from the server. This allows clients to set stringent criteria, ensuring they only accept authentication methods that meet their specific security standards.

6.Efficient Data Loading using COPY

The COPY command in PostgreSQL has been a workhorse for importing and exporting data.

Performance benchmarks conducted revealed staggering improvements of up to 300% in specific scenarios, showcasing the remarkable advancements in PostgreSQL 16’s data loading capabilities.

In different versions of PostgreSQL like 13, 15, and 16, there are significant differences in performance.

For example, when importing 15 million records into PostgreSQL 13, performing the COPY command takes roughly 60 seconds. With PostgreSQL 15, it’s a bit faster, completing the same command in around 53 seconds. But in PostgreSQL 16, this COPY command finishes much quicker, in just 14 seconds.

Postgres-13

1. mafiree=# COPY customer_copy from '/var/lib/pgsql/13/customer_copy.csv';
2. COPY 15000000
3. Time: 60509.483 ms (00:60.509)

Postgres-15

1. mafiree=# COPY customer_copy from '/var/lib/pgsql/15/customer_copy.csv';
2. COPY 15000000
3. Time: 53207.692 ms (00:53.208)

Postgres-16

1. mafiree=# COPY customer_copy from '/var/lib/pgsql/16/customer_copy.csv';
2. COPY 15000000
3. Time: 14300.692 ms (00:14.300)

This indicates that the bulk loading using the COPY command has been enhanced in PostgreSQL version 16.

Feel the benefits of the latest and most advanced release by upgrading to PostgreSQL 16 today!

In Mafiree, we help you in upgrading PostgreSQL and also we offer 24x7 PostgreSQL monitoring, daily automated archival, backups, and various other PostgreSQL services. Feel free to contact us via sales@mafiree.com for PostgreSQL Database Services.