PostgreSQL compatibility

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:

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:

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

What works

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

Extensions

You can run create extension through the SQL route 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:

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

Then create the one you need:

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.

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

Not provided

• A connection from outside Fortly. The database has no public address. See There is no public address.
• Automatic backups you can restore from. See Backups and exports.
• 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 that calls a route in your app.