Extensions: PostGIS, pgvector & More

One reason PostgreSQL is so popular is that you can add new data types, functions, index types and even background workers without changing the server itself. These add-ons are called extensions. Some ship with PostgreSQL, and others, such as PostGIS for maps and pgvector for AI similarity search, are separate projects that turn PostgreSQL into a specialised database.

In this lesson you will install and manage extensions, and meet the ones you are most likely to use: pg_trgm, pgcrypto, pg_stat_statements, PostGIS and pgvector.

Installing an extension

An extension has two steps. First its files must be on the server (they come with PostgreSQL, a package such as postgresql-18-postgis-3, or a Docker image that includes them). Then you enable it in each database that needs it:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE EXTENSION

To see what your server can install, query SELECT name, default_version, installed_version FROM pg_available_extensions;; the versions depend on your PostgreSQL release. \dx in psql lists the extensions installed in the current database, ALTER EXTENSION name UPDATE upgrades one after a server upgrade, and DROP EXTENSION removes it. Creating most extensions needs a superuser or the database owner; managed cloud databases offer an approved list.

Even a fresh database has one extension, the PL/pgSQL language you used in PL/pgSQL Functions & Procedures. That is the only extension available in the browser editor:

SELECT extname, extversion FROM pg_extension ORDER BY extname;

extname | extversion
--------+-----------
plpgsql | 1.0
(1 row)

Useful extensions that ship with PostgreSQL

These contrib modules come with the standard packages:

pg_trgm makes fuzzy search easy. With a trigram index, the query below finds "Mechanical Keyboard" even when a user types "keybord":

CREATE EXTENSION pg_trgm;
CREATE INDEX products_name_trgm ON products USING gin (product_name gin_trgm_ops);

SELECT product_name, round(similarity(product_name, 'keybord')::numeric, 2) AS score
FROM products
WHERE product_name % 'keybord' OR product_name ILIKE '%keybord%'
ORDER BY score DESC;

pg_stat_statements must also be loaded at server start: add it to shared_preload_libraries in postgresql.conf, restart, then run CREATE EXTENSION pg_stat_statements; and query the pg_stat_statements view ordered by total_exec_time.

PostGIS: maps and locations

PostGIS adds geographic types and hundreds of spatial functions, so PostgreSQL can answer questions like "which stores are within 10 km of this customer?". It is a separate project, installed as an extra package. Locations are usually stored as the geography type with longitude first, then latitude:

CREATE EXTENSION postgis;

CREATE TABLE stores (
  store_id int PRIMARY KEY,
  city text,
  location geography(Point, 4326)
);

INSERT INTO stores VALUES
  (1, 'Rohtak', ST_MakePoint(76.61, 28.90)::geography),
  (2, 'Delhi', ST_MakePoint(77.21, 28.61)::geography),
  (3, 'Jaipur', ST_MakePoint(75.79, 26.91)::geography);

CREATE INDEX stores_location_gist ON stores USING gist (location);

SELECT city, round(ST_Distance(location, ST_MakePoint(77.03, 28.46)::geography) / 1000) AS km
FROM stores
WHERE ST_DWithin(location, ST_MakePoint(77.03, 28.46)::geography, 100000)
ORDER BY km;

4326 identifies the WGS 84 coordinate system used by GPS. ST_Distance on geography returns metres, and ST_DWithin keeps stores within 100,000 metres (100 km) of a point in Gurugram, using the GiST index. Delivery apps, property sites and logistics companies build on exactly these functions.

pgvector: similarity search for AI

pgvector adds a vector type for embeddings: lists of numbers that AI models produce to describe the meaning of text or images. Similar meanings give vectors that are close together, so "find the nearest vectors" becomes "find similar products" or "find the paragraphs that answer this question", the core of retrieval-augmented generation (RAG). The tiny 3-number vectors below stand in for the hundreds or thousands of numbers a real model returns:

CREATE EXTENSION vector;

CREATE TABLE product_embeddings (
  product_name text,
  embedding vector(3)
);

INSERT INTO product_embeddings VALUES
  ('Wireless Mouse', '[0.9, 0.1, 0.0]'),
  ('Mechanical Keyboard', '[0.8, 0.2, 0.1]'),
  ('Bluetooth Speaker', '[0.1, 0.9, 0.3]');

SELECT product_name
FROM product_embeddings
ORDER BY embedding <-> '[0.9, 0.12, 0.02]'
LIMIT 2;

CREATE EXTENSION
CREATE TABLE
INSERT 0 3
    product_name
---------------------
 Wireless Mouse
 Mechanical Keyboard
(2 rows)

<-> is Euclidean (L2) distance, <=> is cosine distance and <#> is negative inner product. For large tables, pgvector provides approximate nearest-neighbour indexes, hnsw and ivfflat, for example CREATE INDEX ON product_embeddings USING hnsw (embedding vector_cosine_ops);. Keeping embeddings next to your normal data means one query can combine "similar to this" with ordinary filters such as price and stock.

Common mistakes

• Running CREATE EXTENSION before the package is installed. The error says the control file is missing. Install the OS package or use an image that includes it.
• Enabling an extension in the wrong database. Extensions are per database; enable them where your tables are.
• Forgetting shared_preload_libraries. Some extensions, such as pg_stat_statements, need it and a restart.
• Swapping latitude and longitude. PostGIS points are (longitude, latitude).

Key points

• Extensions add types, functions, operators and index types; enable one with CREATE EXTENSION in each database.
• pg_available_extensions shows what can be installed; \dx and pg_extension show what is installed.
• Contrib extensions such as pg_trgm, pgcrypto, pg_stat_statements and citext ship with PostgreSQL.
• PostGIS adds geographic types and functions such as ST_Distance and ST_DWithin, indexed with GiST.
• pgvector adds a vector type, distance operators and HNSW/IVFFlat indexes for AI similarity search.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE staff_logins (username text PRIMARY KEY, password_hash text NOT NULL);

INSERT INTO staff_logins VALUES ('neha', crypt('Correct-Horse-42', gen_salt('bf')));

SELECT username,
       password_hash = crypt('Correct-Horse-42', password_hash) AS password_ok
FROM staff_logins
WHERE username = 'neha';