Cmdline

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

Me!

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
    1. Stop master (if it's still around)
    2. Touch recovery trigger file on slave to make master
    3. Rebuild old master from scratch as new slave
    4. Pray your master stays up during long rebuild...
      • Rsync helped, but still not ideal for huge databases

pg_rewind

  • New failover steps!
    1. Stop master (if it's still around)
    2. Touch recovery trigger file on slave to make master
    3. pg_rewind on old master to resync with new master and become new slave
    4. Connect new slave to new master
    5. 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?

Thanks to