Pilotbase running a PostgreSQL full-text search for cast iron skillet on one million products, ranked with ts_rank_cd

A shop with a few hundred thousand products, a help centre, a notes app: sooner or later someone wants a search box, and someone else says the word Elasticsearch. Before you add a second system to run, back up and keep in sync, it is worth knowing what the database you already have can do. PostgreSQL, MySQL and SQLite all ship a full-text index. They are not the same index, and the differences show up in the first five minutes of real use.

We loaded the same 1,000,000 made-up product rows into PostgreSQL 18.6, MySQL 8.4.11 (InnoDB) and SQLite 3.54, built each engine's full-text index, and ran the same ten searches on all three. Default server settings unless we say otherwise; Apple M4 Mac mini (10 cores, 16 GB), warm cache, each time the median of five runs after one warm-up. The scripts are listed at the end and rerun with the same seed.

The test data

Each row is a product name and a one-to-four sentence description, 143 characters on average, 166 MB as a tab-separated file. Names look like Juniper lightweight titanium headlamp or Atlas toy car, charcoal (rechargeable battery); descriptions say things like Made from cast iron with a durable design. Dishwasher safe, induction compatible. Common words are common and rare ones rare, as in a real catalogue, and the text includes the awkward tokens real product text has: USB-C, 4K, AA batteries, Wi-Fi 6, battery/batteries.

One honest limit: generated text has a small vocabulary. Our million rows hold only 336 distinct words; a real catalogue has tens of thousands. That makes every index here smaller than it would be on your data. Match counts and the behaviour differences carry over; the sizes are a floor.

sql
create table products (
  id          int primary key,
  name        text not null,      -- varchar(200) on MySQL
  description text not null,
  category    text not null,
  price       numeric(8,2)
);

Setting up the index on each engine

PostgreSQL searches a tsvector: the text broken into normalised words (lexemes) with their positions. You can compute it in the query, but then every search re-parses every row, so store it as a generated column and put a GIN index on it. setweight marks words from the name as more important (A) than words from the description (B), which matters for ranking later.

sql
alter table products add column search tsvector generated always as (
  setweight(to_tsvector('english', name), 'A') ||
  setweight(to_tsvector('english', description), 'B')) stored;

create index products_search on products using gin (search);

MySQL puts a FULLTEXT index straight on the text columns. Queries must name exactly the same column list as the index: match(description) alone fails with Can't find FULLTEXT index matching the column list.

sql
alter table products add fulltext index products_ft (name, description);

SQLite has FTS5, a virtual table. With content='products' it indexes the existing table instead of storing a second copy of the text; you then keep it in step yourself (triggers, shown further down). The default tokenizer, unicode61, does no stemming; porter adds English stemming, so we built both.

sql
create virtual table products_fts using fts5(
  name, description, content='products', content_rowid='id');
insert into products_fts(products_fts) values ('rebuild');

create virtual table products_fts_porter using fts5(
  name, description, content='products', content_rowid='id',
  tokenize='porter unicode61');
insert into products_fts_porter(products_fts_porter) values ('rebuild');

Build time and size, 1M rows

text
                                  build time         extra disk
PostgreSQL 18.6
  tsvector stored column          15.2 s (rewrite)   +211 MB (table 191 -> 402 MB)
  GIN, maintenance_work_mem 64MB   3.9 s             26 MB
  GIN, maintenance_work_mem 512MB  0.9 s             26 MB
MySQL 8.4 InnoDB
  FULLTEXT (name, description)   110 s               +58 MB table rebuild (FTS_DOC_ID)
                                                     +65 MB index files
SQLite 3.54 FTS5 (external content)
  unicode61                        2.5 s             +118 MB
  porter                           2.9 s             +118 MB

Three things in that table are worth knowing before you start.

In PostgreSQL the index is not where the space goes. The GIN index is 26 MB, but the stored tsvector column doubled the table, because it keeps every lexeme with its position for phrase search and ranking. Count both when you plan disk. GIN builds are also memory-hungry: the same index took 3.9 s at the default maintenance_work_mem of 64 MB and 0.9 s at 512 MB, so raise it for the session that builds the index.

