Extensions

Snowflake for Developers/Postgres/Extensions

On this page

Extensions

Postgres

Elizabeth Christensen

What Are Extensions?

Extensions! In my estimation this is the killer feature of Postgres. Other databases give you what ships in the box. Postgres gives you an ecosystem. Extensions add new data types, functions, operators, index types, and whole new capabilities, all without leaving your database.

Think of them like plugins for your editor. The core is solid, and extensions turn Postgres into a spatial database, a vector search engine, a job scheduler, or all of those at once. This is a big part of why people call Postgres the "everything database."

An extension packages up related SQL objects (types, functions, operators, indexes) into a single installable unit. Installing one looks like this:

CREATE EXTENSION pg_stat_statements;

That's it. One command and you've added query performance stats to your database.

To see what's available in your Postgres instance:

SELECT name, default_version, comment
FROM pg_available_extensions
ORDER BY name;

How Extensions Are Made

Postgres has a formal extension packaging system that any developer can use. An extension is a directory with a few standard files:

extension_name.control: metadata such as the extension name, default version, whether it requires a schema, and any dependencies on other extensions.
extension_name--version.sql: the SQL script that creates the extension's objects (types, functions, operators, tables, and so on).
Makefile using PGXS: the Postgres Extension Building Infrastructure. PGXS is a standard makefile system that compiles C code against the correct Postgres headers, links shared libraries, and installs files into the right directories.

For extensions written in C (most of the performance-sensitive ones), the author writes against Postgres's internal APIs: the Server Programming Interface (SPI) for running queries, the Function Manager (fmgr) for defining callable functions, and planner/executor hooks for deeper integration. The build produces a .so (Linux) or .dylib (macOS) shared library that Postgres loads into its process space.

A minimal control file looks like this:

# pg_example.control
comment = 'An example extension'
default_version = '1.0'
module_pathname = '$libdir/pg_example'
relocatable = true

You can also write extensions in plain SQL or PL/pgSQL with no compiled code, which is the simpler path. C extensions get direct access to Postgres internals, though, which is how PostGIS adds its own index support and pgvector adds custom distance operators and index access methods.

The extension system isn't bolted on. It's a first-class packaging mechanism built into the server. CREATE EXTENSION reads the control file, finds the install script, and runs it inside a transaction. Upgrades follow versioned migration scripts (extension--oldver--newver.sql), so extension updates are predictable. Downgrades are a different story: most extensions don't ship downgrade scripts, so test upgrades before you run them in production.

Contrib Extensions vs. Third-Party Extensions

Extensions fall into two broad groups, and the difference tells you what you can expect to find on any given Postgres deployment.

Contrib Extensions

These live in the contrib/ directory of the Postgres source tree. The core development community develops, tests, and releases them alongside Postgres itself. If you install Postgres from a package manager, the contrib modules are usually included or available as a separate postgresql-contrib package.

Contrib extensions follow the Postgres release cycle and quality bar. They go through the same review process as core code, run in the project's regression tests, and are compatible with the major version they ship with.

Common contrib extensions:

Extension	Purpose
`pg_stat_statements`	Query performance statistics
`pg_trgm`	Trigram-based fuzzy text matching
`citext`	Case-insensitive text type
`hstore`	Key-value store type
`btree_gist` / `btree_gin`	B-tree operators for GiST/GIN indexes
`uuid-ossp`	UUID generation functions
`pgcrypto`	Cryptographic functions
`tablefunc`	Crosstab / pivot queries
`pg_buffercache`	Inspect shared buffer contents
`auto_explain`	Log execution plans automatically

A note on uuid-ossp: you often don't need it anymore. gen_random_uuid() has been built in since Postgres 13, and Postgres 18 added a built-in uuidv7().

Because they ship with Postgres, contrib extensions are the easiest to rely on. You'll find them on nearly every Postgres deployment and managed service.

Third-Party Extensions

These come from companies, open source projects, or individual contributors. They have their own release schedules and repositories, and they have to be compiled and installed separately (or provided by your hosting platform).

You get a third-party extension onto a system in one of three ways:

Install from a package manager (e.g., apt install postgresql-18-postgis-3)
Compile from source using PGXS (make && make install)
Use a managed service that ships it

Some of the big third-party extensions:

