Search

Thirteen methods. Full-text, fuzzy, phonetic, vector, autocomplete, facets, percolation. All of them yours through a single function call.

Allow me to show you around the search wing of the manor. Gold Lapel provides 13 search methods available across all seven language wrappers, each mapping to a capability you may know from Elasticsearch — but backed entirely by PostgreSQL. No separate search cluster. No data synchronization pipeline. No second system to monitor at three in the morning.

Every search method returns all columns from the matched table, plus a _score field indicating relevance. Where applicable, a _headline field provides highlighted snippets. The interface is the same in every language — Python, JavaScript, Ruby, Go, Java, PHP, .NET — with naming adapted to each language's conventions (snake_case in Python/Ruby/PHP, camelCase in JavaScript/Java, PascalCase in Go/.NET).

This page is the narrative guide — how to think about search and when to reach for each method. For the complete parameter reference, I would direct you to the API Reference. For how Gold Lapel's search compares feature-by-feature with Elasticsearch, the comparison page lays out the full picture.

Search

Five methods for finding documents. Each approaches the problem differently — full-text for precise keyword matching, fuzzy for typo tolerance, phonetic for sound-alike, vector for semantic meaning, and suggest for autocomplete. Most applications will use two or three of these together.

search(conn, table, column, query)

Full-text search, backed by PostgreSQL's tsvector and tsquery. This is the workhorse — the method you will reach for first. It tokenizes your query, stems the words, removes stop words, and matches against an inverted index. The same fundamental approach Elasticsearch uses, running directly inside your database.

Supports multi-column search by passing a list of column names. Supports 30+ languages via the lang parameter — English, Spanish, French, German, Russian, Japanese, and more. Highlighted snippets are returned in the _headline field with matching terms wrapped in <b> tags.

# Full-text search across one column
results = goldlapel.search(conn, "articles", "body", "machine learning")
# [{"id": 7, "title": "Intro to ML", "body": "...", "_score": 0.0608, "_headline": "...machine **learning**..."}, ...]

# Multi-column search
results = goldlapel.search(conn, "articles", ["title", "body"], "machine learning")

# With highlighting and language
results = goldlapel.search(conn, "articles", "body", "aprendizaje automatico", lang="spanish")

search_fuzzy(conn, table, column, query)

Typo-tolerant search using trigram similarity via pg_trgm. When your users type "postgrsql" and mean "postgresql," this is the method that understands. It computes a similarity score between the query and each value in the column, returning results above a configurable threshold.

Fuzzy search excels at product names, proper nouns, and any content where users are likely to misspell. It is not a replacement for full-text search — it complements it. Use search for natural language queries against long text fields, and search_fuzzy for short-field lookups where exact spelling is uncertain.

# Typo-tolerant search — "postgre" still matches "postgresql"
results = goldlapel.search_fuzzy(conn, "products", "name", "postgre")
# [{"id": 12, "name": "PostgreSQL Handbook", "_score": 0.6}, ...]

# Similarity threshold defaults to 0.3 — lower values are more lenient
results = goldlapel.search_fuzzy(conn, "products", "name", "postgre", threshold=0.2)

search_phonetic(conn, table, column, query)

Sound-alike matching using soundex and dmetaphone from the fuzzystrmatch extension. "Steven" matches "Stephen." "Smith" matches "Smyth." This is invaluable for name searches where spelling varies but pronunciation does not.

Gold Lapel uses Double Metaphone by default, which is more accurate than the original Soundex algorithm and handles a broader range of names. Both English and European names are well served.

# Sound-alike matching — "Steven" matches "Stephen", "Smith" matches "Smyth"
results = goldlapel.search_phonetic(conn, "customers", "last_name", "Smith")
# [{"id": 3, "last_name": "Smith", "_score": 1.0},
#  {"id": 19, "last_name": "Smyth", "_score": 1.0}, ...]

similar(conn, table, column, vector)

Semantic similarity search using pgvector. Pass an embedding vector and Gold Lapel finds the rows whose vectors are nearest. This is the method for "find me articles that are about this topic" rather than "find me articles that contain these words." The distinction matters. A query for "database optimization" will match articles about "query tuning" and "index strategies" even if those exact words never appear.

Requires a vector column in your table populated with embeddings from your model of choice — OpenAI, Cohere, Sentence Transformers, or any other. Gold Lapel does not generate embeddings; it searches them.