MySQL took 110 seconds, almost all of it rebuilding the table. A FULLTEXT index needs a hidden FTS_DOC_ID column, and adding the first FULLTEXT index to a loaded table adds that column by copying the whole table. The MySQL manual's fix is to define FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT with a unique index named FTS_DOC_ID_INDEX when you create the table. The build also sorts in the temporary directory: our first attempt failed after 96 s with Got error 100 - 'InnoDB error' from storage engine because the disk filled up. Check free space in tmpdir before building on a big table.

SQLite was the fastest to build at 2.5 s, and its index is the largest of the three on its own (118 MB, positions included), but with external content it is still far smaller than storing the text twice.

Ten searches, three engines

Each search was written the way a user would type it, then translated to each engine's syntax: websearch_to_tsquery('english', ...) in PostgreSQL (the function that understands quotes, or and - the way a search box does), boolean mode with + on each word in MySQL (so all words are required, as in the other two), and a plain FTS5 MATCH. Times are in milliseconds: counting all matches, then fetching the top 10 by each engine's own relevance score.

text
search typed            matches                              count ms         top-10 ranked ms
                        PG       MySQL    SQLite(porter)     PG  My  Lite     PG   My   Lite
titanium                14,044   14,044   14,044              4   4   0        6   59    8
battery                 125,414  58,134   125,414            48  17   2       52  142   64
stainless steel kettle  3,427    3,427    3,427               3  40   2        4   48    9
"cast iron" (phrase)    25,002   25,002   25,002             48  96   1       30  171   16
recharg* (prefix)       19,819   19,819   19,819             32   5   1       31   62   12
usb-c                   30,217   30,217   error              67   8   -       36   86    -
"usb-c"                 30,217   30,217   30,217             67  98   1       37  174   20
4k video                10,755   0        10,755              3   0   0        6    -    7
aa batteries            7,985    0        7,985               3   0   1        5    -   10
for the office          51,700   0        51,700             43   0   8       42    -   64

Every engine answers every search in well under a fifth of a second on a million rows, so speed is not what separates them at this size. The match counts are. Four of the ten rows differ, and each difference is a rule of the engine that nobody tells you about until a customer searches for a 4K TV and gets nothing.

What surprised us

MySQL silently drops words shorter than three characters. 4k and aa are below innodb_ft_min_token_size (default 3), so they are not in the index at all. With +4k +video in boolean mode, a required word that cannot match means 0 rows, not "ignore it". In natural language mode the same search returns all 10,755 rows with video, because the short word is simply skipped. The phrase "aa batteries" is worse: it quietly becomes batteries and returns 78,305 rows instead of 7,985. Lowering the limit to 2 is a server setting that needs a restart and a rebuilt index (alter table ... drop index ..., add fulltext index ...).

MySQL stop words make required words impossible. for, the, with, is and 32 other words are on InnoDB's default stop word list and never indexed. +for +the +office returns 0 rows; natural language mode returns 51,700. If you build boolean queries from user input by putting + in front of each word, remove stop words and short words first, or switch the stop word list off (innodb_ft_enable_stopword, again with a rebuild).

Only PostgreSQL stems out of the box. battery matched 125,414 rows in PostgreSQL (the english configuration turns both battery and batteries into batteri) and 58,134 in MySQL and in SQLite's default tokenizer, which only find the exact word. SQLite's porter tokenizer gets the full 125,414 at no extra cost in speed. MySQL has no stemming; +batter* or battery batteries without the + gets you there.

A hyphen is an operator. In MySQL boolean mode +usb-c means +usb and not c; it returned the right rows here only because c is too short to be indexed in the first place. In SQLite, usb-c typed into MATCH is an error (no such column: c), because FTS5 reads - and : as syntax. Quote user input as a phrase ("usb-c") before handing it to FTS5. PostgreSQL's websearch_to_tsquery was built for raw user input and handles usb-c, unbalanced quotes and stray operators without errors.

PostgreSQL drops stop words without telling you. for the office became the single lexeme offic. That is usually what you want, and unlike MySQL it never turns a search into zero rows, but it means a search for a band called The The finds nothing.

Ranking: the part that needs tuning on every engine

