A Brief History of Releases

8.0 Features

• Native Microsoft Windows release
• Savepoints
• Point-In-Time Recovery (PITR)
• Tablespaces
• Change column types

8.1 Features

• Two-phase commit
• Role system simplified separate user/group
• Greatly improved memory management for increased concurrency performance
• Auto-vacuum moved into main server (was contrib module)

8.2 Features

• Concentrated on user requested features for easier administration and better performance
• INSERT/UPDATE/DELETE ... RETURNING ...
• Concurrent index creation
• Many query planner improvements
• More efficient locking for better concurrency
• More efficient vacuuming

8.3 Features

• Full text search integrated into core system
• Interactive Demo Available - learn.crunchydata.com -> PG For App Devs -> Quick Intro to Full Text Search in PG
• ENUM & UUID types
• Concurrent autovacuum allowed spawning of multiple vacuum jobs automatically
• Updatable cursors

8.4 Features

Window Functions

• Performs a calculation across a set of table rows that are somehow related to the current row
• The OVER clause determines exactly how the rows of the query are split up for processing by the window function

SELECT salary, sum(salary) OVER () FROM empsalary;

 salary |  sum  
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)


SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

 salary |  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)
                        

Window Functions

• The PARTITION BY list within OVER specifies dividing the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s).

SELECT depname, empno, salary, avg(salary) 
OVER (PARTITION BY depname) 
FROM empsalary;

  depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)


SELECT depname, empno, salary, rank() 
OVER (PARTITION BY depname ORDER BY salary DESC) 
FROM empsalary;

  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)

                        

Common Table Expression

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
                        

Common Table Expressions

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

 sum  
------
 5050
(1 row)

                        

Additional 8.4 Features

• Parallel pg_restore
• Default & variadic parameters for functions
• Column permissions
• Per-database locale settings
• SSL certificate authentication
• Automatic sizing of Free Space Map (eliminated significant administrative headache)
• Live editing of functions (\ef in psql)
• New contrib modules: pg_stat_statements, auto_explain, btree_gin

9.0 Features

Streaming Replication

• Prior replication methods required significant setup and relied on third-party tools (commands given to archive_command)
• Simpler, TCP-based connection method
• Significantly reduced lag time between primary & replica systems (less than 1 second)

Hot Standby

• Allow readonly connections to replica servers
• Offset load on primary systems by sending non-write queries to replica(s)
• Better security: allow access to data but not to a system that permits writes

pg_upgrade

• Previously, all major version upgrades required a full dump & restore
• Can either copy existing files to new data directory or can use hard links to reuse old files
• Link method can allow very quick upgrade of large databases (ex: 1.2TB in < 2min; does not include stats update)
• Minimum version to pg_upgrade straight to latest release: 8.4.7
• Anything older either requires staging through some previous versions or dump & restore (highly recommend D&R)

Additional 9.0 Features

• Enhanced permissions management
• Grant to all objects in schema with single command
• Alter initial, default privileges of objects created by specific roles
• Much Better VACUUM FULL
• Windows 64-bit support
• Anonymous code blocks (DO statement)
• Deferrable unique constraints
• Enhanced EXPLAIN plans. Export in JSON, XML or YAML

9.1 Features

Foreign Data Wrappers

• SQL/MED (Management of External Data) part of SQL Standard
• Connect to remote databases/filesystems/services as if they were local tables inside PostgreSQL
• Oracle, MySQL, SQL Server, SQLite, MongoDB, Redis, CSV Files, Twitter, Blackhole, etc
• Joins & conditions pushed to remote system
• Still performance issues joining local tables w/ remote
• Ease the migration to PostgreSQL from different databases

Extensions

• Package related objects into a managed bundle
• Versioned
• Ensure all systems have the same code
• Controlled upgrades & downgrades
• All contrib modules are now extensions
• Examples: PostGIS, pg_partman, pg_repack

Writable CTE

• WITH statements can now contain INSERT, UPDATE & DELETE
• Use RETURNING clause to do fun things

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;
                        

Writable CTE

• Simplify transactional logic that contains multiple, related insert/update/delete statements
• As a side-effect, significantly improves performance!

BEFORE:
BEGIN;
INSERT INTO users (name, email) VALUES (?,?) RETURNING userid;

