PostgreSQL Extensibility

• PostgreSQL name originates from INGRES project ("Post-Ingres"). PostgreSQL builts on the learnings from that earlier INGRES project rather than reusing its codebase.
• Designed by Michael Stonebraker and Lawrence Rowe in 1986 with extensibility was a foundational design goal from the start.
• Commonly known today as Postgres or PostgreSQL.

• Unlike a rigid relational database, Postgres was designed so that user-defined data types, operators, storage formats, and index access methods could all be extended.
• Extensions are written in C and registered with Postgres through callback functions.
• Core database mechanics do not hard-code data types or behavior. Instead, everything from syntax checking to query planning is driven by system catalogs.

Catalogs are regular tables inside the Postgres database that store metadata.

• pg_type: defines every data type (base and composite types). Stores the type name, an input function, and an output function.
  • Input function: converts a text string into the internal binary representation for storage.
  • Output function: converts the internal binary representation back into a user-presentable string.
• pg_proc: stores function definitions, including custom function names used by extensions.
• pg_operator: stores operator definitions.
• pg_am: stores access methods, defining how indexes and storage mechanisms work.
• pg_class: represents relations (tables, indexes, views, etc.), not data types.
• pg_attribute: stores schema information about a table's columns.

These catalogs together are the fundamental input that the query planner and database engine use to execute and route queries. For example, custom data types or extensions have function names in pg_type/pg_proc, and the query planner routes execution to the appropriate function.

• Extension functions are loaded as dynamic shared libraries (.so files).
• Workflow to build an extension:
  1. Compile the extension against the current Postgres version's headers to produce a shared object (.so) file.
  2. Write SQL to define the data types and external functions that map to the compiled shared library.
  3. Write a control file that configures basic properties of the extension, such as version and schema dependencies.
• The first time a query requires the extension, Postgres loads the .so file and, if present, runs its init function.
• The init function can allocate memory, create background workers, and register callbacks, hooking the extension into the Postgres system so execution is routed through it.

• Prior to Postgres version 12, physical storage was strictly the traditional heap: tuples organized into unordered blocks, with updates handled via MVCC (out-of-place updates creating new tuple versions).
• The Table Access Method API exposes a set of callback routines that can be implemented to define how a table is physically stored and accessed.
• Example alternative storage engines enabled by this API:
  • zheap: updates rows in place instead of creating new versions on every update, useful for high-write transactional environments.
  • Columnar storage.
  • In-memory storage.

• Similar to the table access method, indexing methods can be extended by implementing the callbacks in the index access method routine and registering a custom index access method.
• This extension point enables index types such as:
  • GiST, used for geographic/spatial data types.

  • GIN, used for text search and array/document data.

    postgres=# select * from pg_am;
    
     oid  | amname |      amhandler       | amtype
    ------+--------+----------------------+--------
        2 | heap   | heap_tableam_handler | t
      403 | btree  | bthandler            | i
      405 | hash   | hashhandler          | i
      783 | gist   | gisthandler          | i
     2742 | gin    | ginhandler           | i
     4000 | spgist | spghandler           | i
     3580 | brin   | brinhandler          | i
    (7 rows)
    

• Postgres allows procedural languages to be embedded inside the database so that code can run near the data, instead of relying only on plain SQL.
• Examples: PL/pgSQL, PL/Python, PL/Perl.
• To support a procedural language, a language handler must be defined that can:
  • Execute an inline anonymous code block (a DO block).
  • Optionally compile the source code and cache the compiled result.
  • Interpret and execute the source code with its input arguments and return results.
  • Validate the source code for syntax errors, given the source code and the input argument types.

• Postgres allows specifying callback functions that get called at different points during query execution.
• Hooks exist for various stages, such as:
  • Planner hook: fires when query planning starts.
  • Executor hooks: fire at different stages of query execution (start, run, finish, end).
  • ProcessUtility hook: fires for utility statements.
• Examples of extensions using hooks:
  • TimescaleDB intercepts the query planning phase using the planner hook to rewrite execution trees and optimize partition lookups.
  • Security modules hook into the ProcessUtility hook to intercept statements like ALTER TABLE, VACUUM, and GRANT, and apply custom authorization controls.