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

Postgres - What is New in PostgreSQL 12?

By Günce Kaya

Postgres - What is New in PostgreSQL 12?

1,491

What is New in

PostgreSQL 12

Index

Pluggable Storage

Partition

Partition

Partition

Partition

Partition

Index

Optimizer

Optimizer

Optimizer

Optimizer

Monitoring

Monitoring

Monitoring

Monitoring

Server Configuration

Replication and Recovery

Replication and Recovery

Replication and Recovery

Replication and Recovery

Utilities

Utilities

Utilities

Utilities

Utilities

pg_dump, pg_dumpall

Functions

Functions

VACUUM/ANALYZE

Others

Others

References

Thanks :)

Günce Kaya

Postgres - What is New in PostgreSQL 12?

More from Günce Kaya