database

343 notes in this subject, read out of this brain and free to use. This is page 4 of 6.

Write Ahead Log (WAL) storage

The Write Ahead Log (WAL) is a primary component consuming additional disk space beyond database size. Postgres stores database changes in log files that are cleared away after they are applied to the database. These same files are also used by Read Replicas or other replication methods.

Query WAL file size

To determine the size of WAL files stored on disk, run this query: select pg_size_pretty(sum(size)) as wal_size from pg_ls_waldir();

Vacuum operations and dead tuples

Postgres does not immediately reclaim physical space used by dead tuples (deleted rows). They are marked as 'removed' until a vacuum operation is executed. Deleting data from the database may not immediately reduce reported disk usage.

Vacuum specific table

To immediately clean a specific table, run: vacuum full <table name>; in the SQL Editor.

Vacuum full operation locks table

The vacuum full command will lock the table until the operation concludes. Vacuum operations can temporarily increase resource utilization, which may adversely impact observed performance until the maintenance is completed.

Automatic vacuuming enabled

Supabase projects have automatic vacuuming enabled, which ensures that vacuum operations are performed regularly to keep the database healthy and performant. Autovacuum parameters can be fine-tuned or manual vacuum operations can be initiated.

New Supabase project baseline database size

New Supabase projects have a database size of approximately 40-60MB. This space includes pre-installed extensions, schemas, and default Postgres data. Additional database size is used when installing extensions, even if those extensions are inactive.

Pro Plan auto-scaling disk behavior

Projects on the Pro Plan and higher have auto-scaling disks. Disk size expands automatically when the database reaches 90% of allocated disk size. The disk is expanded to be 50% larger, for example 8 GB expands to 12 GB. Auto-scaling is limited to four modifications within a rolling 24-hour window.

Auto-scaling disk resize limits and read-only mode

If you reach 95% disk utilization and have exhausted your modification quota of four resizes in 24 hours, your project will enter read-only mode. The automatic resize operation will add an additional 50% capped to a maximum of 200 GB.

Pro/Team Plan maximum disk size

The maximum disk size for the Pro/Team Plan is 60 TB. Disk size can be manually expanded on the Database Settings page. If you need more than 60 TB, contact Supabase to learn more about the Enterprise Plan.

Free Plan read-only mode threshold

Free Plan projects enter read-only mode when database size exceeds 500 MB. This is the database size limit (the size of actual Postgres data), not the disk size limit. Free Plan projects include 1 GB of disk space.

Free Plan options to handle read-only mode

Once in read-only mode, Free Plan users can either upgrade to the Pro Plan to increase the database size quota and optionally disable the Spend Cap to auto-scale beyond 8 GB, or disable read-only mode and reduce database size.

Fair Use database size restriction

Organizations can be placed under a Fair Use service restriction (requests return a 402 status code) when database size exceeds the plan quota. This quota is evaluated per organization, summing database size across all projects. It is based on average daily database size over the billing period, not live size.

Reducing database size does not immediately lift Fair Use restriction

Reducing database size does not immediately lift a Fair Use restriction because the average stays elevated until enough lower-usage days accumulate. The average effectively resets when the billing cycle rolls over.

Read-only mode error message

In read-only mode, clients encounter errors such as 'cannot execute INSERT in a read-only transaction'. Regular operation (read-write mode) is automatically re-enabled once usage is below 95% of disk size.

Disks do not automatically downsize

Disks do not automatically downsize during normal operation. Once database size is reduced, disks will automatically 'right-size' during a project upgrade. The final disk size after upgrade is 1.2x the size of the database with a minimum of 8 GB.

Reduce large WAL directory by modifying WAL settings

If you have a large WAL directory, you may modify WAL settings such as max_wal_size. Use at your own risk as changing these settings can have side effects. To query current WAL size, use SELECT SUM(size) FROM pg_ls_waldir();

Migrate database to new project to right-size disk

If your project is already on the latest version of Postgres and waiting for the next release to allow upgrading is a concern, you can migrate your database to a new project following the Migrating within Supabase guide as an alternative to reduce disk size.

Reduce Postgres log volume by configuring logging settings

