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.

Transcript

1. PostgreSQL’s 10 coolest features Alvaro Hernandez
2. ALVARO HERNANDEZ @ahachete DBA and Java Software Developer OnGres and ToroDB Founder PostgreSQL Community active member Database Frequent Speaker
3. 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
4. #1 PostgreSQL Community PostgreSQL's 10 coolest features
5. 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
6. 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.
7. Database of the year 2017 and 2018 PostgreSQL's 10 coolest features
8. 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
9. 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!
10. #2 Transactional DDL PostgreSQL's 10 coolest features
11. 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;
12. 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
13. #3 Variety of indexes PostgreSQL's 10 coolest features
14. 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
15. 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
16. #4 Jsonb:
 MongoDB inside PostgreSQL PostgreSQL's 10 coolest features
17. Relational
18. 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”: “...”}}
 ’);
19. Dozens of operators/functions to query json PostgreSQL's 10 coolest features
20. 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);
21. #5 Advanced SQL PostgreSQL's 10 coolest features
23. 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 │ └──────┘
24. 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;
25. 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), () );
26. 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;
27. SQL-generated fractals! ;) PostgreSQL's 10 coolest features
28. #6 Documentation PostgreSQL's 10 coolest features
29. 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 ---------------------------------------------------------------------------------------
30. 3,300 pages of high quality documentation PostgreSQL's 10 coolest features
31. #7 Physical and logical
 Replication PostgreSQL's 10 coolest features
32. 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
33. 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
34. #8 PostGIS PostgreSQL's 10 coolest features
35. 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
36. #9 FDWs (Foreign Data Wrappers) PostgreSQL's 10 coolest features
37. 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
38. 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
39. 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
40. #10 Extensions PostgreSQL's 10 coolest features
41. 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
42. 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
43. 1177 Avenue of the Americas, Suite 500 New York, 10036, NY United States of America +1 (646) 452 7168 info@ongres.com Carretera de Fuencarral, 44, Edificio 4B, Loft 33 Alcobendas, 28108, MD España +34 918 675 554 info@ongres.com www.ongres.com