> ## Documentation Index
> Fetch the complete documentation index at: https://docs.stackshift.cloud/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL and extensions

> PostgreSQL 15 to 18 with pgvector, PostGIS, pg_cron and more, ready to turn on with one SQL statement.

> [!TIP]
>
> **Live.** This area is documented as current, user-reliable behavior.

## Goal

Pick a PostgreSQL version, turn on the extensions your app needs, and know what changes when your existing database moves to the new image.

## Prerequisites

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

## Workflow

**Step 1**

Create a PostgreSQL database and choose version 18, 17, 16 or 15. Pick 17 or 18 for new apps unless your framework needs an older one.

**Step 2**

Connect with the connection string from the database page.

**Step 3**

Run `CREATE EXTENSION` for each extension you need, for example `CREATE EXTENSION vector;` for embeddings search.

**Step 4**

Check it worked with `SELECT extname, extversion FROM pg_extension;`.

## 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

Every extension below is already installed. Your database user can turn one on with `CREATE EXTENSION <name>;`; there is nothing to request or wait for. The list is the same on every version.

* `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

Turn on the extension, add a `vector` column sized to your embedding model, and add an HNSW index for fast nearest-neighbour search. Use the same distance operator in the index and in your queries: `<=>` for cosine distance, `<->` for Euclidean distance, `<#>` for inner product.

```sql Embeddings table with a cosine index theme={null}
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

pg\_cron is already loaded. Create the extension in the database named in your connection string; jobs run there as the user who scheduled them, with no password setup. Schedules use cron syntax in UTC.

```sql Nightly cleanup at 03:00 UTC theme={null}
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

Install pg\_partman into its own schema so its functions stay separate from your tables.

```sql theme={null}
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

Databases created before this release move to the new image in an announced window, and we email you on the day. For 1 to 5 minutes the database accepts reads but not writes; saves may fail during that window and work again afterwards. Your version, connection string and password stay the same, and your data is copied and checked row by row before the switch. We keep the previous copy read-only for 7 days in case anything needs to be rolled back.

Until your database has moved, only the built-in extensions are available. pgvector, PostGIS, pg\_cron, pg\_partman, pg\_repack and hypopg arrive with the move. Databases on your own servers move on a schedule you agree with us.

## Expected result

> [!TIP]
>
> Your database runs the version you chose and the extensions you created show up in `pg_extension`.

## Common failures

> [!WARNING]
>
> * `extension "vector" is not available`: your database has not moved to the new image yet. It gets every extension after its move window.
> * `pgaudit must be loaded via shared_preload_libraries`: pgaudit is not available on Starter databases yet.
> * `can only create extension in database …` for pg\_cron: connect to the database named in your connection string and run it there.
> * Version 14 is refused: create the database on 16 or newer.
> * A vector index is not used: the query operator must match the index operator class, for example `<=>` with `vector_cosine_ops`.

## Related guides

> **[Create a database](https://docs.stackshift.cloud/databases/create-a-database.md)**
>
> Create a hosted or connected-node database with private, direct public, or StackShift tunnel access.

> **[Database connections and tunneling](https://docs.stackshift.cloud/databases/credentials-pooling-and-usage.md)**
>
> Use private, direct public, or StackShift tunnel endpoints with TLS and explicit client IP policy.

> **[Back up and restore a database](https://docs.stackshift.cloud/databases/back-up-and-restore-a-database.md)**
>
> Use durable recovery storage correctly and understand why backups are independent from portable SQL transfers.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.