LLM Best Practices

GIN Index

Last updated 1 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

A GIN index creates one entry per element of a composite value, so a single row can add hundreds of entries. Add one once full-text search, JSONB containment, or array overlap queries show sequential scans, and expect larger indexes and slower writes in exchange. Confirm the planner uses it with EXPLAIN (query-plan).

Rules

  • Operators: @@ for tsvector; @>, <@, ?, ?|, ?&, @?, @@ for jsonb (default jsonb_ops); @>, <@, && for arrays. jsonb_path_ops is smaller and faster but supports only @>, @?, and @@.
  • fastupdate (default on) buffers inserts in a pending list that vacuum or gin_clean_pending_list() merges; a large pending list slows queries.
  • GiST updates and builds faster than GIN, but GIN answers large-vocabulary text queries faster.
  • Build on live tables with CREATE INDEX CONCURRENTLY.
CREATE INDEX idx_articles_fts ON articles USING gin (search_vec);
SELECT id FROM articles WHERE search_vec @@ to_tsquery('english', 'postgres & index');
 
CREATE INDEX idx_events_data ON events USING gin (data);
SELECT * FROM events WHERE data @> '{"type": "purchase"}';

Backlinks