9.5 Coming To You Live

Upcoming PostgreSQL 9.5 Features

Presented by Keith Fiske / @keithf4

Database Administrator @ OmniTI

Follow along at
http://slides.keithf4.com/pg95live
Script files located in repo
https://github.com/keithf4/pg95live

OmniTI, Inc

Full-stack support for high-traffic websites & applications
Surge Conference

http://surge.omniti.com
Annually in Sept

We're hiring!

http://omniti.com/is/hiring - SysAdmins, DBAs, Developers, Kernel Hackers

Me!

Author of several third party PG Tools:
pg_partman, pg_extractor, mimeo, pg_jobmon

When Is It Coming?

Alpha/Beta/Something expected later this month (June)
Hopeful for release mid/late October 2015.
PGCon 2015 Developer Meeting was yesterday.

INSERT ... ON CONFLICT ... (UPSERT)

"... optimistic variant of regular insertion that first does a pre-check for existing tuples and then attempts an insert."
Similar to MERGE (arguably better)
ON CONFLICT...

ON CONSTRAINT ... (better to name columns)
DO NOTHING
DO UPDATE SET ... [WHERE ...]


CREATE TABLE IF NOT EXISTS upsert_table (                                                
    id serial primary key,                                                               
    some_key text not null unique,                                                       
    some_val int not null default 0                                                      
);                                                                                       
CREATE TABLE                                                                             
INSERT INTO upsert_table (some_key) VALUES ('a'), ('b');                                 
INSERT 0 2                                                                               
SELECT * FROM upsert_table;                                                              
 id | some_key | some_val                                                                
----+----------+----------                                                               
  1 | a        |        0                                                                
  2 | b        |        0                                                                
(2 rows)


insert into upsert_table (some_key) values ('a');
psql:upsert2.sql:3: ERROR:  duplicate key value violates unique constraint "upsert_table_ some_key_key"
DETAIL:  Key (some_key)=(a) already exists.


insert into upsert_table (some_key) values ('a')
    ON CONFLICT DO NOTHING;
INSERT 0 0 
select * from upsert_table;
 id | some_key | some_val 
----+----------+---------- 
  1 | a        |        0
  2 | b        |        0
(2 rows)


insert into upsert_table (some_key) values ('a')
    ON CONFLICT DO update SET some_val = some_val + 1;
psql:upsert4.sql:4: ERROR:  ON CONFLICT DO UPDATE requires inference specification or constraint name
LINE 2:     ON CONFLICT DO update SET some_val = some_val + 1;
            ^ 
HINT:  For example, ON CONFLICT (<column>).


insert into upsert_table (some_key) values ('a')
    ON CONFLICT (some_key) DO update SET some_val = some_val + 1;
psql:upsert5.sql:4: ERROR:  column reference "some_val" is ambiguous
LINE 2: ...  ON CONFLICT (some_key) DO update SET some_val = some_val +...


INSERT INTO upsert_table AS u (some_key) VALUES ('a')
    ON CONFLICT (some_key) DO UPDATE SET some_val = u.some_val + 1;
INSERT 0 1
SELECT * FROM upsert_table;
 id | some_key | some_val
----+----------+----------
  2 | b        |        0
  1 | a        |        1
(2 rows)


INSERT INTO upsert_table AS u (some_key, some_val) VALUES ('b', 10)
    ON CONFLICT ON CONSTRAINT upsert_table_some_key_key DO UPDATE SET some_val = excluded.some_val;                                                                            INSERT 0 1
SELECT * FROM upsert_table;
 id | some_key | some_val
----+----------+----------
  1 | a        |        1
  2 | b        |       10
(2 rows)

Row Level Security (RLS)

Per-row access policies
In addition to existing schema, table, column privileges
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
"Row Level Security" by Stephen Frost tomorrow @ 4pm in DMS 1140


CREATE TABLE clients (
    id serial primary key,
    account_name text not null unique,
    account_manager text not null
);
CREATE TABLE
 
CREATE USER peter;
CREATE ROLE
 
CREATE USER joanna;
CREATE ROLE
 
CREATE USER bill;
CREATE ROLE
 
GRANT ALL ON TABLE clients TO peter, joanna, bill;
GRANT
 
