9. Internals

This part contains assorted information that might be of use to PostgreSQL developers.

• 9.1. Overview of PostgreSQL Internals
  • 9.1.1. The Path of a Query
  • 9.1.2. How Connections Are Established
  • 9.1.3. The Parser Stage
    • 9.1.3.1. Parser
    • 9.1.3.2. Transformation Process
  • 9.1.4. The PostgreSQL Rule System
  • 9.1.5. Planner/Optimizer
    • 9.1.5.1. Generating Possible Plans
  • 9.1.6. Executor
• 9.2. System Catalogs
  • 9.2.1. Overview
  • 9.2.2. pg_aggregate
  • 9.2.3. pg_am
  • 9.2.4. pg_amop
  • 9.2.5. pg_amproc
  • 9.2.6. pg_attrdef
  • 9.2.7. pg_attribute
  • 9.2.8. pg_authid
  • 9.2.9. pg_auth_members
  • 9.2.10. pg_cast
  • 9.2.11. pg_class
  • 9.2.12. pg_collation
  • 9.2.13. pg_constraint
  • 9.2.14. pg_conversion
  • 9.2.15. pg_database
  • 9.2.16. pg_db_role_setting
  • 9.2.17. pg_default_acl
  • 9.2.18. pg_depend
  • 9.2.19. pg_description
  • 9.2.20. pg_enum
  • 9.2.21. pg_event_trigger
  • 9.2.22. pg_extension
  • 9.2.23. pg_foreign_data_wrapper
  • 9.2.24. pg_foreign_server
  • 9.2.25. pg_foreign_table
  • 9.2.26. pg_index
  • 9.2.27. pg_inherits
  • 9.2.28. pg_init_privs
  • 9.2.29. pg_language
  • 9.2.30. pg_largeobject
  • 9.2.31. pg_largeobject_metadata
  • 9.2.32. pg_namespace
  • 9.2.33. pg_opclass
  • 9.2.34. pg_operator
  • 9.2.35. pg_opfamily
  • 9.2.36. pg_parameter_acl
  • 9.2.37. pg_partitioned_table
  • 9.2.38. pg_policy
  • 9.2.39. pg_proc
  • 9.2.40. pg_publication
  • 9.2.41. pg_publication_namespace
  • 9.2.42. pg_publication_rel
  • 9.2.43. pg_range
  • 9.2.44. pg_replication_origin
  • 9.2.45. pg_rewrite
  • 9.2.46. pg_seclabel
  • 9.2.47. pg_sequence
  • 9.2.48. pg_shdepend
  • 9.2.49. pg_shdescription
  • 9.2.50. pg_shseclabel
  • 9.2.51. pg_statistic
  • 9.2.52. pg_statistic_ext
  • 9.2.53. pg_statistic_ext_data
  • 9.2.54. pg_subscription
  • 9.2.55. pg_subscription_rel
  • 9.2.56. pg_tablespace
  • 9.2.57. pg_transform
  • 9.2.58. pg_trigger
  • 9.2.59. pg_ts_config
  • 9.2.60. pg_ts_config_map
  • 9.2.61. pg_ts_dict
  • 9.2.62. pg_ts_parser
  • 9.2.63. pg_ts_template
  • 9.2.64. pg_type
  • 9.2.65. pg_user_mapping
• 9.3. System Views
  • 9.3.1. Overview
  • 9.3.2. pg_available_extensions
  • 9.3.3. pg_available_extension_versions
  • 9.3.4. pg_backend_memory_contexts
  • 9.3.5. pg_config
  • 9.3.6. pg_cursors
  • 9.3.7. pg_file_settings
  • 9.3.8. pg_group
  • 9.3.9. pg_hba_file_rules
  • 9.3.10. pg_ident_file_mappings
  • 9.3.11. pg_indexes
  • 9.3.12. pg_locks
  • 9.3.13. pg_matviews
  • 9.3.14. pg_policies
  • 9.3.15. pg_prepared_statements
  • 9.3.16. pg_prepared_xacts
  • 9.3.17. pg_publication_tables
  • 9.3.18. pg_replication_origin_status
  • 9.3.19. pg_replication_slots
  • 9.3.20. pg_roles
  • 9.3.21. pg_rules
  • 9.3.22. pg_seclabels
  • 9.3.23. pg_sequences
  • 9.3.24. pg_settings
  • 9.3.25. pg_shadow
  • 9.3.26. pg_shmem_allocations
  • 9.3.27. pg_stats
  • 9.3.28. pg_stats_ext
  • 9.3.29. pg_stats_ext_exprs
  • 9.3.30. pg_tables
  • 9.3.31. pg_timezone_abbrevs
  • 9.3.32. pg_timezone_names
  • 9.3.33. pg_user
  • 9.3.34. pg_user_mappings
  • 9.3.35. pg_views