INSERT INTO addresses (userid, address, city, state, zip) 
VALUES (?,?,?,?,?) RETURNING addressid;

INSERT INTO user_history (userid, addressid, action) VALUES (?,?,?) RETURNING historyid;
COMMIT;


AFTER:
WITH userdata AS (
  INSERT INTO users (name, email) VALUES (?,?)
  RETURNING userid
), addressdata AS (
  INSERT INTO addresses (userid, address, city, state, zip)
  SELECT userid,?,?,?,?
  FROM userdata
  RETURNING addressid 
), historydata AS (
  INSERT INTO user_history (userid, addressid, action)
  SELECT userid, addressid,?
  FROM userdata, addressdata 
  RETURNING historyid
)
SELECT userid, addressid, historyid 
FROM userdata, addressdata, historydata;
                            

Additional 9.1 Features

• Synchronous Replication
• True serializable transaction isolation
• Unlogged tables

9.2 Features

Range Data Type

• Data type representing a range of values of some element type (time, id, custom)
• Scheduling, probablilty, intersection of ordered data

-- includes 3, does not include 7, and does include all points in between
SELECT '[3,7)'::int4range;

-- does not include either 3 or 7, but includes all points in between
SELECT '(3,7)'::int4range;

-- includes only the single point 4
SELECT '[4,4]'::int4range;

-- includes no points (and will be normalized to 'empty')
SELECT '[4,4)'::int4range;


CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');


-- Containment
SELECT int4range(10, 20) @> 3;
 ?column? 
----------
 f

-- Overlaps
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);
 ?column? 
----------
 t

-- Extract the upper bound
SELECT upper(int8range(15, 25));
 upper 
-------
    25

-- Compute the intersection
SELECT int4range(10, 20) * int4range(15, 25);
 ?column? 
----------
 [15,20)

                        

Range Types

• Practical Example: Buying a car in budget (Thank you Jonathan Katz)
• Without range type, table below would have had to have a min_price & max_price column to track its possible range of values

test=# SELECT * FROM cars ORDER BY lower(cars.price_range);
 id  | name                | price_range
-----+---------------------+-----------------
 2   | Buick Skylark       | [2000,4001)
 3   | Pontiac GTO         | [5000,7501)
 4   | Chevrolet Camero    | [10000,12001)
 5   | Ford Mustang        | [11000,15001)
 6   | Lincoln Continental | [12000,14001)
 7   | BMW M3              | [35000,42001)
 8   | Audi RS4            | [41000,45001)
 9   | Porsche 911         | [47000,58001)
 10  | Lamborghini LP700   | [385000,400001)

                        

Range Type

• Budget of $13,000 - $15,000
• Find all cars that have any values in that price range
• With old min_price / max_price columns, you write fun queries like this:

SELECT *
FROM cars
WHERE
    (
        cars.min_price ≤ 13000 AND
        cars.min_price ≤ 15000 AND
        cars.max_price ≥ 13000 AND
        cars.max_price ≤ 15000
    ) OR
    (
        cars.min_price ≤ 13000 AND
        cars.min_price ≤ 15000 AND
        cars.max_price ≥ 13000 AND
        cars.max_price ≥ 15000
    ) OR
    (
        cars.min_price ≥ 13000 AND
        cars.min_price ≤ 15000 AND
        cars.max_price ≥ 13000 AND
        cars.max_price ≤ 15000
    ) OR
    (
        cars.min_price ≥ 13000 AND
        cars.min_price ≤ 15000 AND
        cars.max_price ≥ 13000 AND
        cars.max_price ≥ 15000
    )
ORDER BY cars.min_price;
                        

Range Types

• Range types & operators make this just slightly easier

SELECT *
FROM cars
WHERE cars.price_range && int4range(13000, 15000, '[]')
ORDER BY lower(cars.price_range);

 id | name                | price_range
----+---------------------+---------------
 5  | Ford Mustang        | [11000,15001)
 6  | Lincoln Continental | [12000,14001)
                        

Cascading Replication

• Database can be a streaming replica of another replica
• Every streaming replica off of a primary causes additional load (system & network) for reading its WAL stream
• Distribute replica load among several systems in large, multi-cluster setups

