Don't Forget The Elephant

A Review of Modern PostgreSQL

Presented by Keith Fiske / @keithf4

Senior Database Engineer @ Crunchy Data Solutions, Inc
(pg_partman, pgmonitor, pg_extractor, mimeo)



Follow along at
http://slides.keithf4.com/dontforget

Crunchy Data Solutions, Inc

  • Industry leader in providing enterprise PostgreSQL support and open source solutions
  • Crunchy Certified PostgreSQL
    • 100% Open Source PostgreSQL
    • Common Criteria EAL 2+ Certified
  • We're hiring!

What is PostgreSQL?

  • Open Source RDBMS
  • Started at UC Berkley 1986, open sourced in 1996
  • BSD-type License
  • Follows SQL Standard very closely
  • Yearly major version releases
    • Minor patch releases quarterly at minimum
    • Generally patch all supported versions at one time
  • Third-party Plugin Support
    • Procedural Languages
      • C, Java, Python, Perl, JavaScript, PHP, R, Ruby, etc
    • Extensions
    • Background Workers
  • United States PostgreSQL Association (PgUS) - https://postgresql.us
    • Booth here at this conference!
  • Mailing Lists, Slack, IRC, Wiki, planet.postgresql.org, Conferences & User Groups

A Brief History of Releases

Version Released Final Version EOL
PostgreSQL 6.3 March 1998 6.3.2 March 2003
PostgreSQL 6.4 Oct 1998 6.4.2 Oct 2003
PostgreSQL 6.5 June 1999 6.5.3 June 2004
PostgreSQL 7.0 May 2000 7.0.3 May 2005
PostgreSQL 7.1 Apr 2001 7.1.3 Apr 2006
PostgreSQL 7.2 Feb 2002 7.2.8 Feb 2007
PostgreSQL 7.3 Nov 2002 7.3.21 Nov 2007
PostgreSQL 7.4 Nov 2003 7.4.30 Oct 2010
PostgreSQL 8.0 Jan 2005 8.0.26 Oct 2010
PostgreSQL 8.1 Nov 2005 8.1.23 Oct 2010
PostgreSQL 8.2 Dec 2006 8.2.23 Dec 2011
PostgreSQL 8.3 Feb 2008 8.3.23 Feb 2013
Version Released Final/Latest Version EOL
PostgreSQL 8.4 July 2008 8.4.22 July 2014
PostgreSQL 9.0 Sept 2010 9.0.23 Oct 2015
PostgreSQL 9.1 Sept 2011 9.1.24 Oct 2016
PostgreSQL 9.2 Sept 2012 9.2.24 Nov 2017
PostgreSQL 9.3 Sept 2013 9.3.25 Nov 2018
PostgreSQL 9.4 Dec 2014 9.4.24 Feb 2020
PostgreSQL 9.5 Jan 2016 9.5.19 Feb 2021
PostgreSQL 9.6 Sept 2016 9.6.15 Nov 2021
PostgreSQL 10 Oct 2017 10.10 Nov 2022
PostgreSQL 11 Oct 2018 11.5 Nov 2023
PostgreSQL 12 Oct 2019 12.0 Nov 2024

8.0 Features

(Released Jan 2005 - EOL Oct 2010)
  • Native Microsoft Windows release
  • Savepoints
  • Point-In-Time Recovery (PITR)
  • Tablespaces
  • Change column types

8.1 Features

(Released Nov 2005 - EOL Nov 2010)
  • 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

(Released Dec 2006 - EOL Dec 2011)
  • 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

(Released Feb 2008 - EOL Feb 2013)

8.4 Features

Released July 2009 - EOL July 2014
Final Version 8.4.22

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 provides a way to write auxiliary statements for use in a larger query
  • These statements, referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query
  • WITH queries are useful because they are evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries.

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

Allow recursive queries

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

Released Sept 2010 - EOL Sept 2015
Final Version 9.0.23

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

Released Sept 2011 - EOL Oct 2016
Final verison 9.1.24

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

Released Sept 2012 - EOL Nov 2017
Final version 9.2.24

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

Released Sept 2013 - EOL Nov 2018
Current Version 9.3.25

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).
                        

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

Released Dec 2014 - EOL Feb 2020
Current Version 9.4.24

Binary JSON data type

Binary JSON data type


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

Friendly Reminder

  • If you are running any version <= 9.3, you are no longer receiving support!
  • 9.3 support ended Nov 2018
  • 9.4 support will end Feb 2020
  • No security updates & no data corruption fixes!
  • If on pre-8.4, getting to new version now will make future upgrades much easier (pg_upgrade)
  • We can help! - crunchydata.com

9.5 Features

Released Jan 2016 - EOL Feb 2021
Current Version 9.5.19

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
  • Foreign Table Inheritance - child tables on remote databases

9.6 Features

Released Sept 2016 - EOL Nov 2021
Current Version 9.6.15

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

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

Released Oct 2017 - EOL Nov 2022
Current Version 10.10

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

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)
  • 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

Released Oct 2018 - EOL Nov 2023
Current Version 11.5

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

Released Oct 2019 - EOL Nov 2024
Current Version 12.0

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

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

Release Est Oct 2020

Upcoming 13 Features

Thank you!