Boost Address Keyword Search with PostgreSQL: GIN, pg_trgm, and pg_bigm

In map or GIS scenarios, address keyword search is crucial; users often type a keyword such as "China Agricultural University" instead of the full address.

PostgreSQL is praised as the most advanced open‑source database, offering strong text‑search capabilities, including full‑text, fuzzy, and regex queries, as well as expression and GIN indexes, making it advantageous for address keyword retrieval.

1. Application Background

In address search scenarios, user input is tokenized and matched against a full‑text index of address corpora to obtain normalized address information. Queries are split into keywords, matched to historical address data, and results returned. Matching can take seconds to tens of seconds depending on tokenization and matching methods.

Common approaches include:

Using dedicated search engines such as Elasticsearch or Lucene.

Leveraging built‑in database search capabilities.

While Elasticsearch provides flexible search, it increases development and operational costs. This article explores how to achieve efficient Chinese address search using PostgreSQL’s native features.

2. Technical Solution

GIN (Generalized Inverted Index) stores (key, posting list) pairs, where each key maps to the tuple IDs where it appears, enabling fast lookup of rows containing specific keywords.

The pg_trgm extension implements a trigram (3‑gram) tokenization; it slides a window of three characters over the text, creates tokens, and builds a GIN index for efficient fuzzy matching.

The pg_bigm extension is similar but uses bigrams (2‑gram), which performs better for one‑ or two‑character queries.

We built a dataset of about 100 million Beijing address records and benchmarked PostgreSQL. Results show that both pg_trgm+gin and pg_bigm+gin outperform traditional B‑tree indexes for fuzzy searches. Because logistics keywords are usually three characters or longer, pg_trgm+gin provides millisecond‑level response times.

For Chinese text segmentation, the pg_jieba extension integrates the Jieba library, offering customizable dictionaries, keyword extraction, and part‑of‑speech tagging, further improving search relevance.

3. Conclusion

PostgreSQL’s rich indexing options, powerful full‑text search, and extensive plugin ecosystem allow developers to implement high‑performance address keyword search without synchronizing data to external search engines, simplifying architecture, reducing development cost, and ensuring real‑time query results.