Re-enable GraphQL introspection after pg_graphql 1.6.0 upgrade
To re-enable introspection on a schema after upgrading to pg_graphql 1.6.0, run this SQL:
comment on schema public is e'@graphql({"introspection": true})';
If your schema already has a comment with other directives (e.g. inflect_names), combine the keys:
comment on schema public is e'@graphql({"inflect_names": true, "introspection": true})';
To verify introspection is enabled:
select graphql.resolve('{ __schema { queryType { name } } }');
Queue creation table structure
When a queue is created, two tables are created in the pgmq schema. A Basic Queue creates pgmq.q_<queue_name> and pgmq.a_<queue_name> as logged tables. An Unlogged Queue creates pgmq.q_<queue_name> as an unlogged table for better performance while pgmq.a_<queue_name> is created as a logged table. The q_ tables store and process active messages while the a_ tables store archived messages.
pgmq extension Postgres version requirement
The pgmq extension is available in Postgres version 15.6.1.143 or later.
pgmq.read_with_poll provides long-poll functionality
Function signature: pgmq.read_with_poll(queue_name text, vt integer, qty integer, max_poll_seconds integer default 5, poll_interval_ms integer default 100) returns setof pgmq.message_record. Same as read() but provides long-poll functionality. When there are no messages in the queue, the function waits for max_poll_seconds duration before returning. If messages arrive during this time, they are read and returned immediately. Parameters: queue_name (text) - the queue name, vt (integer) - visibility timeout in seconds, qty (integer) - number of messages to read (defaults to 1), max_poll_seconds (integer) - wait duration before returning (defaults to 5), poll_interval_ms (integer) - milliseconds between internal poll operations (defaults to 100).
pgmq.read reads messages from queue with visibility timeout
Function signature: pgmq.read(queue_name text, vt integer, qty integer) returns setof pgmq.message_record. Reads one or more messages from a queue. The VT (visibility timeout) specifies the duration in seconds that the message is invisible to other consumers; after that duration, the message is visible again. Parameters: queue_name (text) - the name of the queue, vt (integer) - time in seconds the message is invisible after reading, qty (integer) - number of messages to read, defaults to 1. Returns message records with msg_id, read_ct, enqueued_at, vt, and message fields.
pgmq.send_batch sends multiple messages to queue
Function signature: pgmq.send_batch(queue_name text, msgs jsonb[], delay integer default 0) returns setof bigint. Sends one or more messages to a queue. Parameters: queue_name (text) - the name of the queue, msgs (jsonb[]) - array of messages to send, delay (integer) - time in seconds before messages become visible, defaults to 0. Returns message IDs for each sent message. Example: select * from pgmq.send_batch('my_queue', array['{"hello": "world_0"}'::jsonb, '{"hello": "world_1"}'::jsonb]); returns 1, 2
pgmq.drop_queue deletes queue and archive table
Function signature: pgmq.drop_queue(queue_name text) returns boolean. Deletes a queue and its archive table. Example: select * from pgmq.drop_queue('my_unlogged'); returns t (true) on success.
pgmq.detach_archive preserves archive table when dropping extension
Function signature: pgmq.detach_archive(queue_name text). Drops the queue's archive table as a member of the PGMQ extension to prevent it from being dropped when drop extension pgmq is executed. This does not prevent further archives() calls from appending to the archive table.
pgmq.create_unlogged for high-throughput queues
Function signature: pgmq.create_unlogged(queue_name text) returns void. Creates an unlogged table for the queue, prioritizing write throughput over durability. See Postgres documentation for unlogged tables for more information. Example: select pgmq.create_unlogged('my_unlogged');
pgmq.create queue management function
Function signature: pgmq.create(queue_name text) returns void. Creates a new queue with the specified name. Example: select from pgmq.create('my_queue');
Enable PGMQ extension with create extension command
To enable the PGMQ extension in Postgres, run the SQL command: create extension pgmq;
PGMQ extension enables lightweight message queues in Postgres
PGMQ is a lightweight message queue built on Postgres with no background worker or external dependencies. It is packaged as a Postgres extension and provides exactly once delivery of messages to a consumer within a visibility timeout. The extension has API parity with AWS SQS and RSMQ. Messages stay in the queue until explicitly removed, and can be archived instead of deleted for long-term retention and replayability.
pgmq.purge_queue permanently deletes all messages in queue
Function signature: pgmq.purge_queue(queue_name text) returns bigint. Permanently deletes all messages in a queue. Returns the number of messages that were deleted. Example: select * from pgmq.purge_queue('my_queue'); when the queue contains 8 messages, returns 8.
pgmq.pop reads and deletes message atomically
Function signature: pgmq.pop(queue_name text) returns setof pgmq.message_record. Reads a single message from a queue and deletes it upon read. Use of pop() results in at-most-once delivery semantics if the consuming application does not guarantee processing of the message. Example: select * from pgmq.pop('my_queue'); returns the message record before deletion.
pgmq.archive moves batch of messages to archive table
Function signature: pgmq.archive(queue_name text, msg_ids bigint[]) RETURNS SETOF bigint. Deletes a batch of messages from the specified queue and inserts them into the queue's archive. Parameters: queue_name (text) - the name of the queue, msg_ids (bigint[]) - array of message IDs to archive. Returns array of message IDs that were successfully archived. Non-existent message IDs are silently ignored.
pgmq.delete removes batch of messages from queue
Function signature: pgmq.delete(queue_name text, msg_ids bigint[]) returns setof bigint. Deletes one or many messages from a queue. Parameters: queue_name (text) - the name of the queue, msg_ids (bigint[]) - array of message IDs to delete. Returns the message IDs that were successfully deleted. Only deletes messages that exist; non-existent message IDs are silently ignored.
pgmq.message_record type structure
The message_record type represents a complete message in a queue with the following fields: msg_id (bigint) - unique ID of the message, read_ct (bigint) - number of times the message has been read (increments on read()), enqueued_at (timestamp with time zone) - time the message was inserted into the queue, vt (timestamp with time zone) - timestamp when the message will become available for consumers to read, message (jsonb) - the message payload.
pgmq.list_queues shows all existing queues
Function signature: pgmq.list_queues() RETURNS TABLE(queue_name text, created_at timestamp with time zone, is_partitioned boolean, is_unlogged boolean). Lists all queues that currently exist with their creation timestamp and configuration flags. Example output includes columns for queue_name, created_at, is_partitioned (boolean), and is_unlogged (boolean).
pgmq.set_vt modifies message visibility timeout
Function signature: pgmq.set_vt(queue_name text, msg_id bigint, vt_offset integer) returns pgmq.message_record. Sets the visibility timeout of a message to a specified time duration in the future. Parameters: queue_name (text) - the name of the queue, msg_id (bigint) - ID of the message to update, vt_offset (integer) - duration from now in seconds that the message's VT should be set to. Returns the updated message record. Example: select * from pgmq.set_vt('my_queue', 11, 30); sets visibility timeout of message 11 to 30 seconds from now.
Database security guides index
Supabase provides security guides for the database covering: row level security, column level security, securing the API, custom claims and role based access control, managing Postgres roles, managing secrets with Vault, Postgres connection logging, and superuser access and unsupported operations.
Postgres SSL enforcement in Supabase
Supabase projects support connecting to the Postgres database without SSL enforced by default to maximize client compatibility. Organizations can prevent clients from connecting if they are not using SSL for increased security.
Example init script for custom extension in docker-compose.yml
db:\n volumes:\n # ...keep the existing mounts (the data dir, roles.sql, etc.) and add:\n - ./volumes/db/pg_uuidv7.sql:/docker-entrypoint-initdb.d/migrations/99-pg_uuidv7.sql:Z
Limitations of static linking for Postgres extensions
Static linking for extension dependencies has limits: it will not help a dependency that dlopen()s plugins at runtime, and linking a library that is also loaded by another extension (such as two copies of OpenSSL in one process) can clash. Some extensions hard-code their own name, so you cannot rename one to sidestep a collision with a bundled extension. When static linking is not practical, use the Nix build path.
Static linking for extension dependencies
When a compiled .so has runtime dependencies beyond libc.so.6 and ld-linux-*, the recommended approach is to statically link the extra dependencies into the .so, so its only remaining NEEDED is libc.so.6. This requires compiling the dependency from source to produce a minimal static version. PGXS may ignore the extension's CFLAGS, so pass extra include paths via PG_CPPFLAGS on the make command line rather than editing the Makefile.
Always build against same major version you run
Always build your custom Postgres image against the same major version you run in production, and rebuild your custom image whenever you upgrade the base image.
Keep custom Postgres image in sync with base image
Because a custom image is pinned to a specific supabase/postgres tag and depends on that build's internal Nix paths, treat it as coupled to the base image. Rebuild with the new SUPABASE_POSTGRES_TAG (and matching PG_MAJOR) every time you upgrade Postgres. Re-test CREATE EXTENSION after each rebuild because a change to the base OS or Nix layout can require adjusting the build.
Supabase Nix build documentation for extensions
The authoritative and maintained instructions for building extensions into the Nix image live in the supabase/postgres repository at: https://github.com/supabase/postgres/blob/develop/nix/docs/ including Adding a new extension package, Creating a pgrx extension, and Building Postgres.
Nix build approach for custom Postgres extensions
The most reliable way to add an extension to the Supabase Postgres image is to compile it into the image's Nix build, so it is compiled with the exact same toolchain as everything else in the image. This sidesteps libc/ABI concerns entirely and is how the bundled extensions are built. It requires Nix, building the image from source, and maintaining a fork of supabase/postgres. This approach is recommended for bulletproof ABI match, merging upstream, or extensions that need preload / supautils wiring.
shared_preload_libraries has no append syntax
shared_preload_libraries does not support an append syntax. You must include the full existing list plus your library in the configuration, or you will disable the extensions that Supabase relies on.
Extensions requiring shared_preload_libraries
If an extension must be listed in shared_preload_libraries, add it using the conf.d/ mechanism. The conf.d/ include runs after the baked-in setting, so restate the current value plus your library. Example: docker exec supabase-db bash -c 'CUR=$(psql -U postgres -tAc "show shared_preload_libraries" | tr -d "\n")\ncat > /etc/postgresql-custom/conf.d/99-preload.conf <<EOF\nshared_preload_libraries = '"'"'$CUR, custom_bgworker'"'"'\nEOF'\nsh run.sh restart db
Verify extension after installation
docker compose exec db psql -U postgres -c "CREATE EXTENSION IF NOT EXISTS pg_uuidv7;" -c "SELECT uuid_generate_v7();"
Example SQL file to create extension on init
CREATE EXTENSION IF NOT EXISTS pg_uuidv7;
Create extension automatically on first boot via init scripts
The image's init scripts run as supabase_admin (a superuser) on first boot. Drop a SQL file into /docker-entrypoint-initdb.d/migrations/ in the db volume to create the extension automatically for new databases. Init scripts only run when the data directory (./volumes/db/data) is empty, that is on first initialization. For an already-initialized database, connect as supabase_admin and run CREATE EXTENSION manually.
/etc/postgresql-custom/ persists across restarts
/etc/postgresql-custom/ is on the db-config named volume, so configuration changes persist across restarts.
Allow postgres role to create custom extension via supautils config
The Postgres 17 image loads any .conf file from /etc/postgresql-custom/conf.d/. Append the extension name to supautils.privileged_extensions in a custom configuration file. Example: docker exec supabase-db bash -c 'CUR=$(psql -U postgres -tAc "show supautils.privileged_extensions" | tr -d "\n")\ncat > /etc/postgresql-custom/conf.d/99-custom-extensions.conf <<EOF\nsupautils.privileged_extensions = '"'"'$CUR, pg_uuidv7'"'"'\nEOF'\nsh run.sh restart db\ndocker compose exec db psql -U postgres -c "CREATE EXTENSION pg_uuidv7;"
Postgres role cannot create native extensions by default
The Supabase postgres role is intentionally not a superuser, and extension creation is gated by supautils. A native extension that is not on the supautils.privileged_extensions allow-list can only be created by the supabase_admin superuser.
Recreate database service after image change
After updating the image in docker-compose.yml, run: sh run.sh recreate db
Point docker-compose.yml to custom Postgres image
In docker-compose.yml, change the db service image to your custom image: db:\n image: supabase-postgres-custom:17.6.1.136\n # ...leave the rest of the service definition unchanged
Docker build command for custom Postgres extension
docker build --build-arg SUPABASE_POSTGRES_TAG=17.6.1.136 --build-arg PG_MAJOR=17 -t supabase-postgres-custom:17.6.1.136 .
Check compiled extension dependencies with readelf
To verify that a compiled .so only depends on glibc, run inside the builder stage: docker build --target builder -t ext-builder . && docker run --rm --entrypoint sh ext-builder -c 'readelf -d /src/*.so | grep NEEDED'. If only libc.so.6 and ld-linux-* are listed, the extension has no additional runtime dependencies. Any other shared library must be handled as a dependency.
Example Dockerfile for custom Postgres extension (pg_uuidv7)
# syntax=docker/dockerfile:1
ARG SUPABASE_POSTGRES_TAG=17.6.1.136
ARG PG_MAJOR=17
# --- Builder: glibc image matching the Postgres major version ---
FROM postgres:${PG_MAJOR}-bookworm AS builder
ARG PG_MAJOR
ARG EXT_VERSION=v1.7.0
RUN apt-get update && apt-get install -y --no-install-recommends \
build-essential git ca-certificates postgresql-server-dev-${PG_MAJOR} \
&& rm -rf /var/lib/apt/lists/*
WORKDIR /src
RUN git clone --depth 1 --branch ${EXT_VERSION} https://github.com/fboulnois/pg_uuidv7 .
RUN make
# --- Runtime: Supabase Alpine image ---
FROM supabase/postgres:${SUPABASE_POSTGRES_TAG}
USER root
COPY --from=builder /src/pg_uuidv7.so /tmp/
COPY --from=builder /src/pg_uuidv7.control /tmp/
COPY --from=builder /src/sql/ /tmp/ext-sql/
# The running postgres wrapper redirects its module dir via NIX_PGLIBDIR.
# Install the .so there; install control/SQL into pg_config --sharedir.
RUN set -eux; \
PLUGIN_DIR="$(grep -E '^export NIX_PGLIBDIR' /usr/bin/postgres | sed -E "s/.*'([^']*)'.*/\1/")";
SHARE_DIR="$(pg_config --sharedir)/extension"; \
install -m 755 /tmp/pg_uuidv7.so "$PLUGIN_DIR/"; \
install -m 644 /tmp/pg_uuidv7.control "$SHARE_DIR/"; \
install -m 644 /tmp/ext-sql/*.sql "$SHARE_DIR/"; \
rm -rf /tmp/pg_uuidv7.* /tmp/ext-sql
Multi-stage Docker build for custom Postgres extension
Use a two-stage Dockerfile: a glibc builder stage (postgres:<major>-bookworm) to compile the extension, and the Supabase Postgres image as the runtime stage that installs the artifacts into the correct Nix locations.
NIX_PGLIBDIR module directory override
The running postgres in the Supabase image is a wrapper script that overrides its library directory via a NIX_PGLIBDIR environment variable. A compiled .so module must be installed into that directory, not the path that pg_config --pkglibdir reports. The upstream install instructions typically use make install or copy to pg_config --pkglibdir, which will not work for custom extensions on the Supabase image.
Check Supabase Postgres image glibc version
To read the glibc version in a supabase/postgres image, run: docker run --rm --entrypoint sh supabase/postgres:17.6.1.136 -c 'ls -d /nix/store/*glibc-2.*-* 2>/dev/null | grep -oE "glibc-2\.[0-9]+" | sort -uV | tail -1'
glibc version compatibility for custom extensions
Match the Postgres major version and keep the builder's glibc no later than the image's glibc version. Extensions are ABI-stable across an entire Postgres major version, but an extension built against a newer glibc than the runtime provides will fail to load with an error like version 'GLIBC_2.xx' not found. The current Supabase image ships glibc 2.40, so build on Debian 12 (postgres:17-bookworm, glibc 2.36). Avoid the default postgres:17 tag as it uses Debian 13 with glibc 2.41.
Alpine base image uses glibc runtime
The Supabase Postgres base image is Alpine Linux, but the Postgres binaries and bundled extensions live in a /nix store and use glibc runtime, not musl. Extensions must be compiled with glibc, not Alpine's native musl toolchain. Compiling with musl will cause CREATE EXTENSION to fail with an error like libc.musl-aarch64.so.1: cannot open shared object file.
Custom Postgres image disclaimer
A custom image built by layering extensions onto the published supabase/postgres image is unofficial and unsupported. It is not guaranteed to work and may break with future changes to the base image. Testing, quality assurance, and ongoing maintenance are the builder's responsibility.
Pure SQL or trusted procedural language extensions
If an extension is written in pure SQL or a trusted procedural language, you do not need a custom image. Use pg_tle, which is already bundled and preloaded in the Supabase Postgres image. Install extensions through it by running CREATE EXTENSION pg_tle; first.
Supabase Postgres image extension mechanism
The supabase/postgres image includes a curated set of extensions compiled at build time. There is no runtime mechanism to install a compiled native .so extension into a running container. You cannot use apk add or apt-get install to add extension packages because the image is built with Nix and its extension set is fixed when the image is produced.
pg_graphql extension disabled by default in Postgres 17
On a fresh Postgres 17 deployment, the pg_graphql extension is disabled by default. It can be enabled from Studio (Database > Extensions) or with the SQL command: create extension pg_graphql;. Databases that already use GraphQL keep it enabled after an upgrade.
Extensions removed in Postgres 17
The following extensions are not available in Postgres 17 builds and must be dropped before upgrading: timescaledb (not built for Postgres 17), plv8 (not built for Postgres 17), plcoffee (companion to plv8), and plls (companion to plv8). The upgrade script will prompt you to drop any of these if found. None are installed by default in self-hosted Supabase.
Deploy schema changes to remote with supabase db push
After logging in with supabase login and linking your remote project with supabase link, use supabase db push to push your local migration changes to the remote database.
Reset local database to previous version
Use supabase db reset --version <timestamp> to rollback the local database to a specific migration during development. This allows you to keep new schema changes in a single migration file. Do not reset a version that is already deployed to production.
Declarative schemas source of truth
In declarative schema workflows, the files in supabase/schemas/ are the source of truth. The supabase db diff command compares those schema files against your migrations, not the live database. Changes made directly to the database through Studio, the SQL editor, or psql are invisible to the diff and will be silently dropped. Always edit the schema files, then run db diff to generate migrations.
Declarative schema workflow overview
Declarative schemas provide a developer-friendly way to maintain schema migrations. Instead of imperatively specifying how to change the database, you declare the desired state of your database in schema files, and the migration instructions are generated automatically. This keeps related information together in one place rather than scattered across multiple migration files.
Create schema file in supabase/schemas directory
Create SQL files in the supabase/schemas directory to define your database tables and other entities. For example, create supabase/schemas/employees.sql with a CREATE TABLE statement for the employees table.
Generate migration from schema with supabase db diff
Use the command supabase db diff -f <migration_name> to compare your declared schema files against existing migrations and generate a new migration file. The -f flag specifies the migration name.
Apply pending migrations with supabase migration up
Use supabase migration up to apply pending migrations to the local database. This must be run after supabase start to bring the local database schema in sync with your migration files.
Schema diff limitations and caveats
Schema diffs are generated by pg-delta (default) or migra (legacy). Known limitations include: DML statements (insert, update, delete) are not captured; view ownership and security invoker settings are not tracked; materialized view changes are not tracked; views are not recreated when altering column type; RLS policy alter statements are not captured; column privileges are not tracked; schema privileges are not tracked; comments are not tracked; partitions are not tracked; alter publication statements are not tracked; create domain statements are ignored; grant statements may be duplicated from default privileges. Review every generated migration and use versioned migrations for these entities.
Rollback deployed migration by reverting schema files
If you need to rollback a migration that is already deployed to production, first revert the changes to your schema files, then generate a new migration file containing the down migration. This ensures production migrations always roll forward. SQL statements in down migrations are usually destructive and must be reviewed carefully to avoid unintentional data loss.