PostgreSQL Extensions & Specialized Features - Complete Notes

An in-depth, practical and interview-focused guide to the PostgreSQL extension lifecycle, trigram fuzzy matching, full-text search, PostGIS spatial queries, and pgvector similarity search.

00. The specialized-feature mental model

PostgreSQL is more than a fixed set of SQL features. Its extension system can install a coherent package of types, functions, operators, casts, and index support into one database, while built-in subsystems such as full-text search use the same extensible ideas.

01. What an extension is

An extension is a database-level package whose member objects and installed version PostgreSQL tracks as one logical unit.

The server installation supplies an extension control file, SQL installation and update scripts, and sometimes a native shared library. CREATE EXTENSION runs the appropriate script in the current database and records every member object. A package can contain types, functions, operators, operator classes, casts, views, tables, text-search objects, and more.

SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
WHERE name IN ('pg_trgm', 'postgis', 'vector')
ORDER BY name;

SELECT name, version, installed, superuser, trusted, relocatable, schema
FROM pg_available_extension_versions
WHERE name = 'pg_trgm'
ORDER BY version;

SELECT e.extname, e.extversion, n.nspname AS target_schema, r.rolname AS owner
FROM pg_extension AS e
JOIN pg_namespace AS n ON n.oid = e.extnamespace
JOIN pg_roles AS r ON r.oid = e.extowner
ORDER BY e.extname;

02. Create, update, move, and drop

Treat an extension version as application infrastructure: discover it, install deliberately, validate it, upgrade through supported paths, and remove it only after inspecting dependents.

CREATE SCHEMA extensions;
REVOKE CREATE ON SCHEMA extensions FROM PUBLIC;

CREATE EXTENSION pg_trgm
  WITH SCHEMA extensions
  VERSION '1.6';

ALTER EXTENSION pg_trgm UPDATE;
ALTER EXTENSION pg_trgm UPDATE TO '1.6';

-- Only succeeds when the extension declares itself relocatable.
ALTER EXTENSION pg_trgm SET SCHEMA extensions;

DROP EXTENSION pg_trgm;
-- CASCADE also drops objects outside the extension that depend on its members.
DROP EXTENSION pg_trgm CASCADE;

IF NOT EXISTS prevents a duplicate-name error, but it does not prove that the installed package has the desired version, schema, ownership, or definition. Migration tooling should query and assert the resulting state. CREATE EXTENSION ... CASCADE may install missing prerequisite extensions recursively at their default versions, which is convenient but less explicit than a reviewed dependency plan.

1. Verify the exact package and versions available on every environment.
2. Create a secure target schema when the package permits schema selection.
3. Install with an explicit version when reproducibility matters.
4. Grant only the application privileges needed on the installed objects.
5. Record and test a supported update path before changing server packages.
6. Run the update in a maintenance window appropriate to its locks and data rewrites.
7. Validate the version, dependent objects, plans, and application behavior afterward.

Dependencies, dump, and restore

Objects that use an extension type or function depend on that member. Dropping without CASCADE reports blockers. Dropping with CASCADE can remove application columns, indexes, views, or functions, so read the dependency report rather than adding CASCADE reflexively. A dump normally emits extension creation plus application objects; the destination needs compatible extension files before restore.

SELECT pg_describe_object(classid, objid, objsubid) AS member
FROM pg_depend
WHERE refclassid = 'pg_extension'::regclass
  AND refobjid = (SELECT oid FROM pg_extension WHERE extname = 'vector')
  AND deptype = 'e'
ORDER BY 1;

-- Ask PostgreSQL to report dependents safely. Run in a transaction and roll back.
BEGIN;
DROP EXTENSION vector;
ROLLBACK;

03. Privileges and extension security

Installing code into the database is a supply-chain and privilege decision, not only a SQL convenience.

Normally, the installer needs the privileges required by every command in the installation script, which often means superuser. A control file can mark an extension as trusted. Then a non-superuser with CREATE on the database may install it even when its script needs elevated execution. Trusted describes an explicitly authored installation model; it does not mean every extension from any source is safe.

• Use packages from a reviewed source and pin artifacts or image versions.
• Review release notes, update scripts, native-code provenance, and supported PostgreSQL versions.
• Install into a schema where untrusted roles cannot create objects.
• Do not put writable schemas before trusted schemas in privileged users' search paths.
• Schema-qualify privileged function and operator references when practical.
• Separate the migration owner from the runtime application role.
• Grant runtime roles only USAGE, EXECUTE, and table rights they need.
• Remember that native extensions run inside the database server process.

