PostgreSQL Database Kernel analysisJump to: Navigation, search
Directory
- 1 system overview
- 2 architecture
- 3 Storage management
- 4 index
- 5 query compilation
- 6 query execution
- 7 transaction processing and concurrency control
- 8 database security
- 9 Appendix A develop and debug with Eclipse
|
System Overview
- Initialize database:./initdb--no-locale-d. /data
- ./pg_ctl start-d. /data
- Database command: Initdb createuser dropuser createdb dropdb pg_dump pg_restore pg_ctl vacuumdb psql
- Psql meta command: \? \O \l \q \c \dt \d \di \i (SQL);
System Structure
- Main system tables and their dependencies
- Pg_namespace (Nspname, Nspowner, Nspacl)
- Pg_tablespace (Spcname, Spcowner, Spclocation, Spcacl)
- Pg_database
- Pg_class
- Pg_type
- Pg_attribute
- Pg_index
- System view: Pg_cursors pg_group pg_indexes pg_locks pg_roles pg_rules ...
- Data Set Cluster
- Table/index: Over 1G split, filenode.1 ...
- If some attribute drugs store big data, then there will be associated toast tables
- Subdirectories and files in Pg_data: pg_version base global Pg_clog pg_tblspc ...
- Postgres.bki
- Initdb the execution of the process
- System database: Template1 template0 postgres
- Process structure: Postmaster Postgres syslogger pgstat autovacuum bgwriter walwriter pgarch
- Postmaster
- Memorycontext
- GUC Configuration Parameters
- Signal Processing: Sighup_handler Pmdie Reaper (cleanup of exited sub-processes)
- Worker process Start
- Worker process
- Walwriter: Segment number starting from 0, cannot be reused
- Pgarch (Wal log archive): Call the shell command directly? K
- Postgres
- Exec_simple_query
Storage Management
- External memory Management: Table file, free space, virtual file descriptor (VFD), Big Data
- 8.2 + visibility map VM idle map FSM
- heap File: Table file + tuple not associated, {normal, temporary, Sequence, TOAST}
- Physical structure: Pageheaderdata linp<n> ... Freespace. tuple<n> special_space
- "Hot technology"
- each version of a tuple has a corresponding version of the index ==〉 ... Tag Delete
- Disk Management (smgr)
- mdfdvec:vfd, Segno, chain
- vfd mechanism
- LRU Pool (vfdcache)
- FSM
- p66 fp_next_slot
- fsm_search max heap binary tree?
- VM: Speed up vacuum as a hint
- Big data:
- TOAST: Store variable-length data? such as varchar, need to exceed 2KB; out-of-line/compression 2 storage mechanisms
- LOB
- memory management
- Memorycontext:allocset
- Cache: Syscache/relcache
- buffer pool
- IPC
- table operations and tuple operations
- Synchronous Scan (with shared buffering on multiple scans)
- vacuum mechanism
- Lazy: Invalid token is available
- full
- RESOURCEOWNER resource tracking
I feel the description here is very confusing.
Index
- Index mode
- Partial index? CREATE INDEX idx on student (name) WHERE (id>1 and id<255);
- An expression index? CREATE INDEX idx on student (lower (name))
- PG_AM: Each tuple includes an access function (pg_proc.oid) provided by that index type?
- B-tree Index
- Each non-right-most node: High-key
- Btwritestate: Record information throughout the index creation process
- Generates a btpagestate for each layer whose btps_next points to the parent node (? )
- Fill factor: ... With (FILLFACTOR=70);
- Scan Index
- Hash index
- 4 Kinds of pages: meta (0#) bucket overflow (elements in the bucket) bitmap (usage of the former two)
- Gist
- Consistent (E,Q) Union (p) Same (E1,E2) penalty (e1,e2) picksplit (p) Compress (e) decompress (e)
- Gistinsertstack?
- GIN
- Compare, Extractvalue, extractquery, consistent (equals like Hashtable?) ), comparepartial
- TSearch2
Query Compilation
- Query analysis
- Query rewriting
- Query planning: Query tree link list + Execution plan chain list
- The size, path, and cost are estimated for each intermediate relationship generated during path generation.
- DP, GA
- Basic Relationship Access Path
- Index Scan Path
- TID (The physical address of the tuple?) )
- Generate an Min/max aggregation plan that can be optimized
- Generate a general Plan
- Scan: Order/Index
- Connections: Nested loops, hashes, merges
- Others: Append, Result, materialization
- Generate a complete plan (+ Gather/Sort)
- Organize the Planning tree
- Cost estimate
- Genetic algorithm
Query Execution
- Non-optimized statements
- An optimized statement
- Scheduling node
- Control: Result Append bitmapand/or recursiveunion
- Scan: Seq Index bitmapheap bitmapindex Tid subquery Function Values Cte worktable
- Materialized: Material Sort Group Agg unqiue Hash setop Limit Windowagg
- Connection: Type (Inner left/right/full_outer Semi Anti), operation
- Other sub-functions
- Tuple operations
- Expression evaluation
- Projection
transaction processing and concurrency control
- Tblockstate
- 2PC
- 3 Kinds of Locks
- SpinLock
- Lwlock
- Regularlock
- Lock management mechanism
- Dead lock
- Wait Diagram (WFG)
- MVCC (the explanation here seems not clear enough)
- Log Management: Xlog/clog
- SLRU Buffer Pool
- Subtrans Log Manager?
- Multixact Log Manager: Record the combined transaction ID?
Database SecurityAppendix A development and debugging with Eclipse
PostgreSQL Database Kernel Analysis Notes (this book is not very good to see, mainly is the data structure, concepts and process of text introduction)