Additional 9.2 Features

• Index-Only scan
• JSON Data Type (very limited usefulness)

9.3 Features

Reduce System V shared memory

1. Install OS
2. Install PostgreSQL package
3. Do tuning adjustments to fix your shared_buffers (32mb is enough for anyone right?)
4. Restart to put new settings into place
5. Uh oh...

IpcMemoryCreate: shmget(key=5432001, size=415776768, 03600) failed: Invalid argument 

This error usually means that PostgreSQL's request for a shared memory 
segment exceeded your kernel's SHMMAX parameter. You can either 
reduce the request size or reconfigure the kernel with larger SHMMAX. 
To reduce the request size (currently 415776768 bytes), reduce 
PostgreSQL's shared_buffers parameter (currently 50000) and/or 
its max_connections parameter (currently 12).
                        

• No longer an issue with 9.3
• Blog post by Robert Haas explains the original cause and fix

Custom Background Worker

• PostgreSQL already used distinct background processes for its own inner workings (postmaster, writer, logger, autovacuum, stats collector, etc)
• Now able to program your own workers and let PostgreSQL manage them for you
• Auto-start & stop along with the database
• Use custom configuration options in postgresql.conf
• Example: pg_partman has a BGW scheduler so separate cronjob is no longer required for partition maintenance

Additional 9.3 Features

• JSON Operators - Almost useful!
• LATERAL queries/joins
• Writable Foreign Data Wrappers
• An actual PostgreSQL FDW!
• Parallel pg_dump
• Materialized Views (kind of useless)
• Checksum data pages

9.4 Features

Binary JSON data type

• Actually useful! Possibly replace MongoDB.
• Benchmark PG vs Mongo - https://github.com/EnterpriseDB/pg_nosql_benchmark
• Interactive Demo Available - learn.crunchydata.com -> PG For App Devs -> Quick Intro to JSON in PG
• Same data type validation as before
• Stored in format that does not require reparsing original text
• Allows indexing and more efficient storage
• New operators and functions for better interaction with JSON data
• Fields are de-dupped. If you need dupped fields in tact, use normal json type

Binary JSON data type

• Great blogpost with JSONB examples: Unleash the Power of Storing JSON in Postgres

CREATE TABLE cards (
  id integer NOT NULL,
  board_id integer NOT NULL,
  data jsonb
);
 
INSERT INTO cards VALUES (1, 1, '{"name": "Paint house", "tags": ["Improvements", "Office"], "finished": true}');

SELECT data->>'name' AS name FROM cards;

SELECT count(*) FROM cards WHERE data->>'finished' = 'true';

Aggregate (cost=335.12..335.13 rows=1 width=0) (actual time=4.421..4.421 rows=1 loops=1) -> Seq Scan on cards (cost=0.00..335.00 rows=50 width=0) (actual time=0.016..3.961 rows=4938 loops=1) 
    Filter: ((data ->> 'finished'::text) = 'true'::text) 
    Rows Removed by Filter: 5062 
Planning time: 0.071 ms 
Execution time: 4.465 ms

CREATE INDEX idxfinished ON cards ((data->>'finished'));

Aggregate (cost=118.97..118.98 rows=1 width=0) (actual time=2.122..2.122 rows=1 loops=1) -> Bitmap Heap Scan on cards (cost=4.68..118.84 rows=50 width=0) (actual time=0.711..1.664 rows=4938 loops=1) 
    Recheck Cond: ((data ->> 'finished'::text) = 'true'::text) 
    Heap Blocks: exact=185 
    -> Bitmap Index Scan on idxfinished (cost=0.00..4.66 rows=50 width=0) (actual time=0.671..0.671 rows=4938 loops=1) 
        Index Cond: ((data ->> 'finished'::text) = 'true'::text) 
Planning time: 0.084 ms 
Execution time: 2.199 ms
                         

Replication Slots

• Allows primary servers to be aware of all replication state
• Primary keeps WAL in pg_xlogs (now pg_wal) for known replicas if they disconnect
• Previously had to either set wal_keep_segments high or have secondary WAL storage to avoid replica rebuild
• Ties in with upcoming logical replication features

Additional 9.4 Features

