database features & extensions

26 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Where clause filtering with HNSW indexes

Adding a where clause to a vector query does not bypass the HNSW index. The Postgres planner decides whether to use the index or sequential scan based on filter selectivity and table size. When the index is used, the filter is applied as the index returns candidates.

HNSW filtering trade-off with selective filters

When a filter is selective, an HNSW scan returns the top k rows by distance, but if most are filtered out you can end with fewer rows than your LIMIT. From pgvector 0.8.0, the planner supports iterative index scans that automatically scan more of the index until enough results are found.

hnsw.iterative_scan GUC parameter

The hnsw.iterative_scan GUC controls iterative index scans for HNSW in pgvector 0.8.0+. Default is off. Two enabled modes: (1) strict_order preserves exact distance ordering across iterations, (2) relaxed_order allows slight reordering across iterations for better recall.

HNSW iterative scan limits: hnsw.max_scan_tuples and hnsw.scan_mem_multiplier

Two parameters bound how far HNSW iterative scans go: hnsw.max_scan_tuples with default 20,000 and hnsw.scan_mem_multiplier with default 1.

HNSW algorithm for approximate nearest neighbor search

HNSW is an algorithm for approximate nearest neighbor search that can improve performance when querying highly-dimensional vectors like embeddings.

Create HNSW index for Euclidean L2 distance

To create an HNSW index using Euclidean L2 distance, use: create index on items using hnsw (column_name vector_l2_ops);

Create HNSW index for inner product distance

To create an HNSW index using inner product distance, use: create index on items using hnsw (column_name vector_ip_ops);

Create HNSW index for cosine distance

To create an HNSW index using cosine distance, use: create index on items using hnsw (column_name vector_cosine_ops);

pgvector 0.7.0+ maximum dimensions for HNSW indexes

For pgvector versions 0.7.0 and above, HNSW indexes support the following maximum dimensions: vector type up to 2,000 dimensions, halfvec type up to 4,000 dimensions, bit type up to 64,000 dimensions.

HNSW index for high-dimensional vectors with halfvec

For vectors with more than 2,000 dimensions, use the halfvec type to create HNSW indexes. Example with 3,072 dimensions: CREATE TABLE documents (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, content text, embedding vector(3072)); CREATE INDEX ON documents USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);

HNSW hierarchical structure based on skip lists

The hierarchical aspect of HNSW builds on skip lists, which are multi-layer linked lists. The bottom layer connects all ordered elements, and each layer above removes some elements based on a fixed probability, producing a sparser subsequence. This enables O(log n) average complexity for search and insertion/deletion.

Navigable Small World in HNSW

A Navigable Small World (NSW) is a proximity graph where each node connects to nearby neighbors plus long-range links. The long-range connections support the small world property, allowing almost any node to reach any other within a few hops. The navigable property enables logarithmic scaling of greedy search algorithms.

HNSW combines hierarchical and navigable small world concepts

HNSW combines hierarchical skip lists with Navigable Small World graphs. The bottom layer consists of NSW with short links between nodes, while each layer above creates longer links between nodes further away. Search starts at the top layer and descends, using multi-dimensional distance measures like Euclidean distance instead of scalar comparison.

When to create HNSW indexes

HNSW should be your default choice for vector indexes. Create the index when you don't need 100% accuracy and are willing to trade a small amount of accuracy for significant throughput gains.

HNSW indexes safe to build immediately after table creation

Unlike IVFFlat indexes, HNSW indexes are safe to build immediately after a table is created. HNSW indexes are based on graphs which are not affected by the same limitations as IVFFlat. As new data is added to the table, the index fills automatically and the index structure remains optimal.

How to check pgvector version

You can check your current pgvector version by running the SQL query `SELECT * FROM pg_extension WHERE extname = 'vector';` or by navigating to the Extensions tab in your Supabase project dashboard.

Postgres database in every Supabase project

Every Supabase project includes a full Postgres database.

Vector embeddings storage capability

Supabase supports storing vector embeddings right next to the rest of your data in the Postgres database.

Database webhooks feature

Supabase provides database webhooks to send database changes to any external service.

Supabase Vault for secrets and encryption

Supabase Vault is a Postgres extension that allows you to encrypt sensitive data and store secrets.

Database replication with Supabase Pipelines

Supabase Pipelines allows automatic replication of your database to destination systems like data warehouses and analytics platforms.

Change Hibernate default schema from public

By default Hibernate creates tables in the public schema, which Supabase exposes as a data API. It is recommended to change this. Create a custom schema like 'app' by running 'create schema if not exists app;' in the SQL Editor, then point Hibernate to it by setting spring.jpa.properties.hibernate.default_schema=app in application.properties.

Replace ddl-auto with database migrations before production

For production deployments, replace spring.jpa.hibernate.ddl-auto with explicit database migrations instead of auto schema generation.

Extensions deprecated in Postgres 17

Projects upgrading from Postgres 15 to Postgres 17 must disable these extensions in the Supabase Dashboard before upgrade: plcoffee, plls, plv8, timescaledb, and pgjwt. pgjwt was enabled by default on every Supabase project until Postgres 17; if you weren't explicitly using it, it is safe to disable. Existing projects on lower Postgres versions are not impacted; these extensions continue to be supported on Postgres 15 projects until end of life.

pg_cron historical records must be cleaned before upgrade

pg_cron does not automatically clean up historical records. Large cron.job_run_details tables can lead to instantaneous disk pressure and upgrade failures when the table is duplicated during the Supabase project upgrade process. Clean unnecessary records from the cron.job_run_details table before upgrade. During upgrade, pg_cron gets dropped and recreated, and the cron.job_run_details table is duplicated to avoid losing historical logs.

pg_graphql 1.6.0 disables introspection by default

Starting with pg_graphql 1.6.0, GraphQL introspection is disabled by default. 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 like graphql-codegen, Relay compiler, and any tool calling __schema or __type directly. Regular data queries like accountCollection and insertIntoAccountCollection are not affected. Enable introspection by running: comment on schema public is e'@graphql({"introspection": true})'; If your schema has other directives, combine the keys: comment on schema public is e'@graphql({"inflect_names": true, "introspection": true})'; Verify with: select graphql.resolve('{ __schema { queryType { name } } }');

Give your agent this brain