GRANT ALL ON SEQUENCE clients_id_seq TO peter, joanna, bill;
GRANT
 
INSERT INTO clients (account_name, account_manager)
    VALUES ('initrode', 'peter'), ('initech', 'bill'), ('chotchkie''s', 'joanna');
INSERT 0 3


\c - peter
You are now connected to database "keith" as user "peter".
SELECT * FROM clients;
 id | account_name | account_manager 
----+--------------+-----------------
  1 | initrode     | peter
  2 | initech      | bill
  3 | chotchkie's  | joanna
(3 rows)


\c - keith
You are now connected to database "keith" as user "keith".
CREATE POLICY just_own_clients ON clients
    FOR all
    TO public
    USING ( account_manager = current_user );
CREATE POLICY
 
ALTER TABLE clients ENABLE ROW LEVEL SECURITY;
ALTER TABLE


\c - peter
You are now connected to database "keith" as user "peter".
SELECT * FROM clients;
 id | account_name | account_manager 
----+--------------+-----------------
  1 | initrode     | peter
(1 row)


\c - joanna
You are now connected to database "keith" as user "joanna".
SELECT * FROM clients;
 id | account_name | account_manager 
----+--------------+-----------------
  3 | chotchkie's  | joanna
(1 row)


\c - peter
You are now connected to database "keith" as user "peter".
INSERT INTO clients (account_name, account_manager) VALUES ('hack', 'bill');
psql:row_level6.sql:5: ERROR:  new row violates row level security policy for "clients"
INSERT INTO clients (account_name, account_manager) VALUES ('hack', 'peter');
INSERT 0 1


\c - keith
You are now connected to database "keith" as user "keith".
DROP POLICY just_own_clients ON clients;
DROP POLICY
CREATE POLICY just_own_clients ON clients
    FOR all
    TO public
    USING ( account_manager = current_user )
    WITH CHECK ( account_manager IN ( 'bill', current_user ) );
CREATE POLICY
\d clients
                              Table "public.clients"
     Column      |  Type   |                      Modifiers                       
-----------------+---------+------------------------------------------------------
 id              | integer | not null default nextval('clients_id_seq'::regclass)
 account_name    | text    | not null
 account_manager | text    | not null
Indexes:
    "clients_pkey" PRIMARY KEY, btree (id)
    "clients_account_name_key" UNIQUE CONSTRAINT, btree (account_name)
Policies:
    POLICY "just_own_clients" FOR ALL
      USING (account_manager = ("current_user"())::text)
      WITH CHECK (account_manager = ANY (ARRAY['bill'::text, ("current_user"())::text]))


\c - peter
You are now connected to database "keith" as user "peter".
SELECT * FROM clients;
 id | account_name | account_manager 
----+--------------+-----------------
  1 | initrode     | peter
  5 | hack         | peter
(2 rows)

INSERT INTO clients (account_name, account_manager) VALUES ('hack2', 'bill');
INSERT 0 1

Checkpoint segments gone!

checkpoint_segments replaced by min_wal_size & max_wal_size

min_wal_size = 80MB (default)
max_wal_size = 128MB (default)

Moving average of WAL segments (pg_xlogs) based on current activity
Checkpoints are auto-tuned to happen in between this size of WAL segments existing
Will only use up to max_wal_size if actually needed
If you set them the same, it disables the auto-tuning feature and essentially gives old behavior, but with a max size in place

pg_rewind

Old failover steps

