PostgreSQL’s 10 coolest features Alvaro Hernandez

ALVARO HERNANDEZ @ahachete DBA and Java Software Developer OnGres and ToroDB Founder PostgreSQL Community active member Database Frequent Speaker

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

#1 PostgreSQL Community PostgreSQL's 10 coolest features

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

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.

Database of the year 2017 and 2018 PostgreSQL's 10 coolest features

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

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!

#2 Transactional DDL PostgreSQL's 10 coolest features

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;

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

#3 Variety of indexes PostgreSQL's 10 coolest features

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 PostgreSQL's 10 coolest features

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

#4 Jsonb:
 MongoDB inside PostgreSQL PostgreSQL's 10 coolest features

Relational

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”: “...”}}
 ’);

Dozens of operators/functions to query json PostgreSQL's 10 coolest features

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);

#5 Advanced SQL PostgreSQL's 10 coolest features

No content

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 │ └──────┘

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;

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), () );

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;

SQL-generated fractals! ;) PostgreSQL's 10 coolest features

#6 Documentation PostgreSQL's 10 coolest features

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 ---------------------------------------------------------------------------------------

3,300 pages of high quality documentation PostgreSQL's 10 coolest features

#7 Physical and logical
 Replication PostgreSQL's 10 coolest features

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

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

#8 PostGIS PostgreSQL's 10 coolest features

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

#9 FDWs (Foreign Data Wrappers) PostgreSQL's 10 coolest features

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

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

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

#10 Extensions PostgreSQL's 10 coolest features

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

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

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