Hooks: The secret feature powering the Postgres ecosystem

• psql-hooks

  1 161 2.1 Python

  Unofficial documentation for PostgreSQL hooks

What do developers mean when they say Postgres is "extensible"? They're referring to low-level APIs which can change the core functionality of the database. In this post, we will explore a secret - meaning undocumented - feature called hooks which allow developers to not only add features to Postgres, but modify the way that queries are executed and data is parsed.

• PostgreSQL

  405 14,673 10.0 C

  Mirror of the official PostgreSQL GIT repository. Note that this is just a *mirror* - we don't work with pull requests on github. To contribute, please see https://wiki.postgresql.org/wiki/Submitting_a_Patch

  

Hooks need to be placed in the contrib Postgres source code path folder. You can check the source path present here for extensions already shipped with Postgres.

• WorkOS

  workos.com sponsored

  The modern identity platform for B2B SaaS. The APIs are flexible and easy-to-use, supporting authentication, user identity, and complex enterprise features like SSO and SCIM provisioning.

  

• TimescaleDB

  82 16,472 9.8 C

  An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.

  

The most important hook for Timescale is probably the planner_hook which deals with the query plan that Postgres produces when a user or application sends it a SQL statement. In src/planner.c we see how they use the function timescaledb_planner to modify the typical query plan to include chunks and ensure that HyperTables are queried correctly. Once the planner has completed Timescale does further transformations on the query using the post_parse_analyze_hook that they initialized at the very start of Postgres operation in src/loader/loader.c.

• supautils

  1 49 7.7 C

  PostgreSQL extension that secures a cluster on a cloud environment

  

Supabase uses ProcessUtility to jump into supautils.c , a library which checks if the role that is executing the statement is allowed to do so. This helps them match the web-based authentication on supabase.com to the database role inside Postgres and provide a very smooth authentication experience. Beyond reading the pg_stat_statements C code, their blog post and supautils.c is the best reference for implementing a moderately complex hook.

Suggest a related project