Something went wrong. Try again.
A community based topic aggregation platform built on atproto
Something went wrong. Try again.
3.3 kB · 57 lines
SQL
at main
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758-- +goose Up-- +goose NO TRANSACTION-- Read-path indexes, and removal of indexes that only cost writes.---- CONCURRENTLY throughout: posts, comments and votes are the largest, most-- written tables, and a plain CREATE/DROP INDEX would block the firehose-- consumers' writes for the whole build.
-- Discover's new and top sorts, and the timeline's, order posts across-- communities. Every existing posts index leads with community_did, so both-- read and sorted the entire table per request. Walking these instead stops at-- the page's LIMIT. The trailing uri matches the feeds' final tiebreaker.CREATE INDEX CONCURRENTLY idx_posts_created ON posts (created_at DESC, uri DESC) WHERE deleted_at IS NULL;CREATE INDEX CONCURRENTLY idx_posts_score ON posts (score DESC, created_at DESC, uri DESC) WHERE deleted_at IS NULL;
-- Comment permalinks and "continue this thread" (getComments?parentRkey=) find-- the parent by (root_uri, rkey). idx_comments_root has no rkey, so the lookup-- read every comment in the thread, on every page.CREATE INDEX CONCURRENTLY idx_comments_root_rkey ON comments (root_uri, rkey);
-- The subscription consumer resolves an unsubscribe by its record URI, which-- had no index: every unsubscribe event, and every replay of one, scanned the-- whole table.CREATE INDEX CONCURRENTLY idx_subscriptions_record_uri ON community_subscriptions (record_uri);
-- Each of these duplicates the index behind a UNIQUE constraint exactly (or is-- a prefix of it), so the planner never needs it, and every insert and every-- non-HOT update of the row paid to maintain it. Votes update posts.score, so-- that is every vote.DROP INDEX CONCURRENTLY IF EXISTS idx_posts_uri; -- posts_uri_keyDROP INDEX CONCURRENTLY IF EXISTS idx_votes_uri; -- votes_uri_keyDROP INDEX CONCURRENTLY IF EXISTS idx_votes_voter_subject; -- unique_voter_subject_activeDROP INDEX CONCURRENTLY IF EXISTS idx_comments_uri; -- comments_uri_keyDROP INDEX CONCURRENTLY IF EXISTS idx_comments_uri_lookup; -- comments_uri_keyDROP INDEX CONCURRENTLY IF EXISTS idx_subscriptions_user_community; -- community_subscriptions_user_did_community_did_keyDROP INDEX CONCURRENTLY IF EXISTS idx_subscriptions_user; -- prefix of the same key
-- +goose Down-- +goose NO TRANSACTIONCREATE INDEX CONCURRENTLY IF NOT EXISTS idx_subscriptions_user ON community_subscriptions(user_did);CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_subscriptions_user_community ON community_subscriptions(user_did, community_did);CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_comments_uri_lookup ON comments(uri);CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_comments_uri ON comments(uri);CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_votes_voter_subject ON votes(voter_did, subject_uri) WHERE deleted_at IS NULL;CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_votes_uri ON votes(uri);CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_posts_uri ON posts(uri);
DROP INDEX CONCURRENTLY IF EXISTS idx_subscriptions_record_uri;DROP INDEX CONCURRENTLY IF EXISTS idx_comments_root_rkey;DROP INDEX CONCURRENTLY IF EXISTS idx_posts_score;DROP INDEX CONCURRENTLY IF EXISTS idx_posts_created;