Supabase pre-installed extensions count
Supabase is pre-configured with over 50 extensions. Additional extensions can be installed through the database.dev package manager.
Supabase · Database · all subjects
postgres_extensions
70 notes in this subject, read out of this brain and free to use. This is page 1 of 2.
Request new extensions
To request a new Postgres extension for Supabase, add or upvote it in the GitHub Discussion at https://github.com/orgs/supabase/discussions/33754
Enable extension with SQL
To enable a Postgres extension in Supabase, use the SQL command: CREATE EXTENSION extension_name WITH SCHEMA extensions; where 'extensions' is the recommended schema to avoid namespace pollution.
Disable extension with SQL
To disable a Postgres extension in Supabase, use the SQL command: DROP EXTENSION extension_name;
Enable extension through dashboard
Extensions can be enabled or disabled through the Supabase Dashboard by navigating to the Database page, clicking Extensions in the sidebar, and toggling the desired extension.
Extensions schema namespace pollution
Most extensions are installed under the 'extensions' schema which is accessible to public by default. To avoid namespace pollution, do not create other entities in the extensions schema. If you need to restrict user access to extension-managed tables, create a separate schema for installing that specific extension.
Extension-specific schema requirements
Some extensions create their own schema with a specific name and can only be created under that schema. For example, the postgis_tiger_geocoder extension creates a schema named 'tiger'. Before enabling such extensions, ensure you have not created a conflicting schema with the same name.
Upgrade extensions in Supabase
To access new versions of extensions when they become available on Supabase, initiate a software upgrade in the Infrastructure Settings page or restart your server in the General Settings page.
Install custom SQL extensions
In addition to pre-configured extensions, you can install your own SQL extensions directly in the database using Supabase's SQL editor. The SQL code for extensions, including plpgsql extensions, can be added through the SQL editor.
pg_hashids id_encode function
The id_encode function converts a numeric ID into a short hash identifier. For example, id_encode(1) returns 'jR'. This is useful for providing customers with obfuscated identifiers that do not expose sequential database IDs.
pg_hashids usage example
Example showing how to use pg_hashids with an orders table: ```sql create table orders ( id serial primary key, description text, price_cents bigint ); insert into orders (description, price_cents) values ('a book', 9095); select id, id_encode(id) as short_id, description, price_cents from orders; ``` This query returns the sequential ID alongside the encoded short_id (e.g., 'jR' for ID 1), allowing you to give customers the short_id instead of exposing the sequential ID.
pg_hashids extension overview
pg_hashids provides a secure way to generate short, unique, non-sequential IDs from numbers. The hashes are intended to be small, easy-to-remember identifiers that can be used to obfuscate data optionally with a password, alphabet, and salt. It is commonly used to hide data like user IDs, order numbers, or tracking codes in favor of unique identifiers.
Enable pg_hashids extension via SQL
To enable pg_hashids, run: `create extension pg_hashids with schema extensions;`. It is good practice to create the extension within a separate schema like 'extensions' to keep the public schema clean. To disable the extension, use: `drop extension if exists pg_hashids;`.
pg_hashids id_decode function
The id_decode function reverses a short hash identifier back into its original numeric ID. It is the inverse operation of id_encode.
pg_jsonschema validation functions
pg_jsonschema provides two functions for validating data. json_matches_schema(schema json, instance json) checks if a json instance conforms to a JSON Schema schema. jsonb_matches_schema(schema json, instance jsonb) checks if a jsonb instance conforms to a JSON Schema schema. Both functions return a boolean indicating whether the instance matches the schema.
Query pg_jsonschema with select statement
pg_jsonschema utilities are exposed as functions and can be executed with a select statement. Example: select extensions.json_matches_schema(schema := '{"type": "object"}', instance := '{}');
pg_jsonschema extension overview
pg_jsonschema is a Postgres extension that adds the ability to validate Postgres's built-in json and jsonb data types against JSON Schema documents. JSON Schema is a language for annotating and validating JSON documents.
Enable pg_jsonschema with SQL
To enable the pg_jsonschema extension, use the SQL command: create extension pg_jsonschema with schema extensions;. It is good practice to create the extension within a separate schema like extensions to keep the public schema clean. To disable the extension, use: drop extension if exists pg_jsonschema;
pg_jsonschema with check constraints example
pg_jsonschema is generally used in tandem with a check constraint to constrain the contents of a json/b column to match a JSON Schema. Example: create table customer(id serial primary key, metadata json, check (json_matches_schema('{"type": "object", "properties": {"tags": {"type": "array", "items": {"type": "string", "maxLength": 16}}}}', metadata)));. Valid inserts matching the schema are accepted. Invalid inserts that violate the schema produce an error: ERROR: new row for relation "customer" violates check constraint.
Enable pg_stat_statements extension via SQL
To enable pg_stat_statements, run: create extension pg_stat_statements with schema extensions; To disable, run: drop extension if exists pg_stat_statements; It is good practice to create the extension within a separate schema (like extensions) to keep the public schema clean.
pg_stat_statements common use case
pg_stat_statements is commonly used to track down expensive or slow queries by examining the statistics in the pg_stat_statements view, which contains a row for each executed query with statistics inlined. These statistics help identify queries to optimize or index.
pg_stat_statements extension overview
pg_stat_statements is a Postgres extension that exposes a view to track statistics about SQL statements executed on the database, including planning and execution statistics.
pg_stat_statements view columns
The pg_stat_statements view contains the following columns: userid (oid, references pg_authid.oid, OID of user who executed the statement), dbid (oid, references pg_database.oid, OID of database in which statement was executed), toplevel (bool, true if query was executed as top-level statement, always true if pg_stat_statements.track is set to top), queryid (bigint, hash code to identify identical normalized queries), query (text, text of a representative statement), plans (bigint, number of times statement was planned, zero if pg_stat_statements.track_planning is not enabled), total_plan_time (double precision, total time spent planning in milliseconds, zero if track_planning not enabled), min_plan_time (double precision, minimum time spent planning in milliseconds, zero if track_planning not enabled).
Query expensive queries with pg_stat_statements
Example SQL to identify frequently executed and slow queries: select calls, mean_exec_time, max_exec_time, total_exec_time, stddev_exec_time, query from pg_stat_statements where calls > 50 and mean_exec_time > 2.0 and total_exec_time > 60000 and query ilike '%user_in_organization%' order by calls desc; This filters for queries with at least 50 calls, averaging at least 2ms per call, at least one minute total server time spent, and matching a table pattern.
pg_repack CLI command syntax
All pg_repack commands should include the -k flag to skip the client-side superuser check. The syntax is: pg_repack -k [OPTION]... [DBNAME]
pg_repack limitations
pg_repack cannot reorganize temporary tables. pg_repack cannot cluster tables by GiST indexes. DDL commands cannot be performed on target tables except VACUUM or ANALYZE while pg_repack is working; pg_repack holds an ACCESS SHARE lock on the target table to enforce this restriction.
pg_repack extension overview
pg_repack is a Postgres extension to remove bloat from tables and indexes and optionally restore the physical order of clustered indexes. Unlike CLUSTER and VACUUM FULL, pg_repack runs online and does not hold exclusive locks on processed tables, allowing ongoing database operations. Its efficiency is comparable to using CLUSTER directly.
pg_repack optimization methods
pg_repack provides four methods to optimize physical storage: Online CLUSTER for ordering table data by cluster index in a non-blocking way, ordering table data by specified columns, Online VACUUM FULL for packing rows in a non-blocking way, and rebuild or relocation of table indexes only.
pg_repack requirements
A target table must have a PRIMARY KEY or a UNIQUE total index on a NOT NULL column. Performing a full-table repack requires free disk space about twice as large as the target table and its indexes combined.
pg_repack superuser requirement on Supabase
pg_repack requires the Postgres superuser role by default, which is not available to users on the Supabase platform. To avoid this requirement, use the -k or --no-superuser-check flags on every pg_repack CLI command.
pg_repack minimum version for non-superuser support
The first version of pg_repack with full support for non-superuser repacking is 1.5.2. Check your version with: select default_version from pg_available_extensions where name = 'pg_repack';. If pg_repack is not present or version is less than 1.5.2, upgrade to the latest version of Supabase to gain access.
Enable pg_repack extension in Supabase
To enable pg_repack in Supabase, go to the Database page in the Dashboard, click on Extensions in the sidebar, search for 'pg_repack', and enable the extension. Alternatively, run: create extension pg_repack with schema extensions;
pg_repack example command
Example: perform an online VACUUM FULL on tables public.foo and public.bar in the database postgres: pg_repack -k -h db.<PROJECT_REF>.supabase.co -p 5432 -U postgres -d postgres --no-order --table public.foo --table public.bar
PGroonga extension overview
PGroonga is a Postgres extension that adds full text search indexing based on Groonga. While native Postgres supports full text indexing, it is limited to alphabet and digit based languages. PGroonga offers wider character support making it viable for multilingual full text search including languages like Japanese and Chinese.
Enable PGroonga extension with SQL
To enable PGroonga, run: create extension pgroonga with schema extensions; To disable it, run: drop extension if exists pgroonga;
Enable PGroonga via Dashboard
To enable PGroonga through the Supabase Dashboard: Go to the Database page, click on Extensions in the sidebar, search for 'pgroonga' and enable the extension.
Create PGroonga full text search index
To create a full text search index on a text column using PGroonga, use the syntax: create index ix_memos_content ON memos USING pgroonga(content); Replace the table name, index name, and column name as appropriate.
PGroonga full text search operator
The &@~ operator performs full text search in PGroonga. It returns any matching results and is case-insensitive, unlike the LIKE operator. Example: select * from memos where content &@~ 'groonga';
PGroonga match any search words
To find rows where content contains ANY of the specified words using PGroonga, use uppercase OR in the &@~ operator. Example: select * from memos where content &@~ 'postgres OR pgroonga'; returns rows containing either 'postgres' or 'pgroonga'.
PGroonga search with negation
To find rows where content contains a word but excludes another using PGroonga, use the minus symbol (-) before the excluded word. Example: select * from memos where content &@~ 'postgres -pgroonga'; returns rows containing 'postgres' but not 'pgroonga'.
Postgres query planner and index usage
For very small tables, Postgres query planner may choose to scan the entire table instead of using an index, as it is faster. To force index usage during testing, disable sequential scans with: set enable_seqscan = off; Note: this should not be done in production.
pg_stat_statements extension requirements
The following inspection commands require pg_stat_statements to be enabled: calls, locks, cache-hit, blocking, unused-indexes, index-usage, bloat, outliers, table-record-counts, replication-slots, seq-scans, vacuum-stats, and long-running-queries. The pg_stat_statements extension only stores the latest 5,000 statements. After optimizing queries, reset the analysis by running `select pg_stat_statements_reset();`
pg_stat_statements enabled by default in Supabase
Every Supabase project has the pg_stat_statements extension enabled by default. This extension records query execution performance details and is the best way to find inefficient queries.
OrioleDB overview and purpose
OrioleDB is a Postgres extension that provides a drop-in replacement storage engine for the default heap storage method. It is designed to improve Postgres' scalability and performance by removing bottlenecks in the shared memory cache under high concurrency and optimizing write-ahead-log (WAL) insertion through row-level WAL logging.
OrioleDB index-organized tables
OrioleDB uses index-organized tables where table data is stored in the index structure. This design eliminates the need for separate heap storage, reduces overhead, and improves lookup performance for primary key queries.
OrioleDB current limitations
OrioleDB is in active development with certain limitations. Currently, only B-tree indexes are supported, so features like pg_vector's HNSW indexes are not yet available. An Index Access Method bridge to unlock support for all index types used with heap storage is under active development.
OrioleDB performance benchmark
OrioleDB demonstrates a 3.3x speedup compared to the default Postgres heap method on the TPC-C benchmark (warehouses = 500) when tested on a c7g.metal instance.
OrioleDB buffer management
OrioleDB bypasses Postgres's shared buffer pool using direct links between in-memory pages and storage pages. This eliminates the associated complexity and contention in buffer mapping and allows OrioleDB to implement no buffer mapping.
OrioleDB MVCC implementation with undo log
OrioleDB implements Multi-Version Concurrency Control (MVCC) using an undo log. The undo log stores previous row versions and transaction information, which enables consistent reads while removing the need for table vacuuming completely.
OrioleDB copy-on-write checkpoints
OrioleDB implements copy-on-write checkpoints to persist data efficiently. This approach writes only modified data during a checkpoint, reducing the I/O overhead compared to traditional Postgres checkpointing and allowing row-level WAL logging.
Creating OrioleDB project in Supabase
To get started with OrioleDB in Supabase, create a new Supabase project and choose 'OrioleDB Public Alpha' as the Postgres version. In the Supabase OrioleDB image, the default storage method has been updated to use OrioleDB, granting better performance out of the box.
Creating tables with OrioleDB
To create a table using the OrioleDB storage engine, execute the standard CREATE TABLE statement. By default, it will create a table using the OrioleDB storage engine.
OrioleDB primary key requirement
OrioleDB tables always have a primary key. If a primary key is not defined explicitly, a hidden primary key is created using the ctid column.
Creating indexes in OrioleDB
You can create secondary indexes on OrioleDB tables using the standard CREATE INDEX statement. However, only B-tree indexes are currently supported.
Data manipulation in OrioleDB
You can query and modify data in OrioleDB tables using standard SQL statements, including SELECT, INSERT, UPDATE, DELETE, and INSERT ... ON CONFLICT.
OrioleDB table creation example
Example of creating an OrioleDB table: CREATE TABLE blog_post (id int8 not null, title text not null, body text not null, author text not null, published_at timestamptz not null default CURRENT_TIMESTAMP, views bigint not null, primary key (id));
OrioleDB index creation examples
Example of creating indexes on an OrioleDB table: CREATE INDEX blog_post_published_at ON blog_post (published_at); CREATE INDEX blog_post_views ON blog_post (views) WHERE (views > 1000);
OrioleDB data manipulation example
Example of inserting and querying data in an OrioleDB table: INSERT INTO blog_post (id, title, body, author, views) VALUES (1, 'Hello, World!', 'This is my first blog post.', 'John Doe', 1000); SELECT * FROM blog_post ORDER BY published_at DESC LIMIT 10;
OrioleDB EXPLAIN query plans
You can view execution plans for OrioleDB tables using the standard EXPLAIN statement. OrioleDB typically uses index scans for efficient query execution, as shown in plans that may use Index Scan Backward for reverse ordering and Index Scan for primary key lookups.
Refresh subscription after adding tables to publication
When adding more tables to an existing publication, you must run REFRESH SUBSCRIPTION on the subscribing database for the changes to take effect. Refer to PostgreSQL documentation at https://www.postgresql.org/docs/current/sql-alterpublication.html for details.