# Semantic similarity with pgvector — pass an embedding vector
embedding = openai.embeddings.create(input="database optimization", model="text-embedding-3-small")
results = goldlapel.similar(conn, "articles", "embedding", embedding.data[0].embedding)
# [{"id": 42, "title": "Query Tuning Guide", "_score": 0.92}, ...]

suggest(conn, table, column, prefix)

Autocomplete. As the user types, this method returns matching values ranked by similarity. It combines ILIKE prefix matching with trigram similarity scoring, so "post" returns "PostgreSQL" before "Postman" if the similarity is higher.

I would recommend this for search-as-you-type inputs, product search bars, and any interface where the user expects instant suggestions after two or three keystrokes.

# Autocomplete — prefix matching ranked by similarity
results = goldlapel.suggest(conn, "products", "name", "post")
# [{"id": 12, "name": "PostgreSQL Handbook", "_score": 0.85},
#  {"id": 8, "name": "Postman API Guide", "_score": 0.71}, ...]

Aggregations

Two methods for summarizing data. If you have used Elasticsearch's terms aggregation or metric aggregations, these will feel familiar — the same concepts, expressed as function calls rather than JSON query syntax.

facets(conn, table, column)

Category counts. "How many articles are in each category?" "What are the top tags?" This is the building block for faceted navigation — the sidebar filters you see on e-commerce sites and search interfaces.

Pass an optional query and query_column to filter the counts by a full-text search. This answers a more specific question: "Of the articles that match 'machine learning,' how many are in each category?" The combination of search and facets in a single call is what makes search interfaces feel responsive — one round-trip for both the results and the filters.

# Category counts across all articles
results = goldlapel.facets(conn, "articles", "category")
# [{"value": "technology", "count": 842}, {"value": "science", "count": 531}, ...]

# Filtered by a search query — "what categories contain articles about ML?"
results = goldlapel.facets(conn, "articles", "category", query="machine learning", query_column="body")
# [{"value": "technology", "count": 47}, {"value": "research", "count": 23}, ...]

aggregate(conn, table, column, func)

Compute count, sum, avg, min, or max over a column, optionally grouped by another column. This covers the territory of Elasticsearch's metric aggregations — "average order value by region," "total revenue by month," "maximum response time by endpoint."

# Average order total grouped by region
results = goldlapel.aggregate(conn, "orders", "total", "avg", group_by="region")
# [{"region": "us-east", "value": 89.50}, {"region": "eu-west", "value": 72.30}, ...]

# Global sum — no group_by
results = goldlapel.aggregate(conn, "orders", "total", "sum")
# [{"value": 1847293.50}]

Reverse Search (Percolation)

Three methods that invert the search model. Instead of "find documents matching this query," reverse search asks "find queries matching this document." Elasticsearch calls this the Percolator. It is a powerful pattern for alerts, classification, and content routing.

The workflow: store a set of named queries, then as new documents arrive, match each document against the stored queries. A news article mentioning "earthquake" triggers the "breaking-news" alert. A support ticket containing "billing" routes to the billing team. The queries live in the database, not in application code — they can be added, modified, and removed at runtime without redeployment.

percolate_add(conn, name, query_id, query)

Store a query for reverse matching. Each query has a namespace (name), an identifier (query_id), the search text, and optional metadata that is returned when the query matches.

# Store a query for reverse matching — "notify me when this topic appears"
goldlapel.percolate_add(conn, "alerts", "breaking-news",
    "breaking news earthquake",
    metadata={"notify": "slack", "channel": "#alerts"})

goldlapel.percolate_add(conn, "alerts", "product-launch",
    "product launch announcement",
    metadata={"notify": "email", "to": "team@company.com"})

percolate(conn, name, text)

Match a document against all stored queries in a namespace. Returns the matching queries with their metadata and relevance scores.

# Match a new document against all stored queries
matches = goldlapel.percolate(conn, "alerts",
    "A 6.2 magnitude earthquake struck the coast, breaking news from the USGS.")
# [{"query_id": "breaking-news", "query_text": "breaking news earthquake",
#   "metadata": {"notify": "slack", "channel": "#alerts"}, "_score": 0.12}]

percolate_delete(conn, name, query_id)

Remove a stored query when it is no longer needed.

goldlapel.percolate_delete(conn, "alerts", "breaking-news")
# True

Configuration & Debugging

