Moving Mastodon search from Elasticsearch to ParadeDB

Mastodon works like Twitter: short posts, replies, follows. The difference is that no single company runs the whole network. Anyone can run a server, called an instance, and instances talk to each other. I run two: pegelinux.top and fedi.my.id. A post on Mastodon is called a status, or a toot. Search here means searching old statuses, not the web.

Elasticsearch is a separate search engine. It was the only part of my setup that had nothing to do with Mastodon itself. pegelinux.top ran ES 7.17.24 in Docker, fed by Chewy, the code that copies new statuses into Elasticsearch, so searching for “linux” did not have to scan 7 million statuses. fedi.my.id ran with search switched off, so it had no status search at all.

ParadeDB puts the index inside Postgres, the database Mastodon already uses. It ships a Postgres image with the pg_search extension, which adds BM25 full-text search to Postgres. Search then runs in the same database as the rest of the data. No JVM, same backup tooling, and I can query it with plain SQL.

I had no complaint about ES speed. ES is fast. I don’t like a Java-heavy process eating memory on the same box as Postgres. My complaint was that search had grown its own stack: another dataset to back up, another process to watch, another reindex ritual nobody runs until something breaks. Two instances meant two of those, except the second ran with search switched off.

The rest of this post covers the abstraction layer, the denormalized table, the double write, two backfills, the cutover, and the bugs. The bugs took most of my time.

A switch, not a fork

I run glitch-soc, a Mastodon variant with extra features, plus five patches of my own on top. Adding a hard dependency on a new search engine would have made the next rebase worse. So the first patch is an abstraction layer that changes nothing.

module SearchBackend
  class << self
    def current
      @current ||= begin
        name = ENV.fetch('SEARCH_BACKEND') { Chewy.enabled? ? 'elasticsearch' : 'database' }.downcase
        case name
        when 'paradedb' then SearchBackends::Paradedb.new
        when 'elasticsearch', 'chewy', 'es' then SearchBackends::Elasticsearch.new
        else SearchBackends::Database.new
        end
      end
    end

    def search_enabled?
      !database? && (paradedb? || Chewy.enabled?)
    end

    def dual_write?
      ENV['SEARCH_DUAL_WRITE'] == 'true'
    end
  end
end

With no SEARCH_BACKEND set, the default is still “Chewy if ES_ENABLED, otherwise database”. Upstream installs do not notice the patch exists. Rollback is one environment variable, not a revert. I kept the ES index alive until the parity check passed, then stopped the container.

The three knobs:

SEARCH_BACKEND=paradedb
SEARCH_DUAL_WRITE=false
PARADEDB_DATABASE_URL=postgresql://postgres@paradedb:5432/mastodon_search

search_enabled? also replaced Chewy.enabled? in InitialStateSerializer, which is what decides whether the frontend shows the advanced search menu at all. This comes back at cutover.

Do not index the statuses table

The obvious move is a BM25 index on statuses. It does not work. statuses.text holds HTML, and Mastodon only strips it in Ruby. searchable_by, the array of account ids allowed to find a status with in:library, is computed from mentions, favourites, reblogs, bookmarks, and polls. There is no way to build a BM25 index across those joins.

So the patch adds a denormalized table, status_search_documents, kept current by the existing write paths:

CREATE TABLE status_search_documents (
  status_id bigint PRIMARY KEY,
  account_id bigint NOT NULL,
  account_domain varchar,
  searchable_text text NOT NULL DEFAULT '',
  tags varchar[] DEFAULT '{}',
  language varchar,
  visibility integer NOT NULL DEFAULT 0,
  indexable boolean NOT NULL DEFAULT false,
  properties varchar[] DEFAULT '{}',
  searchable_by bigint[] DEFAULT '{}',
  status_created_at timestamp(6) NOT NULL,
  created_at timestamp(6) NOT NULL,
  updated_at timestamp(6) NOT NULL
);

CREATE INDEX ON status_search_documents USING gin (searchable_by);
CREATE INDEX ON status_search_documents USING gin (tags);

