STGY Full-Text Search

This article explains STGY's full-text search for posts and users. Instead of adding full-text indexes to PostgreSQL, STGY uses TTTS (time-tiered text search), an independent service built on SQLite FTS5. The search index is split into multiple files by time range; newer files are searched first, and processing stops as soon as enough results have been collected.

This feature prioritizes retrieving recent documents within a bounded amount of time over finding the documents with the highest relevance score. The search index is treated as derived data that can be rebuilt from PostgreSQL. Updates are asynchronous so that a failure of the search service does not directly block post or user updates.

Why Full-Text Search Is a Separate Service

If the only requirement were simple body-text search over posts and user profiles, PostgreSQL could execute a query such as the following.

SELECT id
FROM post_details
WHERE content ILIKE '%検索語%';

This approach, however, requires reading a broad range of bodies that might match the query. Adding a full-text index can accelerate the search, but then a large index must be updated whenever a post or user changes. Queries for common terms ordered by recency can also have very large candidate sets. If a full-text index first enumerates candidates and another index then orders them by time, it becomes difficult to predict the work required as data volume grows.

STGY does not need sophisticated relevance ranking like a general Web search engine. It is enough for the search boxes in post lists, user lists, and external publication sites to accept a string and retrieve a bounded set of matching documents. TTTS therefore encodes an order that favors newer candidates directly in the index's internal IDs and partitions searchable data into chronological ranges.

Making full-text search a separate service also has the following advantages.

  • Heavy search processing is less likely to interfere directly with ordinary PostgreSQL queries.
  • Search-index update failures are isolated from post and user update operations.
  • The search index can be discarded and rebuilt from PostgreSQL.
  • Posts and users can have independent index files and settings.
  • The search service can later be moved to a separate host more easily.

The tradeoff is that a PostgreSQL write and its reflection in the search index no longer belong to the same transaction. Search results may temporarily be stale. Accepting that property, STGY provides a queue that prevents update requests from being lost and administrative functions that can rebuild the index.

Overall Architecture

Full-text search involves PostgreSQL, STGY backend workers, TTTS, SQLite, and Redis. The approximate flow when a post is created or updated is as follows.

投稿をPostgreSQLへ保存
  ↓
search_indexing_tasksへインデックス更新要求を追加
  ↓
searchIndexWorkerが更新要求を取得
  ↓
TTTSの/postsへADDまたはREMOVEを送信
  ↓
TTTS内部の永続キューへ登録
  ↓
SQLite FTS5の該当シャードを更新してCOMMIT

The search flow is as follows.

フロントエンド
  ↓
STGYバックエンドの/posts/searchまたは/users/search
  ↓
Redisの検索結果キャッシュを確認
  ↓
TTTSの/posts/searchまたは/users/search
  ↓
新しいSQLiteシャードから順にFTS5検索
  ↓
実検索トークン列・フレーズ単位列と文書ID配列を返す
  ↓
PostgreSQLから投稿またはユーザの表示用データを取得
  ↓ 必要なら
フレーズ単位列ごとのRedis KWICキャッシュを確認
  ↓ cache missした表示中IDだけ
PostgreSQLから原文を取得してKWIC ASTを生成

TTTS runs posts for post documents and users for user documents as separate resources. They share one HTTP server, but their index files, task queues, settings, and search processing are independent. Resource names also prefix the filenames.

posts-1700000000.db
posts-common.db
users-1700000000.db
users-common.db

*-common.db is the SQLite database that stores task queues, while filenames containing timestamps are FTS5 index shards.

Documents Included in Search

For posts, the indexed text is not raw Markdown source. It is plain text produced by removing presentation syntax from Markdown. Headings and body text are not treated as separate fields; the entire text is tokenized as one body. If the post title appears as an H1 heading in the body, it is searchable just like any other text.

The locale used to tokenize a post is determined in the following order.

投稿に明示されたlocale
  ↓ nullなら
投稿所有者のlocale
  ↓ nullなら
STGY_DEFAULT_LOCALE
  ↓ それもなければ
en

The same priority is used when a post is created, updated, or when the entire search index is rebuilt. A post's locale in the database may be null, but an effective locale passed to TTTS is always determined.

For a user, the searchable text is the nickname followed by a newline and the profile text, with Markdown in the profile converted to plain text.

ニックネーム
自己紹介のプレーンテキスト

A user document uses that user's locale. Because locale is a required user attribute, fallback is normally unnecessary.

