# Postgres extensions

> Turn on Postgres extensions per database from the Settings tab: pgvector, PostGIS, pg_trgm, pg_cron, pgmq, pg_partman, pg_duckdb and more, without superuser.

Each managed Postgres database has an **Extensions** section on its **Settings** tab. Turn an extension on there and Ownkube runs `CREATE EXTENSION` for you as the database superuser, so your own role never needs superuser rights. Turn it off and Ownkube runs `DROP EXTENSION`.

## Turn an extension on

1. Open the database and go to the **Settings** tab.
2. In **Extensions**, switch on the ones you need. Each staged change shows an **On after deploy** or **Off after deploy** pill.
3. Deploy the change. Ownkube creates the extension in the app database.

Check it from [`okctl connect`](/docs/deployments/shell#connect-to-a-database-or-cache) or the [data console](/docs/data/console):

```sql
SELECT extname, extversion FROM pg_extension ORDER BY extname;
```

## Available extensions

| Group | Extension | What it does | Restarts |
| --- | --- | --- | --- |
| Search | pgvector (`vector`) | Store embeddings and run vector similarity search | No |
| Search | `pg_trgm` | Fast fuzzy text search and `LIKE` queries with trigram indexes | No |
| Search | `unaccent` | Strip accents so searches match café and cafe | No |
| Search | `fuzzystrmatch` | Levenshtein distance and sound-alike matching | No |
| Geo | PostGIS (`postgis`) | Geographic types, spatial indexes and distance queries | No |
| Types and indexes | `citext` | Case-insensitive text, handy for emails and usernames | No |
| Types and indexes | `hstore` | Key-value pairs in a single column | No |
| Types and indexes | `btree_gin` | Combine plain columns with GIN indexes | No |
| Types and indexes | `btree_gist` | Exclusion constraints, such as no overlapping bookings | No |
| Crypto and IDs | `pgcrypto` | Hashing and encryption functions in SQL | No |
| Crypto and IDs | `uuid-ossp` | Generate UUIDs, including v1 and v5 | No |
| Jobs and queues | `pg_cron` | Schedule SQL to run on a cron schedule | Yes |
| Jobs and queues | `pgmq` | A lightweight message queue stored in Postgres tables | No |
| Jobs and queues | `pg_partman` | Create and retire time or ID based table partitions | No |
| Analytics | `pg_duckdb` | Run analytical queries on a fast columnar engine | Yes |
| Analytics | `pg_ivm` | Materialized views that update as rows change | Yes |
| Analytics | `hll` | Approximate distinct counts in a few bytes | No |
| Analytics | `tdigest` | Approximate percentiles and medians over large tables | No |
| Query insights | `pg_stat_statements` | Track query timing to find slow queries | Yes |

Extensions are available on Postgres 17 and 18, on every plan. If the Extensions section is missing or shows fewer entries, the database runs an older DB platform version: use **Upgrade** on its Overview tab (see [Upgrades](/docs/data/postgres#upgrades)).

## Extensions that restart the database

`pg_cron`, `pg_duckdb`, `pg_ivm` and `pg_stat_statements` load when Postgres starts, so turning one on or off restarts the database. Open connections drop for a moment. The toggle shows a **Restarts** pill, and the section warns you before you deploy. Every other extension applies without a restart.

## Turning an extension off

Turning an extension off drops it. If a table, column or index still uses it, the drop fails and the extension stays on. Remove what depends on it first, for example the `vector` column or the HNSW index, then turn it off.

An extension you created yourself in SQL, and never touched in Settings, is left alone. Ownkube only manages the ones you switch in the Extensions section.

## Notes on specific extensions

### pgvector

Turn it on, then add a `vector(n)` column sized to your embedding model and an HNSW index:

```sql
CREATE TABLE documents (
  id        bigserial PRIMARY KEY,
  content   text NOT NULL,
  embedding vector(1536) NOT NULL
);
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
```

Size the box so the index fits in memory. The [pgvector guide](/blog/pgvector) covers operators, index settings and sizing.

### pg_cron

Jobs run in the app database, on background workers, with up to 5 jobs running at once.

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

For jobs that run code rather than SQL, use a [scheduled job](/docs/deployments/jobs) instead.

### pg_duckdb

Analytical queries run in DuckDB's columnar engine inside Postgres. On Ownkube, pg_duckdb is limited to a quarter of the box memory, spills to at most a quarter of the volume, and cannot read files or reach the network. It suits aggregates over your own tables.

### pg_stat_statements

Once on, the `pg_stat_statements` view lists the queries that take the most time:

```sql
SELECT calls, round(mean_exec_time::numeric, 1) AS mean_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
```

- [Postgres](/docs/data/postgres)
- [Data console](/docs/data/console)
- [pgvector guide](/blog/pgvector)

---

**Need an extension that isn't listed?** Email [support@ownkube.io](mailto:support@ownkube.io?subject=Postgres%20extension%20request) with the name and what you use it for.

## Search

This site has a search endpoint: https://context7.com/api/v2/ask?siteKey=ask_c64dfe8ffaa8d3ad19576838&query=<URL-encoded question>. It returns documentation that answers the question, with a source link for each part. No API key is needed. If nothing matches, it says so.