Postgres emits logs for connections, checkpoints, statements, and more. Adjusting settings such as log_connections, log_min_duration_statement, and log_statement can significantly reduce Postgres log volume. See the Customizing Postgres configs guide for the full list of configurable parameters.

PostgREST API vs Supavisor egress labeling

On the Supabase Dashboard, egress from the PostgREST API is labeled as Database Egress, while egress through Supavisor is labeled as Shared Pooler Egress. These are different ways to interact with your database.

Debug egress usage with query performance view

On the Advisors Query performance view at /dashboard/project/_/database/query-performance you can see the most frequent queries and the average number of rows returned. This helps identify what database queries are driving the most traffic and contributing to high egress usage.

Shared pooler egress definition and example

Shared pooler egress is data sent to the client when using the shared connection pooler (Supavisor) to access your database. When using the shared connection pooler, database egress is not counted to avoid double counting. Example: when using the shared connection pooler to query a list of invoices in your backend, the data returned from that query contributes to Shared Pooler Egress.

Default connection limits by compute size

The default connection limits for Postgres and Supavisor are based on your compute size. Default connection numbers are specified in the Compute Add-ons documentation section.

Query performance analysis

Unoptimized queries are a major cause of poor database performance. Query performance can be analyzed using the Debugging and monitoring guide.

pg_stat_activity view for connection debugging

The pg_stat_activity view can be used to debug which clients are holding open connections on your database. However, pg_stat_activity only exposes information on direct connections to the database, not on connections to Supavisor. Information on the number of connections to Supavisor is available via the metrics endpoint.

Solutions for insufficient database connections

When connection limits are insufficient, you can: upgrade to a larger compute add-on, configure your clients to use fewer connections, or manually configure the database for a higher number of connections.

Connection pooling for transient workflows

Transient workflows that can scale up and down rapidly in response to traffic, such as serverless functions, can especially benefit from using a connection pooler like Supavisor rather than connecting to the database directly. Clients can be configured to work with fewer connections by imposing a limit on the maximum number of connections they are allowed to use, or by shifting specific workloads to connect via a connection pooler.

Custom Postgres configuration for connection limits

Postgres connection limit and other parameters can be configured using Custom Postgres Config.

Connection slots reserved error

When the number of connections is insufficient, Postgres returns the error: 'FATAL: remaining connection slots are reserved for non-replication superuser connections'.

Connection logs visibility in Logs Explorer

Connection events appear in Postgres logs in the Logs Explorer. Connection lifecycle messages may be hidden by default to reduce noise. Use the connection logs filter in the sidebar to show or hide them.

Verify connection logging setting with SQL

To verify the current connection logging setting, run the SQL command: show log_connections;

Connection logging for compliance programs

Connection logging supports audit and monitoring controls required by compliance programs. For HIPAA, high-compliance projects should keep connection logging enabled. For SOC 2, users who need connection audit evidence must enable logging and retain logs according to their own policies. Disabling connection logging does not affect other Supabase logging such as Platform Audit Logs, Auth Audit Logs, or pgAudit.

Enable connection logging via dashboard

Connection logging can be configured from the Log connections setting in the Database Settings section of the Dashboard. You must have Owner or Admin permissions for the project.

Postgres SSL modes and their properties

Postgres supports multiple SSL modes: disable (No encryption, No CA verification, No hostname verification - SSL is not used, all data is transmitted in plaintext), allow (Optional encryption, No CA verification, No hostname verification - tries non-SSL first then falls back to SSL), prefer (Optional encryption, No CA verification, No hostname verification - tries SSL first then falls back to non-SSL, this is the default), require (Encryption enabled, No CA verification, No hostname verification - always uses SSL but does not verify server certificate or hostname), verify-ca (Encryption enabled, Verifies CA, No hostname verification - uses SSL and verifies server certificate is signed by trusted CA), verify-full (Encryption enabled, Verifies CA, Verifies hostname - uses SSL, verifies CA certificate, and confirms hostname matches certificate, recommended when SSL enforcement is enabled).

Configure SSL enforcement via dashboard