Stop master (if it's still around)
Touch recovery trigger file on slave to make master
Rebuild old master from scratch as new slave
Pray your master stays up during long rebuild...

Rsync helped, but still not ideal for huge databases

pg_rewind

New failover steps!

Stop master (if it's still around)
Touch recovery trigger file on slave to make master
pg_rewind on old master to resync with new master and become new slave
Connect new slave to new master
Do a little dance...

Understands postgres file properties & format so knows better than rsync how to reset old master.
Requires data checksum turned on (cluster init only) or wal_log_hints = on (requires restart)

Foreign Table Inheritance

Foreign tables can now be inheritance children or parents
More options for partitioning & sharding


CREATE DATABASE foreign_db;
\c foreign_db
You are now connected to database "foreign_db" as user "keith".
CREATE TABLE log_entry_y2014(log_time timestamp,
       entry text,
       check (date(log_time) >= '2014-01-01' AND
              date(log_time) < '2015-01-01'));
CREATE TABLE log_entry_y2015(log_time timestamp,
       entry text,
       check (date(log_time) >= '2015-01-01' AND
              date(log_time) < '2016-01-01'));
\c keith
You are now connected to database "keith" as user "keith".


CREATE EXTENSION postgres_fdw;
CREATE SERVER myfdw FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'localhost', dbname 'foreign_db');
CREATE USER MAPPING FOR PUBLIC SERVER myfdw OPTIONS (password '');


CREATE TABLE log_entries(log_time timestamp, entry text);
CREATE FOREIGN TABLE log_entry_y2014_f (log_time timestamp, entry text)
   INHERITS (log_entries) SERVER myfdw OPTIONS (table_name 'log_entry_y2014');
psql:inherit_fdw3.sql:10: NOTICE:  merging column "log_time" with inherited definition
psql:inherit_fdw3.sql:10: NOTICE:  merging column "entry" with inherited definition
CREATE FOREIGN TABLE
CREATE FOREIGN TABLE log_entry_y2015_f (log_time timestamp, entry text)
   INHERITS (log_entries) SERVER myfdw OPTIONS (table_name 'log_entry_y2015');
psql:inherit_fdw3.sql:13: NOTICE:  merging column "log_time" with inherited definition
psql:inherit_fdw3.sql:13: NOTICE:  merging column "entry" with inherited definition
CREATE FOREIGN TABLE
\d+ log_entries
                                 Table "public.log_entries"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 log_time | timestamp without time zone |           | plain    |              | 
 entry    | text                        |           | extended |              | 
Child tables: log_entry_y2014_f,
              log_entry_y2015_f

\d+ log_entry_y2014_f
                                 Foreign table "public.log_entry_y2014_f"
  Column  |            Type             | Modifiers | FDW Options | Storage  | Stats target | Description 
----------+-----------------------------+-----------+-------------+----------+--------------+-------------
 log_time | timestamp without time zone |           |             | plain    |              | 
 entry    | text                        |           |             | extended |              | 
Server: myfdw
FDW Options: (table_name 'log_entry_y2014')
Inherits: log_entries

Import Foreign Schema

Create all tables in a defined FDW's schema as local foreign tables with a single command
"Transacting with foreign servers - Two's company, three is ..." by Ashutosh Bapat tomorrow @ 2pm in DMS 1160


CREATE SCHEMA fdw_schema;
IMPORT FOREIGN SCHEMA public FROM SERVER myfdw INTO fdw_schema;
\dE *.*
                   List of relations
   Schema   |       Name        |     Type      | Owner 
------------+-------------------+---------------+-------
 fdw_schema | log_entry_y2014   | foreign table | keith
 fdw_schema | log_entry_y2015   | foreign table | keith
 public     | log_entry_y2014_f | foreign table | keith
 public     | log_entry_y2015_f | foreign table | keith
(4 rows)

Grouping Sets

Aggregate data by different GROUP BY clauses at once
Previously achieved by conjoined UNION ALL statements
ROLLUP & CUBE
Depesz does great job of explaining further on his blog


CREATE TABLE db_details (
    appnumber int4,
    day date,
    inserts int8,
    updates int8,
    deletes int8,
    transactions int8,
    PRIMARY KEY (appnumber, day)
);
CREATE TABLE
CREATE SEQUENCE x INCREMENT BY 7;
CREATE SEQUENCE
INSERT INTO db_details
SELECT
    i,
    j,
    nextval('x'),
    nextval('x'),
    nextval('x'),
    nextval('x')
FROM
    generate_series(1,3) i,
    generate_series(now() - '2 days'::interval, now(), '1 day'::interval) j;
INSERT 0 9
DROP SEQUENCE x;
DROP SEQUENCE
SELECT * FROM db_details;
 appnumber |    day     | inserts | updates | deletes | transactions 
-----------+------------+---------+---------+---------+--------------
         1 | 2015-06-14 |       1 |       8 |      15 |           22
         1 | 2015-06-15 |      29 |      36 |      43 |           50
         1 | 2015-06-16 |      57 |      64 |      71 |           78
         2 | 2015-06-14 |      85 |      92 |      99 |          106
         2 | 2015-06-15 |     113 |     120 |     127 |          134
         2 | 2015-06-16 |     141 |     148 |     155 |          162
         3 | 2015-06-14 |     169 |     176 |     183 |          190
         3 | 2015-06-15 |     197 |     204 |     211 |          218
         3 | 2015-06-16 |     225 |     232 |     239 |          246
(9 rows)


SELECT appnumber, sum(inserts), sum(updates), sum(deletes), sum(transactions)
FROM db_details
GROUP BY appnumber;
 appnumber | sum | sum | sum | sum 
-----------+-----+-----+-----+-----
         1 |  87 | 108 | 129 | 150
         3 | 591 | 612 | 633 | 654
         2 | 339 | 360 | 381 | 402
(3 rows)

SELECT day, sum(inserts), sum(updates), sum(deletes), sum(transactions)
FROM db_details
GROUP BY day;
    day     | sum | sum | sum | sum 
------------+-----+-----+-----+-----
 2015-06-15 | 339 | 360 | 381 | 402
 2015-06-14 | 255 | 276 | 297 | 318
 2015-06-16 | 423 | 444 | 465 | 486
(3 rows)


SELECT
    appnumber,
    day,
    sum(inserts),
    sum(updates),
    sum(deletes),
    sum(transactions)
FROM
    db_details
GROUP BY GROUPING SETS ( appnumber, day );
 appnumber |    day     | sum | sum | sum | sum 
-----------+------------+-----+-----+-----+-----
         1 |            |  87 | 108 | 129 | 150
         2 |            | 339 | 360 | 381 | 402
         3 |            | 591 | 612 | 633 | 654
           | 2015-06-14 | 255 | 276 | 297 | 318
           | 2015-06-15 | 339 | 360 | 381 | 402
           | 2015-06-16 | 423 | 444 | 465 | 486
(6 rows)

Multi-Column Sub-Select UPDATE

Update more than one column with subselect
Standard SQL Syntax


UPDATE tab SET (col1, col2) =
    (SELECT foo, bar FROM tab2)
WHERE ...

Numeric generate_series()

generate_series() could do int & time already
Can now start & end higher than int


SELECT generate_series(1,10);
 generate_series 
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)