Counting matches is easy; putting the right ten first is where full-text search earns its keep. Each engine got it wrong in its own way out of the box.

PostgreSQL ts_rank runs out of room. For cast iron skillet, 1,815 of the 2,984 matches scored exactly 1.0, because a name (weight A) that contains all three words pushes ts_rank to its ceiling. The order among those 1,815 is then whatever the tie-breaker says. ts_rank_cd (cover density: how close together the matching words are) has no such ceiling: its best score for this search was 1.712, held by one row, with the rest spread out below it. That is the query in the screenshot. Always add a tie-breaker such as id or a popularity column, or results shuffle between pages.

Pilotbase query editor on PostgreSQL 18: websearch_to_tsquery for cast iron skillet ranked by ts_rank_cd, top rows Osprey Lane classic cast iron skillet 1.712, Tamarack slim cast iron skillet 1.689
sql
select id, name, round(ts_rank_cd(search, q)::numeric, 3) as rank, price
from products, websearch_to_tsquery('english', 'cast iron skillet') q
where search @@ q
order by rank desc, id
limit 20;

SQLite's bm25 ignores which column matched. With plain order by rank, the top three for battery were Northpeak keyboard, grey, Northpeak hammer, navy (20V lithium battery) and Halden dash cam, grey: two of them only mention a battery in the description. 36,698 of the 58,134 matches don't have the word in the name. Give the name column a weight: bm25(products_fts, 10.0, 1.0) (weights in column order) put Kestrel wireless earbuds, charcoal (rechargeable battery) first. Set it once for the table and order by rank uses it:

sql
insert into products_fts(products_fts, rank) values ('rank', 'bm25(10.0, 1.0)');

select p.id, p.name
from products_fts f join products p on p.id = f.rowid
where products_fts match 'battery'
order by rank
limit 10;

Note that bm25 is lower-is-better (the scores are negative), the opposite of the other two.

MySQL natural language mode is OR. against ('stainless steel kettle') without boolean mode matched 130,611 rows (anything with any of the three words) against 3,427 with all three. The ranking did put the right kettles first (scores 16.8, 14.5, 14.2), so it works for a ranked list as long as you never show a total. MySQL also has no column weights; to rank name matches higher, add a second FULLTEXT index on name alone and add its score to the first. And ranked top-10 queries were the slowest of the three engines: 142 ms for battery and 171 ms for the phrase cast iron, against 52 ms and 30 ms in PostgreSQL.

The LIKE '%word%' baseline

The first search box in most apps is where description like '%battery%'. On the same million rows (description column only):

text
                                         titanium        battery
PostgreSQL ILIKE, parallel seq scan      176 ms          158 ms
PostgreSQL ILIKE, pg_trgm GIN index        9 ms           50 ms
MySQL LIKE (ci collation), full scan     553 ms          506 ms
SQLite LIKE, full scan                   189 ms          127 ms
rows found                               9,375           41,287

Under 0.2 s on PostgreSQL and SQLite and half a second on MySQL is slow but survivable at a million rows, and grows in a straight line with the table. The bigger problem is what it finds. %battery% found 41,287 descriptions; %batteries% found a different 55,962; the full-text battery found 90,626 descriptions with either. LIKE also matches inside words (%cat% finds education) and gives you no ranking at all.

A pg_trgm GIN index makes ILIKE '%...%' fast in PostgreSQL (9 ms for titanium) and is the right tool when users search for fragments of codes, SKUs or e-mail addresses rather than words. It cost 9.6 s to build and 125 MB on the description column alone, five times the size of the full-text GIN index.

What the index costs on every insert

Search indexes are not free on writes. We inserted 10,000 rows in one INSERT ... SELECT with and without each index:

text
PostgreSQL 18.6
  no tsvector column                               12 ms
  stored tsvector column, no index                171 ms
  + GIN (default fastupdate = on)            196-206 ms
  + GIN with fastupdate = off                     399 ms
  pg_trgm GIN on description alone                499 ms
  both GIN indexes                        454 ms - 3.1 s
MySQL 8.4 (times include starting the mysql client)
  no FULLTEXT index                             68-70 ms
  FULLTEXT index                              138-146 ms
