Postgres 9.3


An Overview of New Goodies


Robert Treat
@robtreat2

xzilla.net



operations :: consulting 

Postgres


Major Contributor
Author / Speaker
PG.US
Postgres SPI

OmniTI

"omni-t-i"

"A web consultancy that specializes in full stack development and management of large scale web infrastructure" 


24x7 Monitoring
RemoteDBA
Postgres Consulting

OmniOS

lots lots more

OVERVIEW


9.2 -> Performance Focus

9.3 -> Ease of Use


History


  • Branch 9.2 ==> 2012-06-01
  • Commit Fest 1 --> June 2012
  • Commit Fest 2 --> Sept 2012
  • Commit Fest 3 --> Nov 2012
  • Commit Fest 4 --> Jan 2013




IN THEORY

"Moses Mode"


Dec 2012 / Jan 2013

wander
wander
wander

BETA
2013-05-13

Changes?


  • 1901 Files Changed
  • 69730 Additions
  • 187838 Deletions

Give or Take!

SO WHEN?!?!


  • May => 0%
  • June => 3%
  • July => 21%
  • August => 27%
  • September => 34%
  • October => 14%


September 9th


9.3.1 - October 10th

Outline



  • "FOR FREE"
  • REPLICATION
  • VIEWS
  • TYPES
  • FDW
  • QUERIES

No Work Required




With every new release, we get performance enhancements that do not require changes to your application. These are changes under the hood that you get

"for free"

No More SYSV Memory


 $ 2013-04-28 12:00:33 EDT [6429]: [1-1] FATAL:  XX000: could not 
create shared memory segment: Cannot allocate memory
2012-04-28 12:00:33 EDT [6429]: [2-1] DETAIL:  Failed system call was shmget(key=5432001, size=7700914176, 03600).
2012-04-28 12:00:33 EDT [6429]: [3-1] HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 7700914176 bytes), reduce PostgreSQL's shared_buffers parameter (currently 917504) and/or its max_connections parameter (currently 503).
         The PostgreSQL documentation contains more information about shared memory configuration. 

  • move from sysv to mmap 

  • no more shmmax tom-foolery!

Better CONCURRENCY for FK Locking


OLD: 

  • UPDATE to parent required "for update" row lock
  • UPDATE to child required "for share" row lock


NEW

  • UPDATE to parent of NON-Keys == "for no key update"
  • UPDATE to child == "for key share" 



no blocking unless a foreign key column is changed

Cache Local Locks



speeds lock release at statement completion in transactions that hold many locks


(pg_dump)

(complex queries)

Split "pg_Stat_tmp" File


info now stored:

  • file per database 
  • global file




reduces statistics management overhead

Constraint Violation Details


Constraint Violations Now Report Separately

  • table
  • column
  • data type
  • constraint name


Currently available via error string parsing 