SELECT generate_series('2014-01-01 00:00:00'::timestamp, now(), '65 days 7 hours 13 minutes 2 seconds'::interval);
    generate_series     
------------------------
 2014-01-01 00:00:00-05
 2014-03-07 07:13:02-05
 2014-05-11 14:26:04-04
 2014-07-15 21:39:06-04
 2014-09-19 04:52:08-04
 2014-11-23 12:05:10-05
 2015-01-27 19:18:12-05
 2015-04-03 02:31:14-04
 2015-06-07 09:44:16-04
(9 rows)


SELECT generate_series(10.1, 11.6, .2);
 generate_series 
-----------------
            10.1
            10.3
            10.5
            10.7
            10.9
            11.1
            11.3
            11.5
(8 rows)

Arrays As Input To array_agg() and ARRAY()

Would previously throw errors when used on an array column
Now creates multi-dimensional array output

SKIP LOCKED

Skip locked rows when using SELECT ... NOWAIT


CREATE TABLE jobs ( id serial primary key, priority int4 NOT NULL, payload text);
CREATE TABLE
INSERT INTO jobs (priority, payload) SELECT random() * 100, 'payload #' || i FROM generate_series(1,100) i;
INSERT 0 100
SELECT * FROM jobs LIMIT 10;
 id | priority |   payload   
----+----------+-------------
  1 |        6 | payload #1
  2 |       85 | payload #2
  3 |        7 | payload #3
  4 |       61 | payload #4
  5 |       92 | payload #5
  6 |       47 | payload #6
  7 |       39 | payload #7
  8 |       51 | payload #8
  9 |       46 | payload #9
 10 |       82 | payload #10
(10 rows)


BEGIN;
BEGIN
SELECT * FROM jobs ORDER BY priority DESC, id ASC LIMIT 1 FOR UPDATE;
 id | priority |   payload   
----+----------+-------------
 61 |      100 | payload #61
(1 row)


