Extensions

Snowflake for Developers/Postgres/Extensions

On this page

Extensions

Postgres

Elizabeth Christensen

What Are Extensions?

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

Think of extensions like plugins for your favorite editor. The core is solid, but extensions turn Postgres into a spatial database, a vector search engine, a time-series store, a job scheduler, or all of the above at once. This is what makes Postgres the "everything database."

An extension packages up related SQL objects — types, functions, operators, indexes — into a single installable unit. Installing one is as simple as:

CREATE EXTENSION pg_stat_statements;

That's it. One command and you've added query performance analytics 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. At its core, an extension is a directory containing a few standard files:

extension_name.control — metadata: 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, etc.).
Makefile using PGXS — the Postgres Extension Building Infrastructure. PGXS is a standard makefile system that handles compiling C code against the correct Postgres headers, linking shared libraries, and installing files into the right directories.

For extensions written in C (the majority of performance-critical 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 node/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 extension control file looks like this:

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

Extensions can also be written in pure SQL/PL-pgSQL (no compiled code needed), which is the simpler path. But C extensions get direct access to Postgres internals, which is how PostGIS can add entirely new index access methods and pgvector can implement custom distance operators.

The key insight: Postgres's 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 executes it inside a transaction. Upgrading follows versioned migration scripts (extension--oldver--newver.sql), making extension updates predictable and reversible.

Contrib Extensions vs. Third-Party Extensions

Extensions fall into two broad categories, and understanding the distinction matters for knowing what you'll find available in any given Postgres deployment.

Contrib Extensions

These live in the contrib/ directory of the PostgreSQL source tree. They're developed, tested, and released alongside PostgreSQL itself by the core development community. When you install PostgreSQL from a package manager, the contrib modules are typically either included automatically or available as a separate postgresql-contrib package.

Contrib extensions follow PostgreSQL's own release cycle and quality standards. They go through the same peer review process as core code, are covered by the project's regression test suite, and are guaranteed to be compatible with the major version they ship with.

Common contrib extensions include:

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

Because they ship with PostgreSQL, contrib extensions are the easiest to rely on — they're available on virtually every Postgres deployment and managed service.

Third-Party Extensions

These are developed independently by companies, open-source projects, or individual contributors. They have their own release schedules, their own repositories, and they must be compiled and installed separately (or made available by your hosting provider).

Third-party extensions go through a different path to get onto your system. You either:

Install from a package manager (e.g., apt install postgresql-16-postgis-3)
Compile from source using PGXS (make && make install)
Use a managed service that pre-installs them

The heavy-hitter third-party extensions include:

Extension	Purpose	Maintained by
PostGIS	Spatial/geographic data	OSGeo / Crunchy Data
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

The distinction matters practically: contrib extensions are always there, third-party ones depend on what your platform supports. Managed Postgres services (including Snowflake Postgres) curate a supported list of third-party extensions — 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 provides the LOAD command for temporarily loading a shared library into your current session:

LOAD 'pg_stat_statements';

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's purely loading compiled code into memory.

This is useful in a few scenarios:

Testing a new extension — verify the library loads without errors before committing to a full install.
Hooks and background behavior — some extensions (like auto_explain or pg_stat_statements) work primarily through hooks that intercept query execution. Loading the library activates those hooks 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';

There are important limitations to understand:

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/types defined by the extension won't exist. You'll have the compiled code but not the SQL interface.
Some extensions require shared_preload_libraries in postgresql.conf to function correctly (they need to initialize at server start). For these, LOAD may load the library but the extension won't fully work.

For most day-to-day work, CREATE EXTENSION is what you want. But LOAD is a handy tool for quick validation or activating hook-based behavior in a single session.

Extensions on Snowflake Postgres

aside positive Snowflake Postgres supports a curated set of extensions including PostGIS, pgvector, pg_partman, pg_cron, and pg_stat_statements. Extensions are pre-installed — just run CREATE EXTENSION to enable them. See the Snowflake Postgres extensions documentation for the full list of supported extensions and version details.

On a managed service like Snowflake Postgres, you don't need to compile or install extensions yourself — the supported extensions are already present on the filesystem. You simply enable them:

-- 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 is available. This is a deliberate choice for security, stability, and supportability. The extensions Snowflake Postgres includes cover the most common use cases: spatial data, vector search, partitioning, scheduling, and observability.

Let's look at the most commonly used extensions in detail.

Essential Extensions

Before we dive into the flagship extensions, here are the ones that belong in nearly every production database:

pg_stat_statements — Query Analysis

This is the single most important extension for understanding your database's performance. 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;

pg_cron — Job Scheduling

pg_cron lets you schedule recurring SQL jobs directly in 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 transforms Postgres into a full-featured geographic information system. It adds geometry and geography data types, hundreds of spatial functions, and specialized indexes — making Postgres the gold standard for spatial data.

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 a full walkthrough of spatial types, indexing strategies, and common query patterns, see the PostGIS Guide.

pgvector: AI and Embeddings

pgvector brings vector similarity search into Postgres — store embeddings from AI models right alongside your relational data. No separate vector database needed.

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 the full story — 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 work of creating and maintaining table partitions. It auto-creates future partitions and can drop old ones on 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 operations, and performance tuning, see the Partitioning Guide.

pg_lake: Lakehouse Integration

pg_lake bridges Postgres and the data lakehouse, letting you query Iceberg tables directly from within your database:

CREATE EXTENSION pg_lake;

-- Query data lake tables from within Postgres
SELECT * FROM iceberg_scan('s3://my-bucket/warehouse/events');

For setup, configuration, and advanced 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.4.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. With a single CREATE EXTENSION command you can add spatial intelligence, vector search, job scheduling, partition management, or dozens of other capabilities. The ecosystem keeps growing, and these extensions have production-hardened track records across thousands of deployments.

The extensions covered here — PostGIS for spatial, pgvector for AI, pg_cron for scheduling, pg_partman for partitions, and pg_stat_statements for observability — represent the core toolkit that modern applications reach for most often. But there are hundreds more to explore.

Related Resources

PostGIS Guide

Full walkthrough of spatial types, functions, indexing, and common query patterns.

AI in Postgres Guide

Deep dive into pgvector, embedding workflows, RAG patterns, and building AI-powered 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 lakehouse with pg_lake and Iceberg integration.

PostgreSQL Documentation — Extensions

Official PostgreSQL docs on the extension system, packaging, and how to create your own extensions.

Snowflake Docs — Snowflake Postgres Extensions

Full list of supported extensions and version details for Snowflake Postgres.

Updated 2026-07-08

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