CREATE INDEX index_status_search_documents_on_bm25
  ON status_search_documents
  USING bm25 (
    status_id, searchable_text, tags, account_id, account_domain,
    language, visibility, indexable, properties, status_created_at,
    searchable_by
  )
  WITH (key_field = 'status_id');

Both the extension and the index are conditional. The migration wraps CREATE EXTENSION pg_search in a DO block that raises a notice instead of failing, and the bm25 index only gets created if the access method exists. A plain Postgres can run the migration and the app falls back to ILIKE.

pg_search 0.26 logs key_field is deprecated as of 0.26.0 and is a no-op. I left the clause in because older builds require it. It runs and does nothing.

Translating the query DSL

Mastodon’s search box is not a plain string. It is a grammar written with Parslet, a Ruby parser library (from:, has:, is:, before:, in:, quoted phrases, negation). SearchQueryTransformer compiles it into an AST, a tree-shaped version of the query, which then emits Elasticsearch hashes.

The patch adds a second visitor over the same AST instead of forking the parser. ParadedbQueryTransformer turns each clause into SQL. A query for linux has:media from:@someone becomes roughly:

SELECT status_id FROM status_search_documents
WHERE searchable_text @@@ 'linux'
  AND 'media' = ANY (properties)
  AND account_id = ANY (ARRAY[1234])
  AND ((visibility = 0 AND indexable = true)
       OR searchable_by @> ARRAY[9876]::bigint[])
ORDER BY status_id DESC
LIMIT 20 OFFSET 0;

Each clause becomes a SQL fragment. @@@ matches the whole searchable_text column.

has: and is: bind different values to the same array check. from: resolves through Account.find_remote first. must terms join with AND. must_not terms become NOT (...), joined with OR, so a -b -c reads as a AND NOT (b OR c). Every query also gets the visibility predicate (visibility = 0 AND indexable = true) OR searchable_by @> ARRAY[?], and StatusFilter still runs on the way out.

Ordering stays ORDER BY status_id DESC, the same as the ES path. This is recency-sorted search, not relevance-sorted.

Results come back as status ids only. The service rehydrates Status rows and runs them through StatusFilter. A BM25 score is not a privacy model.

When the bm25 access method is missing, every positive term falls back to ILIKE. One of the bugs below came from that fallback.

Double write

pegelinux.top kept Elasticsearch as the read path while every status write also produced a document in ParadeDB. That is what SEARCH_DUAL_WRITE=true does. The hook sits in Status::SearchConcern:

after_commit :sync_search_documents, on: [:create, :update]
after_destroy_commit :remove_search_document

def sync_search_documents
  return unless SearchBackend.paradedb? || SearchBackend.dual_write?

  UpsertStatusSearchDocumentWorker.perform_async(id)
end

Sidekiq runs background jobs. The worker goes on a new search Sidekiq queue, three jobs at a time. It is idempotent by design: status_id is the primary key and every write is find_or_initialize_by(status_id:), so a retry overwrites instead of duplicating.

fedi.my.id skipped this phase entirely. It never had a working ES index, so there was nothing to compare against. It was written straight into ParadeDB and validated by searching.

The bugs

Most of the time went to bugs. Three reviews before the first deploy. Two of them said no.

Search is not a module

First boot on the server:

/opt/mastodon/app/lib/search/backend.rb:3:in '<main>': Search is not a module (TypeError)
/opt/mastodon/app/models/search.rb:3: previous definition of Search was here

Mastodon already has class Search < ActiveModelSerializers::Model in app/models/search.rb. I named the new namespace Search::Backend, and Zeitwerk, the code loader, refused to reopen a class as a module. Everything moved: SearchBackend, SearchBackends::Paradedb, UpsertStatusSearchDocumentWorker. The rename touched 17 files. It only shows up when the app boots, after the image is built.

status.deleted? does not exist

Mastodon soft-deletes statuses with Discard: it marks them deleted instead of removing them, configured as discard_column = :deleted_at. Discard gives you discarded? and kept?. There is no deleted?.

Both the worker and the model called status.deleted? in the upsert path. Every single upsert raised NoMethodError, Sidekiq retried three times, and the job died. The backfill had the same call, so every row in the CLI deploy would have failed too. status_search_documents would have stayed empty while the logs looked like normal retry noise.