SSL enforcement can be configured via the 'Enforce SSL on incoming connections' setting under the SSL Configuration section in the Database Settings page of the Supabase dashboard.

SSL enforcement triggers fast database reboot

Applying or updating SSL enforcement triggers a fast database reboot. On small projects this usually completes in a few seconds, but larger databases may see a longer interruption. Updating SSL enforcement requires a brief database reboot that restarts only the database and involves a few minutes of downtime.

SSL enforcement applies only to Postgres connections

SSL enforcement only applies to connections to Postgres, Supavisor (shared Connection Pooler), and PgBouncer (dedicated Connection Pooler). All HTTP APIs offered by Supabase (such as PostgREST, Storage, and Auth) automatically enforce SSL on all incoming connections.

Connect to Supabase with verify-full SSL mode

To connect to Supabase using verify-full SSL mode with psql or your client library, after adding the CA certificate to the trusted list, use a connection string like: psql "postgresql://aws-0-eu-central-1.pooler.supabase.com:6543/postgres?sslmode=verify-full" -U postgres.<user>

Download Supabase CA certificate for verify-full mode

To use the verify-full SSL mode, you need to download the Supabase CA certificate for your database. The certificate is available through the dashboard under the SSL Configuration section in the Database Settings page. Once downloaded, add the certificate to the certificate authority list used by Postgres by appending it to ~/.postgres/root.crt using: cat {location of downloaded prod-ca-2021.crt} >> ~/.postgres/root.crt

verify-full is the strongest Postgres SSL mode

The strongest mode offered by Postgres is verify-full, which uses SSL, verifies the CA certificate, and confirms the hostname matches the certificate. This is the recommended mode to use when SSL enforcement is enabled.

Queues authorization with RLS

Supabase Queues supports granular authorization to control client-side consumer access to Queues using API permissions and Row Level Security (RLS) policies.

Queues management in dashboard

Supabase Queues can be created, managed, and monitored directly in the Supabase Dashboard.

Queues message durability and archival

Messages in Supabase Queues are stored in Postgres. Developers can choose to archive messages for analytical or auditing purposes.

Queues guaranteed message delivery

Messages added to Supabase Queues are guaranteed to be delivered to consumers. Messages are delivered exactly once to a consumer within a customizable visibility window.

Queues built on pgmq extension

Supabase Queues is built on the pgmq database extension. Queues can be created and managed with any Postgres tooling.

Supabase Queues overview

Supabase Queues is a Postgres-native durable Message Queue system with guaranteed delivery built on the pgmq database extension. It enables developers to persist and process messages in the background while improving application resiliency and scalability, with zero configuration required.

pgmq_public.read function

The pgmq_public.read(queue_name, sleep_seconds, n) function reads up to n Messages from the specified Queue with an optional visibility timeout. Parameters: queue_name (text, required) specifying the Queue name, sleep_seconds (integer, required) for the visibility timeout in seconds, n (integer, required) for the maximum number of Messages to read.

pgmq_public.pop function

The pgmq_public.pop(queue_name) function retrieves the next available message and deletes it from the specified Queue. It takes one parameter: queue_name (text, required) which specifies the Queue name.

pgmq_public schema for queue operations

When you create a Queue in Supabase, you can choose to create helper database functions in the pgmq_public schema. This schema exposes operations to manage Queue Messages to consumers client-side, but does not expose functions for creating or dropping Queues. Database functions in pgmq_public can be exposed via Supabase Data API so consumers client-side can call them.

pgmq_public.send_batch function

The pgmq_public.send_batch(queue_name, messages, sleep_seconds) function adds a batch of Messages to the specified Queue, optionally delaying their availability to all consumers by a number of seconds. Parameters: queue_name (text, required), messages (jsonb[], required) as an array of message payloads to send, sleep_seconds (integer, optional, defaults to 0) to delay messages visibility by specified seconds.

pgmq_public.archive function

The pgmq_public.archive(queue_name, message_id) function archives a Message by moving it from the Queue table to the Queue's archive table. Parameters: queue_name (text, required) specifying the Queue name, message_id (bigint, required) for the ID of the Message to archive.

pgmq_public.delete function

