refactor(analytics): add indexes to improve query performance

Commit `98e7f62`

Eric Bower · 2026-01-16 20:33:07 -0500 EST

parent c3c3ade

refactor(analytics): add indexes to improve query performance

2 files changed, +10, -1

M Makefile

A sql/migrations/20260116_add_analytics_filter_indexes.sql

M Makefile

+2, -1

......@@ -144,10 +144,11 @@ migrate:
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250418_add_project_post_idx_analytics.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251217_add_access_logs_table.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251226_add_pipe_monitoring.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260116_add_analytics_filter_indexes.sql
.PHONY: migrate

latest:
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251226_add_pipe_monitoring.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260116_add_analytics_filter_indexes.sql
.PHONY: latest

psql:

A sql/migrations/20260116_add_analytics_filter_indexes.sql

+8, -0

......@@ -0,0 +1,8 @@
CREATE INDEX analytics_visits_user_created_idx 
  ON analytics_visits(user_id, created_at DESC);

CREATE INDEX analytics_visits_user_host_created_status_idx 
  ON analytics_visits(user_id, host, created_at DESC, status);

CREATE INDEX analytics_visits_user_path_created_status_idx 
  ON analytics_visits(user_id, path, created_at DESC, status);

...	...		@@ -144,10 +144,11 @@ migrate:
144	144		$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250418_add_project_post_idx_analytics.sql
145	145		$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251217_add_access_logs_table.sql
146	146		$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251226_add_pipe_monitoring.sql
	147	+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260116_add_analytics_filter_indexes.sql
147	148		.PHONY: migrate
148	149
149	150		latest:
150		-	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251226_add_pipe_monitoring.sql
	151	+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260116_add_analytics_filter_indexes.sql
151	152		.PHONY: latest
152	153
153	154		psql:

...	...		@@ -0,0 +1,8 @@
	1	+	CREATE INDEX analytics_visits_user_created_idx
	2	+	ON analytics_visits(user_id, created_at DESC);
	3	+
	4	+	CREATE INDEX analytics_visits_user_host_created_status_idx
	5	+	ON analytics_visits(user_id, host, created_at DESC, status);
	6	+
	7	+	CREATE INDEX analytics_visits_user_path_created_status_idx
	8	+	ON analytics_visits(user_id, path, created_at DESC, status);

Commit 98e7f62

Commit `98e7f62`