The document timestamp is the creation time, in seconds, extracted from the Snowflake ID of the post or user. It is not the post's publication time or update time. Editing a post therefore does not move it to another chronological shard, and its TTTS candidate order remains based on creation time.

PostgreSQL-Side Update Queue

When a post or user is updated, the STGY backend does not synchronously call TTTS. Instead, it registers an index-update request in PostgreSQL's search_indexing_tasks table.

CREATE TABLE search_indexing_tasks (
  id BIGINT PRIMARY KEY,
  name_prefix VARCHAR(50) NOT NULL,
  doc_id VARCHAR(100) NOT NULL,
  doc_timestamp BIGINT NOT NULL,
  body_text TEXT,
  locale VARCHAR(10)
);
CREATE INDEX idx_search_indexing_tasks_name_prefix_id
ON search_indexing_tasks(name_prefix, id);

The workers for posts and users fetch tasks in ID order with a fixed name_prefix, so they use the composite index (name_prefix, id). This allows each worker to obtain a constant number of the oldest tasks for its prefix without scanning a backlog belonging to another prefix.

name_prefix is either posts or users. A null body_text means delete; a non-null value means add or update. Given the document ID and timestamp, the shard containing a document to delete can be identified.

searchIndexWorker runs independent processing loops for posts and users. Each loop retrieves up to 100 of the oldest tasks and sends them to TTTS in order. A PostgreSQL task is deleted only after TTTS has accepted the request into its internal queue.

search_indexing_tasks
  ↓ 最大100件取得
TTTSへ送信
  ↓ HTTP成功
処理済みタスクをPostgreSQLから削除

If the worker stops after sending to TTTS but before deleting the PostgreSQL task, the same request may be sent again. Adding is replacement by the same document ID, and deleting a nonexistent document is a no-op, so duplicate execution does not change the final state. The system therefore aims for at-least-once execution and tolerates duplicates rather than attempting strict exactly-once delivery.

By default, the worker waits 500 milliseconds after each task. It also waits after failures and after completing a pass. The priority is not to drain index updates as quickly as possible, but to avoid excessive load on ordinary backend processing and TTTS.

TTTS's Persistent Internal Queues

A request received by TTTS is not applied directly to FTS5 immediately. Each resource has the following queues in its common database.

  • document_tasks: document add/delete requests that have not begun processing
  • batch_tasks: document requests being applied to an FTS5 transaction or waiting for COMMIT
  • management_tasks: administrative requests such as flush, optimize, reconstruct, reserve, and shard deletion

When processing document tasks, TTTS first moves them from document_tasks to batch_tasks. This move is performed in a single transaction within the common database.

document_tasks
  ↓ BEGIN IMMEDIATE
batch_tasksへINSERT
  ↓
document_tasksからDELETE
  ↓ COMMIT
batch_tasks

It then updates the FTS5 database of the target shard. A row in batch_tasks is not deleted until the corresponding shard transaction has been committed.

batch_tasksに保持
  ↓
FTS5へADDまたはREMOVE
  ↓
SQLiteインデックスをCOMMIT
  ↓
batch_tasksから削除

This ordering is important for recovery. If TTTS stops after making uncommitted FTS5 updates, SQLite rolls those updates back, while the corresponding batch_tasks remain. At startup TTTS reads the remaining tasks, reexecutes them, and commits all affected shards.

Conversely, if TTTS stops after the FTS5 COMMIT but before deleting batch_tasks, the same operation is reexecuted on the next startup. Add and delete operations are idempotent, so preferring duplicate execution over lost updates is safer.

Administrative tasks use a queue separate from document tasks. When administrative tasks exist, they are processed before document tasks. Maintenance mode stops document-task processing but still allows administrative tasks.

Chronological Sharding of the Index

Instead of one enormous FTS5 database, document creation times are divided into fixed-duration buckets, each stored in an independent SQLite file.

The shard start time is calculated with the following expression.

floor(timestamp / bucketDurationSeconds) * bucketDurationSeconds

The bucket duration can be configured independently for posts and users through environment variables.

STGY_SEARCH_POSTS_BUCKET_DURATION
STGY_SEARCH_USERS_BUCKET_DURATION

In the repository's current env.dev.txt, env.vps.txt, env.native.txt, and env.hybrid.txt, both are set to 50,000,000 seconds. This creates roughly one shard every 579 days. The code defaults differ between posts and users, but normal distributed configurations override them with environment variables.

