PostgreSQL Database Kernel Analysis Notes (this book is not very good to see, mainly is the data structure, concepts and process of text introduction)

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

1. Initialize database:./initdb--no-locale-d. /data
2. ./pg_ctl start-d. /data
3. Database command: Initdb createuser dropuser createdb dropdb pg_dump pg_restore pg_ctl vacuumdb psql
4. Psql meta command: \? \O \l \q \c \dt \d \di \i (SQL);

System Structure

2. Main system tables and their dependencies
   1. Pg_namespace (Nspname, Nspowner, Nspacl)
   2. Pg_tablespace (Spcname, Spcowner, Spclocation, Spcacl)
   3. Pg_database
   4. Pg_class
   5. Pg_type
   6. Pg_attribute
   7. Pg_index
4. System view: Pg_cursors pg_group pg_indexes pg_locks pg_roles pg_rules ...
6. Data Set Cluster
   1. Table/index: Over 1G split, filenode.1 ...
   2. If some attribute drugs store big data, then there will be associated toast tables
   3. Subdirectories and files in Pg_data: pg_version base global Pg_clog pg_tblspc ...
   4. Postgres.bki
   5. Initdb the execution of the process
   6. System database: Template1 template0 postgres
8. Process structure: Postmaster Postgres syslogger pgstat autovacuum bgwriter walwriter pgarch
   1. Postmaster
      1. Memorycontext
      2. GUC Configuration Parameters
      3. Signal Processing: Sighup_handler Pmdie Reaper (cleanup of exited sub-processes)
      4. Worker process Start
   2. Worker process
      1. Walwriter: Segment number starting from 0, cannot be reused
      2. Pgarch (Wal log archive): Call the shell command directly? K
   3. Postgres
   4. Exec_simple_query

Storage Management

1. External memory Management: Table file, free space, virtual file descriptor (VFD), Big Data
   1. 8.2 + visibility map VM idle map FSM
2. heap File: Table file + tuple not associated, {normal, temporary, Sequence, TOAST}
   1. Physical structure: Pageheaderdata linp<n> ... Freespace. tuple<n> special_space
   2. "Hot technology"
      1. each version of a tuple has a corresponding version of the index ==〉 ... Tag Delete
3. Disk Management (smgr)
   1. mdfdvec:vfd, Segno, chain
4. vfd mechanism
   1. LRU Pool (vfdcache)
5. FSM
   1. p66 fp_next_slot
   2. fsm_search max heap binary tree?
6. VM: Speed up vacuum as a hint
7. Big data:
   1. TOAST: Store variable-length data? such as varchar, need to exceed 2KB; out-of-line/compression 2 storage mechanisms
   2. LOB
8. memory management
   1. Memorycontext:allocset
   2. Cache: Syscache/relcache
   3. buffer pool
   4. IPC
9. table operations and tuple operations
   1. Synchronous Scan (with shared buffering on multiple scans)
10. vacuum mechanism
    1. Lazy: Invalid token is available
    2. full
11. RESOURCEOWNER resource tracking

I feel the description here is very confusing.

Index

1. Index mode
   1. Partial index? CREATE INDEX idx on student (name) WHERE (id>1 and id<255);
   2. An expression index? CREATE INDEX idx on student (lower (name))
2. PG_AM: Each tuple includes an access function (pg_proc.oid) provided by that index type?
3. B-tree Index
   1. Each non-right-most node: High-key
   2. Btwritestate: Record information throughout the index creation process
   3. Generates a btpagestate for each layer whose btps_next points to the parent node (? ）
   4. Fill factor: ... With (FILLFACTOR=70);
   5. Scan Index
4. Hash index
   1. 4 Kinds of pages: meta (0#) bucket overflow (elements in the bucket) bitmap (usage of the former two)
5. Gist
   1. Consistent (E,Q) Union (p) Same (E1,E2) penalty (e1,e2) picksplit (p) Compress (e) decompress (e)
   2. Gistinsertstack?
6. GIN
   1. Compare, Extractvalue, extractquery, consistent (equals like Hashtable?) ), comparepartial
7. TSearch2

Query Compilation

1. Query analysis
2. Query rewriting
3. Query planning: Query tree link list + Execution plan chain list
   1. The size, path, and cost are estimated for each intermediate relationship generated during path generation.
      1. DP, GA
      2. Basic Relationship Access Path
      3. Index Scan Path
      4. TID (The physical address of the tuple?) ）
   2. Generate an Min/max aggregation plan that can be optimized
   3. Generate a general Plan
      1. Scan: Order/Index
      2. Connections: Nested loops, hashes, merges
      3. Others: Append, Result, materialization
   4. Generate a complete plan (+ Gather/Sort)
   5. Organize the Planning tree
4. Cost estimate
5. Genetic algorithm

Query Execution

1. Non-optimized statements
2. An optimized statement
3. Scheduling node
   1. Control: Result Append bitmapand/or recursiveunion
   2. Scan: Seq Index bitmapheap bitmapindex Tid subquery Function Values Cte worktable
   3. Materialized: Material Sort Group Agg unqiue Hash setop Limit Windowagg
   4. Connection: Type (Inner left/right/full_outer Semi Anti), operation
4. Other sub-functions
   1. Tuple operations
   2. Expression evaluation
   3. Projection

transaction processing and concurrency control

1. Tblockstate
2. 2PC
3. 3 Kinds of Locks
   1. SpinLock
   2. Lwlock
   3. Regularlock
4. Lock management mechanism
5. Dead lock
   1. Wait Diagram (WFG)
6. MVCC (the explanation here seems not clear enough)
7. Log Management: Xlog/clog
   1. SLRU Buffer Pool
   2. Subtrans Log Manager?
   3. 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)