BEGIN;
BEGIN
SELECT * FROM jobs ORDER BY priority DESC, id ASC LIMIT 1 FOR UPDATE NOWAIT;
psql:skip_locked3.sql:5: ERROR:  could not obtain lock on row in relation "jobs"


ROLLBACK;
ROLLBACK
BEGIN;
BEGIN
SELECT * FROM jobs ORDER BY priority DESC, id ASC LIMIT 1 FOR UPDATE SKIP LOCKED;
 id | priority |   payload   
----+----------+-------------
 90 |       98 | payload #90
(1 row)


BEGIN;
BEGIN
SELECT * FROM jobs ORDER BY priority DESC, id ASC LIMIT 1 FOR UPDATE SKIP LOCKED;
 id | priority |   payload   
----+----------+-------------
 59 |       95 | payload #59
(1 row)

Reduced Lock Levels

Reduced lock levels for

CREATE TRIGGER (not DROP or ALTER)
ALTER TABLE ENABLE TRIGGER
ALTER TABLE DISABLE TRIGGER
ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY

ShareRowExclusive: Allows Reads, Blocks Writes

BRIN Indexes

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

GiST Index-Only Scans

Box & Point data types only
Big improvement for geometric databases (PostGIS)

Reindex Schema

REINDEX SCHEMA ...
Reindex all tables in a given schema

Sorting Speed Improvement

Sorting used by CREATE INDEX, CLUSTER & query plans much faster (3-4x) for text and numeric data
Abbreviated Keys
Exhaustive explanation on Peter Geoghegan's Blog

Toggle UNLOGGED

ALTER TABLE ... SET LOGGED / UNLOGGED
Beware changing on large tables (requires table rewrite)

Parallel vacuumdb

Allows vacuumdb to open several server connections to vacuum or analyze several tables simultaneously.
--jobs=# (-j#)
Starts with largest tables first

pg_xlogdump

New --stats argument prints summary statstics for the contents of WAL files
--stats=record : generate per record instead of per resource manager
Figure out what's using up the space in the WAL log files to see what's causing the most activiy (DML, inserts, vacuum, etc)


$ pg_xlogdump --stats /opt/pgdata/95/pg_xlog/000000010000000000000001
Type                                           N      (%)          Record size      (%)             FPI size      (%)        Combined size      (%)
----                                           -      ---          -----------      ---             --------      ---        -------------      ---
XLOG                                          50 (  0.12)                 4212 (  0.37)                    0 (  0.00)                 4212 (  0.08)
Transaction                                  700 (  1.66)                22484 (  1.98)                    0 (  0.00)                22484 (  0.44)
Storage                                       53 (  0.13)                 2120 (  0.19)                    0 (  0.00)                 2120 (  0.04)
CLOG                                           1 (  0.00)                   28 (  0.00)                    0 (  0.00)                   28 (  0.00)
Database                                       5 (  0.01)                  200 (  0.02)                    0 (  0.00)                  200 (  0.00)
Tablespace                                     0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
MultiXact                                      0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
RelMap                                         1 (  0.00)                  548 (  0.05)                    0 (  0.00)                  548 (  0.01)
Standby                                        0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Heap2                                        699 (  1.65)                20360 (  1.79)              2066796 ( 52.05)              2087156 ( 40.88)
Heap                                       15522 ( 36.71)               424348 ( 37.40)               586652 ( 14.77)              1011000 ( 19.80)
Btree                                      25235 ( 59.68)               657158 ( 57.92)              1317360 ( 33.18)              1974518 ( 38.68)
Hash                                           0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Gin                                            0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Gist                                           0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Sequence                                      17 (  0.04)                 3094 (  0.27)                    0 (  0.00)                 3094 (  0.06)
SPGist                                         0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
BRIN                                           0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
CommitTs                                       0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
ReplicationOrigin                              0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
                                        --------                      --------                      --------                      --------
Total                                      42283                       1134552 [22.22%]              3970808 [77.78%]              5105360 [100%]
pg_xlogdump: FATAL:  error in WAL record at 0/1802848: invalid record length at 0/1802870


