Something went wrong. Try again.
A community based topic aggregation platform built on atproto
Something went wrong. Try again.
1.0 kB · 20 lines
SQL
at main
123456789101112131415161718192021-- +goose Up-- Store the search vector so the planner always uses the GIN index and ranking-- never re-tokenises post bodies. English matches the configured search parser;-- title matches rank above content matches through A/B weights.-- Bound inputs to stay below tsvector's 1 MiB limit: the firehose can index-- federated content that never passed the local API's 3,000/100,000-byte caps.-- left() counts characters, so content within either byte cap remains complete.-- ADD COLUMN ... GENERATED ... STORED rewrites posts under an ACCESS EXCLUSIVE lock.ALTER TABLE posts ADD COLUMN search_vector tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('english', left(coalesce(title, ''), 3000)), 'A') || setweight(to_tsvector('english', left(coalesce(content, ''), 100000)), 'B') ) STORED;
CREATE INDEX idx_posts_search_vector ON posts USING gin(search_vector) WHERE deleted_at IS NULL;
-- +goose DownDROP INDEX IF EXISTS idx_posts_search_vector;ALTER TABLE posts DROP COLUMN IF EXISTS search_vector;