What is New in

PostgreSQL 12

9 July 2019

Günce Kaya

guncekaya.blogspot.com

@gguncesi

Index

Pluggable Storage
Partition
Indexes
Optimizer
Monitoring
Server Configuration
Streaming Replication and Recovery
Utilities
Functions
Others

guncekaya.blogspot.com / @gguncesi

Pluggable Storage

Some architectural changes and refactoring
Basis for ZHEAP and columnar storage
CREATE ACCESS METHOD for TABLEs

guncekaya.blogspot.com / @gguncesi

Partition

Working with around a thousand partition tables is now more efficient.
INSERT and COPY to partitioned tables.
ATTACH PARTITION reduced locking queries.
FOREIGN KEY on partitioned tables

guncekaya.blogspot.com / @gguncesi

Partition

New Introspection functions

pg_partition_root()
pg_partition_ancestors()
pg_partition_tree()

guncekaya.blogspot.com / @gguncesi

Partition

New Introspection functions

pg_partition_root()

test=# SELECT pg_partition_root('players_2019') ;
 pg_partition_root
-------------------
 players
(1 row)

guncekaya.blogspot.com / @gguncesi

Partition

New Introspection functions

pg_partition_ancestors()

test=# SELECT pg_partition_ancestors('players_2019') ;
 pg_partition_ancestors
------------------------
 players_2019
 players
(2 rows)

guncekaya.blogspot.com / @gguncesi

Partition

New Introspection functions

pg_partition_tree()

test=# SELECT pg_partition_tree('players');
           pg_partition_tree            
----------------------------------------
 (players,,f,0)
 (players_2019,players,t,1)
 (players_2018,players,t,1)
 (players_2017,players,t,1)
(4 rows)

guncekaya.blogspot.com / @gguncesi

Index

Performance improvements for B-tree
Less disk space for B-tree
INCLUDE for GIST Indexes
Reduce WAL write for B-tree, GIN, SP-GIST
VACUUM is more efficient to remove tuples from indexes
Less locking when renaming indexes name

guncekaya.blogspot.com / @gguncesi

Optimizer

CTE can be inlined

[pg11] # EXPLAIN WITH test AS (SELECT generate_series(1,5)) SELECT * FROM test;
                       QUERY PLAN                        
---------------------------------------------------------
 CTE Scan on test  (cost=5.02..25.02 rows=1000 width=4)
   CTE test
     ->  ProjectSet  (cost=0.00..5.02 rows=1000 width=4)
           ->  Result  (cost=0.00..0.01 rows=1 width=0)
(4 rows)

[pg12]=# EXPLAIN WITH test as (SELECT generate_series(1,5)) SELECT * FROM test;
                   QUERY PLAN                   
------------------------------------------------
 ProjectSet  (cost=0.00..0.04 rows=5 width=4)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
(2 rows)

guncekaya.blogspot.com / @gguncesi

Optimizer

CTE with MATERIALIZED

[pg12]=# EXPLAIN WITH test as (SELECT generate_series(1,5)) 
         SELECT * FROM test;
                   QUERY PLAN                   
------------------------------------------------
 ProjectSet  (cost=0.00..0.04 rows=5 width=4)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
(2 rows)

[pg12]=# EXPLAIN WITH test as MATERIALIZED (SELECT generate_series(1,5)) 
       SELECT * FROM test;
                       QUERY PLAN                       
--------------------------------------------------------
 CTE Scan on test  (cost=0.04..0.14 rows=5 width=4)
   CTE test
     ->  ProjectSet  (cost=0.00..0.04 rows=5 width=4)
           ->  Result  (cost=0.00..0.01 rows=1 width=0)
(4 rows)

guncekaya.blogspot.com / @gguncesi

Optimizer

CTE with MATERIALIZED

[pg12]=# EXPLAIN WITH test AS NOT MATERIALIZED (SELECT generate_series(1,5)) 
         SELECT * FROM test;
                   QUERY PLAN                   