$ pg_xlogdump --stats=record /opt/pgdata/95/pg_xlog/000000010000000000000001
Type                                           N      (%)          Record size      (%)             FPI size      (%)        Combined size      (%)
----                                           -      ---          -----------      ---             --------      ---        -------------      ---
XLOG/CHECKPOINT_SHUTDOWN                      17 (  0.04)                 1768 (  0.16)                    0 (  0.00)                 1768 (  0.03)
XLOG/CHECKPOINT_ONLINE                        21 (  0.05)                 2184 (  0.19)                    0 (  0.00)                 2184 (  0.04)
XLOG/NEXTOID                                  13 (  0.03)                  364 (  0.03)                    0 (  0.00)                  364 (  0.01)
Transaction/COMMIT                           689 (  1.63)                22048 (  1.94)                    0 (  0.00)                22048 (  0.43)
Transaction/ABORT                              4 (  0.01)                  128 (  0.01)                    0 (  0.00)                  128 (  0.00)
Transaction/COMMIT                             7 (  0.02)                  308 (  0.03)                    0 (  0.00)                  308 (  0.01)
Storage/CREATE                                53 (  0.13)                 2120 (  0.19)                    0 (  0.00)                 2120 (  0.04)
CLOG/ZEROPAGE                                  1 (  0.00)                   28 (  0.00)                    0 (  0.00)                   28 (  0.00)
Database/CREATE                                5 (  0.01)                  200 (  0.02)                    0 (  0.00)                  200 (  0.00)
RelMap/UPDATE                                  1 (  0.00)                  548 (  0.05)                    0 (  0.00)                  548 (  0.01)
Heap2/CLEAN                                   77 (  0.18)                 2464 (  0.22)               205476 (  5.17)               207940 (  4.07)
Heap2/FREEZE_PAGE                            239 (  0.57)                 7170 (  0.63)              1500872 ( 37.80)              1508042 ( 29.54)
Heap2/VISIBLE                                375 (  0.89)                10500 (  0.93)               360448 (  9.08)               370948 (  7.27)
Heap2/MULTI_INSERT                             1 (  0.00)                   30 (  0.00)                    0 (  0.00)                   30 (  0.00)
Heap2/MULTI_INSERT+INIT                        7 (  0.02)                  196 (  0.02)                    0 (  0.00)                  196 (  0.00)
Heap/INSERT                                14521 ( 34.34)               392067 ( 34.55)               400688 ( 10.09)               792755 ( 15.53)
Heap/DELETE                                  139 (  0.33)                 4448 (  0.39)                    0 (  0.00)                 4448 (  0.09)
Heap/UPDATE                                   68 (  0.16)                 2584 (  0.23)                81232 (  2.05)                83816 (  1.64)
Heap/HOT_UPDATE                              357 (  0.84)                13566 (  1.20)                50332 (  1.27)                63898 (  1.25)
Heap/LOCK                                      6 (  0.01)                  186 (  0.02)                 5380 (  0.14)                 5566 (  0.11)
Heap/INPLACE                                 195 (  0.46)                 5070 (  0.45)                40976 (  1.03)                46046 (  0.90)
Heap/INSERT+INIT                             231 (  0.55)                 6237 (  0.55)                    0 (  0.00)                 6237 (  0.12)
Heap/UPDATE+INIT                               5 (  0.01)                  190 (  0.02)                 8044 (  0.20)                 8234 (  0.16)
Btree/INSERT_LEAF                          24933 ( 58.97)               648258 ( 57.13)              1109412 ( 27.94)              1757670 ( 34.43)
Btree/INSERT_UPPER                           112 (  0.26)                 2912 (  0.26)                12960 (  0.33)                15872 (  0.31)
Btree/SPLIT_L                                 46 (  0.11)                 1472 (  0.13)                30156 (  0.76)                31628 (  0.62)
Btree/SPLIT_R                                 66 (  0.16)                 2112 (  0.19)                36300 (  0.91)                38412 (  0.75)
Btree/SPLIT_L_ROOT                             3 (  0.01)                   96 (  0.01)                    0 (  0.00)                   96 (  0.00)
Btree/SPLIT_R_ROOT                             3 (  0.01)                   96 (  0.01)                    0 (  0.00)                   96 (  0.00)
Btree/DELETE                                   1 (  0.00)                   44 (  0.00)                    0 (  0.00)                   44 (  0.00)
Btree/NEWROOT                                 45 (  0.11)                 1440 (  0.13)                    0 (  0.00)                 1440 (  0.03)
Btree/VACUUM                                  26 (  0.06)                  728 (  0.06)               128532 (  3.24)               129260 (  2.53)
Sequence/LOG                                  17 (  0.04)                 3094 (  0.27)                    0 (  0.00)                 3094 (  0.06)
                                        --------                      --------                      --------                      --------