During search, shards are traversed from newest start time to oldest. Each shard is asked only for the number still needed; once offset + limit results have been collected, older shards are not searched.

最新シャードを検索
  ↓ 件数が不足
一世代前を検索
  ↓ 必要数に到達
終了

In an SNS, recent posts are more likely to be viewed, and search results are also commonly expected newest first. Instead of always searching all historical documents, TTTS moves into older shards only when necessary, limiting work for typical searches even after the overall dataset becomes large.

Splitting shards into separate files also confines recovery when corruption occurs. The detailed shard-list API checks whether each file can be opened and whether table and FTS5 statistics can be read, reporting files that cannot be opened as unhealthy shards. The administrative utility can rebuild only those unhealthy shards from PostgreSQL.

FTS5 Data Structure

Each shard mainly contains the following tables.

CREATE TABLE id_tuples (
  internal_id INTEGER PRIMARY KEY,
  external_id TEXT UNIQUE,
  labels_json TEXT NOT NULL DEFAULT '[]',
  numeric_value REAL
);

CREATE TABLE extra_attrs (
  external_id TEXT PRIMARY KEY,
  attrs TEXT
);

CREATE TABLE fts_meta (
  k TEXT PRIMARY KEY,
  v INTEGER
);

CREATE VIRTUAL TABLE docs USING fts5(
  tokens,
  labels,
  tokenize = "unicode61 categories 'L* N* Co M* P* S*' remove_diacritics 0 tokenchars ' '",
  detail = 'none'
);

recordPositions, recordContents, and autoPhraseCheck are configurable through environment variables.

STGY_SEARCH_RECORD_POSITIONS
STGY_SEARCH_RECORD_CONTENTS
STGY_SEARCH_AUTO_PHRASE_CHECK

The current env.dev.txt, env.vps.txt, env.native.txt, and env.hybrid.txt set recordPositions=false, recordContents=true, and autoPhraseCheck=false. The schema example above therefore uses detail='none': FTS5 posting lists retain document IDs but no column numbers or token positions. With recordPositions=true, TTTS uses detail='full' and records token positions. With recordContents=false, TTTS creates the FTS5 table as contentless (content='' and contentless_delete=1). Body tokens and search-space labels remain separate stored columns, but non-positional search does not use column filters.

Before indexing a label, TTTS converts it to a synthetic token of the form U+E000 + "L" + label and stores it in the labels column. U+E000 is reserved for TTTS internal tokens. If external body text, a search query, or a label contains U+E000, TTTS replaces it with U+0020 SPACE before tokenization or label normalization. Body-derived terms therefore cannot collide with synthetic label terms. Search does not use tokens: or labels: column filters; instead, synthetic label conditions and body conditions are placed in one table-wide MATCH expression. FTS5 can intersect those posting lists inside the same table, so with recordPositions=false there is no need to retain column information and detail='none' is sufficient. At the same time, with recordContents=true the stored tokens and labels columns remain separate, so pseudo-phrase verification and fetched body text can use tokens directly.

This synthetic-label representation is not backward-compatible with indexes that stored raw labels in the labels column. When upgrading TTTS to this format, reset the search indexes and rebuild them from PostgreSQL.

recordPositions and recordContents are recorded in fts_meta when a shard is created. When an existing shard is opened, TTTS uses the values in fts_meta rather than the current environment, so changing the environment does not alter the structure or search semantics of existing shards. To apply either changed setting to existing data, reset the search index and regenerate it with the new settings. autoPhraseCheck, by contrast, is a query-time option that does not change the index format and is not stored in fts_meta. Restarting TTTS with a different value applies it to existing shards without rebuilding the index.

External IDs are stored in external_id and FTS5 rowid uses a small integer internal_id. Newer documents receive smaller internal IDs, so ORDER BY docs.rowid ASC yields newer documents first.

Search uses docs CROSS JOIN id_tuples to keep FTS5 as the outer loop and looks up id_tuples by INTEGER PRIMARY KEY for each candidate. Synthetic label conditions are placed before the body condition in a table-wide FTS5 MATCH expression without column filters, so label and body posting lists are intersected with the labels leading. FTS5's case-insensitive label match is accepted directly; there is no labels_json recheck. numeric_value is evaluated as a predicate on the same candidate and has no separate search B-tree index.

Regression tests inspect EXPLAIN QUERY PLAN to require FTS5 scan → INTEGER PRIMARY KEY lookup and no USE TEMP B-TREE. This preserves newest-first LIMIT early termination after adding attribute filters.