Extension	Purpose	Maintained by
PostGIS	Spatial/geographic data	PostGIS Project Steering Committee (OSGeo)
pgvector	Vector similarity search	pgvector community
TimescaleDB	Time-series optimization	Timescale
pg_cron	Job scheduling	Citus / Microsoft
pg_partman	Partition management	Community
Citus	Distributed Postgres	Microsoft
HypoPG	Hypothetical indexes	Dalibo
pg_repack	Online table repacking	Community

In practice this means contrib extensions are almost always there, and third-party ones depend on your platform. Managed Postgres services (Snowflake Postgres included) curate a supported list. You can't install arbitrary ones because the service controls the filesystem and shared library loading.

Testing Extensions with LOAD

Before you formally install an extension with CREATE EXTENSION, Postgres gives you the LOAD command for loading a shared library into your current session:

LOAD 'auto_explain';

LOAD loads the shared library (.so file) into the backend process for your current connection. It does NOT create any SQL objects. No functions, types, or views get defined. It only loads compiled code into memory.

Where this comes in handy:

Testing a new extension. Check that the library loads without errors before you do a full install.
Hook-based behavior. Some modules, like auto_explain, work by hooking into query execution. Loading the library turns those hooks on for your session.
Debugging. Load a diagnostic library temporarily without affecting other connections.

-- Load auto_explain for this session to see query plans in the log
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '100ms';
SET auto_explain.log_analyze = true;

-- Now run your queries; plans will appear in the server log
SELECT * FROM orders WHERE status = 'pending';

The limitations:

LOAD only works if the .so file exists in Postgres's $libdir directory (or a directory in dynamic_library_path).
It only affects the current backend process. Other connections don't see it.
It does NOT run any SQL install scripts, so functions and types defined by the extension won't exist. You get the compiled code but not the SQL interface.
Some extensions have to be in shared_preload_libraries so they can initialize at server start. pg_stat_statements is the common example, since it needs shared memory. For these, LOAD won't get you a working extension.

For day-to-day work you want CREATE EXTENSION. Keep LOAD around for quick validation or for turning on hook-based behavior in one session.

Extensions on Snowflake Postgres

Snowflake Postgres supports a curated set of extensions including PostGIS, pgvector, pg_partman, pg_cron, pg_stat_statements, and pg_lake. They're already installed on the server, so you just run CREATE EXTENSION to enable them. See the Snowflake Postgres extensions documentation for the full list.

On a managed service like Snowflake Postgres you don't compile or install anything. The supported extensions are already on the filesystem, and you enable them per database:

-- Enable extensions on Snowflake Postgres
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS vector;  -- pgvector
CREATE EXTENSION IF NOT EXISTS postgis;

The trade-off with any managed Postgres is that you can't bring arbitrary third-party extensions, only the curated set. That's a deliberate choice for security, stability, and supportability. The list covers the common needs: spatial (PostGIS, pgRouting, H3), vector search, partitioning, scheduling, observability, and data lake access with pg_lake.

Here are the extensions we reach for most often.

Essential Extensions

Before the bigger extensions, here are the ones that belong in nearly every production database.

pg_stat_statements: Query Analysis

If you install one extension, make it this one. It tracks execution statistics for every query that runs.

CREATE EXTENSION pg_stat_statements;

-- Find your slowest queries by total time
SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Start with total_exec_time. A query that takes 5ms but runs a million times an hour usually matters more than one slow report.

pg_cron: Job Scheduling

pg_cron lets you schedule recurring SQL jobs inside the database, no external cron daemon needed:

CREATE EXTENSION pg_cron;

-- Vacuum a table every night at 3am
SELECT cron.schedule(
    'nightly-vacuum',
    '0 3 * * *',
    'VACUUM ANALYZE orders'
);

-- Purge old logs every Sunday
SELECT cron.schedule(
    'weekly-log-cleanup',
    '0 4 * * 0',
    $$DELETE FROM app_logs WHERE created_at < now() - interval '90 days'$$
);

-- List scheduled jobs
SELECT jobid, schedule, command, nodename
FROM cron.job;

-- Remove a scheduled job
SELECT cron.unschedule('nightly-vacuum');

PostGIS: Spatial Data

PostGIS turns Postgres into a full geographic information system. It adds geometry and geography data types, hundreds of spatial functions, and spatial index support. It's the standard people measure other spatial databases against.

