PostgreSQL and extensions

​

Goal

​

Prerequisites

• A StackShift account
• A PostgreSQL client or an app with a PostgreSQL driver

​

Workflow

​

Versions

• You can choose PostgreSQL 18, 17, 16 or 15.
• PostgreSQL 14 is no longer offered for new databases because it stops getting security fixes on 12 November 2026. Choose 16 or newer.
• Every new database has data checksums turned on, so silent disk corruption is caught instead of spreading.
• Sorting rules (collation): 17 and 18 use the built-in C.UTF-8 collation, which never changes under you when the operating system updates. 15 and 16 use ICU en-US. If you need language-aware sorting on 17 or 18, set it per column, for example name text COLLATE "en-US-x-icu".

​

Available extensions

• vector (pgvector 0.8.7): store embeddings and search by similarity, for AI search, recommendations and RAG.
• postgis (PostGIS 3.6.4): maps, locations and geographic queries. postgis_raster and postgis_topology are separate extensions.
• pg_cron (1.6.8): run SQL on a schedule inside the database.
• pg_partman (5.5.0): create and drop time- or ID-based table partitions automatically.
• pg_repack (1.5.3): remove table and index bloat without a long lock.
• hypopg (1.4.3): test whether an index would help before you build it.
• Built-in extensions: pg_stat_statements, pg_trgm, pgcrypto, uuid-ossp, citext, hstore, ltree, intarray, btree_gin, btree_gist, unaccent, fuzzystrmatch, cube, earthdistance, tablefunc, pg_buffercache, pgstattuple, pg_prewarm, pg_walinspect, postgres_fdw, dblink and amcheck.
• pgaudit is not available on Starter databases yet.

​

Set up pgvector

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  content text NOT NULL,
  embedding vector(1536) NOT NULL
);

CREATE INDEX documents_embedding_idx
  ON documents USING hnsw (embedding vector_cosine_ops);

SELECT id, content
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

​

Schedule jobs with pg_cron

CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule('nightly-cleanup', '0 3 * * *',
  $$DELETE FROM sessions WHERE expires_at < now()$$);

SELECT jobid, jobname, schedule FROM cron.job;

​

Partitions with pg_partman

CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

​

Slow queries

• Statements that take longer than 1 second are written to the database logs.
• For statements longer than 5 seconds, the logs also include the query plan, so you can see why it was slow.
• pg_stat_statements is loaded, so SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; shows your heaviest queries.

​

Existing databases

​

Expected result

​

Common failures

​

Related guides