Tokenization and Normalization

TTTS does not pass documents directly to FTS5. Node.js tokenizes them first and stores the tokens joined by LF characters. The LF characters do not preserve source line breaks; they are an internal separator that makes one TTTS token correspond to one FTS5 term.

The following normalization is applied first.

  • Normalize Unicode with NFKC.
  • Convert letters to lowercase.
  • Replace control characters and invisible formatting characters with spaces.
  • Replace U+E000, reserved for TTTS internal tokens, with U+0020 SPACE.
  • Remove diacritical marks within tokens.

For example, differences in case and full-width/half-width forms are absorbed, and Café is generally stored as cafe.

When Kuromoji is enabled for Japanese, morphological analysis uses Kuromoji's IPADIC dictionary. Otherwise, JavaScript's standard Intl.Segmenter is used at word granularity. Docker TTTS sets STGY_SEARCH_ENABLE_KUROMOJI=true.

Because ordinary word segmentation can discard punctuation, technical terms such as the following are extracted in preprocessing and preserved as single tokens.

C++     → c++
C#      → c#
.NET    → .net
node.js → node.js

Ordinary symbol-separated text, on the other hand, follows normal word segmentation.

check-out → check, out
24/7      → 24, 7

The FTS5 unicode61 configuration permits punctuation and symbols as token characters and additionally adds U+0020 SPACE through tokenchars. Printable characters and SPACE inside body tokens or synthetic labels therefore remain inside one term, while the LF separators inserted by TTTS split terms. This prevents values such as c++ or a label containing SPACE from being split again by FTS5. The U+E000 prefix of a synthetic label is a Private Use character (category Co), so the unicode61 configuration retains it inside the term. Terms containing characters with special meaning in an FTS5 query expression are quoted when the expression is built.

A search query replaces U+E000 with SPACE before parsing whitespace-delimited input units. Thus foo<U+E000>bar is handled exactly like foo bar as two input units and, even when positions are recorded, cannot accidentally become one phrase merely because the reserved character appeared between the words.

A document is tokenized as a whole using its specified effective locale. No per-line language detection is performed. For a search query, the specified locale is treated as the preferred candidate and a simple character-based locale detection is applied.

  • If hiragana or katakana is present, use Japanese.
  • If Hangul is present, use Korean.
  • If Han characters are present and the preferred locale is Chinese, use Chinese.
  • Otherwise, Han characters imply Japanese.
  • If there is no evidence, use the specified locale.

This is not a sophisticated language identifier. The document language trusts the locale of the post or its owner, while the query side only corrects obvious Japanese, Chinese, or Korean scripts.

A single document may register at most 50,000 tokens. A search query may use at most eight tokens. These limits prevent huge bodies or extremely complex searches from consuming excessive memory or CPU.

Tokenizer output can be inspected through the HTTP API.

curl --get 'http://localhost:3200/posts/tokenize' \
  --data-urlencode 'text=私はC++言語が好きです' \
  --data-urlencode 'locale=ja'

With Kuromoji enabled, the result is approximately as follows.

["私","は","c++","言語","が","好き","です"]

Search Query Semantics

Each whitespace-delimited input unit is tokenized independently. On recordPositions=true shards, if one such unit becomes multiple tokens, those tokens are searched as one positional phrase even without double quotes. Thus if インストールや設定作業 becomes the internal tokens インストール, や, 設定 and 作業, the search still requires them to be consecutive. Separate whitespace-delimited units remain AND conditions.

When recordPositions=false, recordContents=true, and autoPhraseCheck=true, an unquoted input unit containing Han, hiragana, katakana, or Hangul is also treated automatically as a phrase when it yields multiple tokens. For example, if 脚本家 becomes 脚本 and 家, FTS5 first narrows candidates with 脚本 AND 家 and TTTS then requires 脚本\n家 to occur consecutively in the stored canonical token sequence. The automatic check is not applied to input units containing only non-CJK scripts. With the default autoPhraseCheck=false, the tokens retain ordinary AND semantics. If recordContents=false, consecutive-token verification is impossible, so the query falls back to AND even when autoPhraseCheck=true.

Double quotes explicitly request a phrase. On recordPositions=false shards with recordContents=true, a quoted multi-token phrase first narrows candidates with FTS5 AND terms and then checks the stored canonical token sequence in docs.tokens for consecutive tokens. Synthetic labels are stored in the separate labels column and therefore never enter pseudo-phrase verification. Quoted phrases keep this behavior regardless of autoPhraseCheck.