04. pg_trgm and trigram thinking

pg_trgm compares text by the three-character groups it contains, making it useful for fuzzy spelling, partial strings, and indexed pattern matching.

PostgreSQL pads each word and extracts trigrams after ignoring non-word characters. Strings that share many trigrams receive a higher score. This is character similarity, not linguistic meaning: automobile and car are conceptually related but share few characters.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT show_trgm('PostgreSQL');
SELECT similarity('postgres', 'postgress');          -- whole strings
SELECT word_similarity('post', 'PostgreSQL guide'); -- best continuous extent
SELECT strict_word_similarity('post', 'Post guide');

GIN, GiST, and indexed patterns

CREATE INDEX customers_name_trgm_gin
ON customers USING gin (full_name gin_trgm_ops);

CREATE INDEX products_name_trgm_gist
ON products USING gist (name gist_trgm_ops(siglen=32));

SELECT customer_id, full_name, similarity(full_name, 'jon smth') AS score
FROM customers
WHERE full_name % 'jon smth'
ORDER BY score DESC, customer_id
LIMIT 20;

SELECT product_id, name, name <-> 'wireles hedphones' AS distance
FROM products
ORDER BY name <-> 'wireles hedphones', product_id
LIMIT 10;

SELECT product_id, name
FROM products
WHERE name ILIKE '%headphone%';

Both trigram operator classes support similarity and can accelerate LIKE, ILIKE, regular-expression, and equality searches when the pattern yields usable trigrams. GIN is commonly strong for filtering many matches. GiST supports distance-ordered nearest-neighbor queries, and its signature length trades index precision against index size. Always compare on representative data and the exact query shape.

• A pattern with no extractable trigram can degenerate into scanning many index entries.
• Very short queries have little trigram evidence and often produce weak selectivity.
• Leading-wildcard patterns can benefit, unlike an ordinary B-tree prefix search.
• Expression indexes must match the query expression, such as lower(name).
• GIN and GiST add write, WAL, cache, vacuum, and storage costs.
• Use a deterministic tie-breaker after a similarity or distance expression.

Thresholds and a robust fuzzy-search pattern

BEGIN;
SET LOCAL pg_trgm.similarity_threshold = 0.42;

SELECT id, display_name, similarity(display_name, $1) AS score
FROM people
WHERE display_name % $1
ORDER BY score DESC, id
LIMIT 25;
COMMIT;

Lower thresholds improve recall but produce more candidates and false positives. Higher thresholds improve precision but miss more variations. Tune using labeled examples from the real language and name distribution. Do not change a global threshold casually, because the same operator can then mean something different across requests. A useful application often combines exact normalized equality, prefix matching, and fuzzy fallback in that priority order.

05. Full-text search pipeline

PostgreSQL full-text search turns documents and queries into the same normalized lexeme language, then matches those representations.

1. A parser splits input into token types such as words, numbers, URLs, and spaces.
2. A text-search configuration maps each token type to an ordered dictionary list.
3. The first dictionary that recognizes a token emits normalized lexemes or removes a stop word.
4. to_tsvector stores sorted lexemes, positions, and optional weights.
5. A query constructor produces a normalized tsquery.
6. The @@ operator tests the document vector against the query.
7. Ranking and highlighting use the matched query plus vector or original text.

SELECT alias, description, token
FROM ts_debug('english', 'Running PostgreSQL databases in 2026');

SELECT to_tsvector(
  'english',
  'The striped cats were running past databases'
);
-- Stop words can disappear; cats can become cat; running can become run.

A parser identifies token shape but does not decide vocabulary. Dictionaries normalize tokens, recognize stop words, stem language forms, apply synonyms, or match thesaurus phrases. The configuration connects parser output types to dictionary chains. Use the same explicit configuration on both document and query sides. Relying on default_text_search_config makes behavior environment-dependent.

06. Building tsvector documents

A stored search vector makes the document pipeline explicit, reusable, and indexable.

CREATE TABLE articles (
    article_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    summary text,
    body text NOT NULL,
    published_at timestamptz,
    search_document tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(summary, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(body, '')), 'D')
    ) STORED
);