Three methods for understanding and tuning the search pipeline. When a search produces unexpected results — matching documents it should not, or missing documents it should — these are the tools that explain why.

create_search_config(conn, name, copy_from)

Create a custom text search configuration by copying an existing one. This gives you a named configuration you can pass as the lang parameter to any search method. You might create a custom configuration to add domain-specific synonyms, change the stemmer, or adjust stop word lists.

# Create a custom text search configuration
goldlapel.create_search_config(conn, "my_english", copy_from="english")

# Use it in subsequent searches
results = goldlapel.search(conn, "articles", "body", "query", lang="my_english")

analyze(conn, text, lang)

Show the tokenization pipeline — exactly how PostgreSQL processes text for search. This is the equivalent of Elasticsearch's _analyze API. It reveals which tokens survive after stemming and stop word removal, which is essential when debugging "why did this search not match?"

If you are wondering why "running" matches "ran" (it should — they share the stem "run"), or why "the" is ignored (it is a stop word), analyze shows you the answer in one call.

# Show the tokenization pipeline — how PostgreSQL processes search text
tokens = goldlapel.analyze(conn, "The quick brown foxes jumped")
# [{"alias": "english_stem", "token": "quick", "lexemes": ["quick"]},
#  {"alias": "english_stem", "token": "brown", "lexemes": ["brown"]},
#  {"alias": "english_stem", "token": "foxes", "lexemes": ["fox"]},
#  {"alias": "english_stem", "token": "jumped", "lexemes": ["jump"]}]

explain_score(conn, table, column, query, id_column, id_value)

The full diagnostic for a specific document. Given a query and a document ID, this method returns the document text, its tokenized form, the query tokens, whether they match, the relevance score, and a highlighted headline. This is the equivalent of Elasticsearch's _explain API — the answer to "why did document #42 score 0.06 and not higher?"

# Why did document #42 score what it did?
result = goldlapel.explain_score(conn, "articles", "body",
    "machine learning", id_column="id", id_value=42)
# {"document_text": "An introduction to machine learning...",
#  "document_tokens": "'introduct':2 'learn':5 'machin':4 ...",
#  "query_tokens": "'learn' & 'machin'",
#  "matches": True,
#  "score": 0.0607927,
#  "headline": "An introduction to **machine** **learning**..."}

Proxy Auto-Indexing

You will not need to create indexes yourself. When Gold Lapel detects repeated search patterns flowing through the proxy, it creates the optimal index automatically. The mapping is deterministic:

Index creation is non-blocking (CREATE INDEX CONCURRENTLY) and logged in the dashboard. If an appropriate index already exists, Gold Lapel leaves it alone. All identifiers — table names, column names, index names — are validated against SQL injection before any DDL is issued.

Extensions Used

Search methods use a small number of PostgreSQL extensions, most of which ship with every PostgreSQL installation. Gold Lapel creates them lazily on first use — no manual CREATE EXTENSION required if the proxy has sufficient permissions.

For a thorough account of every extension Gold Lapel works with, the Extensions page covers installation, provider compatibility, and what each enables.

Putting It Together

A realistic flow — searching articles, tolerating typos in product names, and building faceted navigation — all in a few lines.

import goldlapel

# Start the proxy
conn = goldlapel.start("postgresql://user:pass@localhost:5432/mydb")

# Full-text search with highlighting
articles = goldlapel.search(conn, "articles", "body", "database optimization")
for article in articles:
    print(f"{article['title']} (score: {article['_score']:.3f})")
    print(f"  {article['_headline']}")

# Fuzzy search for products — tolerates typos
products = goldlapel.search_fuzzy(conn, "products", "name", "postgrsql")

# Faceted navigation — category counts filtered by the same query
categories = goldlapel.facets(conn, "articles", "category",
    query="database optimization", query_column="body")
for cat in categories:
    print(f"  {cat['value']}: {cat['count']} articles")

Three search methods, one connection, zero infrastructure beyond your existing PostgreSQL database. The proxy handles indexing, extension management, and query optimization behind the scenes.

Further Reading

This has been the narrative tour. For every parameter, default value, and return type, the API Reference is the definitive source. For a side-by-side comparison of these capabilities against Elasticsearch — aggregation pipelines, percolation, vector search, and the rest — the Elasticsearch comparison presents the full picture.

If you have arrived here from the book, the next chapter is waiting: What's Next covers how these search capabilities fit into the broader story of what PostgreSQL can replace when given the proper tools.