Lock in $30 Savings on PRO—Offer Ends Soon! ⏳

PostgreSQL's 10 coolest features

OnGres
January 26, 2019

PostgreSQL's 10 coolest features

PostgreSQL is the database of the year 2017 and 2018, and the database with the highest popularity growth in the last years. It is very highly ranked, reputed, trusted. But what backs this reputation? Is it worth the praises?

If you consider the database a “commodity” where 99% of what you do is INSERT, SELECT, GROUP BY, JOIN, keep reading. PostgreSQL is an incredible database with so many cool features that will make your job easier. Less buggy. More robust. Cheaper. And last but not least, more fun.

This is a tour of 10 really cool PostgreSQL features, like rolling back your live DDL changes, incredibly powerful JSON, functionality extension mechanism (like plugins) or how to connect to almost any external datasource.

OnGres

January 26, 2019
Tweet

More Decks by OnGres

Other Decks in Technology

Transcript

  1. ALVARO HERNANDEZ @ahachete DBA and Java Software Developer OnGres and

    ToroDB Founder PostgreSQL Community active member Database Frequent Speaker
  2. Why PostgreSQL? There are quite some RDBMSs, and many more

    NoSQL. Why choose PostgreSQL? It’s easy (sometimes) to rule out commercial databases due to licensing costs. How about other open source databases? “X is trendier” or “I started with Y” are usually not very good reasons to go X or Y... PostgreSQL's 10 coolest features
  3. Like Linux, but better PostgreSQL is developed by an open

    community. No single company behind it. Several companies and individuals contribute. No strong leadership. PGDG (PostgreSQL Global Development Group) and Core Members oversee the project Open membership, meritocratic decisions. PostgreSQL's 10 coolest features
  4. Liberal license PostgreSQL's 10 coolest features ‣PostgreSQL is fully open

    source, under a very liberal PostgreSQL License, similar to BSD. ‣ No GPL + proprietary linking legal problem. ‣Make even commercial forks if you want! Netezza, Vertica, Greenplum, ParAccel (RedShift) and many others.
  5. Reputation PostgreSQL is very highly considered by the tech community:

    almost no one complains about it, almost everybody praises it: ✓Robustness ✓Standards conformance ✓Huge array of features ✓Conservative progress and yearly releases PostgreSQL's 10 coolest features
  6. Community events PostgreSQL's 10 coolest features ‣3 or more large

    yearly conferences ‣Dozens of 1-2 days country events ‣Hundreds of meetups ‣Participation highly encouraged!
  7. Undo any DDL change PostgreSQL's 10 coolest features BEGIN; DO

    language plpgsql $$ BEGIN EXECUTE 'CREATE SCHEMA ddl'; FOR i IN 1..1000 LOOP EXECUTE 'CREATE TABLE ddl._' || i || '(i integer)'; EXECUTE 'ALTER TABLE ddl._' || i || ' ADD COLUMN t text'; END LOOP; END $$; ROLLBACK;
  8. Just watch out for... Locks: not all DDL operations have

    fine locks (ALTER TABLE … ADD COLUMN is fine if the values is nullable or you supply a default value and you run PG11+. Bloat could be easily created. PostgreSQL's 10 coolest features
  9. Advanced B-Tree indexes Expression (functional) indexes: ✓CREATE INDEX ... ON

    expression_involving_cols ✓Any expression: functions, operators… ✓Just don’t use mutable expressions Partial indexes: ✓CREATE INDEX … WHERE <condition> PostgreSQL's 10 coolest features
  10. More than B-Trees! Hash indexes (use only PG >=10). Faster

    for equality. BRIN: very low storage requirements, great for naturally ordered data & analytical queries GIN: specialized index for column “sub elements”, like arrays, json or FTS. GIST: specialized indexes (like for GIS) PostgreSQL's 10 coolest features
  11. Any json, in any column PostgreSQL's 10 coolest features CREATE

    TABLE registered_user ( email varchar NOT NULL PRIMARY KEY, provider uuid NOT NULL REFERENCES provider(id), data jsonb ); INSERT INTO registered_user VALUES (‘...’, ...,‘
 {“name”: “...”, “tags”: [ …, …, … ],
 “subdoc”: {“a”: “...”}}
 ’);
  12. Specialized indexes PostgreSQL's 10 coolest features ➡ Normal B-tree on

    expression: CREATE INDEX idx ON table ((data->>’field'));
 ➡ GIN index for all fields: CREATE INDEX idx ON table USING gin (data);
 ➡ GIN index for paths: CREATE INDEX ON table USING GIN (data json_path_ops);
  13. Recursive queries PostgreSQL's 10 coolest features WITH RECURSIVE t(n) AS

    ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t; ┌──────┐ │ sum │ ├──────┤ │ 5050 │ └──────┘
  14. Move data atomically PostgreSQL's 10 coolest features WITH moved_rows AS

    ( DELETE FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' RETURNING * ) INSERT INTO products_log SELECT * FROM moved_rows;
  15. Grouping sets PostgreSQL's 10 coolest features SELECT gender, extract(year from

    hire_date) AS year, max(birth_date), count(*) FROM employees WHERE hire_date>'1990-01-01' GROUP BY GROUPING SETS ( (gender, year), (gender), () );
  16. Even cool queries like this one PostgreSQL's 10 coolest features

    WITH RECURSIVE x(i) AS ( VALUES(0) UNION ALL SELECT i + 1 FROM x WHERE i < 101 ), Z(Ix, Iy, Cx, Cy, X, Y, I) AS ( SELECT Ix, Iy, X::FLOAT, Y::FLOAT, X::FLOAT, Y::FLOAT, 0 FROM (SELECT -2.2 + 0.031 * i, i FROM x) AS xgen(x,ix) CROSS JOIN (SELECT -1.5 + 0.031 * i, i FROM x) AS ygen(y,iy) UNION ALL SELECT Ix, Iy, Cx, Cy, X * X - Y * Y + Cx AS X, Y * X * 2 + Cy, I + 1 FROM Z WHERE X * X + Y * Y < 16.0 AND I < 27 ), Zt (Ix, Iy, I) AS ( SELECT Ix, Iy, MAX(I) AS I FROM Z GROUP BY Iy, Ix ORDER BY Iy, Ix ) SELECT array_to_string( array_agg(SUBSTRING(' .,,,-----++++%%%%@@@@#### ', GREATEST(I,1),1)),'' ) FROM Zt GROUP BY Iy ORDER BY Iy;
  17. 25% of source code are comments PostgreSQL's 10 coolest features

    $ ./cloc ~/github/https/postgres/postgres [...] --------------------------------------------------------------------------------------- Language files blank comment code --------------------------------------------------------------------------------------- C 1282 161798 297227 876208 C/C++ Header 811 14918 41114 77465 yacc 10 3576 2988 32487 lex 12 966 1983 4972 ... ... ... ... ... --------------------------------------------------------------------------------------- SUM: 3470 308366 489799 1449857 ---------------------------------------------------------------------------------------
  18. Physical replication Default since 9.0 Very low latency, efficient (Uber

    disagrees) Supports cascading, sync/async, multiple quorum, remote apply or local apply… Bit-by-bit copy, the whole cluster Secondaries are read-only Building block for read scale-out and HA PostgreSQL's 10 coolest features
  19. Logical replication Since 10.0 Publisher/subscriber model Replicate any subset: databases,

    schemas, tables Uni-directional. Bi-directional with BDR fork Replicate cross-versions, provide online upgrade procedure PostgreSQL's 10 coolest features
  20. PostGIS PostgreSQL's 10 coolest features The most advanced GIS system

    for a db. Installation: CREATE EXTENSION postgis; New data types, functions, operator, indexes Its own ecosystem of companion tools, GUI editors, data import/ export systems Another 826 pages manual yeah, that’s it
  21. Foreign Data Wrappers PostgreSQL’s implementation of SQL/MED. Basically, an API

    to call external data as if it were a normal table. You can query, join, aggregate normal and external tables alike. Easy to create new FDWs: either in C (less than 1,000 LOCs) or Python (multicorn). PostgreSQL's 10 coolest features
  22. Advanced features PostgreSQL's 10 coolest features ‣Import remote schema (creates

    many tables from the remote system). ‣SELECT only or also writable. ‣Predicate pushdown. ‣Gather remote statistics to improve planner efficiency. ‣postgres_fdw is the gateway to other PG
  23. Some FDWs Generic SQL wrappers (JDBC, ODBC) SQL: PG, Oracle,

    MySQL, SQL Server, Sybase NoSQL: MongoDB, Cassandra, CouchDB, Redis, HBase, ClickHouse File: CSV, JSON, XML, TAR, ZIP… Web: Facebook, Google, S3, Twitter… Other: IMAP, Docker, cstore, PGStrom, ORC... PostgreSQL's 10 coolest features
  24. Extensions PostgreSQL's 10 coolest features ‣Like plugins for the database.

    ‣Mechanism to create, upgrade, remove. No special privileges. ‣Simple extensions: pack your data types, functions, operators, aggregates or even your DDL! ‣Actually, it’s a great way of contained deployment
  25. Advanced Extensions Actually extensions could be written in C and

    call any function in PostgreSQL source code. They could implement one of the many hooks available to extend functionality. It could replace/improve code. For example, citus extends the planner and executor and provides a sharded database within just an extension installation. Or timescaledb, that adds time-series temporal and spatial partitioning PostgreSQL's 10 coolest features
  26. 1177 Avenue of the Americas, Suite 500 New York, 10036,

    NY United States of America +1 (646) 452 7168 [email protected] Carretera de Fuencarral, 44, Edificio 4B, Loft 33 Alcobendas, 28108, MD España +34 918 675 554 [email protected] www.ongres.com