the best of bruce's postgres slides - momjianmomjian.us/main/writings/pgsql/best_of.pdf · the...

The Best of Bruce’s Postgres Slides

BRUCE MOMJIAN

This talk has the best slides from my 25+ Postgres presentations.Creative Commons Attribution License http://momjian.us/presentations

Last updated: November, 2018

1 / 26

Postgres System Architecture

utility

Plan

Optimal Path

Query

Postmaster

Postgres Postgres

Libpq

Main

Generate Plan

Traffic Cop

Generate Paths

Execute Plan

e.g. CREATE TABLE, COPYSELECT, INSERT, UPDATE, DELETE

Rewrite Query

Parse Statement

UtilityCommand

Storage ManagersCatalogUtilities

Access Methods Nodes / Lists

Mastering PostgreSQL Administration

2 / 26

http://momjian.us/main/writings/pgsql/administration.pdf

Shared Memory Creation

postmaster postgres postgres

Program (Text)

Data

Program (Text)

Data

Shared Memory

Program (Text)

Data

Shared Memory Shared Memory

Stack Stack

fork()

Stack

Inside PostgreSQL Shared Memory

3 / 26

http://momjian.us/main/writings/pgsql/inside_shmem.pdf

Shared Buffers and WAL

Recovery

fsync

fsync

Query and Checkpoint Operations Transaction Durability

BackendPostgres

BackendPostgres

BackendPostgres

PostgreSQL Shared Buffer Cache Write−Ahead Log

Kernel Disk Buffer Cache

Disk Blocks

PostgreSQL Performance Tuning

4 / 26

http://momjian.us/main/writings/pgsql/performance.pdf

Backend Flowchart - Magnified

utility

Plan

Optimal Path

Query

Generate Plan

Traffic Cop

Generate Paths

Execute Plan

e.g. CREATE TABLE, COPYSELECT, INSERT, UPDATE, DELETE

Rewrite Query

Parse Statement

UtilityCommand

PostgreSQL Internals Through Pictures

5 / 26

http://momjian.us/main/writings/pgsql/internalpics.pdf

Query Processing

FindExec: found "/var/local/postgres/./bin/postmaster" using argv[ 0]./bin/postmaster: BackendStartup: pid 3320 user postgres db test socket 5./bin/postmaster child[ 3320 ]: starting with (postgres −d99 −F −d99 −v131072 −p test )FindExec: found "/var/local/postgres/./bin/postgres" using argv[ 0]DEBUG: connection: host=[local] user=postgres database=testDEBUG: InitPostgresDEBUG: StartTransactionCommandDEBUG: query: SELECT firstname FROM friend WHERE age = 33;DEBUG: parse tree: { QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable ({ RTE :relname friend :relid 26912 :subquery <> :alias <> :eref { ATTR :relname friend :attrs ( "firstname" "lastname" "city" "state" "age" )} :inh true :inFromCl true :checkForRead true :checkForWrite false :checkAsUser 0}) :jointree { FROMEXPR :fromlist ({ RANGETBLREF 1 }) :quals { EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 0 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 5 :vartype 23 :vartypmod − 1 :varlevelsup 0 :varnoold 1 :varoattno 5} { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 33 0 0 0 ] })}} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1042 :restypmod 19 :resname firstname :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 19 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}DEBUG: rewritten parse tree:DEBUG: { QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable ({ RTE :relname friend :relid 26912 :subquery <> :alias <> :eref { ATTR :relname friend :attrs ( "firstname" "lastname" "city" "state" "age" )} :inh true :inFromCl true :checkForRead true :checkForWrite false :checkAsUser 0}) :jointree { FROMEXPR :fromlist ({ RANGETBLREF 1 }) :quals { EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 0 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 5 :vartype 23 :vartypmod − 1 :varlevelsup 0 :varnoold 1 :varoattno 5} { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 33 0 0 0 ] })}} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1042 :restypmod 19 :resname firstname :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 19 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}DEBUG: plan: { SEQSCAN :startup_cost 0.00 :total_cost 22.50 :rows 10 :width 12 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1042 :restypmod 19 :resname firstname :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 19 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual ({ EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 5 :vartype 23 :vartypmod − 1 :varlevelsup 0 :varnoold 1 :varoattno 5} { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 33 0 0 0 ] })}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 }DEBUG: ProcessQueryDEBUG: CommitTransactionCommandDEBUG: proc_exit( 0)DEBUG: shmem_exit( 0)DEBUG: exit( 0)./bin/postmaster: reaping dead processes..../bin/postmaster: CleanupProc: pid 3320 exited with status 0