SQLite 3.54
  no FTS5 tables                                   14 ms
  two FTS5 tables kept in step by a trigger       119 ms

In PostgreSQL most of the cost is computing the tsvector (parsing and stemming the text), not the index. GIN's fastupdate collects new entries in a pending list and merges them later; that halves the insert time, but when the list passes gin_pending_list_limit (4 MB by default) the unlucky insert that triggers the merge pays for everyone, which is why the two-index runs ranged from 0.45 to 3.1 s. Autovacuum also merges the list, so on a table with steady writes this mostly evens out.

MySQL doubles the insert time and keeps deleted rows in the index: after deleting 30,000 test rows, information_schema.innodb_ft_deleted listed 30,000 document ids still waiting to be filtered out at query time. optimize table with innodb_optimize_fulltext_only = on clears them; schedule it after big deletes.

SQLite with external content does nothing on its own: if a row changes and you forget to tell the FTS table, the index lies. Triggers on insert, update and delete are the usual answer (the FTS5 documentation has the exact three), or insert into products_fts(products_fts) values ('rebuild') after bulk changes, which took 2.7 s here.

sql
create trigger products_ai after insert on products begin
  insert into products_fts(rowid, name, description)
  values (new.id, new.name, new.description);
end;
create trigger products_ad after delete on products begin
  insert into products_fts(products_fts, rowid, name, description)
  values ('delete', old.id, old.name, old.description);
end;
create trigger products_au after update on products begin
  insert into products_fts(products_fts, rowid, name, description)
  values ('delete', old.id, old.name, old.description);
  insert into products_fts(rowid, name, description)
  values (new.id, new.name, new.description);
end;

Which one, and when to reach for a search engine

If you are on PostgreSQL, use it: stemming that works, a query function made for raw user input, phrase and prefix search, weighted columns, and the fastest ranked queries in our test. Budget disk for the stored tsvector, rank with ts_rank_cd and a tie-breaker, and add pg_trgm only if users search for fragments rather than words.

On MySQL, FULLTEXT is usable once you know its rules: build the query yourself, drop stop words and words under three characters before adding +, or lower innodb_ft_min_token_size if your products are full of 4K, AA and TV. Define FTS_DOC_ID up front on big tables, and run OPTIMIZE TABLE after big deletes.

On SQLite (a desktop app, a phone, a small site), FTS5 was the fastest of the three here and builds in seconds. Use porter for English, weight the name column, quote user input, and keep the index in step with triggers.

A separate search engine starts to pay for itself when you need things none of these do well: typo tolerance (titanum finds nothing in any of the three; pg_trgm similarity can patch it, but it is a second query to write and tune), synonyms (sofa for couch), counts per category beside the results across millions of rows, many languages in one index, or more search traffic than your main database should carry. Until then, the database you already run is enough.

Try these queries on your own tables: Pilotbase connects to PostgreSQL, MySQL, SQLite and more, with a SQL editor and a results grid. Free for Windows, macOS and Linux, or Docker on a server.

Get Pilotbase

Need a million rows to test search on before your real data arrives? testData writes one INSERT ... SELECT that fills your tables on PostgreSQL, MySQL or SQLite.

Open testData

Related: Partial indexes: index only the rows your queries ask for · Case-insensitive text in Postgres: citext, lower() or a nondeterministic collation · SQLite in production: WAL, busy_timeout and backups that work

Sources: PostgreSQL 18 documentation: full text search (websearch_to_tsquery, setweight, ts_rank and ts_rank_cd, stored generated tsvector columns), GIN fast update and gin_pending_list_limit, pg_trgm. MySQL 8.4 reference manual: InnoDB full-text indexes (FTS_DOC_ID, deleted-document table, innodb_optimize_fulltext_only), full-text stopwords (InnoDB's default list), fine-tuning full-text search (innodb_ft_min_token_size, restart and rebuild), boolean mode. SQLite FTS5 (external content tables and triggers, bm25 weights, rank configuration, porter tokenizer, query syntax). All numbers are from our own run on 2026-10-27: PostgreSQL 18.6, MySQL 8.4.11, SQLite 3.54.1 (Python) and 3.54.0 (shell) on an Apple M4 Mac mini; one million generated product rows, seed 432.