How does PostgreSQL actually work?

1. How does PostgreSQL actually work? Peter Eisentraut peter.eisentraut@2ndquadrant.com @petereisentraut
2. 2ndQuadrant >10 years of PostgreSQL services >50 staff in >20 countries offers 24x7 production support and consulting for PostgreSQL one of the leading code contributors
3. 2ndQuadrant PostgreSQL Conference http://www.2qpgconf.com/ November 6 & 7 New York November 9 Chicago
4. “From Query to Disk and Back Again”
5. query SELECT date_trunc('month', orderdate), sum(netamount) FROM customers JOIN orders USING (customerid) WHERE customers.age BETWEEN 18 AND 35 GROUP BY date_trunc('month', orderdate) ORDER BY sum(netamount) DESC;
6. date_trunc | sum ------------------------+---------- 2004-07-01 00:00:00-04 | 54849.04 2004-04-01 00:00:00-05 | 53741.94 2004-05-01 00:00:00-04 | 50858.31 2004-06-01 00:00:00-04 | 50694.45 2004-12-01 00:00:00-05 | 50298.48 2004-08-01 00:00:00-04 | 49868.39 2004-10-01 00:00:00-04 | 49151.65 2004-11-01 00:00:00-05 | 49041.88 2004-01-01 00:00:00-05 | 47463.62 2004-03-01 00:00:00-05 | 47455.33 2004-09-01 00:00:00-04 | 46128.39 2004-02-01 00:00:00-05 | 44295.26 (12 rows) Time: 15.794 ms
7. psql while (true) { char *input = readline("=> "); if (strcmp(input, "\q") == 0) exit(0); PGresult = PQexec(conn, input); /* print result */ }
8. psql/libpq $ otool -L /usr/local/opt/postgresql@10/bin/psql /usr/local/opt/postgresql@10/bin/psql: /usr/local/Cellar/postgresql@10/10.0/lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.1 /usr/local/opt/gettext/lib/libintl.8.dylib (compatibility version 10.0.0, current version 10.5.0) /usr/local/opt/readline/lib/libreadline.7.dylib (compatibility version 7.0.0, current version 7.0.0) /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1238.50.2)
9. libpq PGconn *conn = PQconnectdb(...); PQexec(); fd = socket(); write(fd, ...); read(fd, ...);
10. frontend/backend protocol C→S 'Q' num "SELECT ..." S→C 'T' num ... S→C 'D' num ... S→C 'D' num ... ... S→C 'Z' num 'I' C→S 'Q' ... ... C→S 'X' num
11. to learn more tcpdump Wireshark PostgreSQL documentation
12. lexical analysis yylex() SELECT date_trunc('month', orderdate), sum(netamount) FROM ... SELECT IDENT '(' SCONST ',' IDENT ')' ',' IDENT '(' IDENT ')' FROM ...
13. parsing yyparse() lex result → parse tree
14. parse tree List: - SelectStmt: targetList: - ResTarget - ResTarget fromClause: - JoinExpr whereClause: ... groupClause: ... sortClause: - SortBy: ...
15. to learn more flex bison scan.l gram.y
16. parse analysis the rest of src/backend/parser/ additional syntax checks check that tables, columns, etc. exist check that types match system catalogs (pg_class, pg_type, ...) results in modified parse tree
17. parse analysis WHERE customers.age BETWEEN 18 AND 35 select oid from pg_type where typname = 'int2'; select oprcode from pg_operator where oprname = '>=' and oprleft = 21 and oprright = 21; select prosrc from pg_proc where proname = 'int2ge'; Datum int2ge(PG_FUNCTION_ARGS) { int16 arg1 = PG_GETARG_INT16(0); int16 arg2 = PG_GETARG_INT16(1); PG_RETURN_BOOL(arg1 >= arg2); }
18. rewriting src/backend/rewrite/ expand views, rules results in parse tree
19. planning/optimizing src/backend/optimizer/ parse tree → execution plan
20. plan tree - Plan: Node Type: "Sort" Sort Key: - "(sum(orders.netamount)) DESC" Plans: - Node Type: "Aggregate" Strategy: "Hashed" Group Key: ... Plans: - Node Type: "Hash Join" Hash Cond: "(orders.customerid = customers.customerid)" Plans: - Node Type: "Seq Scan" Relation Name: "orders" - Node Type: "Hash" Plans: - Node Type: "Seq Scan" Relation Name: "customers" Filter: "((age >= 18) AND (age <= 35))"
21. to learn more src/backend/optimizer/README
22. SELECT ↓ PREPARE →parse rewrite plan → EXPLAIN EXECUTE →execute → EXPLAIN ANALYZE ↓ result
23. execution src/backend/executor/ ExecSort ExecAgg ExecHashJoin ExecSeqScan ExecHash ExecSeqScan
24. to learn more src/backend/executor/README
25. access methods src/backend/access/ sequential scan rel = heap_open(oid, lock); scandesc = heap_beginscan(rel, snapshot, nkeys, keys); tuple = heap_getnext(scandesc, direction); heap_endscan(scandesc); heap_close(rel);
26. to learn more src/backend/access/ heap/ nbtree/ hash/ gist/ ...
27. storage management $PGDATA/base/xxx/yyy +----+----+----+---- | 8k | 8k | 8k | ... +----+----+----+---- heap scan ---->
28. to learn more contrib/pageinspect pg_filedump src/backend/storage/
29. WAL logging heap_update() | log_heap_update() | XLogInsert()
30. to learn more src/backend/access/transam/README src/backend/access/transam/xlog.c
31. the pieces of PostgreSQL psql libpq protocol lexer/parser — catalogs planner executor — fmgr, types, sort, hash, ... access — heap — WAL — replication smgr
32. things to try EXPLAIN, EXPLAIN ANALYZE tcpdump pg_filedump pg_waldump study extensions
33. PostgreSQL 10 coming Thursday October 5
34. 2ndQuadrant PostgreSQL Conference http://www.2qpgconf.com/ November 6 & 7 New York November 9 Chicago