visibility_before_type_cast stored private toots as public

The document row stores visibility as an integer, and 0 is public in Mastodon’s enum. The first version wrote status.visibility_before_type_cast, which is the string "private" for a private toot. Cast to an integer, that string becomes 0. Every private and unlisted status landing in the index would have been marked public.

The fix is Status.visibilities[status.visibility]. No exception, no log line. The only symptom was wrong values in the table.

The queue that did not exist

config/sidekiq.yml had no search queue. Sidekiq accepted the job, then never looked at it. Documents for new statuses never appeared. The dual-write window would have shown a growing gap between ES and ParadeDB with no error anywhere.

-word queries and the missing index

Without pg_search, @@@ is not a valid operator. The positive-term path fell back to ILIKE, but the negative branch kept emitting NOT (searchable_text @@@ ?) unconditionally. Any query with -word raised a SQL error on a plain Postgres, and the service’s rescue turned that into an empty result list. Search looked like it worked and returned nothing. Fixed with an ILIKE fallback in both branches, escaped with sanitize_sql_like.

Postgres 18 does not mount there anymore

The ParadeDB container crash-looped on first start:

Error: in 18+, these Docker images are configured to store database data
in a format which is compatible with pg_ctlcluster ... there appears to be
PostgreSQL data in: /var/lib/postgresql/data (unused mount/volume)

The volume was ./data:/var/lib/postgresql/data, the standard Postgres mount path. In 18 the image expects /var/lib/postgresql. Changed the mount, wiped the half-initialized directory, started clean.

The backfill that looked frozen

tootctl is Mastodon’s command-line admin tool. tootctl search deploy --backend=paradedb uses ruby-progressbar, which needs a terminal (a TTY) to draw the progress bar. Detached inside Docker, stdout is buffered and the progress bar renders nothing. The command looked hung at Progress:.

Proof of life came from polling the other side: 0 → 21,599 → 41,796, about 250 documents per second. Run the backfill under tmux, or with docker compose exec -d, and watch the count in Postgres instead of the terminal.

Three backfills at once

At some point three tootctl processes were iterating overlapping ranges. The table survived it because status_id is the primary key and the upsert is idempotent. I checked with one query, which returned zero rows:

SELECT status_id, count(*) FROM status_search_documents
GROUP BY status_id HAVING count(*) > 1;

I killed the extras anyway. They were doubling the write load for nothing.

--clean hung on a cross-database query

The CLI’s --clean deletes documents whose status no longer exists:

valid_ids = Status.pluck(:id)
StatusSearchDocument.where.not(status_id: valid_ids).delete_all

With the document table in a separate database, that is no longer a subquery. It is 7 million integers loaded into Ruby, formatted into one enormous NOT IN list. The process sat at 6,865,432, nine rows from the end, pg_stat_activity showed no active query, and strace showed no syscalls.

kill -9, rerun with --no-clean. That flag now means “skip the cross-database orphan check”, which is the right default when the table lives on another connection.

restart does not re-read the env file

After the cutover, pegelinux.top’s search box no longer offered the search options. The env file said SEARCH_BACKEND=paradedb. The container said:

$ docker compose exec mastodon-web env | grep SEARCH_BACKEND
SEARCH_BACKEND=database

docker compose restart restarts the process inside the existing container. env_file is read at container creation, so the container kept the old value. The web container had been created before that line was edited. Recreating it fixed it:

docker compose up -d mastodon-web mastodon-streaming sidekiq

Same trap on fedi.my.id, where the container still pointed at the wrong database. docker inspect --format '{{json .Config.Env}}' is the fastest way to see what a container actually believes.

Two instances, one table

The original plan was one search database shared by both instances. Snowflake ids are unique by design and don’t collide, so I assumed the same remote toot would have the same id everywhere.

Wrong. The id is local to the instance. The same remote status got a different id on each instance, and account_id and searchable_by are local too. A shared table would have mixed two instances’ visibility rules under one primary key.

Created fedimyid_search, dumped the schema, pointed PARADEDB_DATABASE_URL at it, and backfilled again.

