PostgreSQL 12: What is coming up?, Enterprise Postgres Day

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.3 ME: • Using PostgreSQL since 1998. • London PostgreSQL Prime Minister • “The Guy With The PostgreSQL Tattoo” • Responsible for PostgreSQL YUM (https://yum.postgresql.org) and ZYPP (https://zypp.postgresql.org) repositories, where we host 200+ software • PostgreSQL Major Contributor • Fedora / EPEL packager for 50+ packages. • London, UK.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.8 AGENDA • JIT • psql improvements • DBA features • Table Partitioning • • Indexes • The Query Planner • SQL Features • Odds and Ends • DoS prevention for some commands • Postscript: Advanced Server

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.12 JIT  Now enabled by default  auto_explain and EXPLAIN exposes more info about JIT  Red Hat / CentOS / Fedora requires postgresql12-llvmjit  Debian / Ubuntu already bundles in core package.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.15 psql improvements • Add options for procedures in df • Show IP addresses in conninfo  $ psql -p 5412 -h localhost -U postgres -c "conninfo"  You are connected to database "postgres" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5412".  $ psql -p 5412 -U postgres -c "conninfo"  You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5412".

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.16 psql: Improvements • Add options for procedures in df • Show IP addresses in conninfo  $ psql -p 5412 -h localhost -U postgres -c "conninfo"  You are connected to database "postgres" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5412".  $ psql -p 5412 -U postgres -c "conninfo"  You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5412". • Useful if hostname resolves to multiple IP addresses

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.17 psql: URLs in HELP • [postgres] # help CREATE DATABASE • Command: CREATE DATABASE • Description: create a new database • Syntax: • CREATE DATABASE name • [ [ WITH ] [ OWNER [=] user_name ] • [ TEMPLATE [=] template ] • <trrimmed> • URL: https://www.postgresql.org/docs/12/sql-createdatabase.html

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.18 psql: Tab complete improvements • ALTER DATABASE … SET TABLESPACE • Include partitioned tables in what's offered after ANALYZE. • Include toast_tuple_target in what's offered after ALTER TABLE ... SET|RESET. • Include HASH in what's offered after PARTITION BY. • CREATE TABLE <name> with '(', OF or PARTITION OF. • CREATE TABLE <name> OF with list of composite types. • CREATE TABLE name (...) with PARTITION OF, WITH, TABLESPACE, ON

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.19 psql: Tab complete improvements • COMMIT (depending on the presence of a temporary table). • -CREATE TABLE ON COMMIT with actions (only for temporary tables). • SKIP_LOCKED option for VACUUM and ANALYZE • ALTER INDEX … ALTER COLUMN … <column number goes here) • Add completion for storage parameters after CREATE TABLE WITH • Improve tab completion of ALTER INDEX/TABLE with SET STATISTICS in psql • ...and a few more.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.21 SKIP_LOCKED for VACUUM and ANALYZE • Allows VACUUM to skip the work on a relation if there is a conflicting lock on it when trying to open it at the beginning of its processing.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.22 SKIP_LOCKED for VACUUM and ANALYZE • Allows VACUUM to skip the work on a relation if there is a conflicting lock on it when trying to open it at the beginning of its processing. • Note: v11: VACUUM can process multiple tables

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.23 SKIP_LOCKED for VACUUM and ANALYZE • Allows VACUUM to skip the work on a relation if there is a conflicting lock on it when trying to open it at the beginning of its processing. • Note: v11: VACUUM can process multiple tables • vacuumdb also now has--skip-locked.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.24 SKIP_LOCKED for VACUUM and ANALYZE • [pagila] # VACUUM (FULL, SKIP_LOCKED,VERBOSE) film,language; • WARNING: skipping vacuum of "film" --- lock not available • INFO: vacuuming "public.language" • INFO: "language": found 0 removable, 6 nonremovable row versions in 1 pages • DETAIL: 0 dead row versions cannot be removed yet. • CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.07 s. • VACUUM

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.26 vacuumdb: --min-xid-age and --min-mxid-age • Improves the selectivity of the relations to vacuum or and analyze • Transaction ID or multixact ID

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.27 vacuumdb: --min-xid-age and --min-mxid-age • Improves the selectivity of the relations to vacuum or and analyze • Transaction ID or multixact ID • Chance to prioritize tables to prevent wraparound of one or the other.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.28 vacuumdb: --min-xid-age and --min-mxid-age • Improves the selectivity of the relations to vacuum or and analyze • Transaction ID or multixact ID • Chance to prioritize tables to prevent wraparound of one or the other. • When used with --table, opportunity to target subset of tables

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.30 max_connections and max_wal_senders • max_wal_senders is no more a part of max_connections • No more blocking of base backups, if there are enough max_wal_senders are available

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.31 max_connections and max_wal_senders • max_wal_senders is no more a part of max_connections • No more blocking of base backups, if there are enough max_wal_senders are available • Now it is like autovacuum and bgworkers.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.32 pg_dumpall and pg_dump • pg_dumpall: Now can exclude a database from pg_dumpall  Can be given once  The argument can be a pattern including wildcard characters.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.33 pg_dumpall and pg_dump • pg_dumpall: Now can exclude a database from pg_dumpall  Can be given once  The argument can be a pattern including wildcard characters. • pg_dump: Include ALTER INDEX … ALTER COLUMN … SET STATISTICS info

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.34 pg_dumpall and pg_dump • pg_dumpall: Now can exclude a database from pg_dumpall  Can be given once  The argument can be a pattern including wildcard characters. • pg_dump: Include ALTER INDEX … ALTER COLUMN … SET STATISTICS info • pg_dump: Now allows multiple rows per insert  Useful to speed up loading data in a different database engine.  pg_dump dbname --inserts --rows-per-insert=250 (Default:100)

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.35 postgresql.conf updates • New parameters:  ssl_min_protocol_version = 'TLSv1'  ssl_max_protocol_version = ''  shared_memory_type (Alternatives: nmap, sysv, windows)  log_statement_sample_rate = 1 → (0.1 to 1)

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.36 postgresql.conf updates • New parameters:  plan_cache_mode = auto (Alternatives: force_generic_plan, force_custom_plan  Executor level, not planner level

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.37 postgresql.conf updates • New parameters:  plan_cache_mode = auto (Alternatives: force_generic_plan, force_custom_plan  Executor level, not planner level • Updated parameters:  autovacuum_vacuum_cost_delay: 200 ms → 2 ms  extra_float_digits : 0 → 1  jit = off → on

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.39 PARTITION PRUNING  edb=# d foo  Table "public.foo"  Column | Type | Collation | Nullable | Default  --------+---------+-----------+----------+---------  a | integer | | |  b | text | | |  Partition key: RANGE (a)  Indexes:  "foo_a_idx" UNIQUE, btree (a)  Number of partitions: 1000 (Use d+ to list them.)  edb=# select * from foo where a = 20190625;  a | b  ----------+--------  20190625 | filler  (1 row)

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.40 PARTITION PRUNING edb=# EXPLAIN UPDATE foo set b = 'modification' WHERE a = 20190625; QUERY PLAN ------------------------------------------------------ Update on foo (cost=0.42..8.44 rows=1 width=42) Update on foo20 -> Index Scan using foo20_a_idx on foo20 (cost=0.42..8.44 rows=1 width=42) Index Cond: (a = 20190625) (4 rows)

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.42 FASTER COPY INTO PARTITIONED TABLES rhaas=# d bar Table "public.bar" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | not null | b | text | | | Partition key: HASH (a) Indexes: "bar_pkey" PRIMARY KEY, btree (a) Number of partitions: 8 (Use d+ to list them.) rhaas=# copy bar from '/Users/rhaas/testcase/testbar.csv' csv; -- 100k rows Time: 330.384 ms (on v11) Time: 276.175 ms (on v12beta, ~16% faster)

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.43 CONCURRENT ATTACH PARTITION rhaas=# select count(*) from foo; ... rhaas=# create table foo1000 (a int, b text); CREATE TABLE rhaas=# alter table foo attach partition foo1000 for values from (1000000000) to (1001000000); ALTER TABLE

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.44 CONCURRENT ATTACH PARTITION rhaas=# select count(*) from foo; ... rhaas=# create table foo1000 (a int, b text); CREATE TABLE rhaas=# alter table foo attach partition foo1000 for values from (1000000000) to (1001000000); ALTER TABLE On v11, the ALTER TABLE will block until the query completes.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.45 CONCURRENT ATTACH PARTITION rhaas=# select count(*) from foo; ... rhaas=# create table foo1000 (a int, b text); CREATE TABLE rhaas=# alter table foo attach partition foo1000 for values from (1000000000) to (1001000000); ALTER TABLE On v11, the ALTER TABLE will block until the query completes. On v12, it will not block.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.46 FOREIGN KEYS TO PARTITIONED TABLES rhaas=# d bar Table "public.bar" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | not null | b | text | | | Partition key: HASH (a) Indexes: "bar_pkey" PRIMARY KEY, btree (a) Number of partitions: 8 (Use d+ to list them.) rhaas=# create table bar_details (id serial primary key, a integer references bar (a), s text); ERROR: cannot reference partitioned table "bar"

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.47 FOREIGN KEYS TO PARTITIONED TABLES rhaas=# d bar Table "public.bar" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | not null | b | text | | | Partition key: HASH (a) Indexes: "bar_pkey" PRIMARY KEY, btree (a) Number of partitions: 8 (Use d+ to list them.) rhaas=# create table bar_details (id serial primary key, a integer references bar (a), s text); ERROR: cannot reference partitioned table "bar" • On v12, the error is gone!

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.48 AVOIDING MERGE APPEND v11: rhaas=# EXPLAIN SELECT * FROM foo ORDER BY a; QUERY PLAN ---------------------------------------------------- Merge Append (cost=475.04..86230275.02 rows=1000001270 width=11) Sort Key: foo0.a -> Index Scan using foo0_a_idx on foo0 (cost=0.42..31389.42 rows=1000000 width=11) ...

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.49 AVOIDING MERGE APPEND v12: rhaas=# EXPLAIN SELECT * FROM foo ORDER BY a; QUERY PLAN ---------------------------------------------------- Append (cost=425.15..36394030.55 rows=1000001270 width=11) -> Index Scan using foo0_a_idx on foo0 (cost=0.42..31389.42 rows=1000000 width=11) ...

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.50 AVOIDING MERGE APPEND: RESULTS rhaas=# SELECT * FROM foo ORDER BY a OFFSET 1000000000; a | b ---+--- (0 rows) Time: 209820.957 ms (03:29.821) (on v11) Time: 169733.233 ms (02:49.733) (on v12beta, about 19% faster)

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.52 BTREE INDEX IMPROVEMENTS: TEST SETUP pgbench -i -s 100 CREATE INDEX on pgbench_accounts (filler); SELECT oid::regclass, pg_relation_size(oid) FROM pg_class WHERE relname LIKE 'pgbench%' AND relkind = 'i'; pgbench -T 300 -c 8 -j 8 -N SELECT oid::regclass, pg_relation_size(oid) from pg_class WHERE relname like 'pgbench%' and relkind = 'i';

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.53 BTREE INDEX IMPROVEMENTS: RESULTS Version Index on “filler” Index on “aid” v11 (initial) 1089 MB 214 MB v11 (final) 1204 MB 240 MB v12beta (initial) 1091 MB (+0.1%) 214 MB (+0.0%) v12beta (final) 1118 MB (-7.2%) 214 MB (-10.9%)

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.54 REINDEX CONCURRENTLY REINDEX takes ShareLock on table and AccessExclusiveLock on index, blocking basically all access to the table – everything except prepared queries that don’t use the index in question.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.55 REINDEX CONCURRENTLY REINDEX takes ShareLock on table and AccessExclusiveLock on index, blocking basically all access to the table – everything except prepared queries that don’t use the index in question. REINDEX CONCURRENTLY takes ShareUpdateExclusiveLock on both table and index, permitting concurrent reads and writes.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.56 REINDEX CONCURRENTLY REINDEX takes ShareLock on table and AccessExclusiveLock on index, blocking basically all access to the table – everything except prepared queries that don’t use the index in question. REINDEX CONCURRENTLY takes ShareUpdateExclusiveLock on both table and index, permitting concurrent reads and writes. Similar to DROP INDEX CONCURRENTLY + CREATE INDEX CONCURRENTLY.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.57 REINDEX CONCURRENTLY REINDEX takes ShareLock on table and AccessExclusiveLock on index, blocking basically all access to the table – everything except prepared queries that don’t use the index in question. REINDEX CONCURRENTLY takes ShareUpdateExclusiveLock on both table and index, permitting concurrent reads and writes. Similar to DROP INDEX CONCURRENTLY + CREATE INDEX CONCURRENTLY. Waits for concurrent transactions to end, twice.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.58 REINDEX CONCURRENTLY REINDEX takes ShareLock on table and AccessExclusiveLock on index, blocking basically all access to the table – everything except prepared queries that don’t use the index in question. REINDEX CONCURRENTLY takes ShareUpdateExclusiveLock on both table and index, permitting concurrent reads and writes. Similar to DROP INDEX CONCURRENTLY + CREATE INDEX CONCURRENTLY. Waits for concurrent transactions to end, twice. Watch out for invalid indexes if fails or is interrupted.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.61 GiST and SP-GiST Indexes • GiST indexes now support INCLUDE columns. • SP-GiST indexes now support K-nearest-neighbor searches. • GiST, GIN, and SP-GiST indexes now generate less WAL during index creation. • VACUUM of GiST indexes is now more efficient and can recycle empty leaf pages.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.63 PLAN CACHE MODE • Prepared queries can be handled in two ways.  Strategy #1: Replan the query each time it’s executed for the particular parameter values in use. (“custom plan”)  Strategy #2: Create a plan that will work with any parameter values and reuse it. (“generic plan”)

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.64 PLAN CACHE MODE • Prepared queries can be handled in two ways.  Strategy #1: Replan the query each time it’s executed for the particular parameter values in use. (“custom plan”)  Strategy #2: Create a plan that will work with any parameter values and reuse it. (“generic plan”) • By default, PostgreSQL will try to adaptively pick the best strategy.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.65 PLAN CACHE MODE • If you know better, you can set plan_cache_mode.  Typical use: Force custom plans, because the generic plans are worse than the planner thinks.  Possible use: Don’t waste any planning time trying to create worthless custom plans.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.66 SUPPORT FUNCTIONS FOR SQL FUNCTIONS • v12: • rhaas=# explain select * from generate_series(1, 437218) g; QUERY PLAN ---------------------------- -------------------------- • Function Scan on generate_series g (cost=0.00..4372.18 rows=437218 width=4)

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.68 SUPPORT FUNCTIONS FOR SQL FUNCTIONS • v11: • rhaas=# EXPLAIN SELECT * FROM generate_series(1, 437218) g; • QUERY PLAN • ------------------------------------------------------ • Function Scan on generate_series g (cost=0.00..10.00 rows=1000 width=4)

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.69 SUPPORT FUNCTIONS FOR SQL FUNCTIONS • v12: rhaas=# EXPLAIN SELECT * FROM generate_series(1, 437218) g; QUERY PLAN ------------------------------ ------------------------ Function Scan on generate_series g (cost=0.00..4372.18 rows=437218 width=4)

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.71 GENERATED COLUMNS • rhaas=# CREATE TABLE gce (a int, b int, c int GENERATED ALWAYS AS(a + b) stored); • Column c can’t be manually updated.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.72 GENERATED COLUMNS • rhaas=# CREATE TABLE gce (a int, b int, c int GENERATED ALWAYS AS(a + b) stored); • Column c can’t be manually updated. • It will be recomputed after every INSERT/UPDATE.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.73 GENERATED COLUMNS • rhaas=# CREATE TABLE gce (a int, b int, c int GENERATED ALWAYS AS(a + b) stored); • Column c can’t be manually updated. • It will be recomputed after every INSERT/UPDATE. • Easier (but not necessarily faster) than a TRIGGER.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.77 R.I.P. TO RECOVERY.CONF • Finally… • PostgreSQL now throws an error if recovery.conf is found. • (Almost) everything is moved into postgresql.conf

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.78 R.I.P. TO RECOVERY.CONF • Finally… • PostgreSQL now throws an error if recovery.conf is found. • (Almost) everything is moved into postgresql.conf • No more standby_mode

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.79 R.I.P. TO RECOVERY.CONF • Finally… • PostgreSQL now throws an error if recovery.conf is found. • (Almost) everything is moved into postgresql.conf • No more standby_mode • recovery_target_timeline=latest by default

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.81 R.I.P. TO RECOVERY.CONF • Settings previously stored in recovery.conf are now in postgresql.conf • Use recovery.signal or standby.signal to trigger recovery or standby mode

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.82 R.I.P. TO RECOVERY.CONF • Settings previously stored in recovery.conf are now in postgresql.conf • Use recovery.signal or standby.signal to trigger recovery or standby mode • Your backup management tool (or scripts) will likely need an update.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.83 R.I.P. TO RECOVERY.CONF • Settings previously stored in recovery.conf are now in postgresql.conf • Use recovery.signal or standby.signal to trigger recovery or standby mode • Your backup management tool (or scripts) will likely need an update. • A few recovery-related parameters can now be changed without restarting the server: archive_cleanup_command, promote_trigger_file, recovery_end_command, and recovery_min_apply_delay.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.84 R.I.P. TO RECOVERY.CONF • trigger_file → promote_trigger_file • pg_basebackup -R appends to postgresql.conf • A reload is enough for these parameters now: • archive_cleanup_command • promote_trigger_file • recovery_end_command • recovery_min_apply_delay;

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.85 ENABLE OR DISABLE CHECKSUMS OFFLINE [rhaas ~]$ pg_ctl stop waiting for server to shut down.... done server stopped [rhaas ~]$ time pg_checksums -e Checksum operation completed Files scanned: 6289 Blocks scanned: 10080538 pg_checksums: syncing data directory pg_checksums: updating control file Checksums enabled in cluster real 2m42.120s user 0m20.674s sys 1m30.388s [rhaas ~]$ du -hs $PGDATA 87G /Users/rhaas/pgdata

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.86 TABLE ACCESS METHODS • PostgreSQL can now support multiple table storage formats, just as we have for years been able to support multiple index formats (hash, btree, gist, etc.).

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.87 TABLE ACCESS METHODS • PostgreSQL can now support multiple table storage formats, just as we have for years been able to support multiple index formats (hash, btree, gist, etc.). • Currently, the only in-core table storage method is ‘heap’.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.88 TABLE ACCESS METHODS • PostgreSQL can now support multiple table storage formats, just as we have for years been able to support multiple index formats (hash, btree, gist, etc.). • Currently, the only in-core table storage method is ‘heap’. • Expect more choices in a year or two.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.89 TABLE ACCESS METHODS • PostgreSQL can now support multiple table storage formats, just as we have for years been able to support multiple index formats (hash, btree, gist, etc.). • Currently, the only in-core table storage method is ‘heap’. • Expect more choices in a year or two. • Support for hidden OID columns removed.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.90 SOME OTHER STUFF • GSSAPI encryption support • Progress reporting for CLUSTER and VACUUM FULL • SERIALIZABLE for parallel query

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.91 SOME OTHER STUFF • pg_upgrade can use filesystem cloning. • Unified logging framework for client tools, including colorization support.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.92 SOME OTHER STUFF • Avoid some rewrites in “ALTER TABLE … SET DATA TYPE timestamp” • When the timezone is UTC, timestamptz and timestamp are binary coercible, avoid the table rewrite, and continue to needlessly rewrite any index on an affected column.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.93 SOME OTHER STUFF • Log PostgreSQL version number on startup  2019-03-14 11:33:39.842 CET [7829] LOG: starting PostgreSQL 12devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190223 (Red Hat 8.3.1-2), 64-bit  2019-03-14 11:33:39.842 CET [7829] LOG: listening on IPv4 address "127.0.0.1", port 5412  2019-03-14 11:33:39.916 CET [7829] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5412"  2019-03-14 11:33:40.059 CET [7829] LOG: listening on Unix socket "/tmp/.s.PGSQL.5412"

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.94 SOME OTHER STUFF • Allow COPY FROM to filter data using WHERE conditions  Extends the COPY FROM command with a WHERE condition, which allows doing various types of filtering while importing the data (random sampling, condition on a data column, etc.).  Low overhead

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.95 SOME OTHER STUFF • Allow COPY FROM to filter data using WHERE conditions  Extends the COPY FROM command with a WHERE condition, which allows doing various types of filtering while importing the data (random sampling, condition on a data column, etc.).  Low overhead • pg_upgrade: --socketdir option  This allows control of the directory in which the postmaster sockets are created for the temporary postmasters started by pg_upgrade  Useful for long path names.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.97 SOME OTHER STUFF • pg_stat_statements_reset can now reset statistics specific to a particular user/db/query.  Now, it can discard the statistics gathered so far by pg_stat_statements corresponding to the specified userid, dbid, and queryid.  If no parameter is specified or all the specified parameters have default value 0, it will discard all statistics as per the old behavior.

CONFIDENTIAL © Copyright EnterpriseDB Corporation, 2019. All rights reserved.99 WHAT IS NEW IN ADVANCED SERVER 12? • Interval Partitioning • Compound Triggers • MEDIAN, LISTAGG • CAST(MULTISET) • System View Improvements

PostgreSQL 12: What is coming up?, Enterprise Postgres Day

More Related Content

What's hot

Similar to PostgreSQL 12: What is coming up?, Enterprise Postgres Day

More from EDB

Recently uploaded

PostgreSQL 12: What is coming up?, Enterprise Postgres Day