Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Postgres present and future

Postgres present and future

A talk walking through recent features in Postgres 9.5, upcoming features in Postgres 9.6, and a brief tour of some extensions.

Craig Kerstiens

July 16, 2016
Tweet

More Decks by Craig Kerstiens

Other Decks in Technology

Transcript

  1. @craigkerstiens Head of Citus Cloud Curate Postgres Weekly Launched Python

    support on Heroku 5 years ago, ran product for a number of areas at Heroku, primarily Heroku Postgres
  2. Pronunciation It might help to explain that the pronunciation is

    "post-gres" or "post-gres-cue-ell", not "post-gray-something". I heard people making this same mistake in presentations at this past weekend's Postgres Anniversary Conference :-( Arguably, the 1996 decision to call it PostgreSQL instead of reverting to plain Postgres was the single worst mistake this project ever made. It seems far too late to change now, though. regards, tom lane
  3. Postgres – TLDR; Datatypes Conditional indexes Transactional DDL Foreign Data

    Wrappers Extensions Common Table Expressions Geospatial capabilities Full text search Fast Column Addition Listen/Notify Table Inheritance Per Transaction Sync. Replication Window Functions JSONB Momentum
  4. Rough outline Postgres 9.5 – released 5 months ago Postgres

    9.6 – feature frozen 3 months ago Extensions – a small tour of a few Some other cool things
  5. Upsert Try to insert a record If some key identifier

    is already there, simply update the record Each user can have only one, if you try to save mine and it already exists, just update the data.
  6. Before upsert WITH upsert AS ( UPDATE pinned_tweet SET tally=tally+1

    WHERE user_id=1 AND tweet_id=2 RETURNING * ) INSERT INTO pinned_tweet (user_id, tweet_id) SELECT 1, 3 WHERE NOT EXISTS (SELECT * FROM upsert)
  7. Indexes - which to use Gin Use with multiple values

    in a single column hstore/array/JSONB
  8. But first, foreign data wrappers Connect from inside Postgres to

    some other data source and query directly in Postgres Import Foreign Schema
  9. Import Foreign Schema CREATE EXTENSION mongo_fdw; CREATE SERVER foo… CREATE

    FOREIGN TABLE my_mongo_table ( id int, title varchar(255), description text )
  10. Grouping Sets Handy for analytics that otherwise took a lot

    of case statements Essentially new types of grouping that lets you easily cube data.
  11. Grouping Sets SELECT department, role, age, count(*) FROM employees GROUP

    BY your_grouping_type_here; Options include: Grouping Sets, Cube, Rollup
  12. Grouping Sets GROUPING SETS (department, role, age, ()); department |

    role | age | count ------------+-----------------+-----------+------- Finance | | | 3 IT | | | 2 Sales | | | 2 | | | 7 | | 30 | 3 | | 40 | 4 | Accountant | | 1 | Manager | | 3 | Project Manager | | 3 (9 rows)
  13. Cube CUBE (department, role, age); department | role | age

    | count ------------+-----------------+-----------+------- Finance | Accountant | 40 | 1 Finance | Accountant | | 1 Finance | Manager | 40 | 1 Finance | Manager | | 1 Finance | Project Manager | 30 | 1 Finance | Project Manager | | 1 Finance | | | 3 IT | Manager | 40 | 1 IT | Manager | | 1 …
  14. JSONB Came in Postgres 9.4 Binary JSON, more or less

    what mongo does Can index it–indexes an entire document
  15. JSONB - Functions Concatenation Remove specific keys Pretty print SELECT

    ‘{ "name": "Craig", "city": “Albany” }'::jsonb || ‘{ "talk": “Postgres” }'::jsonb; ?column? ----------------------------- {“city”: “Albany”, "name": "Craig", "talk": "Postgres"} (1 row)
  16. JSONB - Functions Concatenation Remove specific keys Pretty print SELECT

    ‘{ "name": "Craig", "city": “Albany” }’::jsonb - ‘city’ ?column? ----------------------------- {"name": "Craig"} (1 row)
  17. JSONB - Functions Concatenation Remove specific keys Pretty print SELECT

    jsonb_pretty(’{ "name": "Craig", "city": “Albany”, "talk": "Postgres" }’::jsonb) ?column? ----------------------------- { + "city": “Albany”, + "name": "Craig", + "talk": "Postgres"+ } (1 row)
  18. Postgres – 9.6 Parallelism Parallel sequential scans Parallel joins No

    more full-table vacuums Bloom filter Postgres FDW improvements
  19. Parallel scans Now: set max_parallel_degree = 4; w1 w2 w3

    w4 Rough benchmarking shows an example of 743 ms to 213 ms - per http://rhaas.blogspot.co.nz/2015/11/parallel-sequential-scan-is- committed.html
  20. Bloom filter Space efficient probabilistic data structure Example schema: columns

    a, b, c, d, e You want to search for WHERE a = 23 and b = 785
  21. Bloom filter CREATE extension bloom; CREATE INDEX bloomidx ON table

    using bloom (a, b, c, d, e); Might return false positives, but Postgres will then filter those.
  22. Postgres – 9.6 Parallel Parallel sequential scans Parallel joins No

    more full-table vacuums Bloom filter Postgres FDW improvements
  23. Citus Postgres works best when data is in memory Most

    commonly it’s one table you need to scale out You might need to shard if you have a table called: events, logs, messages
  24. Citus Postgres extension that allows you to turn Postgres into

    a distributed database. You go from one database, to spreading your data set across a single node Think parallel, but across multiple instances not just scaled up
  25. Citus CREATE EXTENSION citus; CREATE TABLE tweets (id uuid, user_id

    uuid, tweet varchar(140)); SELECT master_create_distributed_table( 'tweets', 'id', 'hash'); SELECT master_create_worker_shards('tweets', 16, 1); INSERT INTO tweets (id, user_id, tweet) VALUES (1, 2, “Words of wisdom”)
  26. Hyper log log KMV - K minimum value Bit observable

    patterns Stochastic averaging Harmonic averagaing
  27. Hyperloglog SELECT EXTRACT (month from date) AS MONTH, hll_cardinality(hll_union_agg(users)) FROM

    daily_uniques WHERE date >= ‘2016-01-01’ AND date < ‘2016-02-01’ GROUP BY 1;
  28. Hyperloglog SELECT EXTRACT (month from date) AS MONTH, hll_cardinality(hll_union_agg(users)) FROM

    daily_uniques WHERE date >= ‘2016-01-01’ AND date < ‘2016-02-01’ GROUP BY 1;
  29. Extensions Citus Hyperloglog HypoPG PLV8 Multicorn pg_partman pg_repack range_partitioning orafce

    mysql_fdw mongo_fdw cstore_fdw sqlite_fdw oracle_fdw ldap_fdw postgres_fdw
  30. Rough recap Postgres 9.5 released 4 months ago Highlights include

    Upsert, lots of small usability improvements Postgres 9.6 Feature frozen 1 week ago Mostly performance improvements Extensions Citus - Horizontally scalable Postgres HLL - Almost exact uniques across large datasets Lots more - explore them at pgxn.org