Database

Timeouts

Extend database timeouts to execute longer transactions

Change Postgres timeout

You can change the Postgres timeout at the:

  1. Session level
  2. Function level
  3. Global level
  4. Role level

Session level

Session level settings persist only for the duration of the connection.

Set the session timeout by running:


_10
set statement_timeout = '10min';

Because it applies to sessions only, it can only be used with connections through Supavisor in session mode (port 5432) or a direct connection. It cannot be used in the Dashboard, with the Supabase Client API, nor with Supavisor in Transaction mode (port 6543).

This is most often used for single, long running, administrative tasks, such as creating an HSNW index. Once the setting is implemented, you can view it by executing:


_10
SHOW statement_timeout;

See the full guide on changing session timeouts.

Function level

This works with the Database REST API when called from the Supabase client libraries:


_10
create or replace function myfunc()
_10
returns void as $$
_10
select pg_sleep(3); -- simulating some long-running process
_10
$$
_10
language sql
_10
set statement_timeout TO '4s'; -- set custom timeout

This is mostly for recurring functions that need a special exemption for runtimes.

Role level

This sets the timeout for a specific role.

The default role timeouts are:

  • anon: 3s
  • authenticated: 8s
  • service_role: none (capped by API to be 60s)
  • postgres: none (capped by default global timeout to be 2min)

Run the following query to change a role's timeout:


_10
alter role example_role set statement_timeout = '10min'; -- could also use seconds '10s'

Unlike global settings, the result cannot be checked with SHOW statement_timeout. Instead, run:


_12
select
_12
rolname,
_12
rolconfig
_12
from pg_roles
_12
where
_12
rolname in (
_12
'anon',
_12
'authenticated',
_12
'postgres',
_12
'service_role'
_12
-- ,<ANY CUSTOM ROLES>
_12
);

Global level

This changes the statement timeout for all roles and sessions without an explicit timeout already set.


_10
alter database postgres set statement_timeout TO '4s';

Check if your changes took effect:


_10
show statement_timeout;

Although not necessary, if you are uncertain if a timeout has been applied, you can run a quick test:


_10
create or replace function myfunc()
_10
returns void as $$
_10
select pg_sleep(601); -- simulating some long-running process
_10
$$
_10
language sql;

Identifying timeouts

The Supabase Dashboard contains tools to help you identify timed-out and long-running queries.

Using the Logs Explorer

Go to the Logs Explorer, and run the following query to identify timed-out events (statement timeout) and queries that successfully run for longer than 10 seconds (duration).


_20
select
_20
cast(postgres_logs.timestamp as datetime) as timestamp,
_20
event_message,
_20
parsed.error_severity,
_20
parsed.user_name,
_20
parsed.query,
_20
parsed.detail,
_20
parsed.hint,
_20
parsed.sql_state_code,
_20
parsed.backend_type
_20
from
_20
postgres_logs
_20
cross join unnest(metadata) as metadata
_20
cross join unnest(metadata.parsed) as parsed
_20
where
_20
regexp_contains(event_message, 'duration|statement timeout')
_20
-- (OPTIONAL) MODIFY OR REMOVE
_20
and parsed.user_name = 'authenticator' -- <--------CHANGE
_20
order by timestamp desc
_20
limit 100;

Using the Query Performance page

Go to the Query Performance page and filter by relevant role and query speeds. This only identifies slow-running but successful queries. Unlike the Log Explorer, it does not show you timed-out queries.

Understanding roles in logs

Each API server uses a designated user for connecting to the database:

RoleAPI/Tool
supabase_adminUsed by Supabase to configure projects and for monitoring
authenticatorPostgREST
supabase_auth_adminAuth
supabase_storage_adminStorage
supabase_realtime_adminRealtime
supabase_replication_adminSynchronizes Read Replicas
postgresSupabase Dashboard and External Tools (e.g., Prisma, SQLAlchemy, PSQL...)
Custom rolesExternal Tools (e.g., Prisma, SQLAlchemy, PSQL...)

Filter by the parsed.user_name field to only retrieve logs made by specific users:


_10
-- find events based on role/server
_10
... query
_10
where
_10
-- find events from the relevant role
_10
parsed.user_name = '<ROLE>'