The UI does not provide OR, NOT, wildcard, fuzzy, or related-term expansion. A search query may contain at most eight tokens.

Both GET /{resource}/search and GET /{resource}/search-fetch return a top-level object with tokens, phrases and result. tokens is the normalized minimal content-token sequence that TTTS actually used to build the FTS5 query, after the query token limit has been applied and before FTS5 quoting or AND/phrase serialization. phrases is the complete sequence of search units for downstream presentation such as KWIC. Every effective term appears there: a one-token term is copied as one phrase, while multiple tokens that are actually constrained to be consecutive are represented by one phrase string. For example, a non-positional search with autoPhraseCheck=false for インストールや設定作業 may return tokens = ["インストール", "や", "設定", "作業"] and the same four strings in phrases; a positional search for the same input may return tokens = ["インストール", "や", "設定", "作業"] but phrases = ["インストールや設定作業"]. With no positions but autoPhraseCheck=true, if 脚本家 is split into 脚本 and 家 and constrained by the canonical-token check, the response is tokens = ["脚本", "家"] and phrases = ["脚本家"]. Order and duplicates are preserved. Label and numeric filters are not included. result contains document IDs for search and fetched TTTS documents for search-fetch. Empty search results still return both lists.

Restricting the Search Space with labels and numeric_value

Each document may carry zero or more opaque labels and one nullable generic numeric_value. A label may contain arbitrary printable characters and U+0020 SPACE as one term and is matched case-insensitively by FTS5. U+E000 in external input is reserved and normalized to U+0020 SPACE; the normalized label is stored in labels_json. For the FTS5 labels column, TTTS additionally prefixes the label with U+E000 + "L" and stores the resulting synthetic label token. Search does not use a column filter; the synthetic label term is placed in the same table-wide MATCH expression as body terms. Multiple labels use AND semantics, and label conditions are placed before the body condition. Numeric filters support eq, gt, gte, lt and lte. TTTS assigns no application-specific meaning to them. labels_json is not rechecked during search.

STGY posts use owner:<userId> as a label and the external publication time in epoch milliseconds as numericValue. Unpublished posts use NULL and scheduled posts use a future timestamp. Logged-in search converts owner:<userId> / owner:me into a label filter and status:published into numeric_value <= now.

Because labels participate directly in the FTS5 posting-list intersection, user-specific search does not have to enumerate body hits across the whole SNS and then check owner afterward. Numeric filtering is a post-filter on FTS candidates. If H candidates must be examined to obtain K results in a shard of N documents, the metadata-check overhead is approximately O(H log N). With numeric-filter pass rate p, roughly H ≈ K/p; the worst case examines all M candidates matching the body plus labels.

Anonymous Search from External Publication Sites

Search on /sites/[id] does not call TTTS during SSR. After hydration, a Client Component calls /posts/search with credentials: "omit". Anonymous search requires ownedBy; the backend forces owner:<ownedBy> and numeric_value <= now and PostgreSQL rechecks owned_by and published_at <= now after TTTS returns candidate IDs.

Anonymous search APIs send the SHA-1 of a canonical representation of all query parameters in the X-STGY-QueryHash header. This value is not secret; it cheaply rejects primitive clients that replay a discovered URL without reproducing client logic. Because it is a custom header, cross-origin JavaScript requires CORS preflight, and allowed origin is limited to the STGY frontend.

In production Caddy, /backend/posts/search and /backend/posts/kwic-pub share the search API IP limit of 60 requests per hour. STGY also has a general site-wide rate limit. Anonymous search shares a fixed dummy user's DailyTimerThrottleService("db") daily processing-time budget. All STGY pages also deny framing with frame-ancestors 'none' and X-Frame-Options: DENY.

Search Processing and Result Order

TTTS exposes /posts/search and /users/search and returns {tokens, phrases, result}, where result contains candidate IDs newest first by creation time. The STGY backend preserves both tokens and phrases and returns the same top-level shape after loading post/user display data from PostgreSQL. For ordinary logged-in search, the backend requests enough IDs for the displayed offset + limit range.

External-publication post search obtains a bounded TTTS candidate set, rechecks owner and publication time in PostgreSQL, and pages it by publication time newest first. The current /sites/[id] search UI does not show the Oldest switch.