6 / 26


EXPLAIN with Constants of Various Frequencies

l | count | lookup_letter---+-------+-----------------------------------------------------------------------p | 199 | Seq Scan on sample (cost=0.00..13.16 rows=199 width=2)s | 9 | Seq Scan on sample (cost=0.00..13.16 rows=9 width=2)c | 8 | Seq Scan on sample (cost=0.00..13.16 rows=8 width=2)r | 7 | Seq Scan on sample (cost=0.00..13.16 rows=7 width=2)t | 5 | Bitmap Heap Scan on sample (cost=4.29..12.76 rows=5 width=2)f | 4 | Bitmap Heap Scan on sample (cost=4.28..12.74 rows=4 width=2)v | 4 | Bitmap Heap Scan on sample (cost=4.28..12.74 rows=4 width=2)d | 4 | Bitmap Heap Scan on sample (cost=4.28..12.74 rows=4 width=2)a | 3 | Bitmap Heap Scan on sample (cost=4.27..11.38 rows=3 width=2)_ | 3 | Bitmap Heap Scan on sample (cost=4.27..11.38 rows=3 width=2)u | 3 | Bitmap Heap Scan on sample (cost=4.27..11.38 rows=3 width=2)e | 2 | Index Scan using i_sample on sample (cost=0.00..8.27 rows=1 width=2)i | 1 | Index Scan using i_sample on sample (cost=0.00..8.27 rows=1 width=2)k | 1 | Index Scan using i_sample on sample (cost=0.00..8.27 rows=1 width=2)

Explaining the Postgres Query Optimizer

7 / 26

http://momjian.us/main/writings/pgsql/optimizer.pdf

Deadlocks

SELECT pg_sleep(0.500); SELECT * FROM lockview1;pid | vxid | lock_type | lock_mode | granted | xid_lock | relname

-------+-------+---------------+------------------+---------+----------+------------11306 | 2/61 | transactionid | ExclusiveLock | t | 710 |11306 | 2/61 | relation | RowExclusiveLock | t | | i_lockdemo11306 | 2/61 | relation | RowExclusiveLock | t | | lockdemo11306 | 2/61 | tuple | ExclusiveLock | t | | lockdemo11306 | 2/61 | transactionid | ShareLock | f | 711 |11642 | 3/116 | transactionid | ExclusiveLock | t | 711 |11642 | 3/116 | relation | RowExclusiveLock | t | | i_lockdemo11642 | 3/116 | relation | RowExclusiveLock | t | | lockdemo11642 | 3/116 | tuple | ExclusiveLock | t | | lockdemo11642 | 3/116 | transactionid | ShareLock | f | 710 |

Unlocking the Postgres Lock Manager

8 / 26

http://momjian.us/main/writings/pgsql/locking.pdf

MVCC Behavior

INSERT

UPDATE

DELETE

old (delete)

new (insert)

ExpCre 40

ExpCre 40

47

ExpCre 64

78

ExpCre 78

UPDATE is effectively a DELETE and an INSERT.MVCC Unmasked

9 / 26

http://momjian.us/main/writings/pgsql/mvcc.pdf

MVCC Examples

Visible

Invisible

Invisible

Create−Only

Create & Expire

Visible

Visible

Internally, the creation xid is stored in the system column ’xmin’, and expire in ’xmax’.

