# How does PostgreSQL actually work?
[Peter Eisentraut](http://peter.eisentraut.org/)
[peter.eisentraut@2ndquadrant.com](mailto:peter.eisentraut@2ndquadrant.com)
[@petereisentraut](http://twitter.com/petereisentraut)
## 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
## 2ndQuadrant PostgreSQL Conference
November 6 & 7 New York
November 9 Chicago
# “From Query to Disk and Back Again”
## query
``` sql
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;
```
```
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
```
## psql
``` c
while (true)
{
char *input = readline("=> ");
if (strcmp(input, "\q") == 0)
exit(0);
PGresult = PQexec(conn, input);
/* print result */
}
```
## 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.10.0)
/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)
```
## libpq
``` c
PGconn *conn = PQconnectdb(...);
PQexec();
```
-------
``` c
fd = socket();
write(fd, ...);
read(fd, ...);
```
## 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
## to learn more
- tcpdump
- Wireshark
- PostgreSQL documentation
## lexical analysis
``` c
yylex()
```
SELECT date_trunc('month', orderdate), sum(netamount) FROM ...
---
SELECT
IDENT
'('
SCONST
','
IDENT
')'
','
IDENT
'('
IDENT
')'
FROM
...
## parsing
``` c
yyparse()
```
lex result → parse tree
## parse tree
``` yaml
List:
- SelectStmt:
targetList:
- ResTarget
- ResTarget
fromClause:
- JoinExpr
whereClause:
...
groupClause:
...
sortClause:
- SortBy:
...
```
## to learn more
- flex
- bison
- scan.l
- gram.y
## 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
## parse analysis
WHERE customers.age BETWEEN 18 AND 35
---
``` sql
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';
```
---
``` c
Datum
int2ge(PG_FUNCTION_ARGS)
{
int16 arg1 = PG_GETARG_INT16(0);
int16 arg2 = PG_GETARG_INT16(1);
PG_RETURN_BOOL(arg1 >= arg2);
}
```
## rewriting
`src/backend/rewrite/`
- expand views, rules
- results in parse tree
## planning/optimizing
`src/backend/optimizer/`
parse tree → execution plan
## plan tree
``` yaml
- 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))"
```
## to learn more
- `src/backend/optimizer/README`
| |
SELECT |
|
| |
↓ |
|
PREPARE → |
parse |
|
| |
rewrite |
|
| |
plan |
→ EXPLAIN |
EXECUTE → |
execute |
→ EXPLAIN ANALYZE |
| |
↓ |
|
| |
result |
|
## execution
`src/backend/executor/`
```
ExecSort
ExecAgg
ExecHashJoin
ExecSeqScan
ExecHash
ExecSeqScan
```
## to learn more
- `src/backend/executor/README`
## access methods
`src/backend/access/`
sequential scan
``` c
rel = heap_open(oid, lock);
scandesc = heap_beginscan(rel, snapshot, nkeys, keys);
tuple = heap_getnext(scandesc, direction);
heap_endscan(scandesc);
heap_close(rel);
```
## to learn more
- `src/backend/access/`
- `heap/`
- `nbtree/`
- `hash/`
- `gist/`
- ...
## storage management
$PGDATA/base/xxx/yyy
+----+----+----+----
| 8k | 8k | 8k | ...
+----+----+----+----
heap scan ---->
## to learn more
- `contrib/pageinspect`
- `pg_filedump`
- `src/backend/storage/`
## WAL logging
heap_update()
|
log_heap_update()
|
XLogInsert()
## to learn more
- `src/backend/access/transam/README`
- `src/backend/access/transam/xlog.c`
## the pieces of PostgreSQL
- psql
- libpq
- protocol
- lexer/parser — catalogs
- planner
- executor — fmgr, types, sort, hash, ...
- access — heap — WAL — replication
- smgr
## things to try
- `EXPLAIN`, `EXPLAIN ANALYZE`
- `tcpdump`
- `pg_filedump`
- `pg_waldump`
- study extensions
## PostgreSQL 10
coming Thursday October 5
## 2ndQuadrant PostgreSQL Conference
November 6 & 7 New York
November 9 Chicago