internal/storage/postgres/migrations/20250912101325_add_multi_tenant_indexes.sql (3)

3-3: Index choice aligns with query pattern; consider DESC for clarity (optional) and verify duplicate functional indexes

The composite index (tenant_id, id) matches MAX(id) per tenant and supports backward scans. Adding DESC on id is optional (planner can scan backward regardless) but can better communicate intent.

Also, IF NOT EXISTS prevents name collisions but won’t avoid creating a duplicate functional index with a different name. Please verify no equivalent index already exists.

Apply this small clarity-oriented tweak if you prefer:

-CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_transactions_tenant_id ON transactions (tenant_id, id);
+CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_transactions_tenant_id ON transactions (tenant_id, id DESC);

To check for existing equivalent indexes by definition (not just by name), run these queries against your DB:

-- Transactions: list existing indexes that start with (tenant_id, id)
SELECT i.relname AS index_name, ix.indisvalid, pg_get_indexdef(i.oid) AS indexdef
FROM pg_class t
JOIN pg_index ix ON ix.indrelid = t.oid
JOIN pg_class i ON i.oid = ix.indexrelid
WHERE t.relname = 'transactions'
  AND ix.indisready
  AND pg_get_indexdef(i.oid) ILIKE '%(tenant_id, id%';

-- Ensure none of the returned indexdefs are functionally identical.

---

4-4: Good match for “latest schema version per tenant”; optional DESC for intent clarity and null handling check

This supports SELECT … WHERE tenant_id = $1 ORDER BY version DESC LIMIT 1 via index-only backward scan. Adding DESC on version is optional (planner can scan backward); consider it to better reflect the access pattern. Verify version is NOT NULL (or acceptable null ordering).

You may opt for:

-CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_schema_tenant_version ON schema_definitions (tenant_id, version);
+CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_schema_tenant_version ON schema_definitions (tenant_id, version DESC);

Confirm there isn’t already a functionally identical index:

SELECT i.relname AS index_name, ix.indisvalid, pg_get_indexdef(i.oid) AS indexdef
FROM pg_class t
JOIN pg_index ix ON ix.indrelid = t.oid
JOIN pg_class i ON i.oid = ix.indexrelid
WHERE t.relname = 'schema_definitions'
  AND ix.indisready
  AND pg_get_indexdef(i.oid) ILIKE '%(tenant_id, version%';

---

3-4: Consider schema-qualifying table names to avoid search_path surprises

If your goose runtime relies on search_path, unqualified names can target the wrong schema in some environments. Schema-qualify if your project standards require it (e.g., public.transactions). If search_path is pinned in migrations, you can ignore this.

Please confirm whether migrations enforce a fixed search_path. If not, consider qualifying the table names in this migration and future ones.

Configuration used: CodeRabbit UI
Review profile: CHILL
Plan: Pro

internal/storage/postgres/migrations/20250912101325_add_multi_tenant_indexes.sql (1)

2-5: Optional: embed rationale as SQL comments for future maintainers.

Short inline comments help future readers understand why these exact indexes exist and why the legacy one was dropped.

Apply this diff to add succinct rationale:

 -- +goose Up
+-- Rationale: Speed up per-tenant queries
+-- - transactions: allow index-only backward scan for MAX(id) WHERE tenant_id=?
+-- - schema_definitions: allow index-only backward scan for latest version per tenant
 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_transactions_tenant_id ON transactions (tenant_id, id);
 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_schema_tenant_version ON schema_definitions (tenant_id, version);
-DROP INDEX CONCURRENTLY IF EXISTS idx_schema_version;
+-- Drop legacy version-only index; composite (tenant_id, version) covers call-sites
+DROP INDEX CONCURRENTLY IF EXISTS idx_schema_version;

Configuration used: CodeRabbit UI
Review profile: CHILL
Plan: Pro