an evening with postgresql

An Evening with PostgreSQL

Command Prompt, Inc.

Who Am I

● @linuxhiker

● +JoshuaDrakeLinuxHiker

● jd@commandprompt.com

● Lead Consultant – Command Prompt, Inc.

● Director – Software in the Public Interest

● President – United States PostgreSQL

Rated: Pg-13

● I do this for fun. This is not my day job.

● East Coast from the West Coast

● Your ego is not my concern

● To to take offense is to not be comfortable in oneself.

● Hopefully you laugh and learn

Help control license costs

If you are selling licenses to software, you are not helping control license costs.

Proactive SLA

● Remote DBA / Sysadmin

● Proactive Response

● 4 Hours of DBA (minimum)

● SLA / 24x7 / 365

● No Emergency/After Hours rates

● Flat, discounted rate

From 1350.00/mo

What are we talking about

● What is PostgreSQL?

● Community Structure

● Comparison to other databases

● Awesome PostgreSQL stuff + 9.4 features

● WTH were they thinking!

● Guaranteed not to be in order

What is PostgreSQL?

● The oldest of the open source databases (derived from University Ingres 1974)

● The most advanced Open Source (possibly of closed too) database

● A full ACID compliant, relational, object-relational, document, modern, scalable database.

Community Structure

● Everyone Welcome● Meritocracy● INTL: Postgresql.org● Japan: Postgresql.jp● EU: Postgresql.eu● US: Postgresql.us● Smaller ones (Brazil, France, Italy)

Comparison to other databases

● Licensing● Community● No single point of failure● Feature parity with all databases, more advanced than some

● Best of both worlds, relational or document

Licensing

BSD not GPL

● Important for commercial providers

Community

● Large● Vibrant● Active● All walks of life● Driven by the ecosystem, not a company

No single point of failure

● Can not be bought● Can not go out of business● Can not be co-opted● Many known and qualified support/services

companies (CMD, PgExperts, OmniTI, BullInfoSys, Consistent State, 2nd Quadrant)

Feature Parity

● We have reached a point of... oh PostgreSQL● Like MSSQL, Oracle, Sybase, just another SQL

database but with neat stuff● No longer a fringe product (thanks to Oracle)

Standard Features

● SQL Compliance● Partitioning● Replication● Tablespaces● ACID

AWESOME Stuff

BEGIN;

ALTER TABLE foo ADD COLUMN bar text;

\d foo

COMMIT/ROLLBACK;

(Mysql can't do this)

Craziness

BEGIN;

CREATE TABLE foo (id serial, test text);

CREATE TABLE bar (a foo);

insert into bar values (row(1,'this is a test'));

postgres=# select * from bar;

a

----------------------

(1,"this is a test")

– Say what?

It gets better

postgres=# select (a).id from bar;

id

----

1

Wooohaa!

postgres=# select row_to_json(row((a).id),true) from bar; row_to_json ------------- {"f1":1}

Enough of that, let's talk 9.4

● JSONB● Logical Decoding (Including Logical

Replication)● BDR● Wal Bouncer● Other 9.4 stuff

JSON● Added in 9.2

● Input is validated

● Stored as text representation

– Slower on retrieval due to per row parse (per value?)

● Preserves key order and duplicates

● Mature support in 9.3

– Better Functions

– Operators

● Advanced support in 9.4

– Type building functions

● About 15% storage overhead

● Expression only indexes (WHERE foo)

When to use JSON

● Document storage● Duplicate preservation● Key order preservation

JSONB

● 9.4+

● Full JSON Implementation

● Stored as binary (unlike JSON)

● Works with all JSON operators

● HSTORE-style query operators

● No key order

● No duplicate preservation

● Faster access

● GIN Indexing (and expression), for containment ops use json_path_ops

● About 35% storage overhead

● Much faster than JSON for retrieval (slower than HSTORE)

JSONB Features

● Equality operator

– SELECT '{“a”: 1, “b”: 2}'::jsonb = '{“b”:2, “a”:1}'::jsonb

● Containment operator (Softserve)

– SELECT '{“a”: 1, “b”: 2}'::jsonb @> {“b”:2}::jsonb

● Existence

– SELECT '{“a”: 1, “b”: 2}'::jsonb ? 'b';

● Nested operators (softserve works as well)

– SELECT '{“a”: [1,2]}'::jsonb = '{“a”:[1,2]}'::jsonb

● Existence (any) - ?|

● Existence (all) - ?&

JSON/JSBON thanks

Information retrieved from PDXPUG day in 2014 via David Wheeler

● http://vimeo.com/105491487

Logical Decoding

PostgreSQL provides infrastructure to stream the modifications performed via SQL to external consumers.

The format in which those changes are streamed is determined by the output plugin used.

Every output plugin has access to each individual new row produced by INSERT and the new row version created by UPDATE. Availability of old row versions for UPDATE and DELETE depends on the configured REPLICA IDENTITY.

It is also possible to write additional methods of consuming the output of a replication slot without modifying core code.

(http://www.postgresql.org/docs/9.4/static/logicaldecoding.html)

What does Logical Decoding Mean?

● You now have backend access to INSERT/UPDATE/DELETE mechanisms.

● Is used to implement new features such as:– BDR: https://wiki.postgresql.org/wiki/BDR_User_Guide

– Auditing

– Walbouncer: http://www.cybertec.at/en/products/walbouncer-enterprise-grade-partial-replication/

BDR

● Multi-Master without triggers! (Sorry Bucardo)● Uses LLSR (From Logical Decoding)● Supports distributed Sequences● Supports synchronisation functions● Supports conflict handlers● Highly performant● Eventually consistent● Up to 48 nodes

WalBouncer

● Requires 9.4

● Allows partial replication to remote replicas

– Each replica can have a different data set

● Filters based on WAL

● Single master to many slave

● Can be disconcerting to the novice

● http://www.cybertec.at/postgresql_produkte/walbouncer/

Other 9.4 Stuff

● GIN indexes now faster and smaller● pg_prewarm● ALTER SYSTEM● Concurrent materialized view refresh● Update Views with different columns

ALTER SYSTEM

● Commands such as:– ALTER SYSTEM SET log_min_duration_statement

= '5s';

Are now saved to postgresql.auto.conf which is always read last and saved between restarts.

Better updateable views

CREATE TABLE products (

product_id SERIAL PRIMARY KEY,

product_name TEXT NOT NULL,

quantity INT,

reserved INT DEFAULT 0);

CREATE VIEW products_view AS

SELECT product_id,

product_name,

quantity,

(quantity - reserved) AS available

FROM products

WHERE quantity IS NOT NULL;

Views Continued

postgres=# INSERT INTO products_view (product_name, quantity) VALUES

('Budget laptop', 100),

('Premium laptop', 10);

INSERT 0 2

postgres=# SELECT * FROM products;

product_id | product_name | quantity | reserved

------------+----------------+----------+----------

1 | Budget laptop | 100 | 0

2 | Premium laptop | 10 | 0

(2 rows)

Donate

http://www.postgresql.org/about/donate

Questions?

● Political● Community● Technical● Why the hell not?