CREATE INDEX articles_search_document_gin
ON articles USING gin (search_document);

Weights A through D label lexemes by importance; they do not filter by themselves. Ranking functions can assign larger numeric weights to title matches than body matches. coalesce is essential because one null input can otherwise turn a combined document into null. A stored generated column stays synchronized without application discipline.

07. Constructing safe tsquery values

Choose a query constructor from the user experience instead of passing arbitrary user text into strict query syntax.

SELECT to_tsquery('english', 'database & (index | indexing) & !mysql');
SELECT phraseto_tsquery('english', 'query optimization');
SELECT websearch_to_tsquery('english', '"query plan" OR optimizer -mysql');
SELECT to_tsquery('english', 'postgr:*');
SELECT to_tsquery('english', 'postgres:AB');

-- Core operators inside a tsquery:
-- & means AND, | means OR, ! means NOT.
-- <-> means immediately followed by.
-- <3> means positions differ by exactly three.

08. Matching, ranking, and highlighting

WITH input AS (
    SELECT websearch_to_tsquery('english', $1) AS query
)
SELECT
    a.article_id,
    a.title,
    ts_rank_cd(
      ARRAY[0.05, 0.1, 0.4, 1.0]::real[],
      a.search_document,
      input.query,
      32
    ) AS rank,
    ts_headline(
      'english',
      a.body,
      input.query,
      'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, MaxWords=24'
    ) AS snippet
FROM articles AS a
CROSS JOIN input
WHERE a.search_document @@ input.query
  AND a.published_at IS NOT NULL
ORDER BY rank DESC, a.published_at DESC, a.article_id
LIMIT 20;

ts_rank scores frequency and weights. ts_rank_cd uses cover density and therefore needs positional information to reward close groups of matches. Normalization flags adjust scores for factors such as document length; they do not make the score a probability. Use rank to order matches, not as a universal relevance number comparable across unrelated queries.

• Put @@ in WHERE so the GIN index can find candidates.
• Rank the candidate set, not every row in the table.
• Add business signals carefully, such as freshness or popularity, after text relevance.
• Use deterministic tie-breakers for repeatable pagination.
• Sanitize or safely render ts_headline output before placing it in HTML.
• Limit source length or generate snippets only after selecting a bounded candidate set.

GIN versus GiST for full-text search

GIN is the preferred full-text index in most workloads because it stores lexemes as keys and points to matching rows. GiST stores lossy signatures, can produce false matches that require rechecking, and can use included columns for some covering designs. GIN updates can accumulate in a pending list when fast update is enabled, so write bursts and cleanup still need monitoring. Neither index stores relevance ordering, so PostgreSQL normally finds matches and then sorts by rank.

09. Language, dictionaries, and maintenance

Text-search quality is a data-model choice. One configuration rarely fits every language, code token, product identifier, and proper name.

• Use simple when stemming is unwanted and exact normalized tokens matter.
• Use a language configuration when its stemming and stop-word rules match the content.
• Store or derive a document language and route each row to a deliberate configuration.
• Test domain vocabulary, abbreviations, hyphenation, URLs, emails, and product codes.
• Use synonym, Ispell, Snowball, or thesaurus dictionaries only with versioned review and tests.
• Rebuild stored vectors and relevant indexes when configuration behavior changes.

SELECT * FROM ts_token_type('default');
SELECT * FROM ts_parse('default', 'Email admin@example.com about PostgreSQL 18');
SELECT * FROM ts_debug('simple', 'Databases database DATABASE');
SELECT get_current_ts_config();

10. PostGIS spatial mental model

PostGIS adds spatial types and operators, but every correct query still depends on what the coordinates mean.

CREATE EXTENSION postgis;

CREATE TABLE places (
    place_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    location geometry(Point, 4326) NOT NULL
);

CREATE TABLE service_areas (
    area_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    boundary geometry(MultiPolygon, 4326) NOT NULL
);

CREATE INDEX places_location_gist ON places USING gist (location);
CREATE INDEX service_areas_boundary_gist ON service_areas USING gist (boundary);

A geometry has a shape and an SRID, a spatial reference identifier. The SRID tells software which coordinate reference system interprets the numbers. Longitude and latitude in WGS 84 commonly use SRID 4326. Those coordinates are angular degrees, not flat meters. A projected coordinate system can provide locally meaningful linear units.