• 9.4. Frontend/Backend Protocol
  • 9.4.1. Overview
    • 9.4.1.1. Messaging Overview
    • 9.4.1.2. Extended Query Overview
    • 9.4.1.3. Formats and Format Codes
  • 9.4.2. Message Flow
    • 9.4.2.1. Start-up
    • 9.4.2.2. Simple Query
    • 9.4.2.3. Extended Query
    • 9.4.2.4. Pipelining
    • 9.4.2.5. Function Call
    • 9.4.2.6. COPY Operations
    • 9.4.2.7. Asynchronous Operations
    • 9.4.2.8. Canceling Requests in Progress
    • 9.4.2.9. Termination
    • 9.4.2.10. SSL Session Encryption
    • 9.4.2.11. GSSAPI Session Encryption
  • 9.4.3. SASL Authentication
    • 9.4.3.1. SCRAM-SHA-256 Authentication
  • 9.4.4. Streaming Replication Protocol
  • 9.4.5. Logical Streaming Replication Protocol
    • 9.4.5.1. Logical Streaming Replication Parameters
    • 9.4.5.2. Logical Replication Protocol Messages
    • 9.4.5.3. Logical Replication Protocol Message Flow
  • 9.4.6. Message Data Types
  • 9.4.7. Message Formats
  • 9.4.8. Error and Notice Message Fields
  • 9.4.9. Logical Replication Message Formats
  • 9.4.10. Summary of Changes since Protocol 2.0
• 9.5. PostgreSQL Coding Conventions
  • 9.5.1. Formatting
  • 9.5.2. Reporting Errors Within the Server
  • 9.5.3. Error Message Style Guide
    • 9.5.3.1. Unable
    • 9.5.3.2. Bad
    • 9.5.3.3. Illegal
    • 9.5.3.4. Unknown
    • 9.5.3.5. Find vs. Exists
    • 9.5.3.6. May vs. Can vs. Might
    • 9.5.3.7. Contractions
    • 9.5.3.8. Non-negative
  • 9.5.4. Miscellaneous Coding Conventions
• 9.6. Native Language Support
  • 9.6.1. For the Translator
    • 9.6.1.1. Requirements
    • 9.6.1.2. Concepts
    • 9.6.1.3. Creating and Maintaining Message Catalogs
    • 9.6.1.4. Editing the PO Files
  • 9.6.2. For the Programmer
    • 9.6.2.1. Mechanics
    • 9.6.2.2. Message-Writing Guidelines
• 9.7. Writing a Procedural Language Handler
• 9.8. Writing a Foreign Data Wrapper
  • 9.8.1. Foreign Data Wrapper Functions
  • 9.8.2. Foreign Data Wrapper Callback Routines
    • 9.8.2.1. FDW Routines for Scanning Foreign Tables
    • 9.8.2.2. FDW Routines for Scanning Foreign Joins
    • 9.8.2.3. FDW Routines for Planning Post-Scan/Join Processing
    • 9.8.2.4. FDW Routines for Updating Foreign Tables
    • 9.8.2.5. FDW Routines for TRUNCATE
    • 9.8.2.6. FDW Routines for Row Locking
    • 9.8.2.7. FDW Routines for EXPLAIN
    • 9.8.2.8. FDW Routines for ANALYZE
    • 9.8.2.9. FDW Routines for IMPORT FOREIGN SCHEMA
    • 9.8.2.10. FDW Routines for Parallel Execution
    • 9.8.2.11. FDW Routines for Asynchronous Execution
    • 9.8.2.12. FDW Routines for Reparameterization of Paths
  • 9.8.3. Foreign Data Wrapper Helper Functions
  • 9.8.4. Foreign Data Wrapper Query Planning
  • 9.8.5. Row Locking in Foreign Data Wrappers
• 9.9. Writing a Table Sampling Method
  • 9.9.1. Sampling Method Support Functions
• 9.10. Writing a Custom Scan Provider
  • 9.10.1. Creating Custom Scan Paths
    • 9.10.1.1. Custom Scan Path Callbacks
  • 9.10.2. Creating Custom Scan Plans
    • 9.10.2.1. Custom Scan Plan Callbacks
  • 9.10.3. Executing Custom Scans
    • 9.10.3.1. Custom Scan Execution Callbacks
