All articles
postgres

I added a TIN index to my Postgres output and found the schema had never been written

PlanetScale shipped TIN, a BM25 text index for Postgres, and I wanted it as an option for my geocoding output. Adding one flag exposed that the function writing the table schema was fully unit-tested and had no production caller. Along the way I turned an outage banner into a design constraint and named a flag after a vendor.

Vinayak Kulkarni
4 min read

One of my projects is a Rust vector-tile generator. It turns Overture GeoParquet and OpenStreetMap PBF files into PMTiles, and it has a second output for geocoding: a TSV of places you load into Postgres with COPY. Names were indexed with a GIN full-text index.

PlanetScale launched TIN on 2026-09-16. It is a BM25 text index for Postgres, and I wanted it as an option.

I grepped the repo first. There were zero references to tin, USING tin, or CREATE EXTENSION tin. Then I went looking for the code that wrote the table's DDL, to add the flag there.

generate_ddl had no production caller. The pipeline wrote the .tsv and never wrote the table it belonged to.

A schema that never reached disk

\COPY needs a table first. So everyone using the Postgres output had to hand-write a matching CREATE TABLE, with the right column order and the right types.

The schema existed in code, with tests, and never reached disk. A flag added to that function would have changed nothing for anyone.

It hid because generate_ddl was tested. The tests called it directly and checked the SQL string it returned, so it looked alive.

Two mistakes, both from answering before checking

I turned an outage banner into a design constraint. The existing location index is USING GIST (ST_MakePoint(lon, lat)), which needs PostGIS. I claimed PlanetScale disables PostGIS, so that index could never load there, and the design would need a fallback path.

In review, the question came back: was I sure? I was not. PlanetScale's extensions page lists postgis (3.6.1 on PG 18.6) as supported. What I had seen was a temporary banner about an operational issue that had disabled PostGIS for a few days.

Checking removed a design problem I had invented. TIN is an extension like PostGIS, installed with CREATE EXTENSION, and the two sit side by side: PostGIS indexes the point and TIN indexes the text. I did not need a fallback.

I named the flag after a vendor. My first proposal was --pg-index-flavor postgres|planetscale. Review corrected it to tin|gin: name the flag after the index it selects. That is right because TIN also ships on Neki, and the same SQL runs on Lead. A vendor name would have been wrong the day the index showed up somewhere else.

The fix

One flag, --pg-index-flavor, with values tin (the default) and gin. The DDL is now written next to the TSV as <output>.sql. Only the text index differs:

-- tin (default)
CREATE EXTENSION IF NOT EXISTS tin;
CREATE INDEX IF NOT EXISTS places_search_text_idx ON places USING tin (search_text);

-- gin
CREATE INDEX IF NOT EXISTS places_search_text_idx
    ON places USING GIN (to_tsvector('simple', search_text));

The table, the PostGIS location index and the layer index are identical in both. TIN indexes the text column directly. GIN needs a tsvector expression.

I checked the license before picking the default

TIN is proprietary. It runs on PlanetScale Postgres and Neki only.

Lead implements the same SQL and TINQL under AGPL-3.0 and you can self-host it. But its index stores no search data, so every scan returns all heap pages as candidates, and that makes it slow.

So tin is the default because it suits a PlanetScale target, and gin is the portable choice for any plain Postgres.

Tests that do not restate the code

I added three tests, and each asserts SQL taken from the TIN docs rather than echoing the implementation:

  • the extension is created before the table,
  • TIN indexes the column with no to_tsvector,
  • GIN needs no extension.

I also checked the flag on the real binary. --help lists [possible values: tin, gin], and an invalid value fails at argument parsing.

The PR's Codecov comment showed a file at 78.57%. That number was file-level for geocoding/pipeline.rs. The patch overall was 94.23% and passed. The 3 uncovered lines were fallback branches in deriving a table name from the output path: file_stem() returning None, a non-UTF-8 name, and the "geocoding" default. I pulled that logic into a small ddl_table_name function and tested all three branches directly.

Things that will bite you

  • A tested function with no caller looks healthy. Before you add a feature to a function, find its production call site.
  • Vendor banners change within days. Check the vendor's current docs before a banner becomes a design constraint.
  • Name a flag after the thing it selects. tin and gin describe the index, and they stay correct wherever the index ships.
  • Keep the portable option one flag away. A proprietary BM25 index should not silently become the only way your output loads.

The check I run now, before I touch any function I plan to extend:

rg -n 'generate_ddl\('

If the only hits are the definition and its test module, the function has no production caller.

postgresfull-text-searchbm25geocodingrustplanetscale