Invisible

ExpCre 30

ExpCre 50

CreExp

30110

ExpCre 30

75

ExpCre 30

80

ExpCre 110

For simplicity, assume all othertransactions are committed.

Open Transactions: 25, 50, 75

The highest−numberedcommitted transaction: 100

Snapshot

Sequential Scan

MVCC Unmasked

10 / 26

http://momjian.us/main/writings/pgsql/mvcc.pdf

Heap Page Structure

Page Header Item Item Item

Tuple

Tuple Tuple Special

8K


11 / 26


Pg_upgrade: Restore Schema In New Cluster

1

2

3 6

5

4 7

8

9

pg_class

User Tables and Indexes

15 21 27

262014

13 19 25

241812

17 2311

10 16 22

System Tables and Indexes

clog

Old Cluster

1

2

3 6

5

4 7

8

9

pg_class


26


clog

New Cluster

pg_dumpall − −schema

17 23

24

25

27

18

10

11

12

13

14

15

16

19

20

21

22

Rapid Upgrades With Pg_Upgrade

12 / 26

http://momjian.us/main/writings/pgsql/pg_upgrade.pdf

Pg_upgrade: Copy User Heap/Index Files

1

2

3 6

5

4 7

8

9

pg_class


15 21 27

262014

13 19 25

241812

17 2311

10 16 22


clog

Old Cluster

1

2

3 6

5

4 7

8

9

pg_class


26


clog

New Cluster

1610

11

13

12

14

15

19

20

21

22

23

24

25

27

18

17

Rapid Upgrades With Pg_Upgrade

13 / 26

http://momjian.us/main/writings/pgsql/pg_upgrade.pdf

Continuous Archiving

02:0

011

:00

09:0

013

:00

WAL

WALWAL

ContinuousFile System−

Level Backup Archive (WAL)

The Magic of Hot Streaming Replication

14 / 26

http://momjian.us/main/writings/pgsql/hot_streaming_rep.pdf

Point-in-Time Recovery

WAL

WAL

17:0

017

:30

17:4

017

:55

ContinuousFile System−

Level Backup Archive (WAL)

WAL


15 / 26


Streaming Replication Setup

02:0

011

:00

09:0

013

:00

WAL

WALWAL

File System−

Level Backup Server

Standby


16 / 26


Streaming Replication in Operation

archivecommand command

Primary Standby

Network

restore

/pg_wal/pg_wal

Archive

Directory

WAL


17 / 26


Read Scaling Using Pgpool & Streaming Replication

SELECTINSERT, UPDATE,

DELETE to master

host

Slave SlaveMasterreplication

to any host

replicationstreaming

��

��

��

��

pgpool

A full copy of the data exists on every node.

PostgreSQL Replication Solutions 18 / 26

http://momjian.us/main/writings/pgsql/replication.pdf

Write Scaling Using FDW-Based Sharding

��

��

��

��

��

��

SQL Queries

PG FDW

Foreign Server Foreign Server Foreign Server

aggregates (10)joins (9.6)

sorts (9.6)aggs on partitions (11)

The Future of Postgres Sharding

19 / 26

http://momjian.us/main/writings/pgsql/sharding.pdf

Database Server Hardware Priorities

I/O

CPU

Memory

Database Hardware Selection Guidelines

20 / 26

http://momjian.us/main/writings/pgsql/hw_selection.pdf

Postgres’s Central Role

Extensions

NoSQL

Postgres

WarehouseData

Foreign DataWrappers

Window Functions

Data Paritioning

Bitmap Scans Sharding

Oracle

Twitter

MongoDB

Easy DDL

JSON

PL/R

ISN

PostGIS

Making Postgres Central in Your Data Center

21 / 26

http://momjian.us/main/writings/pgsql/central.pdf

Postgres System Tables

starelid

staattnum

staop

pg_statistic

oprleft

oprright

oprresult

oprcom

oprnegate

oprlsortop

oprrsortop

oprcode

