Overview: PostgreSQL 10 contains many new features that will allow users to both scale out and scale up their PostgreSQL infrastructure. To upgrade to PostgreSQL 10 from any lower releases, a pg_dumpall dump and restore or pg_upgrade is necessary. In this article, we will go over some new features that have been added and also significant modifications that have been done with a focus on information relevant for operational DBA’s in normal work.

Highlights:

  • Logical Replication has been included as a built-in option for replicating specific tables or using replication to upgrade
  • Native Table Partitioning with range and list partitioning
  • Additional Query Parallelism is included for index scans, bitmap scans, and merge joins

With highlights of PostgreSQL 10.0 as listed above, let us see the features under the below headers.

Architecture:

  1. Addition of new system catalog tables like pg_hba_file_rules, pg_partitioned_table, pg_stat_subscription, etc is certainly a highlight of PostgreSQL 10.
  2. New columns have been added to few of the earlier catalog tables like pg_class, pg_replication_slots, pg_stat_activity, pg_stat_replication, etc., for efficient management.
  3. PostgreSQL Client library libpq has been enhanced with the feature to add multi-instance specifications. Multiple host names and port numbers can be described in PGHOST And PGPORT. In addition, “passfile” has been added as a new connection attribute. It was earlier specified with the environment variable PGPASSFILE etc.
  4. Log directory pg_log has been changed to log.
  5. Also, pg_xlog has been renamed to pg_wal so as to distinguish more clearly between the directories write-ahead log and server activity logs.
  6. All SQL functions, tools, and options that reference “xlog” have been updated to “wal”. Example: pg_switch_xlog() is now pg_switch_wal(), pg_receivexlog is pg_receivewal, etc.
  7. Transaction status directory pg_clog has been renamed to pg_xact directory. The name “location” indicating the location of WAL has been changed to “lsn”. Example: sent_location to sent_lsn, write_location to write_lsn, etc.
  8. On instance start-up, the listen address and port number are logged, which was not the case earlier.
  9. Hash index of previous versions did not generate WAL on update. In PostgreSQL 10, it now generates
  10. WAL, so it can now be used in streaming replication environments. The current warnings ” hash indexes are not WAL-logged” have been done away with.
  11. The size of WAL file size (determined by –with-wal-segsize option) has been increased to 128, 256, 512, 1024 versus the conventional 1 to 64.

Parameters with default values changed:

password_encryption   md5 
hot_standby           on 
log_line_prefix       %m[%p] 
max_replication_slots 10 
max_wal_senders       10 
wal_level             replica
max_parallel_workers_per_gather 2 

psql utility changes:

The following functions have been added to the psql command.

  1. \d command The format of table information outputted by \d command has been changed. The current “Modifier” column has been divided into Collation, Nullable, and Default.

postgres=# \d pgbench_accounts Table “snapshots.snap_mem”

Table "snapshots.snap_mem"
 Column  |            Type             | Collation | Nullable | Default 
---------+-----------------------------+-----------+----------+---------
 snap_id | integer                     |           |          | 
 dttm    | timestamp without time zone |           |          | now()
 metric  | text                        |           |          | 
 total   | numeric                     |           |          | 
 used    | numeric                     |           |          | 
 free    | numeric                     |           |          | 
Indexes:
    "idx_snap_mem_metric" btree (metric)
    "idx_snap_mem_snap_id" btree (snap_id)

  1. \gx command: This command re-runs the most recently executed SQL statement in the extended format.
postgres=# select count(1) from pgbench_accounts;

## count

5000000 (1 row)

postgres=# \gx 

-[ RECORD 1 ]-- 
count | 5000000

Replication:

  1. Logical replication of tables to standby servers has been included. This provides enhanced replication options, including replication between different major versions of Postgres and selective-table replication.
  2. In Postgresql 9.6, the server always waited for acknowledgement from standby server that appeared first in synchronous_standby_names. However, in version 10, synchronous_standby_names have the FIRST and ANY keywords. synchronous_standby_names = ANY 2(node1,node2,node3); synchronous_standby_names = FIRST 2(node1,node2). If ANY is specified, it will not depend on the order of the instances specified in parameter application_name.
  3. In pg_hba.conf, replication from localhost connections has been enabled.
  4. pg_stat_replication has been added with more columns like write_lag, flush_lag, and replay_lag added to report replication delay times.

Backup and Recovery:

  1. pg_dump is introduced with the options of excluding binary long objects. Adding the option –no-blobs suppresses the dumping of large objects.
  2. pg_dumpall option is introduced with –no-role-passwords. This dumps roles without user passwords.
  3. pg_basebackup:
    • The default WAL transfer mode is now Stream. For this reason, connections to multiple wal sender processes are used by default.
    • The -x option (–xlog option) has been deprecated.
    • The -X option with value “none” (transaction log is not included in the backup) can be specified now.
    • The option “–xlogdir” has been changed to “–waldir”.
    • The option to output backup data to tar file -Ft and the -Xstream option can now be used at the same time. In this case, the pg_wal.tar file, in which transaction logs are stored in the directory specified by the -D option, is the output.
  4. Temporary replication slots can now be created. A temporary replication slot is the same as a normal replication slot except that it is automatically deleted once the session is terminated. When the slot name (-S) is not specified (and –no-slot is not specified), temporary replication slot is used.
  5. In PosgreSQL 10, a new parameter recovery_target_lsn related to “Point In Time Recovery” has been added in recovery configuration file. This parameter specifies the LSN of the write-ahead log location up to which recovery will proceed.
  6. pg_restore now has the flexibility to specify the name of the schema not to be restored. This is a newly added feature.

Monitoring:

  1. Wait event catalog pg_stat_activity has two columns added – wait_event_type and wait_event. Using this, one can narrow down the reason for the wait and more clearly the type of wait.
  2. EXPLAIN statement now has a SUMMARY clause to obtain the execution plan generation time alone.
  3. Output of VACUUM VERBOSE statement now contains the oldest xmin and frozen pages.

Performance:

  1. Partitioning: Tables can be created with PARTITION BY option. “Partition by” has been made an attribute of the table. Unlike earlier version table partitioning which uses conventional inheritance table, performance during data insertion has been greatly improved. 2. Parallel Querying: In PostgreSQL 9.6, parallel query feature was introduced in sequential scan. In PostgreSQL 10 parallel queries are now available in “Index Scan”, “Merge Join”, “Bitmap Join” and so on. It is expected to improve query performance for large amount tables.

References: https://www.postgresql.org/docs/devel/static/release-10.html https://wiki.postgresql.org/wiki/New_in_postgres_10#Additional_FDW_Push-Down http://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_10_New_Features_en_20170522-1.pdf?utm_source=postgresweekly&utm_medium=email