------------------------------------------------
 ProjectSet  (cost=0.00..0.04 rows=5 width=4)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
(2 rows)

guncekaya.blogspot.com / @gguncesi

Optimizer

EXPLAIN (SETTINGS ON)

[pg12]=# SET seq_page_cost=1.5;
SET
[pg12]=# EXPLAIN (settings on) SELECT * FROM test4;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on test4  (cost=0.00..35.40 rows=2040 width=12)
 Settings: seq_page_cost = '1.5'
(2 rows)

guncekaya.blogspot.com / @gguncesi

Monitoring

log_statement_sample_rate
Check CREATE INDEX and REINDEX via pg_stat_progress_create_index view

guncekaya.blogspot.com / @gguncesi

Monitoring

pg_stat_progress_create_index

-[ RECORD 1 ]------+-------------------------------
pid                | 13836
datid              | 16384
datname            | test
relid              | 24586
index_relid        | 0
command            | CREATE INDEX
phase              | building index: scanning table
lockers_total      | 0
lockers_done       | 0
current_locker_pid | 0
blocks_total       | 44248
blocks_done        | 27186
tuples_total       | 0
tuples_done        | 0
partitions_total   | 0
partitions_done    | 0

guncekaya.blogspot.com / @gguncesi

Monitoring

Check CLUSTER and VACUUM FULL via pg_stat_progress_cluster system view
- phases
  - initializing
  - scanning heap
  - vacuuming indexes
  - vacuuming heap
  - cleaning up indexes
  - truncating heap
  - performing final cleanup

guncekaya.blogspot.com / @gguncesi

Monitoring

Check checksum failures with new columns in pg_stat_database
- Checksum_failures and checksum_last_failure
Check last received standby messages pg_stat_replication

guncekaya.blogspot.com / @gguncesi

Server Configuration

Enable/disable checksum in a cluster with pg_checksums
pg_verify_checksum -> pg_checksum
vacuum_cost_delay can be sub-milliseconds
Possible to fractional input for integer server variables

SET effective_cache_size ='3.90';

guncekaya.blogspot.com / @gguncesi

Replication and Recovery

Recovery.conf file integrated into postgresql.conf
- standby_mode parameter is excluded now.
- promote_trigger_file (previous: trigger_file)

guncekaya.blogspot.com / @gguncesi

Replication and Recovery

Change replication parameters with reload
- archive_cleanup_command
- promote_trigger_file
- recovery_end_command
- recovery_min_apply_delay

guncekaya.blogspot.com / @gguncesi

Replication and Recovery

max_wal_senders not count as part of max_connections

guncekaya.blogspot.com / @gguncesi

Replication and Recovery

Promote standby to primary pg_promote()
Copy replication slots with functions
- pg_copy_physical_replication_slot()
- pg_copy_logical_replication_slot()

guncekaya.blogspot.com / @gguncesi

Utilities

REINDEX CONCURRENTLY
- reindexdb --concurrently

guncekaya.blogspot.com / @gguncesi

Utilities

Generated Columns

[pg12]=# CREATE TABLE test4(c1 int, c2 int, c3 int GENERATED ALWAYS AS (c1 * c2) STORED);
CREATE TABLE

[pg12]=# \d test4
                              Table "public.test4"
 Column |  Type   | Collation | Nullable |               Default                
--------+---------+-----------+----------+--------------------------------------
 c1     | integer |           |          | 
 c2     | integer |           |          | 
 c3     | integer |           |          | generated always as (c1 * c2) stored

guncekaya.blogspot.com / @gguncesi

Utilities

Generated Columns
Can not be specified as a partitioning keys!

[pg12]=# SELECT * FROM information_schema.column_column_usage ;
 table_catalog | table_schema | table_name | column_name | dependent_column 