TTTS is a derived candidate index. PostgreSQL remains authoritative for post/user display data and publication eligibility.

Search-Result Cache in Redis

The STGY backend caches the TTTS tokens and phrases together with candidate-ID arrays for post and user searches in Redis. Post-search keys include query, locale, owner, publication scope, and anonymous/authenticated mode.

Ordinary search expands the candidate cache as deeper result ranges are requested. When the candidate array is expanded, the backend preserves the key's remaining TTL instead of extending its expiration time from the original creation. External-publication search caches a bounded candidate set so result navigation and KWIC/Rich/Plain switching do not repeat the same TTTS search. The default TTL is 180 seconds.

offset + limit is bounded. Logged-in search is subject to per-user DB processing-time limits, while anonymous search uses the shared dummy-user budget.

KWIC

KWIC (Keyword In Context) is not generated by TTTS. The frontend uses only the phrases returned by the search response as KWIC keywords and never reconstructs phrase boundaries from tokens or re-parses the raw query string. Because phrases is complete, ordinary one-token search terms are present there as well. A non-positional Japanese query such as インストールや設定作業 may therefore produce four phrase units and anchor KWIC around each actual AND term, while the same positional query may produce the same four tokens but the single KWIC unit phrases = ["インストールや設定作業"].

The STGY backend caches KWIC in a Redis HASH keyed by the resource type and a hash of the ordered phrases array. Document IDs are HASH fields. All requested IDs are read in one HMGET; only cache misses are fetched from PostgreSQL and converted by packages/markdown into KWIC ASTs, then added with one HSET. Labels, numeric filters, owner/publication constraints, offset, and limit do not change the KWIC for a given document and ordered phrase list, so they are deliberately excluded from the KWIC cache key. The same document KWIC can therefore be reused across different searches that produce the same ordered phrases.

The KWIC HASH uses the same default 180-second duration as the search cache, but its lifetime is independent. TTL is assigned only when that keyword HASH is first created; later HSET operations for additional documents do not extend it. Before /posts/kwic-pub returns cache hits, PostgreSQL rechecks those cached IDs for current publication eligibility; cache misses fetch source content with the same publication condition as before. The anonymous-publication boundary therefore cannot be bypassed by the KWIC cache.

The backend provides:

GET /posts/kwic      # authenticated
GET /users/kwic      # authenticated
GET /posts/kwic-pub  # anonymous, published posts only

/posts/kwic-pub requires X-STGY-QueryHash and anonymous throttling and checks published_at <= now in PostgreSQL.

For posts, the Markdown title is kept whole. The body contains at most four segments, normally extending 40 pseudo tokens on each side of a keyword. To prevent one keyword from occupying all segments, each keyword may be used as an anchor at most ceil(maxSegments / keywordCount) times; overlapping segments are merged. The AST contains text / highlight nodes, startPosition / endPosition / isStart / isEnd for body segments, and keywordIndex for highlights. Ellipsis and colors are renderer decisions.

KWIC matching folds NFKC, case, and Latin diacritics. Within runs of Latin, Greek, Cyrillic, Number, or Mark characters, matches inside a larger word-like run are rejected, so pro is not highlighted inside profile. No additional morphological analyzer is instantiated for CJK, where substring false positives are accepted.

Internal post and user search can switch between KWIC and Rich. External-publication post search can switch between KWIC, Rich, and Plain. KWIC is the default, and the external search UI does not show the Oldest switch.

Transactions and COMMIT

TTTS does not COMMIT an FTS5 transaction for every document task. Multiple additions and deletions are grouped into one transaction. Processing the first document task for a shard issues BEGIN, and subsequent tasks are added to that same transaction.

The automatic COMMIT conditions are as follows.

  • At least 10,000 uncommitted updates
  • At least 60 seconds since the transaction began

The current implementation does not test these conditions after every document task. The latest shard is checked every 30 seconds and all shards every 600 seconds. COMMIT is also performed before operations that require consistency, such as flush, optimize, reserve, reconstruct, and orderly shutdown.

Batching updates makes FTS5 segment creation and WAL writes more efficient. The tradeoff is a delay between TTTS accepting a document request and that document becoming searchable. The search index is intentionally eventually consistent.

The main SQLite settings are as follows.

page_size         = 8192 bytes
journal_mode      = WAL
synchronous       = NORMAL
busy_timeout      = 5000 ms
journal_size_limit= 64 MiB
FTS block size    = 8000 bytes