Geometry versus geography

Geography makes common earth-distance questions easier but supports a smaller function set and can cost more CPU. Geometry is not inaccurate by definition; it is accurate when the selected projection fits the location and analysis. Choose from geographic extent, required operations, accuracy, units, and workload, not from a rule that one type is always better.

11. Spatial predicates and distance queries

Ask the topological relationship you mean, and use index-aware predicates to restrict candidates before expensive exact calculations.

-- Longitude is X; latitude is Y.
WITH input AS (
    SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326) AS point
)
SELECT a.area_id, a.name
FROM service_areas AS a
CROSS JOIN input
WHERE ST_Covers(a.boundary, input.point);

-- Cast both sides to geography so the radius is meters.
WITH input AS (
    SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography AS point
)
SELECT
    p.place_id,
    p.name,
    ST_Distance(p.location::geography, input.point) AS distance_m
FROM places AS p
CROSS JOIN input
WHERE ST_DWithin(p.location::geography, input.point, $3)
ORDER BY distance_m, p.place_id
LIMIT 50;

If radius search is frequent, index the same stored type or expression used by the predicate. Repeatedly casting an indexed geometry column to geography will not use a plain geometry index as if the expressions were identical. A dedicated geography column or expression GiST index can make the design explicit.

CREATE INDEX places_location_geography_gist
ON places USING gist ((location::geography));

-- Geometry KNN ordering uses the GiST distance operator.
SELECT place_id, name
FROM places
ORDER BY location <-> ST_SetSRID(ST_MakePoint($1, $2), 4326)
LIMIT 20;

Validity, dimensions, and edge cases

• Validate imported polygons with ST_IsValid; predicates on invalid shapes are unsafe.
• Do not assume longitude-latitude order from a client library; verify its coordinate convention.
• Most common predicates are 2D even when values carry Z; use explicit 3D functions when needed.
• Empty geometry, null geometry, geometry collections, and antimeridian cases need tests.
• Ensure both geometry operands have compatible SRIDs before comparison.
• Use ST_MakeValid as a reviewed repair, not as proof that the repaired meaning is correct.

12. pgvector types and distance metrics

pgvector stores numeric embeddings beside relational data and supplies exact and approximate nearest-neighbor search.

CREATE EXTENSION vector;

CREATE TABLE document_chunks (
    chunk_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL,
    document_id bigint NOT NULL,
    content text NOT NULL,
    embedding_model text NOT NULL,
    embedding vector(1536) NOT NULL,
    UNIQUE (tenant_id, document_id, chunk_id)
);

SELECT
    embedding <-> $1::vector AS l2_distance,
    embedding <#> $1::vector AS negative_inner_product,
    embedding <=> $1::vector AS cosine_distance,
    embedding <+> $1::vector AS l1_distance
FROM document_chunks
LIMIT 1;

Choose the metric recommended by the embedding model and keep preprocessing identical for stored and query vectors. Never compare vectors from incompatible models as though they shared one coordinate space. Store model name, dimensionality assumptions, normalization policy, and generation version so migrations are observable.

Dense, half-precision, binary, and sparse values

A fixed dimension such as vector(1536) lets the type reject mismatched input early. An unconstrained vector column can hold several dimensions, but an approximate expression or partial index must still target compatible dimensions. Current pgvector index limits differ by type and index method, so verify the deployed extension version before selecting a model. Reducing precision, binary-quantizing, indexing subvectors, or reducing dimensions can lower memory, but each can change retrieval quality and should be followed by exact re-ranking when the design requires it.

13. Exact nearest-neighbor search

Without an approximate index, pgvector compares the query vector with every eligible row and returns the true nearest rows under the chosen metric.

SELECT
    chunk_id,
    document_id,
    content,
    embedding <=> $1::vector AS distance
FROM document_chunks
WHERE tenant_id = $2
  AND embedding_model = $3
ORDER BY embedding <=> $1::vector, chunk_id
LIMIT 20;

Exact search is the correctness baseline. It can be fast enough for a small filtered corpus, offline evaluation, or a moderate table with parallel scanning. It also tells you whether an approximate configuration meets recall targets. The query must order directly by a supported distance operator in ascending order and include LIMIT for an approximate nearest-neighbor index to be considered later.

14. HNSW indexes