The pgmq_public.delete(queue_name, message_id) function permanently deletes a Message from the specified Queue. Parameters: queue_name (text, required) specifying the Queue name, message_id (bigint, required) for the ID of the Message to delete.

pg_graphql 1.6.0 introspection disabled by default

Starting with pg_graphql 1.6.0, GraphQL introspection is disabled by default. After upgrade, queries to __schema and __type will return an error unless introspection is explicitly enabled. This affects Studio's GraphQL inspector (GraphiQL), external GraphiQL or GraphQL Playground, code generators (e.g. graphql-codegen), Relay compiler, and any tools that call __schema or __type directly. Regular data queries are not affected.

pg_cron historical records cleanup before upgrade

pg_cron does not automatically clean up historical records. This can lead to extremely large cron.job_run_details tables if records are not regularly pruned. You should clean unnecessary records from this table before an upgrade. During the Supabase project upgrade, the pg_cron extension gets dropped and recreated, and the cron.job_run_details table is duplicated to avoid losing historical logs. An extremely large details table can cause unnecessary performance degradation or upgrade process failures.

Postgres 17 deprecated extensions

In projects using Postgres 17, the following extensions are deprecated: plcoffee, plls, plv8, timescaledb, and pgjwt. Projects planning to upgrade from Postgres 15 to Postgres 17 must first disable these extensions in the Supabase Dashboard. Note that pgjwt was enabled by default on every Supabase project until Postgres 17, so if you weren't explicitly using it, it's most likely safe to disable.

Identify user-defined operators with non-built-in selectivity estimators

To check whether your database has user-defined operators that reference a non-built-in estimator, run: SELECT n.nspname AS schema, o.oprname AS operator FROM pg_operator o JOIN pg_namespace n ON o.oprnamespace = n.oid WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND ((o.oprrest <> 0 AND o.oprrest::oid >= 10000) OR (o.oprjoin <> 0 AND o.oprjoin::oid >= 10000)) AND NOT EXISTS ( SELECT 1 FROM pg_depend d WHERE d.classid = 'pg_operator'::regclass AND d.objid = o.oid AND d.deptype = 'e' ); If this returns no rows, your project is unaffected.

Custom operator selectivity estimators superuser requirement in Postgres 15.18 and 17.10

When upgrading to Postgres 15.18 or 17.10, attaching a non-built-in (extension- or user-provided) selectivity estimator function to an operator now requires superuser. Existing operators continue to work; the check only fires when an operator is recreated, which most commonly occurs during pg_dump/pg_restore, logical restore, or a branch. Because Supabase database roles are not superusers, recreating such an operator can fail with the error: 'ERROR: must be superuser to specify a non-built-in restriction estimator function'.

Reindex ltree indexes after upgrade

To reindex affected ltree indexes after upgrade, use REINDEX INDEX CONCURRENTLY which runs online with no downtime: REINDEX INDEX CONCURRENTLY <index_name>;

Check if ltree reindexing is required

To check whether your database needs ltree reindexing after upgrade, run this SQL: select pg_encoding_to_char(encoding) as encoding, pg_encoding_max_length(encoding) as max_bytes_per_char, datlocprovider as collation_provider, (pg_encoding_max_length(encoding) > 1 or datlocprovider != 'c') as reindex_required from pg_database where datname = current_database(); If reindex_required is false (single-byte encoding like LATIN1 with libc collation), no action is needed. If true, find affected indexes with: select schemaname, tablename, indexname from pg_indexes where indexname in ( select c.relname from pg_index as i join pg_class as c on i.indexrelid = c.oid join pg_attribute as a on a.attrelid = i.indrelid and a.attnum = ANY(i.indkey) join pg_type as t on a.atttypid = t.oid where t.typname in ('ltree', '_ltree') );

Ltree indexes reindexing after upgrade to Postgres 15.18 or 17.10

When upgrading to Postgres 15.18 or 17.10, indexes on ltree columns that were built under the previous version can return incomplete results until the index is rebuilt if your database uses a multibyte encoding (such as UTF-8) or a non-libc collation provider (such as ICU or builtin). This affects label searches which can silently miss rows that are present.

Give your agent this brain