Using WAL allows reader connections to search while a writer is updating. synchronous=NORMAL favors write performance over full synchronization while still relying on SQLite transaction consistency.

Connections and Cache by Generation

Because newer shards are searched more frequently, SQLite connection counts and cache sizes vary by generation.

The default settings are as follows.

*Generation**Reader connections**mmap**SQLite cache**FTS automerge*
Latest2256MiB24MiB8
Previous generation2256MiB24MiB2
Two generations old10400KiB2
Three generations old10400KiB2
Older00400KiB2

For old shards with zero reader connections, the writer connection is also used for search. The two newest, frequently searched generations receive multiple read-only connections and mmap, while resident memory for old shards is kept small.

Readers are chosen round-robin. Even when several search requests arrive simultaneously, they are less likely to concentrate on a single connection for the newest shard. When the shard layout changes, connection counts and cache settings are reapplied according to generation order.

HTTP API and wait

TTTS document updates are asynchronous. The following is an example of adding a document.

curl -i -X PUT 'http://localhost:3200/posts/19F998DD11300000' \
  -H 'Content-Type: application/json' \
  -d '{
    "text": "全文検索の対象となる本文",
    "timestamp": 1780000000,
    "locale": "ja",
    "attrs": null,
    "labels": ["owner:0000000000020101"],
    "numericValue": 1780000000000
  }'

attrs, labels and numericValue are required fields of the PUT snapshot. PUT is a complete snapshot rather than a partial update. The TTTS document queue stores the same complete latest state, and differences from SQLite are evaluated only immediately before applying the task. With recordContents=true, an unchanged canonical body token sequence avoids an FTS5 rewrite. With recordContents=false, the body is always treated as changed and is re-applied to FTS5. attrs=null, labels=[] and numericValue=null are explicit current states.

On search endpoints, repeating label creates an AND condition, while numericOp and numericValue form a numeric filter. For example: ?label=owner%3A0000000000020101&numericOp=lte&numericValue=1780000000000. These filters narrow an ordinary full-text query; TTTS still requires a non-empty body query.

If wait is omitted, TTTS returns 202 Accepted as soon as the request has been registered in its queue.

{
  "result": "accepted",
  "taskId": "d-123"
}

If wait is a positive number of seconds, including a fractional value, TTTS waits for task completion for that duration.

?wait=0.25
?wait=1.5

If the task completes in time, TTTS returns 200 OK and completed. For a document task, the row is removed from batch_tasks only after the corresponding index transaction is committed, so successful completion means persistence into the search index has been reached.

If it does not complete in time, TTTS returns 408 Request Timeout.

{
  "result": "timeout",
  "taskId": "d-123",
  "message": "The task is still being processed"
}

A 408 does not cancel the task. Background processing continues. wait is not a cancellation deadline for the whole operation; it only limits how long the HTTP client waits synchronously.

The main administrative APIs are as follows. The resource component is either posts or users.

  • GET /{resource}/maintenance: check maintenance mode
  • POST /{resource}/maintenance: enter maintenance mode
  • DELETE /{resource}/maintenance: leave maintenance mode
  • POST /{resource}/flush: COMMIT all shards
  • POST /{resource}/optimize: optimize FTS5 and VACUUM the specified shard
  • POST /{resource}/reconstruct: rebuild the specified shard into another file
  • POST /{resource}/reserve: reserve internal_id values corresponding to document IDs
  • GET /{resource}/shards: list shards
  • DELETE /{resource}/shards/{timestamp}: delete a shard
  • DELETE /{resource}/queue: clear the task queue
  • GET /{resource}/tokenize: inspect tokenization output
  • GET /{resource}/search: return {tokens, phrases, result}, with matching document IDs in result
  • GET /{resource}/search-fetch: return {tokens, phrases, result}, with fetched TTTS documents in result

Clearing the queue and reserve require maintenance mode so that ordinary document additions and deletions are stopped and cannot race with these administrative operations.

Rebuilding the Index

Because the search index is derived data from PostgreSQL, the entire index can be rebuilt at any time. From the repository root, use the following command.

npm run reset-search

This command runs the reset process in searchUtil for both users and posts. The approximate steps are as follows.

メンテナンスモードを開始
  ↓
TTTS内部のタスクキューを消去
  ↓
既存シャードを削除
  ↓
PostgreSQLのIDを古い順に走査してinternal_idを予約
  ↓
メンテナンスモードを終了
  ↓
