Documentation

Database

Build the geocoding table and the indexes geolith search needs.

What you need

  • Postgres 18 with PostGIS.
  • The TIN extension: PlanetScale TIN in production, or the Tantivy-backed Lead fork for local development.
  • The geocoding table from geolith's pg-copy output. See the geolith geocoding guide.

Indexes

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS tin;

-- Lets the TIN top-k scan bound the importance term.
CREATE INDEX ON geocoding (importance);

-- Focus boxes and reverse geocoding.
CREATE INDEX geocoding_location_idx ON geocoding USING gist (ST_MakePoint(lon, lat));

-- Full-text search.
CREATE INDEX geocoding_search_text_idx ON geocoding USING tin (search_text);
ANALYZE geocoding;

Faster index with the Lead fork

The local Lead fork can copy columns into the text index and store word prefixes. Set these before CREATE INDEX:

SET maintenance_work_mem = '2GB';
SET tin.build_memory_mb = 8192;
SET tin.rank_columns = 'importance,lon,lat';
SET tin.index_prefixes = '2,5';
CREATE INDEX geocoding_search_text_idx ON geocoding USING tin (search_text);
RESET tin.rank_columns;
RESET tin.index_prefixes;
  • tin.rank_columns ranks by importance inside the index and skips rows outside a focus box.
  • tin.index_prefixes = '2,5' stores every 2–5 character word prefix, so autocomplete reads one posting list.

The SQL that geolith search sends is the same with or without these settings.

Warning

The prefix index is larger. On the 555.6 million row planet table the index is 32 GB, and the build needs about twice that in free disk space while it merges.