• 9.11. Genetic Query Optimizer
  • 9.11.1. Query Handling as a Complex Optimization Problem
  • 9.11.2. Genetic Algorithms
  • 9.11.3. Genetic Query Optimization (GEQO) in PostgreSQL
    • 9.11.3.1. Generating Possible Plans with GEQO
  • 9.11.4. Further Reading
• 9.12. Table Access Method Interface Definition
• 9.13. Index Access Method Interface Definition
  • 9.13.1. Basic API Structure for Indexes
  • 9.13.2. Index Access Method Functions
  • 9.13.3. Index Scanning
  • 9.13.4. Index Locking Considerations
  • 9.13.5. Index Uniqueness Checks
  • 9.13.6. Index Cost Estimation Functions
• 9.14. Generic WAL Records
• 9.15. Custom WAL Resource Managers
• 9.16. B-Tree Indexes
  • 9.16.1. Introduction
  • 9.16.2. Behavior of B-Tree Operator Classes
  • 9.16.3. B-Tree Support Functions
  • 9.16.4. Implementation
    • 9.16.4.1. B-Tree Structure
    • 9.16.4.2. Bottom-up Index Deletion
    • 9.16.4.3. Deduplication
• 9.17. GiST Indexes
  • 9.17.1. Introduction
  • 9.17.2. Built-in Operator Classes
  • 9.17.3. Extensibility
  • 9.17.4. Implementation
    • 9.17.4.1. GiST Index Build Methods
  • 9.17.5. Examples
• 9.18. SP-GiST Indexes
  • 9.18.1. Introduction
  • 9.18.2. Built-in Operator Classes
  • 9.18.3. Extensibility
  • 9.18.4. Implementation
    • 9.18.4.1. SP-GiST Limits
    • 9.18.4.2. SP-GiST Without Node Labels
    • 9.18.4.3. All-the-Same Inner Tuples
  • 9.18.5. Examples
• 9.19. GIN Indexes
  • 9.19.1. Introduction
  • 9.19.2. Built-in Operator Classes
  • 9.19.3. Extensibility
  • 9.19.4. Implementation
    • 9.19.4.1. GIN Fast Update Technique
    • 9.19.4.2. Partial Match Algorithm
  • 9.19.5. GIN Tips and Tricks
  • 9.19.6. Limitations
  • 9.19.7. Examples
• 9.20. BRIN Indexes
  • 9.20.1. Introduction
    • 9.20.1.1. Index Maintenance
  • 9.20.2. Built-in Operator Classes
    • 9.20.2.1. Operator Class Parameters
  • 9.20.3. Extensibility
• 9.21. Hash Indexes
  • 9.21.1. Overview
  • 9.21.2. Implementation
• 9.22. Database Physical Storage
  • 9.22.1. Database File Layout
  • 9.22.2. TOAST
    • 9.22.2.1. Out-of-Line, On-Disk TOAST Storage
    • 9.22.2.2. Out-of-Line, In-Memory TOAST Storage
  • 9.22.3. Free Space Map
  • 9.22.4. Visibility Map
  • 9.22.5. The Initialization Fork
  • 9.22.6. Database Page Layout
    • 9.22.6.1. Table Row Layout
  • 9.22.7. Heap-Only Tuples (HOT)
• 9.23. System Catalog Declarations and Initial Contents
  • 9.23.1. System Catalog Declaration Rules
  • 9.23.2. System Catalog Initial Data
    • 9.23.2.1. Data File Format
    • 9.23.2.2. OID Assignment
    • 9.23.2.3. OID Reference Lookup
    • 9.23.2.4. Automatic Creation of Array Types
    • 9.23.2.5. Recipes for Editing Data Files
  • 9.23.3. BKI File Format
  • 9.23.4. BKI Commands
  • 9.23.5. Structure of the Bootstrap BKI File
  • 9.23.6. BKI Example
• 9.24. How the Planner Uses Statistics
  • 9.24.1. Row Estimation Examples
  • 9.24.2. Multivariate Statistics Examples
    • 9.24.2.1. Functional Dependencies
    • 9.24.2.2. Multivariate N-Distinct Counts
    • 9.24.2.3. MCV Lists
  • 9.24.3. Planner Statistics and Security
• 9.25. Backup Manifest Format
  • 9.25.1. Backup Manifest Top-level Object
  • 9.25.2. Backup Manifest File Object
  • 9.25.3. Backup Manifest WAL Range Object