PostgreSQLの文書を新しい順にTTTSへ再投入
  ↓
flushしてCOMMIT

internal_id values are reserved first so that document creation order and rowid ASC order remain aligned even if reinsertion parallelism or processing order changes. Snowflake IDs are reserved from oldest to newest, assigning smaller internal_id values to newer documents.

To rebuild only unhealthy shards, use the following.

npm run reset-search-restore

In this case, only files that could not be opened by the detailed shard-list check are deleted, and documents from those time ranges are reinserted from PostgreSQL.

TTTS's reconstruct API is different from rereading documents from PostgreSQL. It copies the documents and token sequences already present in an existing shard into a new SQLite file, renumbers internal_id values, optimizes FTS5, and then replaces the original file. It is used to create headroom in internal IDs or compact a file without changing logical data.

Behavior During Failures

Because search-index updates involve several services, a process stop or communication failure can occur at any point. The current design prefers allowing duplicate execution over losing an update request.

The main failure points and recovery behavior are as follows.

searchIndexWorker Stops Before Sending to TTTS

The request remains in PostgreSQL search_indexing_tasks and is fetched again after restart.

The Worker Stops After TTTS Accepts the Request but Before Deleting the PostgreSQL Task

The request remains on both the PostgreSQL and TTTS sides and may be sent again. Add and delete operations for the same document ID are idempotent, so the final state does not change.

TTTS Stops Before the FTS5 COMMIT

Uncommitted SQLite updates are rolled back, but the requests remain in the common database's batch_tasks. They are reexecuted on the next startup.

TTTS Stops After the FTS5 COMMIT but Before Deleting batch_tasks

The same request is reexecuted at the next startup. This is also idempotent, so the document is not registered twice.

A Search-Index File Is Corrupted or Lost

PostgreSQL is the source of truth, so the affected shard or the whole index is rebuilt. There is no need to manage special, highly protected backups of the search index itself. Search results may be incomplete or full-text search unavailable while rebuilding is in progress.

Even with this design, PostgreSQL and TTTS are not guaranteed to represent the same instant in time. Worker delays, TTTS's internal wait before COMMIT, and the Redis cache lifetime all introduce latency before changes appear in search results. Full-text search is eventually consistent; it does not guarantee that a newly created post is immediately searchable.

Performance Characteristics

TTTS prioritizes the following properties.

  • Search chronological shards newest first and stop when enough results are collected.
  • Intersect owner and other labels with body terms in FTS5 before ordinary-table checks.
  • Keep FTS5 as the outer loop with CROSS JOIN and check metadata by primary-key lookup.
  • Treat the low-selectivity numeric condition as a predicate on FTS candidates rather than a B-tree search starting point.
  • Bound repeated work with Redis caching and search-result limits.
  • Return both the actual normalized TTTS search tokens and the complete phrase-unit list with search results, consult the Redis HASH for that ordered phrase list first, and generate KWIC only for displayed cache misses without changing TTTS search complexity.

No relevance score is calculated; ordinary TTTS candidate order is creation-time newest first.

Limitations

  • No relevance score, OR/NOT, wildcard, or fuzzy search is provided.
  • With recordPositions=false and recordContents=true, quoted phrases and CJK input units selected by autoPhraseCheck=true verify the canonical token sequence after candidate retrieval. With recordContents=false, consecutive-token verification is unavailable and these inputs degrade to AND terms.
  • A low numeric-filter pass rate can require examining substantially more FTS candidates than the requested result count.
  • Search indexes and Redis caches are eventually consistent; PostgreSQL performs final current-state checks.
  • The anonymous query hash is not authentication. Bots that implement direct HTTP are constrained by Caddy and processing-time throttles instead.

Summary

STGY uses TTTS, a chronologically sharded SQLite FTS5 service, as a derived full-text index. Posts carry an owner:<userId> label and publication-time numericValue. The FTS5 → primary-key metadata-check execution order is fixed so search-space restrictions preserve newest-first early termination.

External-publication search rechecks owner and published_at <= now in both TTTS filtering and PostgreSQL. Anonymous APIs layer X-STGY-QueryHash, CORS, Caddy IP rate limits, and a shared processing-time budget. KWIC remains outside TTTS and uses the complete phrases list returned by TTTS search. A Redis HASH keyed by the ordered phrase list is reused first, and only displayed cache misses are generated from source Markdown. The search index remains rebuildable derived data rather than an authoritative store.