Search documentation

Search for a topic, a control, or an event type.

    Docs / Databases / Extensions and performance

    Extensions and performance

    Add vector and text search, build indexes and inspect query plans. Owners can install 12 vetted PostgreSQL extensions.

    Extensions

    To store embeddings for similarity search, run this migration as a database Owner. It installs pgvector and creates documents with a 1536-dimensional embedding column. Start without an existing documents table. Each stored embedding must have 1536 numbers.

    Install pgvector and store embeddings
    CREATE EXTENSION vector;
    
    CREATE TABLE documents (
      id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      title text NOT NULL,
      embedding vector(1536) NOT NULL
    );

    An extension adds types, functions, operators, and index methods to PostgreSQL. An Owner installs one with CREATE EXTENSION in a migration. CREATE EXTENSION can share a migration with other statements, so one migration can install vector and create the table that uses it. OAIZ installs only the 12 extensions below, each into a schema named extensions.

    Extensions an Owner can install
    ExtensionWhat it adds
    btree_ginGIN index support for ordinary types such as integers and text, so one GIN index can cover them beside array or JSONB columns.
    btree_gistGiST index support for ordinary types such as integers, text, and timestamps, so one GiST index can cover them beside range columns.
    citextA case-insensitive text type. A unique citext email column treats Ada@example.com and ada@example.com as the same value.
    fuzzystrmatchSoundex, Metaphone, and Levenshtein distance functions for comparing spellings.
    hstoreA type that stores a set of text keys and values in one column.
    intarrayFunctions, operators, and GIN and GiST index support for arrays of integers.
    ltreeA type for hierarchical labels such as Top.Science.Astronomy, with the lquery and ltxtquery pattern types.
    pg_trgmText similarity by trigrams, with the similarity() function, the % operator, and GIN and GiST index support for LIKE, ILIKE, and similarity searches.
    pgcryptoHashing, password hashing, random bytes, and encryption functions such as digest(), crypt(), and gen_random_bytes().
    unaccentThe unaccent() function, which removes accents from text, and the immutable_unaccent() function that OAIZ adds for generated columns and index expressions.
    uuid-osspUUID functions such as uuid_generate_v4(). uuid_generate_v1() is not available, because its values carry the server’s network address.
    vectorpgvector. The vector, halfvec, and sparsevec types, distance operators, and HNSW indexes for nearest-neighbour search.

    Write extension types and functions without a schema prefix. Names resolve in the data schema first and then in extensions, so a table, type, or function of the same name in data takes precedence. You cannot create objects in the extensions schema.

    HNSW is the supported vector index. USING ivfflat is refused with storage-vector-index-refused. Build an HNSW index with CREATE INDEX CONCURRENTLY in a migration of its own. The build reads the whole table, and inserts, updates, and deletes on the table continue while it runs. Another schema change to the same table fails after waiting 3 seconds for the build.

    Build an HNSW index without blocking writes
    CREATE INDEX CONCURRENTLY documents_embedding_hnsw ON documents USING hnsw (embedding vector_cosine_ops);

    A nearest-neighbour query orders rows by distance. The <=> operator is cosine distance, <-> is Euclidean distance, and <#> is negative inner product. Pass the query vector as a parameter. In the body below, replace QUERY_EMBEDDING with the search text’s embedding: a vector literal in square brackets, such as [0.012, -0.034, 0.051], with one number for each of the column’s 1536 dimensions. Send the body below to POST https://api.oaiz.io/storage/databases/DATABASE_ID/query, or pass it to oaiz db query DATABASE_ID with --body. When row rules filter the table, the search keeps reading the index until it fills the LIMIT or reaches hnsw.max_scan_tuples.

    Query body for the 10 nearest documents
    {
      "sql": "SET LOCAL hnsw.ef_search = 100; SELECT id, title FROM documents ORDER BY embedding <=> $1::vector LIMIT 10",
      "params": ["QUERY_EMBEDDING"]
    }

    Put SET LOCAL lines before one query or write to tune that transaction. The query and execute APIs, CLI, query editor, Query Database and Write to Database steps, and Storage Query and Storage Execute tools accept the same SQL. Use = or TO with an unquoted numeric literal, once per name. Quoted strings, expressions, parameters, DEFAULT, other names and other SET or RESET forms are refused with storage-customer-sql-local-refused. A SET LOCAL line after the main statement, or without a main statement, is also refused. To apply a setting on every call of a function, write it in CREATE FUNCTION instead, such as SET hnsw.ef_search = 100.

    hnsw.ef_search
    A whole number from 1 to 1000. The number of candidates an HNSW search keeps. Set it at or above the LIMIT.
    hnsw.max_scan_tuples
    A whole number from 1 to 100000. The most index entries a filtered HNSW search reads. PostgreSQL uses 20000 when unset.
    pg_trgm.similarity_threshold
    A number from 0 to 1. The similarity the % operator requires.
    pg_trgm.word_similarity_threshold
    A number from 0 to 1. The similarity the <% and %> operators require.
    pg_trgm.strict_word_similarity_threshold
    A number from 0 to 1. The similarity the <<% and %>> operators require.

    With pg_trgm installed, a GIN trigram index supports similarity searches on text. This migration adds one to a customers table with a name column, and the query below returns names that look like the parameter. When a row rule filters reads on the table, the %, LIKE, and ILIKE conditions cannot use the trigram index, because PostgreSQL does not classify them as leakproof. A btree index on the rule’s own selective condition can still help.

    Index names for similarity search
    CREATE EXTENSION pg_trgm;
    
    CREATE INDEX customers_name_trgm ON customers USING gin (name gin_trgm_ops);
    Query body for names similar to a search term
    {
      "sql": "SET LOCAL pg_trgm.similarity_threshold = 0.4; SELECT id, name, similarity(name, $1) AS score FROM customers WHERE name % $1 ORDER BY score DESC LIMIT 20",
      "params": ["Acme Incorporated"]
    }

    citext and unaccent change how text compares. A citext column matches values without regard to letter case. unaccent() is not allowed in a generated column or an index expression, so use immutable_unaccent(), which returns the same text.

    Case-insensitive emails and accent-free names
    CREATE EXTENSION citext;
    CREATE EXTENSION unaccent;
    
    CREATE TABLE contacts (
      id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      email citext NOT NULL UNIQUE,
      name text NOT NULL,
      folded_name text GENERATED ALWAYS AS (lower(immutable_unaccent(name))) STORED
    );
    
    CREATE INDEX contacts_folded_name ON contacts (folded_name);

    Indexes

    Choose B-tree for equality, range filters and ORDER BY on ordinary columns. It is the default index method. This Owner migration indexes status on an existing customers table, for queries that filter by status. Indexes use disk space and add work to writes. A table scan can cost less for a small table or a filter that matches most rows.

    Index customer status with B-tree
    CREATE INDEX customers_status_idx ON customers USING btree (status);

    Choose GIN with pg_trgm for text similarity and LIKE or ILIKE patterns such as a word within a name. The name-search example in Extensions installs it. Choose HNSW for approximate nearest-neighbour search over embeddings, using the distance operator that matches the index, such as <=> with vector_cosine_ops. The vector example in Extensions builds that index. Read-filtering row rules can prevent GIN index use, as described there. Use Inspect query plans to check the plan for your query.

    Batched writes

    Send several rows in one multi-row INSERT or UPSERT to avoid one request per row. This single execute statement inserts two customers, or updates their names and statuses when their IDs already exist. It needs an existing customers table with id uuid PRIMARY KEY, name text and status text, and Edit access with row rules that allow the writes. Bind $1 through $6 to the first UUID, name and status, then the second UUID, name and status. Use distinct IDs.

    Upsert two customers in one request
    INSERT INTO customers (id, name, status)
    VALUES ($1, $2, $3), ($4, $5, $6)
    ON CONFLICT (id) DO UPDATE
    SET name = EXCLUDED.name, status = EXCLUDED.status
    RETURNING id, name, status

    For UPDATE or DELETE, select the whole set with a WHERE condition or a join to input rows, rather than sending one request per row. Each execute call accepts one INSERT, UPDATE or DELETE statement. Separate calls commit separately. Keep batches within the statement, parameter and result limits in Limits, events and backups. Row rules still apply.

    Extension and index limits

    Installing
    CREATE EXTENSION accepts IF NOT EXISTS and WITH SCHEMA extensions. An extension outside the list, a VERSION, CASCADE, or another schema is refused with storage-extension-refused, and the message lists the extensions a migration can install. A migration cannot run ALTER EXTENSION or DROP EXTENSION.
    Refused functions
    pg_trgm’s set_limit() changes a setting for the whole connection, and uuid-ossp’s uuid_generate_v1() embeds the server’s network address. A query, write, or migration that calls either is refused. Use the pg_trgm.similarity_threshold setting, and gen_random_uuid() or uuid_generate_v4() for new UUIDs.
    Keys in pgcrypto calls
    Pass keys and passwords to pgcrypto functions such as pgp_sym_encrypt() and crypt() as params, not in the SQL text. PostgreSQL can write the text of a failed statement to the server log.
    Vectors in row events
    Vector values count toward row-event image size limits. A write can still commit when images are omitted. Its event then contains table and operation groups with counts. Limits, events and backups explains the omission rules.
    Concurrent index builds
    CREATE INDEX CONCURRENTLY must be the only statement in its migration and must name its index. A migration that adds another statement is refused with storage-schema-concurrent-index-not-alone. DROP INDEX CONCURRENTLY is refused, and DROP INDEX works in any migration.
    Build time
    A concurrent build stops after 15 minutes. It waits for transactions already running in the database before it reads the table, and it keeps running when the caller disconnects. oaiz db migration apply waits 60 seconds by default, and an apply that builds an index can take up to 19 minutes, so pass --timeout 20m to wait for it. When the caller stops waiting first, apply the same named migration again until the answer has alreadyApplied set. One concurrent build runs in a database at a time. While it runs, the same named migration fails at once with storage-migration-in-progress, and any other concurrent build fails at once with storage-schema-statement-failed. Both change nothing, so apply again later. When the build left the index invalid, the next apply fails with storage-schema-index-invalid instead. When a build finished but its record was lost, the next apply finds the valid index of the name, defined as its statement defines it, and records it. A build holds up to two of the few server connections your database shares with queries and writes for as long as it runs.
    Invalid indexes
    A build that fails or reaches the 15-minute limit after it has created its index leaves the index in the database as invalid. No query uses it. The migration fails with storage-schema-index-invalid and is not recorded. The message says why the build failed, such as a duplicate value under a unique index, so fix that first. The table’s invalidIndexes field in the schema lists the index. Remove it with DROP INDEX in one migration, then apply the build again. An index whose build is still running is also listed in invalidIndexes until the build finishes, so wait for the build before you drop it. A build stopped at the limit before it created its index fails with storage-schema-migration-deadline-exceeded and leaves no index.
    Response for a unique index build over duplicate values
    {
      "message": "conflict: the index shipments_reference_key was left invalid by a failed concurrent build (sqlstate 23505: could not create unique index \"shipments_reference_key\"); DROP INDEX shipments_reference_key removes it",
      "slug": "storage-schema-index-invalid"
    }

    Inspect query plans

    Use oaiz db query to run EXPLAIN for a SELECT. The account needs effective Owner access because a plan can disclose rows hidden by row rules. Owner access can come from a direct grant, a team, or organisation Administrator access.

    EXPLAIN shows how PostgreSQL plans to run the query. Adding ANALYZE executes the SELECT and reports actual timings and row counts. BUFFERS reports buffer use. This example uses a deals table with id, name, amount and stage columns, as in Query from TypeScript and binds the stage value through $1. Replace DATABASE_ID with your database ID.

    Inspect an executed SELECT
    oaiz db query DATABASE_ID \
      --sql 'EXPLAIN (ANALYZE, BUFFERS) SELECT id, name, amount FROM data.deals WHERE stage = $1 ORDER BY amount DESC' \
      --body '{"params":["open"]}'

    The plan describes the SQL as written. Ordinary SELECT queries run inside a LIMIT 1001 wrapper to enforce the result limit, which can change the plan. EXPLAIN runs without that wrapper. Query Database workflow actions and database tools continue to accept SELECT only.