oprrest

oprjoin

pg_operator

typrelid

typelem

typinput

typoutput

typbasetype

pg_type

prolang

prorettype

pg_proc

pg_rewrite

ev_class

datlastsysoid

pg_database

tgfoid

tgrelid

pg_trigger

inhrelid

pg_inherits

inhparent

pg_language

pg_namespacepg_depend pg_shadow

pg_aggregate

aggfinalfn

aggtransfn

aggfnoid

aggtranstype

castsource

casttarget

pg_cast

castfunc

pg_description

pg_constraint

contypid

pg_conversion

conproc

amopopr

amopclaid

pg_attribute

indexrelid

attnum

amopclaid

atttypid

indrelid

pg_attrdef

pg_group

adrelid

pg_index

adnum

pg_am

pg_amop

amgettuple

reltoastidxid

aminsert

reltoastrelid amcostestimate

amproc

ambeginscan

pg_amproc

amrescan

relfilenode

amendscan

relam

ammarkpos

reltype amrestrpos

pg_class

ambuild

opcdeftype

ambulkdelete

pg_opclass

attrelid


http://www.postgresql.org/docs/current/catalogs.html23 / 26


http://www.postgresql.org/docs/current/catalogs.html

CTEs: Mixing Modification Commands

CREATE TEMPORARY TABLE old_orders (order_id INTEGER);

WITH source (order_id) AS (DELETE FROM orders WHERE name = ’my order’ RETURNING order_id

), source2 AS (DELETE FROM items USING source WHERE source.order_id = items.order_id

)INSERT INTO old_orders SELECT order_id FROM source;

Programming the SQL Way with Common Table Expressions

24 / 26

http://momjian.us/main/writings/pgsql/cte.pdf

SSL ’Verify-Ca’ Is SecureFrom Spoofing

server.crt

X

root.crt

Database

Fake PostgreSQL

Database

Server

PostgreSQLSSL verify-ca

Invalid certificate

Server(no CA signature)Client

Database

Securing PostgreSQL From External Attack

25 / 26

http://momjian.us/main/writings/pgsql/securing.pdf

Conclusion: Release Dates and Sizes After 2000

version | reldate | months | relnotes | lines | change | % change----------+------------+--------+----------+---------+--------+----------7.0 | 2000-05-08 | 11 | | 383270 | 51992 | 157.1 | 2001-04-13 | 11 | | 410500 | 27230 | 77.2 | 2002-02-04 | 10 | 250 | 394274 | -16226 | -37.3 | 2002-11-27 | 10 | 305 | 453282 | 59008 | 147.4 | 2003-11-17 | 12 | 263 | 508523 | 55241 | 128.0 | 2005-01-19 | 14 | 230 | 654437 | 145914 | 288.1 | 2005-11-08 | 10 | 174 | 630422 | -24015 | -38.2 | 2006-12-05 | 13 | 215 | 684646 | 54224 | 88.3 | 2008-02-04 | 14 | 223 | 762697 | 78051 | 118.4 | 2009-07-01 | 17 | 314 | 939098 | 176401 | 239.0 | 2010-09-20 | 15 | 237 | 999862 | 60764 | 69.1 | 2011-09-12 | 12 | 203 | 1069547 | 69685 | 69.2 | 2012-09-10 | 12 | 238 | 1148192 | 78645 | 79.3 | 2013-09-09 | 12 | 177 | 1195627 | 47435 | 49.4 | 2014-12-18 | 15 | 211 | 1261024 | 65397 | 59.5 | 2016-01-07 | 13 | 193 | 1340005 | 78981 | 69.6 | 2016-09-29 | 8 | 214 | 1380458 | 40453 | 3

PostgreSQL: Past, Present, and Future

http://momjian.us/presentations 26 / 26

http://momjian.us/main/writings/pgsql/past_present_future.pdf

the best of bruce's postgres slides - momjianmomjian.us/main/writings/pgsql/best_of.pdf · the...

Documents