---------------+--------------+------------+-------------+------------------
 test          | public       | test4      | c1          | c3
 test          | public       | test4      | c2          | c3
(2 rows)

[pg12]=# SELECT attname, attgenerated FROM pg_attribute 
         WHERE attname IN ('c1', 'c2', 'c3') ;
 attname | attgenerated 
---------+--------------
 c1      | 
 c2      | 
 c3      | s
(3 rows)

guncekaya.blogspot.com / @gguncesi

Utilities

COPY FROM with WHERE clause

[pg12]=# COPY(SELECT generate_series(1,10) as a, generate_series(1,5) as b) 
         TO '/tmp/test.csv' 
         HEADER DELIMITER ';' CSV;
COPY 10

[pg12]=# COPY test3 FROM '/tmp/test.csv' HEADER DELIMITER ';' CSV 
         WHERE a>5;
COPY 5

-- OR

[pg12]=# \copy test3 from '/tmp/test.csv' HEADER DELIMITER ';' CSV 
         WHERE a>5;
COPY 5

guncekaya.blogspot.com / @gguncesi

Utilities

COMMIT/ROLLBACK AND CHAIN

[pg12]=# BEGIN;
BEGIN
[pg12]=# INSERT INTO test VALUES(1);
INSERT 0 1
[pg12]=# COMMIT AND CHAIN;
COMMIT
[pg12]=# INSERT INTO test VALUES(2);
INSERT 0 1
[pg12]=# ROLLBACK AND CHAIN;
ROLLBACK
[pg12]=# END;
COMMIT
[pg12]=# SELECT * FROM test;
 generate_series 
-----------------
               1
(1 row)

guncekaya.blogspot.com / @gguncesi

pg_dump, pg_dumpall

pg_dump
- --rows-per-insert
- --on-conflict-do-nothing
pg_dumpall
- --exclude-database
pg_restore is now more efficient in parallel restores

guncekaya.blogspot.com / @gguncesi

Functions

Support new hyperbolic functions
- log10(), sinh(), cosh(), tanh(), asinh(), acosh(), atanh()
variance()
pg_ls_tmpdir()
pg_ls_archive_statusdir()
- ${PGDATA}/pg_wal/archive_status

guncekaya.blogspot.com / @gguncesi

Functions

Check timezone with date_trunc()

postgres=# SELECT date_trunc('day', 
            TIMESTAMP WITH TIME ZONE '2019-05-24 20:38:40+00', 'Africa/Malabo') ;
       date_trunc       
------------------------
 2019-05-23 19:00:00-04
(1 row)

guncekaya.blogspot.com / @gguncesi

VACUUM/ANALYZE

Added SKIP_LOCKED clause
ON/OFF
- VACUUM (VERBOSE OFF, FULL ON, ANALYZE true) test4;

guncekaya.blogspot.com / @gguncesi

Others

Pg_stat_replication has new column: reply_time
Pg_index catalog now shows partitioned indexes
Pg_stat_database has two new columns: checksum_failures and checksum_last_failure

guncekaya.blogspot.com / @gguncesi

Others

Possible to alter system catalog
- allow_system_table_mods value must be on

[pg12]=# ALTER TABLE pg_attribute SET (autovacuum_vacuum_scale_factor=0);
ALTER TABLE

guncekaya.blogspot.com / @gguncesi

References

https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_12_Beta1_New_Features_en_20190524-1.pdf
https://blog.dbi-services.com/postgresql-12-csv-output-format-for-psql/
https://momjian.us/main/writings/pgsql/features.pdf
https://www.postgresql.org/docs/devel/release-12.html
https://www.loxodata.com/post/postgresql-12beta1/
https://www.enterprisedb.com/blog/column-storage-intervals

guncekaya.blogspot.com / @gguncesi

Thanks :)

Günce Kaya

guncekaya.blogspot.com

@gguncesi

guncekaya.blogspot.com / @gguncesi