:-( 

 

Replication



  • Performance
  • Ease of Use
  • Enhance Functionality

Fail Fast



  • Improve performance of streaming log shipping w/ synchronous commit

  • Allow much faster promotion of streaming standby to primary  

Easier

  • pg_is_in_backup()
  • pg_backup_start_time()

  • Add checkpoint's last REDO location to pg_controldata output (used to determine WAL files needed for restore)

  • Allow tools like pg_receivexlog to run on computers with different architectures

  • "pg_basebackup --write-recovery-conf" outputs a minimal recovery.conf


TIMELINE SWITCHING!


allow a streaming replication standby to follow a timeline switch


allows streaming slaves to feed from newly promoted masters.  currently only possible with use of WAL archive directory or shipping timeline files. 


allows for robust / complex scale out of streaming only architectures

Views


  • MALLEABLE
  • UPDATEABLE
  • MATERIALIZEABLE

UPDATEABLE VIEWS



  • "always" been possible using
    • rules for insert/delete/update
    • triggers (9.1+)

  • no longer needed for simple views
    • simple == SQL92 definition

  • can still create your own if you want (or need)

Materialized Views


  • combines rule definition of view (for updating)
  • with heap properties of a table (used by planner)


  • CREATE MATERIALIZED VIEW 
    • builds copy of table

  • REFRESH MATERIALIZED VIEW 
    • rebuilds table
    • requires exclusive lock
    • play tricks with rename?

Types



helper functions
changes to types
working with types easier

Large Objects


  • increase maximum length of large objects from 2GB to 4TB
  • includes new libpq and server-side 64-bit-enabled functions

JSON operators / Functions


 JSON: { "rush":[2,4,6,9] }

  • "->", extract data from JSON
SELECT '{"rush":[2,4,6,9]}'::json -> 'rush' -> 2   returns:: 6


  • "#>", extract chained data from JSON
SELECT '{"rush":[2,4,6,9]}'::json #> ARRAY['rush','2']   returns:: 6

JSON_OBJECT_KEYS


SELECT * FROM json_object_keys(    '{"name":"dylan","game":"put put","score":[2,1,1,2]}' ); 
json_object_keys----------------namegamescore

json_each_text



 SELECT * FROM json_each_text(    '{"name":"dylan","game":"put put","score":[2,1,1,2]}' );
key | value-------+------- name | dylan game | put put score | [2,1,1,2]

lots more JSON!


  • json_array_elements
  • json_array_length
  • json_each
  • json_extract_path
  • json_populate_recordset
  • json_agg
  • to_json
  • hstore_to_json
  • hstore_to_json_loose

Arrays


array_remove

 SELECT array_remove('{rick,phil,keith}'::text[],'phil');     

 returns: {rick,keith}


array_replace

 SELECT array_replace('{rick,phil,keith}'::text[],'phil','payal');
 returns: {rick,payal,keith}


#syntacticsugar

Foreign data Wrappers


  • csv
  • oracle
  • mysql
  • redis
  • mongodb
  • odbc

  • even twitter (!) 

POSTGRES FDW


  • access remote postgres databases
  • supports remote cost estimates
  • basic qual pushdown support
  • reference implementation
  • replaces dblink (but still there)

writable FDW


  • ability to write to foreign tables
  • insert/update/delete
  • transaction aware (!)
  • speed can be an issue

NOTE:

  • fdw driver implementation specific
  • postgres_fdw supports writes

psql


  • new commands
  • new options
  • performance improvements

performance



Adjust function cost settings so psql tab completion and pattern searching is more efficient



\Watch [seconds]


Add to any query to repeat the query each (n) seconds

            \watch SELECT now();
        
defaults to every 1 second

SELECT now(); \watch 5
runs every n seconds

psql \l *


allows the use of database name patterns in \l 


pagila=# \l template*
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |
-----------+----------+----------+-------------+-------------+
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(2 rows) 



Lateral Queries

  • Attaching SELECT in FROM to sub-SELECT in FROM


SELECT     c.relname, p.incrementFROM    pg_class c,    pg_sequence_parameters(c.oid) p WHERE    c.relkind = 'S' limit 3

relname | increment --------------------------+----------- actor_actor_id_seq | 1 category_category_id_seq | 1 film_film_id_seq | 1

Lateral Queries For Humans


SELECT    customer_id, rentalsFROM    customer c,    LATERAL(select count(distinct(rental_date)) as rentals             from rental where customer_id = c.customer_id) as xWHERE    store_id = 1ORDER BY rentals desc LIMIT 5
first_name | last_name | rentals ------------+-----------+--------- ELEANOR | HUNT | 46 MARCIA | DEAN | 42 CLARA | SHAW | 42 TIM | CARY | 39 DAISY | BATES | 38

event triggers

aka "DDL Triggers"


Currently Limited Use Cases 
CREATE OR REPLACE FUNCTION snitch( ) RETURNS event_trigger AS $$ BEGIN     RAISE NOTICE 'snitch: % %' , tg_event, tg_tag; END; $$ LANGUAGE plpgsql;   
CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE PROCEDURE snitch();

Dev Tools



9.3 adds several new features which can 
function as the building blocks
for more advanced tools

background Worker Processes


  • postmaster subprocesses
  • run arbitrary C code
  • access shared memory
  • use backend database connections
  • includes sample implementation


Possible uses:
  • disconnect automatically idle connections
  • customized statistics / logging info
  • automated reindex / cluster / etc... 

You CoulD ALSO


"mongres"


background worker listening for mongo protocol
storage and interaction with json data type



https://github.com/umitanuki/mongres
 

MORE!


  • parallel pg_dump
  • parallel pg_upgrade
  • pg_xlogdump 
  • COPY FREEZE
  • page level checksums
  • index based regex searches

Additional Resources



http://www.depesz.com/tag/pg93/


http://michael.otacoo.com/tag/9-3/


http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3


http://wiki.postgresql.org/wiki/PostgreSQL_9.3_Blog_Posts


Quiz


1. How many times did I reference the band Rush? 

2.  How many syllables are there in my employers name?

3. How many foreign data wrappers did I list in this talk?

4. How many functions were called in this talk?

5. How many slide sections were not mentioned in the outline?

Postgres 9.3

By xzilla

Postgres 9.3

An Overview of New Goodies Coming in Postgres 9.3. Versions of this talk have been delivered at BWPUG May 2013, PGCON 2013, PGConf EU 2013, and PGConf DE 2013.

  • 2,285