• ALTER SYSTEM command to dynamically change postgresql.conf within the database
• Materialzed view refresh no longer blocks reads.
• Actually useful! Still not auto-refreshed
• Dynamic loading/unloading of Background Workers
• pg_prewarm
• Allows immediate loading of relation data during cluster start into shared_buffers to "warm" them up
• Logical decoding in WAL stream. First steps to native logical replication & multi-master.
• Distinct output of Planning & Execution time from EXPLAIN ANALYZE output

9.5 Features

UPSERT

• INSERT ... ON CONFLICT ...
• ON CONSTRAINT ... (better to name columns)
• DO NOTHING
• DO UPDATE SET ... [WHERE ...]
• "... optimistic variant of regular insertion that first does a pre-check for existing tuples and then attempts an insert."
• Not quite the same as MERGE. This is an actual upsert.

Row Level Security

• Control access to data by conditions on the values of that data
• Adds to already extensive privilege controls (database, schema, table, column)
• Superusers and object owners bypass RLS. Unless...
• New GUC - row_security (ON, OFF, FORCE)
• pg_dump sets row_securty to OFF (--enable-row-security)
• Multiple policies per table (Results are OR'ed)
• BYPASSRLS/NOBYPASSRLS - new role property to allow individual roles to bypass RLS
• Row level security shows up in the \d results
• Much better support in more recent versions

BRIN Indexes

• Block Range Index / MinMax Index
• Stores only the bounds-per-heap-page vs B-tree method of one index entry for each value
• Significantly faster index creation, update & smaller size
• Sometimes slower index searches than B-tree
• Works better with statically ordered data
• Configure how many heap pages contribute to index entry
• Really good explanation & examples at python sweetness blog

Additional 9.5 Features

• Sorting Speed Improvements (Abbreviated Keys)
• checkpoint_segments replaced by min_wal_size & max_wal_size
• pg_rewind - no longer have to rebuild failed primary from scratch
• Add IF NOT EXISTS clause to CREATE INDEX, CREATE TABLE AS, CREATE SEQUENCE, and CREATE MATERIALIZED VIEW
• Additional GROUP BY options (GROUPING SETS, CUBE, ROLLUP)
• JSONB improvements
• jsonb_set() - allows in-place editing of JSONB data
• jsonb_pretty() - more human readable jsonb output
• Even more functions and operators. See Release Notes
• How We Tweaked Postgres UPSERT Performance to be 2-3* Faster than MongoDB
• Foreign Table Inheritance - child tables on remote databases

9.6 Features

Introduction of Parallel Queries

• Parallelization supported for sequential scan, hash joins, nested loops and some aggregates
• Only parallelizes on driving table
• Not enabled by default
• Highly recommend more modern version (11+) if parallel is desired

Improved Vacuuming

• Avoids re-vacuuming pages containing only frozen tuples
• Significantly reduces IO and time required for vacuuming tables that don't change much
• Vacuum progress view added - pg_stat_progress_vacuum

Improved pg_stat_activity For Locks

• Original waiting column has been split into wait_event-type and wait_event
• Much more fine-grained detail than the previous, simple boolean value. Better answers "Why is this waiting?"
• May need to adjust monitoring to account for new columns and values
• See documentation for all new value meanings - https://www.postgresql.org/docs/9.6/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

Support Multiple Synchronous Standbys

• Previously, you could configure multiple synchronous standbys, but only one was ever guaranteed to be synchronous. Evaluated in order and first one hit, wins
• Can now specify a minimum number of standbys that must report back as in sync

                            synchronous_standby_names = '2 (s1, s2, s3)'
                           

• Still evaluates in given order, but number at beginning specifies the minimum
• Asynchronous standbys still possible at same time, just leave them out of list
• Version 10 added ability to designate ANY listed replicas to create quorum. FIRST mode is old priority based method.

                            synchronous_standby_names = 'FIRST 2 (s1, s2, s3)'
                            synchronous_standby_names = 'ANY 2 (s1, s2, s3)'
                           

• Documentation - synchronous_standby_names & Multiple Synchronous Standbys

Additional 9.6 Features

• Much improved sorting performance
• Automatic timeout of idle in transaction session - idle_in_transaction_session_timeout
• Added --slot for pg_basebackup to allow specifying a desired replication slot so future replica can pick up where the basebackup left off
• Allow old MVCC snapshots to be invalidated after a configurable timeout to help reduce bloat buildup caused by long running transactions. See old_snapshot_threshold setting.
• Add pg_config system view to allow values of pg_config CLI tool to be visible inside database. pg_config
• Support multiple -c & -f options for psql
• %n log_line_prefix value for unix epoch

10 Features

New version numbering

• Changed from 3 number version (x.x.x) to 2 number version (x.x)
• 9.6.15 to 10.10
• Previous first number was arbitrary and major version was considered first.second with third being patch
• Now first number is always major version. Second number is always patch release.
• Greatly simplified clarity on what version numbers mean

Native Partitioning

• Added language features for declarative partitioning methods
• Limited, introductory feature support. Recommend going to 11 or 12 if you want to use this
• RANGE and LIST supported
• Manual creation of child tables
• More work but allows flexability of direct child table access if needed
• pg_partman - automated child table creation for time/integer based partitioning
• Interactive Demo Available - learn.crunchydata.com -> PostgreSQL Admin -> PG Features -> Native Partitioning

  
  CREATE TABLE measurement (
      city_id         int not null,
      logdate         date not null,
      peaktemp        int,
      unitsales       int
  ) PARTITION BY RANGE (logdate);                   
                      

Native Partitioning

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
                        

keith@keith=# \d+ measurement
                                 Table "public.measurement"
  Column   |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
-----------+---------+-----------+----------+---------+---------+--------------+-------------
 city_id   | integer |           | not null |         | plain   |              | 
 logdate   | date    |           | not null |         | plain   |              | 
 peaktemp  | integer |           |          |         | plain   |              | 
 unitsales | integer |           |          |         | plain   |              | 
Partition key: RANGE (logdate)
Partitions: measurement_y2006m02 FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'),
            measurement_y2006m03 FOR VALUES FROM ('2006-03-01') TO ('2006-04-01')
                       

Logical Replication

• Per-object replication vs instance-based replication (streaming) - https://www.postgresql.org/docs/10/logical-replication.html
• Uses same, WAL based replication method as streaming

wal_level = logical
                               

• Managed via a PUBLICATION of one or more tables that are then read by a SUBSCRIPTION on the destination
• Unlike streaming replication, Subscriber is allowed to write to database
• Not really bi-directional.
• No automatic conflict resolution.
• Interactive Demo Available - learn.crunchydata.com -> PostgreSQL Admin -> PG Features -> Logical Replication

Naming Changes

• Changed folder names in data directory
• Folders named "log" aren't critical right?
• pg_xlog to pg_wal
• pg_clog to pg_xact
• Object & tool name changes
• pg_switch_xlog() to pg_switch_wal()
• pg_receivexlog to pg_receivewal
• --xlogdir to --waldir for above binary
• WAL related objects that had the word location in them changed to lsn (log sequence number)
• Ex. columns in pg_stat_replication system catalog. write_location to write_lsn

SCRAM Authentication

• New password authentication method to modernize current md5/password methods
• Challenge-response scheme that prevents password sniffing on untrusted connections
• Supports storing passwords on the server in a cryptographically hashed form that is thought to be secure
• Most secure of the currently provided methods, but may not be supported by some client libraries
• Control password encryption storage via the CREATE/ALTER role commands
• Control authenication via the pg_hba.conf auth-method

Default Roles

• First round of adding default roles to allow easier/more secure administrative functionality
• Focused on monitoring
• pg_read_all_settings - read all config variables, even superuser only ones
• pg_read_all_stats - read all pg_stat_* views and use stat related extensions, even superuser only ones
• pg_stat_scan_tables - allow execution of monitoring function that may take ACCESS SHARE locks
• pg_monitor - member of all above roles. read/execute various monitoring related views and functions

IDENTITY columns

• Properly follow SQL standard for managing table sequences
• Better handling of sequence permissions when tied to a table
• Better enforcement of only allowing sequence use for column values
• Easier to remove sequences from a table
• Only works when entering data through the parent table in partitioning
• More info on 2ndQuadrant Blog

Additional 10 Features

• Define extended statistics. See Extended Statistics section of docs
• Improved query parallelism (still recommend going to latest major release if this is important)
• Parallel feature now enabled by default max_parallel_workers_per_gather set to 2
• btree index scans, bitmap heap scans, merge joins, non-correlated subqueries
• Added max_parallel_workers to allow limiting worker processes dedicated to parallelism
• pg_basebackup --wal-method=stream (-Xs) is now default
• Hash indexes are now crash-safe & replicated
• Removed ability to store unencrypted passwords
• Background processes are now shown in pg_stat_activity
• SSL config can be updated during reload instead of requiring restart
• Better full text search for json & jsonb

11 Features

Stored Procedures

• Previously all "procedures" in PG had only ever been "functions". Single transaction thread for entire execution
• New CREATE PROCEDURE creation syntax
• Uses CALL syntax vs SELECT

CALL partman.partition_data_proc('public.mytable');
                            

• Allows distinct COMMIT blocks within a single procedure call
• Does not return values
• Cannot (yet) return multiple result sets
• Limited nested procedure calling
• Cannot call procedures from within functions

Improved Partitioning

• New HASH partitioning format in addition to RANGE and LIST
• Allow setting primary keys, foreign keys, indexes and triggers on parent table to be inherited to children
• Primary key must include partition key
• Foreign keys FROM partition sets but not TO them
• Added DEFAULT partition
• Catch data that doesn't fit into defined child
• Beware heavy performance penalty when adding children if default has a lot of data
• Improved SELECT performance using partition pruning methods. MUCH better in 12!

Additional 11 Features

• Just-in-Time (JIT) compilation for some SQL code speeding up expression evaluation
• Even more improved parallelism
• CREATE INDEX (B-tree), CREATE TABLE AS, CREATE MATERIALIZED VIEW, some UNIONS, better hash joins and seq scans
• Covering Indexes
• Include non-key columns in index definition to improve chance of an index-only scan
• Great blog post by Michael Paquier on this
• Improved upon 9.6 VACUUM improvements (avoid uneeded index scans)
• More new default roles for file system access (pg_read_server_files, pg_write_server_files, and pg_execute_server_program)
• Option to allow pg_basebackup to create replication slot at runtime

12 Features

Removal of recovery.conf

• Before 12, existence of recovery.conf file told PG that it should run in recovery mode. This has been replaced with two possible files:
• standby.signal - server will enter recovery and continue trying to replay additional WAL forever (normal replica)
• recovery.signal - used for targetted recovery mode which ends when all archived WAL is replayed or recovery target is reached
• If both file exist, standby.signal takes precendence
• Files are empty and only their existence is used to control recovery
• Configuration options that went into recovery.conf have been moved to main postgresql.conf file
• Archive & Recovery Target Command Reference

Performance Improvements

• B-tree read/write optimization
• Better partition pruning can tremendously improve read performance for partition sets
• ATTACH partition without locking entire partition set
• Automatic (but overridable) inlining of common table expressions (CTEs)
• Multi-column most-common-value (MCV) stats to improve upon new feature added in PG10
• See CREATE STATISTICS and related commands

Generated columns

• Special columns whose values are always computed from other columns
• Previously required triggers to accomplish this
• Currently only "stored" generated column is supported. Computed when row is written and occupies storage like normal column
• Other known type, "virtual", is computed when column is read but not yet supported in PG
• Additional caveats for usage in documentation - https://www.postgresql.org/docs/12/ddl-generated-columns.html

Links to documentation in psql

• The \h command in psql now provides direct links to relevant documentation for the given command
• Tied to psql client version, not server you are connected to

=# \h DROP PROCEDURE
Command:     DROP PROCEDURE
Description: remove a procedure
Syntax:
DROP PROCEDURE [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...]
    [ CASCADE | RESTRICT ]

URL: https://www.postgresql.org/docs/12/sql-dropprocedure.html
                            

Additional 12 Features

• REINDEX CONCURRENTLY
• Foreign keys TO partitioned tables
• Remove the special behavior of oid columns
• Promote standby within database with new function pg_promote()
• VACUUM ... SKIP LOCKED
• Support for SQL/JSON path language
• Enable/disable pg_checksums on an offline cluster. Previously could only be done during initialization

13 Features

Upcoming 13 Features

• ?????????????
• See current Commitfest 2019-11 for current development work