Postgres BIND waiting when tootctl statuses remove is running

[wiegelmann]

Steps to reproduce the problem

Execute

RAILS_ENV=production bin/tootctl statuses remove

Expected behaviour

Remove unreferenced statuses from the database

Actual behaviour

Postgres BIND waiting and Mastodon UI not reachable for 7-9 minutes

Detailed description

• While job is running around 100+ queries stuck for 7-9 minutes until job completed
• 70M records in statuses table
• Postgres reports bind waiting
• Mastodon UI not working while job is running

duration: 118137.071 ms  statement: CREATE INDEX CONCURRENTLY IF NOT EXISTS "index_media_attachments_remote_url" ON "media_attachments" ("remote_url") WHERE remote_url is not null

duration: 8688.548 ms  statement: CREATE INDEX CONCURRENTLY IF NOT EXISTS "index_accounts_local" ON "accounts" ("id") WHERE domain is null

duration: 430940.325 ms  execute <unnamed>: INSERT INTO statuses_to_be_deleted (id) SELECT statuses.id FROM statuses WHERE deleted_at IS NULL AND NOT local AND uri IS NOT NULL AND (id < $1) AND NOT EXISTS (SELECT 1 FROM statuses AS statuses1 WHERE statuses.id = statuses1.in_reply_to_id) AND NOT EXISTS (SELECT 1 FROM statuses AS statuses1 WHERE statuses1.id = statuses.reblog_of_id AND (statuses1.uri IS NULL OR statuses1.local)) AND NOT EXISTS (SELECT 1 FROM statuses AS statuses1 WHERE statuses.id = statuses1.reblog_of_id AND (statuses1.uri IS NULL OR statuses1.local OR statuses1.id >= $1)) AND NOT EXISTS (SELECT 1 FROM status_pins WHERE statuses.id = status_id) AND NOT EXISTS (SELECT 1 FROM mentions WHERE statuses.id = mentions.status_id AND mentions.account_id IN (SELECT accounts.id FROM accounts WHERE domain IS NULL)) AND NOT EXISTS (SELECT 1 FROM favourites WHERE statuses.id = favourites.status_id AND favourites.account_id IN (SELECT accounts.id FROM accounts WHERE domain IS NULL)) AND NOT EXISTS (SELECT 1 FROM bookmarks WHERE statuses.id = bookmarks.status_id AND bookmarks.account_id IN (SELECT accounts.id FROM accounts WHERE domain IS NULL)) AND NOT EXISTS (SELECT 1 FROM follows WHERE statuses.account_id = follows.target_account_id) RETURNING "id"

Mastodon instance

No response

Mastodon version

v4.2.8

Technical details

$ node --version
ruby 3.2.3 (2024-01-18 revision 52bb2ac0a6) [x86_64-linux]

$ node --version
v20.11.0

$ pg_config --version
PostgreSQL 16.1 (Debian 16.1-1.pgdg110+1)