Total                                      42284                       1134656 [22.22%]              3970808 [77.78%]              5105464 [100%]
pg_xlogdump: FATAL:  error in WAL record at 0/1802870: invalid record length at 0/18028E0

WAL compression

Smaller WAL files, faster replication
Pay CPU cost for saving IO & disk space
Only compresses full page images so still useful to gzip them

ALTER SYSTEM RESET ... | ALL

Reset to either postgresql.conf value or default if not explicitly set
ALTER SYSTEM RESET work_mem;

recovery_target_action

Specify action the server should take once the recovery target is reached
pause, promote, shutdown
Pause is useful to check that system is at the desired recovery location. If not, change target settings then...
Paused state can be resumed by using pg_xlog_replay_resume()

Cluster System Process Name

New postgresql.conf setting: cluster_name
Name shows up in process list
Useful when running multiple clusters on the same system


$ ps aux | grep postgres
keith     4468  0.0  0.3 170564 13260 pts/8    S    15:05   0:00 /opt/pgsql95dev/bin/postgres -D /opt/pgdata/95
keith     4470  0.0  0.0 170664  3920 ?        Ss   15:05   0:00 postgres: checkpointer process                
keith     4471  0.0  0.0 170564  1872 ?        Ss   15:05   0:00 postgres: writer process                      
keith     4472  0.0  0.1 170564  5004 ?        Ss   15:05   0:00 postgres: wal writer process                  
keith     4473  0.0  0.0 170984  1940 ?        Ss   15:05   0:00 postgres: autovacuum launcher process         
keith     4474  0.0  0.0  25704  1116 ?        Ss   15:05   0:00 postgres: stats collector process             
keith     5158  0.0  0.0  11740   932 pts/8    S+   16:27   0:00 grep --colour=auto postgres


$ ps aux | grep postgres
keith     5180  0.3  0.3 170564 13248 pts/8    S    16:28   0:00 /opt/pgsql95dev/bin/postgres -D /opt/pgdata/95
keith     5182  0.0  0.0 170564   844 ?        Ss   16:28   0:00 postgres: pg95live: checkpointer process      
keith     5183  0.0  0.0 170564  1084 ?        Ss   16:28   0:00 postgres: pg95live: writer process            
keith     5184  0.0  0.0 170564   844 ?        Ss   16:28   0:00 postgres: pg95live: wal writer process        
keith     5185  0.0  0.0 170984  1876 ?        Ss   16:28   0:00 postgres: pg95live: autovacuum launcher process  
keith     5186  0.0  0.0  25704  1124 ?        Ss   16:28   0:00 postgres: pg95live: stats collector process   
keith     5188  0.0  0.0  11740   936 pts/8    S+   16:28   0:00 grep --colour=auto postgres

Binary Dump Restore Feedback

psql lets you know if you try and restore an -Fc dump when you should instead use pg_restore


$ /opt/pgsql95/bin/psql -X -f 95demo_dump.pgr demo
The input is a PostgreSQL custom-format dump.
Use the pg_restore command-line client to restore this dump to a database.

Parallel Sequential Scan (possibly)

First step towards actual parallelized queries?
Robert Haas & Amit Kapila have a dedicated talk for this tomorrow @ 1pm in DMS 1120

How can you help?

CommitFest

2015-06 is Open Now!
https://commitfest.postgresql.org
9.6 CommitFests

July 1 '15, Sept 1, Nov 1, Jan 1 '16, Mar 1
Feature Freeze Apr 2016

Download, Compile, Install & Test Now! - https://github.com/postgres/postgres

Thanks to

Planet PostgreSQL - http://planet.postgresql.org
Magnus Hagander - For doing this talk in NYC before me already so I could steal it.
Hubert "Depesz" Lubaczewski - http://www.depesz.com - "Waiting For 9.5 ..." series.
Michael Paquier - http://michael.otacoo.com- "9.5 feature highlight ..." series
Reveal.js - https://github.com/hakimel/reveal.js - For this presentation software