The backfill

Both backfills used the same task, run inside each web container:

docker compose exec -d <web-service> bundle exec \
  tootctl search deploy --backend=paradedb --batch-size=500 --no-clean

On pegelinux the service is mastodon-web; on fedi.my.id it is fedi-web. --batch-size=500 sets how many statuses are read per iteration. -d detaches, because a foreground run dies with the shell. --no-clean skips the orphan check that hung later.

The fedi numbers come from the parallel run, so it shared the ParadeDB container with pegelinux.

The gap is expected. The backfill only walks Status.unscoped.kept.without_reblogs, and refresh_for_status! skips reblogs, drops discarded rows, and refuses to index accounts that are not indexable. Direct messages never enter the table. I also checked the tail: the highest indexed status id matched the newest live status id.

pegelinux hit 6,864,559 (94.3%) before the machine rebooted mid-run. It resumed from where it stopped, because the job is an upsert keyed by status_id.

Cutover

Before flipping anything, both backends got the same query. Ten results for linux, same account, same limit:

117513140088817275  117512991337875180  117512922428425364
117512440440179638  117510463202594524  117510356466938241
117510167677336671  117510162335639804  117510058563752452
117510029025083482

Same ten ids, same order. Both backends sort newest-first, so this shows they return the same set, not that their relevance ranking matches. has:media linux differed only because ParadeDB had newer statuses from the dual write that ES had not indexed yet.

Then ES came out:

ES_ENABLED=false
SEARCH_BACKEND=paradedb
SEARCH_DUAL_WRITE=false

sed -i '56,89 s/^\(.*\)$/#\1/' docker-compose.yml   # comment out the es: service
sed -i 's/- es/#- es/' docker-compose.yml           # drop depends_on: es
docker stop mastodon-es-1
docker compose up -d --scale sidekiq=2 --remove-orphans mastodon-web mastodon-streaming sidekiq

Verification was three lines in the Rails console:

SearchBackend.current.class.name   # => "SearchBackends::Paradedb"
SearchBackend.search_enabled?      # => true
SearchService.new.call('linux', Account.local.first, 5)[:statuses].size  # => 5

Memory

ParadeDB’s PG18 image auto-tunes on first boot. On a 15.57 GiB host it chose shared_buffers = 3985MB, exactly 25% of RAM. It also picked maintenance_work_mem = 996MB and work_mem = 39MB. Postgres keeps shared_buffers resident until it restarts, so after the backfill finished the container kept sitting at 3.8 GiB with nothing to do.

Capped it in compose:

command: postgres -c shared_buffers=1536MB -c work_mem=16MB

I left maintenance_work_mem alone because it only matters during index builds.

Container memory went from 3.826 GiB to 102.5 MiB, and host available memory from 3.2 GiB to 7.3 GiB. The index itself sat at 4.4 GB on disk at 94% backfill, under 700 bytes per status, with a 1.5 GB shared buffer pool and the OS page cache covering the rest.

What is still rough

It works, but I am not done.

• No latency numbers. I compared result sets, not timing, so I have no p50 or p95 for ParadeDB and no ES baseline.
• Quoted phrases are AND-ed words. "exact phrase" does not use a phrase matcher yet. It searches both words and accepts any document containing them.
• #tag searches text, not the tags array. The # gets stripped and the term goes into searchable_text, which mostly works because people write the hashtag in the body anyway. Mostly.
• searchable_by goes stale. It updates on status create, update, and delete. Favourites, bookmarks, and new mentions do not rebuild it, so in:library can miss statuses you just saved.
• db/schema.rb was never regenerated. The table lives in a separate database and is created by rake paradedb:migrate, so the primary schema stays untouched. That is deliberate, but db/schema.rb no longer describes everything the app depends on.
• No specs. I never ran the new classes under rspec, and I wrote none. A spec that touched the upsert path would have caught status.deleted?.

Both instances now run SEARCH_BACKEND=paradedb on separate databases (mastodon_search and fedimyid_search). No Elasticsearch container is running and live search returns results. Next I want latency numbers, because right now the performance claim is unproven.