HNSW builds a multilayer proximity graph. It usually offers a strong speed-recall tradeoff at the price of memory, build time, and write maintenance.

CREATE INDEX document_chunks_embedding_hnsw
ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT chunk_id, content, embedding <=> $1::vector AS distance
FROM document_chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1::vector
LIMIT 20;
COMMIT;

• m controls graph connectivity; larger values use more memory and build work.
• ef_construction enlarges the build candidate list and can improve index quality.
• hnsw.ef_search enlarges the query candidate list, improving recall at more cost.
• HNSW does not need a training step and can be created before rows exist.
• Create one operator-class index for each distance metric that must be accelerated.
• Large builds benefit when the graph fits in maintenance_work_mem, within safe RAM limits.

15. IVFFlat indexes

IVFFlat clusters existing vectors into lists, then searches selected lists instead of the entire table.

-- Build after representative data exists.
CREATE INDEX document_chunks_embedding_ivfflat
ON document_chunks
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

BEGIN;
SET LOCAL ivfflat.probes = 10;
SELECT chunk_id, content, embedding <=> $1::vector AS distance
FROM document_chunks
ORDER BY embedding <=> $1::vector
LIMIT 20;
COMMIT;

More lists reduce candidates per list and can improve query speed, but too many lists for too little data produce poor results. More probes search more lists and improve recall at higher latency. Setting probes to the number of lists approaches exact search and can make the planner choose a different plan. Rebuild after major data-distribution changes when measured recall deteriorates.

16. Filtering approximate vector search

Approximate indexes usually retrieve vector candidates first and apply ordinary filters to those candidates, so selective filters can leave fewer than the requested number of rows.

CREATE INDEX document_chunks_tenant_model_idx
ON document_chunks (tenant_id, embedding_model);

BEGIN;
SET LOCAL hnsw.iterative_scan = strict_order;
SET LOCAL hnsw.ef_search = 100;

SELECT chunk_id, content, embedding <=> $1::vector AS distance
FROM document_chunks
WHERE tenant_id = $2
  AND embedding_model = $3
ORDER BY embedding <=> $1::vector
LIMIT 20;
COMMIT;

Iterative scanning can continue deeper into the approximate index until it finds enough filtered results or reaches configured limits. Relaxed ordering can improve recall or performance while allowing slight distance disorder; a materialized outer re-sort can restore strict presentation order. Alternatives include exact search after a highly selective B-tree filter, partitioning by a stable coarse key, partial vector indexes for a few important categories, or retrieving a larger candidate set and applying filters afterward. Tenant and authorization predicates must remain in the database query, never only in application post-processing.

Build, vacuum, monitoring, and recall

• Use pg_stat_progress_create_index to observe supported build phases.
• Use concurrent index operations when uptime requires them and accept their extra work.
• Monitor table and index size, WAL, cache behavior, build memory, query latency, and dead tuples.
• HNSW vacuum can be expensive; pgvector documents reindexing before vacuum as one option.
• Compare approximate results to exact results regularly on a sampled, labeled query set.
• Track recall separately by tenant, filter shape, corpus size, and embedding-model version.
• Null vectors are not indexed; zero vectors are not indexed for cosine distance.

17. Application patterns and hybrid retrieval

Strong search systems use each feature for the signal it models and combine bounded candidate sets deliberately.

Raw scores from different systems are not directly comparable. Full-text rank, trigram similarity, vector distance, and spatial distance have different scales and meanings. Combine them through calibrated rules, rank fusion, or a trained reranker evaluated on labeled data. Keep candidate limits large enough for recall but bounded enough for predictable latency.

18. Migrations and production operations

1. Inventory managed-service support, package versions, PostgreSQL compatibility, and replicas.
2. Install package files everywhere before restoring or running database migrations.
3. Create or update the extension in its own reviewed migration.
4. Add nullable columns or new structures first when a large backfill is required.
5. Backfill in bounded batches while measuring WAL, locks, replication lag, and autovacuum.
6. Build large indexes concurrently where availability requires it.
7. Validate exact counts, nulls, SRIDs, vector dimensions, language routing, and index validity.
8. Deploy read paths behind a controlled rollout and compare plans and quality metrics.
9. Retire old columns or indexes only after rollback windows and dependents are clear.

What to measure

