Extensions (PostGIS, uuid-ossp)

PostgreSQL has a built-in extension system for adding optional functionality — new data types, functions, operators, and index types — without modifying the core server. An extension is packaged code that you opt into per-database, and just as easily remove.

Installing and Listing Extensions

managing-extensions.sql

SQL

-- Install an extension into the current database
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Remove it
DROP EXTENSION IF EXISTS "uuid-ossp";

-- See which extensions are currently installed in this database
SELECT * FROM pg_extension;

-- See which extensions are AVAILABLE to install (bundled with this server)
SELECT * FROM pg_available_extensions;

In psql, the shortcut for listing installed extensions is \dx. Many extensions are bundled with a standard PostgreSQL installation (via the contrib package on most Linux distributions) but not enabled until you run CREATE EXTENSION; others, like PostGIS, are separate installs that add themselves to pg_available_extensions once present on the server.

Notable Extensions

Extension	What It Adds
postgis	Geospatial data types and functions — points, polygons, distance/containment queries, spatial indexes. One of the most capable open-source GIS systems in existence, not just a PostgreSQL add-on.
uuid-ossp	Functions for generating UUIDs (`uuid_generate_v4()`, etc.) — commonly used for primary keys that need to be unique across systems without a central sequence.
pgcrypto	Cryptographic functions — hashing, symmetric/asymmetric encryption, and password hashing helpers usable directly in SQL.
pg_stat_statements	Tracks execution statistics for every distinct query the server runs — call counts, total/average time, rows returned. Essential for finding slow queries in production.

postgis-example.sql

SQL

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE stores (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  location GEOGRAPHY(POINT)
);

INSERT INTO stores (name, location)
VALUES ('Downtown', ST_MakePoint(-73.9857, 40.7484));

-- Find stores within 5km of a given point
SELECT name FROM stores
WHERE ST_DWithin(location, ST_MakePoint(-73.98, 40.75)::geography, 5000);

Note

This extensibility is one of the biggest reasons PostgreSQL has such a strong ecosystem. Many databases that get talked about as though they were entirely separate products — TimescaleDB for time-series workloads is the best-known example — are actually built as PostgreSQL extensions rather than forks of the database engine. You get a specialized, purpose-built feature set while still running ordinary PostgreSQL underneath, with all of its tooling, drivers, and operational knowledge still applying.