Skip to main content

max / makenotwork

2.5 KB · 62 lines History Blame Raw
1 -- Denormalized active-item count per project (ultra-fuzz Run 11 Perf SER-3).
2 --
3 -- discover_projects computed item_count with a LEFT JOIN items + GROUP BY +
4 -- COUNT(...) FILTER over the whole public catalog on every discover page load —
5 -- O(items) per project. This caches the count on the project row.
6 --
7 -- "Active" matches the discover filter exactly: is_public AND listed AND
8 -- deleted_at IS NULL. The count is maintained by a trigger on `items`, not by
9 -- application code, so no write path can forget to adjust it and the value can't
10 -- drift (the maintenance lives in one place, atomic with the item write).
11
12 ALTER TABLE projects ADD COLUMN item_count INTEGER NOT NULL DEFAULT 0;
13
14 -- Backfill from the current truth.
15 UPDATE projects p SET item_count = (
16 SELECT COUNT(*) FROM items i
17 WHERE i.project_id = p.id
18 AND i.is_public = true
19 AND i.listed = true
20 AND i.deleted_at IS NULL
21 );
22
23 CREATE OR REPLACE FUNCTION sync_project_item_count() RETURNS TRIGGER AS $$
24 DECLARE
25 old_active BOOLEAN;
26 new_active BOOLEAN;
27 BEGIN
28 IF TG_OP = 'INSERT' THEN
29 IF NEW.is_public AND NEW.listed AND NEW.deleted_at IS NULL THEN
30 UPDATE projects SET item_count = item_count + 1 WHERE id = NEW.project_id;
31 END IF;
32 RETURN NEW;
33 ELSIF TG_OP = 'DELETE' THEN
34 IF OLD.is_public AND OLD.listed AND OLD.deleted_at IS NULL THEN
35 UPDATE projects SET item_count = item_count - 1 WHERE id = OLD.project_id;
36 END IF;
37 RETURN OLD;
38 ELSE -- UPDATE
39 old_active := OLD.is_public AND OLD.listed AND OLD.deleted_at IS NULL;
40 new_active := NEW.is_public AND NEW.listed AND NEW.deleted_at IS NULL;
41 IF OLD.project_id <> NEW.project_id THEN
42 -- Item moved between projects: settle the count on both sides.
43 IF old_active THEN
44 UPDATE projects SET item_count = item_count - 1 WHERE id = OLD.project_id;
45 END IF;
46 IF new_active THEN
47 UPDATE projects SET item_count = item_count + 1 WHERE id = NEW.project_id;
48 END IF;
49 ELSIF old_active AND NOT new_active THEN
50 UPDATE projects SET item_count = item_count - 1 WHERE id = NEW.project_id;
51 ELSIF new_active AND NOT old_active THEN
52 UPDATE projects SET item_count = item_count + 1 WHERE id = NEW.project_id;
53 END IF;
54 RETURN NEW;
55 END IF;
56 END;
57 $$ LANGUAGE plpgsql;
58
59 CREATE TRIGGER trg_sync_project_item_count
60 AFTER INSERT OR UPDATE OR DELETE ON items
61 FOR EACH ROW EXECUTE FUNCTION sync_project_item_count();
62