• EXPLAIN (ANALYZE, BUFFERS) plans with representative parameters and warm/cold context.
• Latency percentiles, rows examined, rows returned, buffers, temporary I/O, and sort work.
• Index size, scans, tuples read and fetched, update volume, WAL, bloat, and vacuum behavior.
• Search quality: precision, recall, zero-result rate, reformulation rate, and task success.
• Spatial data quality: invalid shapes, unexpected SRIDs, empty values, and coordinate bounds.
• Vector quality: model drift, dimension failures, nulls, norms, and approximate recall at k.
• Extension inventory and version differences across primary, replicas, staging, and restore targets.

19. Performance reasoning

A specialized index is useful when its supported operator removes enough work to repay its storage and maintenance cost.

• Write the indexed operator in the form required by its operator class.
• Keep types, casts, configurations, SRIDs, and distance metrics identical to the index definition.
• Use selective relational filters and ordinary indexes alongside specialized retrieval.
• Expect lossy indexes or bounding boxes to recheck exact conditions.
• Do not force an index when a sequential scan is cheaper for a large result fraction.
• Measure index build and write cost, not only one read benchmark.
• Test real distributions: short text, common lexemes, dense cities, skewed tenants, and filtered vectors.
• Use stable limits and tie-breakers; avoid unbounded ranking of an entire corpus.

20. Common mistakes and corrections

21. Practice exercises

1. Inventory three extensions across development and production. Report available version, installed version, schema, owner, relocatability, and whether an update path exists.
2. Build a typo-tolerant people search. Compare GIN filtering with GiST nearest-neighbor ordering for ten real misspellings and explain threshold selection.
3. Create a weighted generated tsvector for title, tags, summary, and body. Implement plain, phrase, and web-style queries with safe highlighting.
4. Explain every token in ts_debug output for a sentence containing an email, number, hyphenated term, stop words, and inflected words.
5. Model stores and delivery zones in PostGIS. Answer point-in-zone, stores within 5 km, and five nearest stores while stating types, SRIDs, units, and indexes.
6. Load a labeled vector dataset. Compare exact, HNSW, and IVFFlat latency and recall at 10 across three tuning values and two metadata filters.
7. Design a hybrid document search that fuses full-text and vector candidates without treating raw scores as directly comparable.
8. Write a zero-downtime plan to change embedding model and dimension while old reads remain available and rollback stays possible.

22. Interview questions and answers

23. One-screen cheat sheet

• Available: files exist on server; installed: objects exist in one database.
• Inventory: pg_available_extensions, versions view, pg_extension.
• Lifecycle: create, validate, update through scripts, inspect dependents, drop carefully.
• Security: reviewed source, secure schema, controlled search path, least privilege.
• pg_trgm: character similarity, typo tolerance, partial patterns, indexed ILIKE.
• Trigram GIN: filtering; trigram GiST: distance-ordered top k.
• FTS pipeline: parser, token type, dictionaries, configuration, vector, query.
• FTS match: tsvector @@ tsquery; use explicit matching configuration.
• User queries: plain, phrase, or web constructor; bind parameters.
• FTS index: GIN is normally preferred; ranking still needs candidate sorting.
• PostGIS geometry: planar units from SRID; geography: earth model and meters.
• SRID: set labels coordinates; transform converts coordinates.
• Radius: ST_DWithin filter, then distance calculation and order.
• Spatial GiST: candidate bounding boxes plus exact predicate recheck.
• pgvector exact: perfect recall baseline under the chosen metric.
• HNSW: graph, no training, tune ef_search.
• IVFFlat: clusters, build after data, tune lists and probes.
• Filtering ANN: relational indexes, iterative scans, partitions, or exact subset search.
• Quality: evaluate labeled examples; raw rank, similarity, and distance are different scales.
• Operations: version packages, derived data, configuration, models, and indexes separately.

24. Official reference map

• PostgreSQL CREATE EXTENSION
• PostgreSQL ALTER EXTENSION
• PostgreSQL DROP EXTENSION
• Packaging and securing extension objects
• Available extension catalog view
• Official pg_trgm documentation
• PostgreSQL full-text search
• Text-search documents, queries, ranking, and highlighting
• Preferred full-text index types
• Official PostGIS manual
• PostGIS data management and geometry versus geography
• PostGIS spatial queries and indexes
• PostGIS ST_DWithin
• Official pgvector project documentation