Steven Miller: Enter the matrix: the four types of Postgres extensions

Before working for a Postgres company, I had never used extensions. Now, I'm part of a team working to fully automate turning on any extension. I didn't find great resources to explain the process of turning on an extension, and why it varies between different extensions. I want to share my mental model for the different types of extensions, how to know what type of extension you're working with, and how to get it turned on. Turn on an extension​ What's traditionally involved: Find the extension you want Figure out how to build it Sometimes, installation of dependencies (for example with apt-get or yum) Sometimes, installation of other extensions (goto ‘figure out how to build it’) Install your extension Sometimes, load a library Sometimes, provide extension-specific configurations Sometimes, run the CREATE EXTENSION command Building and installing extensions is well covered by other resources. In this blog, I want to focus on steps to get an extension up and running after it's installed, and how I believe that all extensions fit into four mostly-tidy categories. Terminology​ Extensions consist of SQL and / or libraries. A library simply means compiled code, for example written in C or Rust. SQL objects, let's just call it SQL, are extensions of SQL, for example new functions and data types. These are often implemented by a library, but can also be implemented in other ways, for example using a procedural language like PL/pgSQL. Hooks: A Postgres feature informally called hooks can be used to connect into Postgres' existing functionality. Hooks allow for overwriting default Postgres functionality, or calling back into an extension's code at the appropriate time. For example, one type of hook can modify Postgres start up behavior to launch a background worker, and a different type of hook can be used to redirect queries to a different table. note Sometimes extensions are instead referred to as '[...]