desertthunder.dev
a love letter to tangled (android, iOS, and a search API)
1---
2title: ADR Research - PostgreSQL For Production Search
3updated: 2026-03-25
4status: research
5---
6
7## Summary
8
9PostgreSQL is the strongest production candidate if Twister needs a conventional server database with mature operational tooling, strong concurrent write handling, and a full-text search system designed for configurable ranking and long-lived production workloads.
10
11It is not the cheapest path from the current codebase. Moving to PostgreSQL would require rewriting the current SQLite/libSQL search layer, migrations, and indexing queries.
12
13## Why Consider It
14
15Twister's current search hardening plan is explicitly about reducing experimentation overhead. That does not answer the production backend question. PostgreSQL should stay in scope because it offers:
16
17- native full-text search primitives (`tsvector`, `tsquery`)
18- ranking and highlighting support
19- GIN indexes for inverted-index style search
20- mature backup, restore, replication, and managed-hosting options
21- familiar operational patterns for multi-process or multi-host deployments
22
23## Fit For Twister
24
25### Strengths
26
27#### Good Match For Write-Heavy Ingest
28
29Twister has several write-producing paths:
30
31- Tap ingestion
32- read-through indexing jobs
33- JetStream activity cache writes
34- future embedding and reindex jobs
35
36PostgreSQL is designed for this class of service. If Twister eventually runs API, workers, and background consumers as separate production processes, PostgreSQL is the least surprising option.
37
38#### Mature Full-Text Search
39
40PostgreSQL's text search stack includes:
41
42- document parsing and query parsing
43- configurable dictionaries and stop-word handling
44- ranking functions
45- headline generation
46- GIN indexes for `tsvector` search
47
48This means Twister can keep keyword search in-database without depending on a separate search engine.
49
50#### Operational Predictability
51
52For a service that may evolve into a more conventional always-on backend, PostgreSQL has the most established story for:
53
54- backups and point-in-time recovery
55- observability
56- migration tooling
57- managed production hosting
58- separation of app processes from storage
59
60## Costs And Risks
61
62### Migration Cost Is Real
63
64The current codebase is built around SQLite/libSQL semantics:
65
66- SQLite FTS5
67- current migration files
68- current query behavior and ranking assumptions
69
70Switching to PostgreSQL would require:
71
72- replacing FTS5 queries with PostgreSQL full-text search queries
73- rewriting migrations
74- revisiting search ranking and snippets
75- re-testing all search filters and pagination behavior
76
77This is a bigger code migration than staying inside the SQLite/libSQL family.
78
79### GIN Needs Operational Tuning
80
81PostgreSQL GIN indexes are powerful, but they are not free. The docs note that heavy updates can accumulate pending entries and shift cleanup cost into vacuum or foreground cleanup if not tuned carefully.
82
83For Twister, that means ingestion-heavy workloads would need explicit attention to:
84
85- autovacuum behavior
86- index maintenance settings
87- bulk reindex or reembed workflows
88
89### Search Behavior Will Change
90
91Even if the feature set remains the same, PostgreSQL text search will not behave identically to SQLite FTS5. Tokenization, ranking, and snippet behavior will need product-level review.
92
93## Repo-Specific Implications
94
95- The current API already uses `database/sql`, so adding a PostgreSQL driver is straightforward at the connection layer.
96- The hard part is the search repository and migration layer, not connection management.
97- PostgreSQL would decouple production deployment from single-host shared-disk assumptions introduced by the local experimentation workflow.
98
99## When PostgreSQL Is The Better Choice
100
101Choose PostgreSQL if most of the following become true:
102
103- Twister needs multiple long-running workers and API instances writing concurrently.
104- We want mainstream database operations over SQLite-family deployment tricks.
105- Search hardening turns into a durable production service rather than a lightweight adjunct.
106- The project can afford a query and migration rewrite now in exchange for simpler long-term production architecture.
107
108## When PostgreSQL Is Probably Not Worth It
109
110Avoid PostgreSQL for now if most of the following are true:
111
112- The immediate goal is just to stabilize experimentation.
113- We want the smallest migration from the current code.
114- Single-host or single-writer production remains acceptable.
115- Search behavior should stay as close as possible to current SQLite FTS5 behavior.
116
117## Recommendation
118
119PostgreSQL is the strongest long-term production architecture candidate, but not the lowest-friction next step.
120
121If the near-term goal is production hardening with minimal code churn, PostgreSQL should remain an evaluated option rather than the immediate default. If Twister grows into a multi-process write-heavy service, PostgreSQL likely becomes the cleanest production destination.
122
123## Sources
124
125- [PostgreSQL Full Text Search docs](https://www.postgresql.org/docs/current/textsearch.html)
126- [PostgreSQL GIN docs](https://www.postgresql.org/docs/current/gin.html)