Monitor table bloat after large deletes
When deleting a large number of rows, the space is not always reclaimed immediately. Rows are marked as deleted but space is not freed. Use the Supabase CLI to monitor table bloat: supabase inspect db bloat
Supabase · Database · all subjects
database
128 notes in this subject, read out of this brain and free to use. This is page 2 of 3.
Reclaim disk space with VACUUM
Postgres' autovacuum process runs automatically to mark deleted rows as reusable after deletion, but may not keep up with large deletes. Trigger a manual vacuum with VACUUM (verbose) logs; to mark tuples as reusable. Use VACUUM FULL to reclaim disk space by rewriting the entire table, but this takes an ACCESS EXCLUSIVE lock and should only be used during maintenance windows.
Find unused indexes with Supabase CLI
Use the Supabase CLI inspect command to identify unused indexes: supabase inspect db index-stats
Example: DROP INDEX
DROP INDEX IF EXISTS idx_users_legacy_field;
Explain query to verify index usage
Use 'explain select ...' to view the query plan and verify whether Postgres is using indexes. Without an index, the output shows 'Seq Scan' with a filter condition.
B-Tree index default in Postgres
By default, Postgres uses B-Tree indexes. A B-Tree is a generalized form of a binary search tree where nodes can have more than two children. This is the most common index type.
Create basic B-Tree index syntax
To create a basic B-Tree index on a table column, use: create index idx_name on table_name (column_name);
Create index concurrently to avoid write locks
Use 'create index concurrently' instead of 'create index' to prevent the table from being locked during writes. This takes longer to build but does not block write operations.
Partial index example
Create a partial index to improve efficiency when frequently querying a subset of rows. Example: create index idx_living_persons_age on persons (age) where deceased is false;
Order index in descending with nulls last
B-Tree indexes sort in ascending order by default, but you can specify descending order with null handling. Example: create index idx_persons_age_desc on persons (age desc nulls last);
Reindex concurrently to rebuild stale indexes
Use 'reindex index concurrently idx_name;' to rebuild a single stale index without locking it. Alternatively, use 'reindex table concurrently table_name;' to rebuild all indexes on a table. Note that 'reindex [index/table] concurrently' cannot be used inside a transaction.
Index overhead - writes and storage
Indexes improve query performance but come with overhead. They require additional writes during data modifications and increase storage requirements. Understanding when to use indexes is important for efficient database design.
Sequential scan vs index lookup complexity
Without an index, Postgres performs a sequential scan with O(n) complexity, scanning every row to find matches. With an index, traversing to locate a value can be done in O(log n) operations, significantly faster on large datasets.
Importance of knowing PostgreSQL version
Each major version of PostgreSQL has different features and may cause breaking changes. You may need to update your schema when upgrading or downgrading to a different major PostgreSQL version.
Check PostgreSQL version with SQL query
To determine which version of PostgreSQL is running, execute the query `select version();` in the SQL Editor in the Supabase Dashboard. The query returns the full version information including the PostgreSQL version number, architecture, compiler details, and bit version. For example: `PostgreSQL 15.1 on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 10.3.0-1ubuntu1~20.04) 10.3.0, 64-bit`. This query can also be executed via psql or any other query editor when connecting directly to the database.
Global level statement timeout
Change the statement timeout for all roles and sessions without an explicit timeout already set using: alter database postgres set statement_timeout TO '4s';
API server roles for database connections
Each Supabase API server uses designated database roles: supabase_admin (used by Realtime and project configuration), authenticator (PostgREST), supabase_auth_admin (Auth), supabase_storage_admin (Storage), supabase_replication_admin (Read Replicas synchronization), postgres (Dashboard and external tools like Prisma, SQLAlchemy, PSQL), and custom roles (external tools).
View current session timeout
Check the current session timeout by executing: SHOW statement_timeout;
Default role timeouts
The default statement timeouts for built-in roles are: anon (3 seconds), authenticated (8 seconds), service_role (none, defaults to authenticator role's 8 second timeout if unset), postgres (none, capped by default global timeout to 2 minutes).
Change role level statement timeout
Set statement timeout for a specific role using: alter role example_role set statement_timeout = '10min'; The timeout value can be expressed in minutes (e.g., '10min') or seconds (e.g., '10s').
Check role timeouts in pg_roles
View the timeout configuration for specific roles by running: select rolname, rolconfig from pg_roles where rolname in ('anon', 'authenticated', 'postgres', 'service_role'); Unlike global settings, role-level timeouts cannot be checked with SHOW statement_timeout.
Session level statement timeout syntax
Set session-level statement timeout with: set statement_timeout = '10min'; Session level settings persist only for the duration of the connection and can only be used with connections through Supavisor in session mode (port 5432) or direct connections. They cannot be used in the Dashboard, with Supabase Client API, or with Supavisor in Transaction mode (port 6543).
Reload PostgREST after role timeout changes
If changing the timeout for Supabase Client API calls, reload PostgREST to reflect the changes by running: NOTIFY pgrst, 'reload config';
Logical replication architecture components
Logical replication involves three key components: a publication (a set of tables on the primary database that will be published), a replication slot (a slot used for replicating data from a single publication, which specifies the output format of changes), and a subscription (created from an external system such as another Postgres database, must specify the publication name, and automatically creates a replication slot if not specified).
Logical replication output formats
Logical replication is typically output in two forms: pgoutput and wal2json. The output method determines how Postgres sends changes to any active replication slot.
Logical replication WAL file management
When using logical replication, Postgres keeps WAL files around longer than it otherwise needs them. If files are removed too soon, the replication slot can become inactive or lost if the database receives a large number of changes in a short time. Various Postgres options and settings can be tweaked to manage WAL usage effectively, though not all settings are user-configurable as they can impact database stability.
Logical replication configuration settings
Postgres configuration settings for logical replication include: max_replication_slots (max count of replication slots allowed, not user-facing), wal_keep_size (minimum size of WAL files to keep for replication, not user-facing), max_slot_wal_keep_size (max WAL size that can be reserved by replication slots, not user-facing), and checkpoint_timeout (max time between WAL checkpoints, not user-facing). Settings marked as not user-facing cannot be modified by users as they can impact database stability.
Realtime versus replication features
Realtime features and replication serve different purposes. Realtime also uses Postgres changes but is intended for broadcasting database updates to clients such as browsers and mobile apps, rather than maintaining a copy of the database in another system as replication does.
Database replication use cases
Database replication is used for analytics and data warehousing to replicate operational databases to analytics platforms without impacting application performance. It is also used for data integration to keep data synchronized across different systems and services. Additionally, it supports operational reporting by maintaining a copy of selected application data for querying in another system.
Three replication methods in Supabase
Supabase supports three replication methods: read replicas (additional Postgres databases kept in sync with primary), Pipelines (managed CDC product for moving data to supported destinations), and manual replication (using Postgres logical replication with third-party tools like Airbyte, Estuary, Fivetran, Materialize, Stitch, or AWS DMS).
Read replicas purpose and use
Read replicas are additional Supabase Postgres databases kept in sync with the primary database. They should be used when you want read-only query capacity, lower latency in another region, or to isolate analytical reads from application writes while staying inside Supabase Postgres.
Pipelines supported destinations
Pipelines currently supports BigQuery as the managed destination. Early access can be requested for ClickHouse, Snowflake, and DuckLake. Managed Pipelines run in AWS eu-central-1 (Frankfurt). Destination resources should be chosen as close as possible to Frankfurt to reduce network latency and replication lag.
BigQuery replication support operations
BigQuery as a Pipelines destination supports Insert, Update, Delete, and Truncate operations. Schema changes are supported but in Beta with limited functionality.
Manual replication configuration
Manual replication uses the same underlying Postgres logical replication features as Pipelines, but you configure and operate the pieces yourself. Use this approach when connecting tools such as Airbyte, Estuary, Fivetran, Materialize, Stitch, AWS DMS, or other systems that support Postgres logical replication.
Write-Ahead Log (WAL) in Postgres
Postgres uses a Write-Ahead Log (WAL) system to manage changes to the database. As changes are made, they are appended to the WAL, which is a series of files called segments with a specifiable file size. Once a segment is full, Postgres starts appending to a new segment. After a period of time, a checkpoint occurs where Postgres synchronizes the WAL with the database. After checkpoint completion, WAL files can be removed from disk to free up space.
Logical replication and WAL relationship
Logical replication is a replication method where Postgres uses WAL files to transmit changes to another Postgres database or to a system that supports reading WAL files.
LSN (Log Sequence Number)
LSN is a Log Sequence Number that identifies a position in the WAL. It is used to determine the progress of replication in subscribers and to calculate the lag of a replication slot.
Materialized views vs conventional views trade-offs
Materialized views execute faster on repeated reads because data is pre-computed and stored, but data can become outdated. Use materialized views when query execution times are too slow (especially for queries with multiple tables and billions of rows) and you can tolerate stale data. Common use-cases are internal dashboards and analytics. Creating a materialized view is not a solution to inefficient queries—you should still optimize slow-running queries even when using a materialized view.
Table creation syntax with identity primary key
To create a table with an auto-incrementing primary key in Postgres: `create table movies (id bigint generated by default as identity primary key, name text, description text);`. Use `generated by default as identity` to allow inserting custom unique values, or `generated always as identity` to always generate the value automatically.
Table naming convention
When naming tables, use lowercase and underscores instead of spaces. Use `table_name` not `Table Name`.
Postgres default data types reference
Postgres supports the following standard data types with aliases: bigint (int8), bigserial (serial8), bit, bit varying (varbit), boolean (bool), box, bytea, character (char), character varying (varchar), cidr, circle, date, double precision (float8), inet, integer (int/int4), interval [fields], json, jsonb, line, lseg, macaddr, macaddr8, money, numeric (decimal), path, pg_lsn, pg_snapshot, point, polygon, real (float4), smallint (int2), smallserial (serial2), serial (serial4), text, time [without time zone], time with time zone (timetz), timestamp [without time zone], timestamp with time zone (timestamptz), tsquery, tsvector, txid_snapshot (deprecated), uuid, xml. The Table Editor in Supabase supports only a subset of these types.
Primary key best practices
It is recommended to create a primary key for every table in your database. A primary key must be unique for every row. Common choices are uuid type or a numbered identity column.
Schema creation and default schema
Tables belong to schemas, which organize tables often for security reasons. If no schema is specified when creating a table, Postgres creates it in the `public` schema by default. Create custom schemas with: `create schema private;`. Tables in custom schemas can be created with: `create table private.salaries (id bigint generated by default as identity primary key, salary bigint not null, actor_id bigint not null references public.actors);`.
Custom schema exposure and API access
To access a custom schema through the Supabase Data API, you must expose it and grant appropriate permissions. See the Supabase documentation on 'Using Custom Schemas' for detailed steps and 'Securing your API' for security best practices around schema exposure.
View creation syntax
Create a view with: `create view transcripts as select students.name, students.type, courses.title, courses.code, grades.result from grades left join students on grades.student_id = students.id left join courses on grades.course_id = courses.id;`. A view is a shortcut to a query that doesn't store new data but executes the underlying query when accessed.
Materialized view creation
A materialized view stores results to disk for faster subsequent reads compared to conventional views. Create with: `create materialized view transcripts as select students.name, students.type, courses.title, courses.code, grades.result from grades left join students on grades.student_id = students.id left join courses on grades.course_id = courses.id;`. Query it the same way as a conventional view: `select * from transcripts;`.
Materialized view refresh
Data in materialized views becomes stale and must be refreshed regularly. Refresh with: `refresh materialized view transcripts;`. Decide how often to refresh based on your use-case and tolerance for outdated data.
View benefits and use-cases
Views provide four main benefits: (1) Simplicity—replace complex repeated queries with a simple view query; (2) Consistency—modify the underlying query once and changes apply everywhere; (3) Logical Organization—well-named views explain their purpose to team members; (4) Security—restrict access to sensitive columns by excluding them from the view.
Grant permissions on views
Grant permissions on views to roles: `grant all on table transcripts to authenticated;`. This example grants all permissions on the transcripts view to the authenticated role.
supabase_admin role
The supabase_admin role is an internal role used by Supabase for administrative tasks such as running upgrades and automations.
Postgres roles vs Row Level Security
Postgres roles are for configuring system access to the database. For application access control, use Row Level Security (RLS) instead. Role-based Access Control (RBAC) can be implemented on top of RLS.
Users vs roles in Postgres
Roles can function as users or groups. Users are roles with login privileges. Groups (role groups) are roles without login privileges, used to manage permissions for multiple users.
Create role syntax
A role is created using: create role "role_name";
Create role with login and password
To create a role with login privileges and password: create role "role_name" with login password 'extremely_secure_password';
Password best practices
For Postgres roles, use a password manager to generate passwords at least 12 characters long, avoiding common dictionary words, and including uppercase, lowercase, numbers, and special symbols.
Changing postgres role password in Supabase
The postgres role password can be updated from the Supabase Dashboard under Database Settings. Password changes do not cause downtime; PostgREST, PgBouncer, and other Supabase managed services automatically update. External services with hardcoded credentials require manual update.
GRANT command for role permissions
Permissions are granted to roles using the GRANT command. Permissions include SELECT, INSERT, UPDATE, and DELETE and can be configured on tables, views, functions, and triggers.
REVOKE permission syntax
Permissions are revoked from roles using: REVOKE permission_type ON object_name FROM role_name;
Role inheritance syntax
To create a role hierarchy where a child role inherits permissions from a parent role: create role "child_role_name" inherit "parent_role_name";
NOINHERIT to prevent role inheritance
To prevent a role from having a child relationship: alter role "child_role_name" noinherit;