CREATE EXTENSION postgis;

-- Create a table with a geography column
CREATE TABLE coffee_shops (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    location GEOGRAPHY(Point, 4326)
);

-- Find shops within 5km of downtown Portland
SELECT name,
    round(ST_Distance(location, ST_Point(-122.6765, 45.5231)::geography)::numeric) AS distance_m
FROM coffee_shops
WHERE ST_DWithin(location, ST_Point(-122.6765, 45.5231)::geography, 5000)
ORDER BY distance_m;

PostGIS is the go-to for location-based apps, mapping, logistics, and delivery routing. For spatial types, indexing, and common query patterns, see the PostGIS Guide.

pgvector: AI and Embeddings

pgvector adds vector similarity search to Postgres, so you can store embeddings from AI models right next to your relational data. Most of the time you don't need a separate vector database.

CREATE EXTENSION vector;

-- Store documents with their embeddings
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    embedding VECTOR(1536)  -- dimensions match your model
);

-- Find the 5 most similar documents (cosine distance)
SELECT title, 1 - (embedding <=> $1) AS similarity
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

For index types (HNSW vs. IVFFlat), RAG patterns, and embedding workflows, see the AI in Postgres Guide.

pg_partman: Automatic Partition Management

pg_partman handles the tedious parts of table partitioning. It creates future partitions ahead of time and can drop old ones on a schedule.

CREATE EXTENSION pg_partman;

-- Create a time-based partitioned table
CREATE TABLE events (
    id BIGSERIAL,
    event_type TEXT,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Let pg_partman manage partitions automatically
SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'created_at',
    p_interval := '1 day',
    p_premake := 7  -- create partitions 7 days ahead
);

For partition strategies (range, list, hash), maintenance, and performance tuning, see the Partitioning Guide.

pg_lake: Lakehouse Integration

pg_lake is an open source extension from Snowflake that connects Postgres to the data lake. You can create Iceberg tables from Postgres and query Parquet, CSV, and JSON files in object storage:

CREATE EXTENSION pg_lake CASCADE;

-- Create an Iceberg table from Postgres
CREATE TABLE events_iceberg (
    id BIGINT,
    event_type TEXT,
    created_at TIMESTAMPTZ
) USING iceberg;

-- Query Parquet files in object storage
CREATE FOREIGN TABLE raw_events ()
SERVER pg_lake
OPTIONS (path 's3://my-bucket/raw/events/*.parquet');

SELECT count(*) FROM raw_events;

On Snowflake Postgres, pg_lake needs a STANDARD or HIGH MEMORY instance (not BURSTABLE). For setup, storage options, and query patterns, see the pg_lake Guide.

Managing Extensions

Installing and Updating

-- Install an extension
CREATE EXTENSION postgis;

-- Install a specific version
CREATE EXTENSION postgis VERSION '3.6.0';

-- Update to the latest available version
ALTER EXTENSION postgis UPDATE;

-- See installed extensions and their versions
SELECT extname, extversion
FROM pg_extension
ORDER BY extname;

Checking Available and Installed Extensions

-- What's installed?
SELECT extname, extversion FROM pg_extension;

-- What's available to install?
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
WHERE installed_version IS NULL
ORDER BY name;

Conclusion

Extensions are what make Postgres the Swiss Army knife of databases. One CREATE EXTENSION gets you spatial queries, vector search, job scheduling, partition management, or dozens of other features. The popular ones have years of production use behind them.

The ones covered here (PostGIS, pgvector, pg_cron, pg_partman, pg_stat_statements, and pg_lake) are the toolkit most applications reach for first. There are hundreds more out there once you start looking.

Related Resources

PostGIS Guide

Spatial types, functions, indexing, and common query patterns.

AI in Postgres Guide

pgvector, embedding workflows, RAG patterns, and building AI applications with Postgres.

Partitioning Guide

Partition strategies (range, list, hash), pg_partman setup, maintenance, and performance tuning.

pg_lake Guide

Connect your Postgres instance to the data lake with pg_lake and Iceberg.

PostgreSQL Documentation: Extensions

Official Postgres docs on the extension system, packaging, and building your own extensions.

Snowflake Docs: Snowflake Postgres Extensions

Full list of supported extensions for Snowflake Postgres.

Updated Oct 27, 2026

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances