Index conversation, message, memory and artifact foreign keys #76

2026-10-12T23:02:54-04:00

ryan commented

2026-10-12 23:02:54 -04:00

Postgres had only primary keys on conversations, conversation_messages, user_memories and artifacts, so conversation loads/listing, the first-message check on every send, and memory/artifact lookups were sequential scans.

Adds: conversations(user_id, updated_at), conversation_messages(conversation_id, created_at), user_memories(user_id), artifacts(conversation_id), artifacts(message_id).

Migration 19 generated with aerich against a throwaway pgvector DB (not hand-written). Besides the indexes it contains the no-op JSONB re-casts aerich emits every run (as in migrations 16 and 18); verified they don't rewrite tables (relfilenode unchanged). Upgrade and downgrade both verified. Backward-compatible for blue-green.

🤖 Generated with Claude Code

Postgres had only primary keys on conversations, conversation_messages, user_memories and artifacts, so conversation loads/listing, the first-message check on every send, and memory/artifact lookups were sequential scans. Adds: conversations(user_id, updated_at), conversation_messages(conversation_id, created_at), user_memories(user_id), artifacts(conversation_id), artifacts(message_id). Migration 19 generated with aerich against a throwaway pgvector DB (not hand-written). Besides the indexes it contains the no-op JSONB re-casts aerich emits every run (as in migrations 16 and 18); verified they don't rewrite tables (relfilenode unchanged). Upgrade and downgrade both verified. Backward-compatible for blue-green. 🤖 Generated with [Claude Code](https://claude.com/claude-code)

ryan added 1 commit 2026-10-12 23:02:54 -04:00

Index conversation, message, memory and artifact foreign keys 746aaa4844

Postgres had only primary keys on these tables, so loading a
conversation, listing conversations, checking for a first message on
every send, and fetching memories/artifacts were sequential scans.
Migration generated with aerich; index-only apart from the no-op JSONB
re-casts aerich emits on every run (as in migrations 16 and 18), which
were checked not to rewrite tables. Upgrade and downgrade verified on a
throwaway pgvector database.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

ryan merged commit b2a53821f8 into main

2026-10-12 23:06:09 -04:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: ryan/simbarag#76