# PostgreSQL compatibility

> The PostgreSQL version your project runs, what your database role can and cannot do, which extensions you can add, and how connections are pooled.

Source: https://docs.fortly.com/data/postgres

Your project's database is a real PostgreSQL 16 database. Fortly doesn't translate or emulate anything: your SQL goes to the server as you wrote it, and types, constraints, functions and indexes behave exactly as PostgreSQL documents them. What differs from a server you run yourself is what your database role is allowed to do, and that is covered here.

## Version

PostgreSQL 16. To see the exact version, run this in the **Query** tab of the project's **Data** section:

```sql
select version();
```

## Your database and your role

Your project has one database on a PostgreSQL server that is shared with other projects. A login role of its own owns that database, and that role is the identity everything runs as: your app's `DATABASE_URL`, the data APIs, the console and imports.

The role owns everything it creates, so within your database you can create and change schemas, tables, types, functions, triggers, views and indexes. It is created with:

```sql
nosuperuser nocreatedb nocreaterole noreplication nobypassrls
```

So it can't:

- create other databases or roles
- connect to any other project's database
- change server settings (`ALTER SYSTEM`) or read and write files on the server, such as `COPY … FROM '/path'` or `pg_read_file()`
- use replication, logical decoding or subscriptions
- create extensions PostgreSQL doesn't mark as trusted. See [Extensions](#extensions)

## What works

All of this is the server's own behaviour, so PostgreSQL's documentation is the reference for each one:

| Area | Notes |
| --- | --- |
| Types | Everything built in, including `numeric(p,s)` at full precision, `timestamptz`, `uuid`, `jsonb`, arrays, `bytea`, ranges, enums (`create type … as enum`) and domains |
| Keys and constraints | Primary and foreign keys, `unique`, `check`, `not null`. Enforced by the server |
| Generated values | `generated … as identity`, `serial`, defaults, and stored generated columns |
| Logic in the database | Functions and procedures in SQL and PL/pgSQL, and triggers. The console's **Routines** section edits and calls them |
| Queries | CTEs, including recursive ones, window functions, `jsonb` operators, and partial and expression indexes |
| Views | Ordinary and materialized views. You refresh a materialized view yourself with `refresh materialized view` |
| Full-text search | `tsvector`, `tsquery` and GIN indexes. The data APIs' [`fts` filter](https://docs.fortly.com/data/server-api#full-text-search) uses a table's `tsvector` column when it has one |
| Transactions | Ordinary MVCC, with concurrent readers and writers, savepoints and every isolation level |

> **Note:**
>
> The data APIs serve ordinary tables in the `public` schema. Views, partitioned tables and tables in other schemas work in SQL but aren't served by the data APIs. Read them through a [routine](https://docs.fortly.com/data/server-api#call-a-routine).

## Extensions

You can run `create extension` through the [SQL route](https://docs.fortly.com/data#change-the-schema) or the **Query** tab. Whether it works depends on two things: the extension has to be installed on the server, and, because your role isn't a superuser, PostgreSQL has to mark the extension as **trusted**. Fortly doesn't install extensions on a project's behalf.

To see which extensions your role can create, run this in the **Query** tab:

```sql
select name, version
from pg_available_extension_versions
where trusted
order by name;
```

Then create the one you need:

```sql
create extension if not exists pg_trgm;
```

An extension that isn't available is refused with PostgreSQL's own error. An import that contains a `create extension` line runs it like any other statement, so a dump that needs a missing extension stops at that line and nothing is applied. PostGIS and TimescaleDB aren't offered.

## Connection pooling

This applies to a project that runs as a long-running process and connects through `DATABASE_URL`. A Worker has no direct connection. See [Connect from your app](https://docs.fortly.com/data#connect-from-your-app).

`DATABASE_URL` goes through a connection pooler in **session mode**. Your client keeps the same server connection for as long as it stays connected, so session features behave as they do on a direct connection: prepared statements, `LISTEN` and `NOTIFY`, advisory locks, temporary tables and `SET`.

Where it's available, `DATABASE_TRANSACTION_URL` reaches the same database through a pooler in **transaction mode**. You hold a server connection only for the length of one transaction, and the next transaction may get a different one. That lets more clients share the database's 10 connections, but nothing tied to a session survives between transactions: `LISTEN`, advisory locks, temporary tables, cursors held across transactions and session-level `SET` are lost or unreliable. Drivers that cache prepared statements by name can also fail with `prepared statement "…" does not exist`. Turn that cache off in your driver if you use this URL, for example `statement_cache_capacity(0)` in SQLx.

Use `DATABASE_URL` unless you've tested your app against the transaction URL.

## Limits

Each database's role has a 10-connection limit, a 30-second statement timeout, a 60-second idle-in-transaction timeout and a 10-second lock timeout. See [Limits on each database](https://docs.fortly.com/data#limits-on-each-database). These are role settings, so a `SET statement_timeout` in your own session can change the statement timeout for that session.

## Row-level security

You can enable row-level security and write policies. PostgreSQL exempts a table's owner from them unless the table also has `force row level security`. Your project's role owns its tables, and every path in, including the data APIs, connects as that role. So policies don't restrict the data APIs by default. Even when forced, they see one role for every caller and can't tell your users apart. For per-user rules, see [What the key cannot do](https://docs.fortly.com/data/frontend-api#what-the-key-cannot-do).

## Not provided

- **A connection from outside Fortly.** The database has no public address. See [There is no public address](https://docs.fortly.com/data#there-is-no-public-address).
- **Automatic backups you can restore from.** See [Backups and exports](https://docs.fortly.com/data/backups).
- **Realtime or change feeds.** Fortly doesn't stream changes from your database. A long-running process can still use `LISTEN` and `NOTIFY` over `DATABASE_URL`.
- **Incremental materialized views.** Refresh them yourself, for example from a [schedule](https://docs.fortly.com/deploy/schedules) that calls a route in your app.