repos / pico

commit

83a5183

parent

dc6ed61

author

Eric Bower

date

2026-05-05 10:47:35 -0400 EDT

chore(visits): new indexes and parallel queries

 1@@ -151,10 +151,11 @@ migrate:
 2 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260116_add_analytics_filter_indexes.sql
 3 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260305_add_forms_table.sql
 4 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260503_add_analytics_summary_tables.sql
 5+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260504_add_analytics_summary_indexes.sql
 6 .PHONY: migrate
 7 
 8 latest:
 9-	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260503_add_analytics_summary_tables.sql
10+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260504_add_analytics_summary_indexes.sql
11 .PHONY: latest
12 
13 psql:

  1@@ -9,6 +9,7 @@ import (
  2 	"slices"
  3 	"sort"
  4 	"strings"
  5+	"sync"
  6 	"time"
  7 
  8 	"github.com/jmoiron/sqlx"
  9@@ -724,14 +725,28 @@ func visitFilterBy(opts *db.SummaryOpts) (string, string) {
 10 }
 11 
 12 func (me *PsqlDB) visitUnique(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
 13-	intervals, err := me.visitUniqueFromSummary(opts)
 14-	if err != nil {
 15-		return nil, fmt.Errorf("query summary visits: %w", err)
 16-	}
 17+	var intervals, currentIntervals []*db.VisitInterval
 18+	var sumErr, rawErr error
 19 
 20-	currentIntervals, err := me.visitUniqueFromRaw(opts)
 21-	if err != nil {
 22-		return nil, fmt.Errorf("query raw visits: %w", err)
 23+	var wg sync.WaitGroup
 24+	wg.Add(2)
 25+
 26+	go func() {
 27+		defer wg.Done()
 28+		intervals, sumErr = me.visitUniqueFromSummary(opts)
 29+	}()
 30+	go func() {
 31+		defer wg.Done()
 32+		currentIntervals, rawErr = me.visitUniqueFromRaw(opts)
 33+	}()
 34+
 35+	wg.Wait()
 36+
 37+	if sumErr != nil {
 38+		return nil, fmt.Errorf("query summary visits: %w", sumErr)
 39+	}
 40+	if rawErr != nil {
 41+		return nil, fmt.Errorf("query raw visits: %w", rawErr)
 42 	}
 43 
 44 	// Merge: current month data may overlap with summary data, combine counts
 45@@ -744,23 +759,13 @@ func (me *PsqlDB) visitUniqueFromSummary(opts *db.SummaryOpts) ([]*db.VisitInter
 46 	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
 47 	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
 48 
 49-	// Always include at least the previous month in the summary query, since raw data
 50-	// for the previous month may have been deleted by aggregation. visitUniqueFromRaw
 51-	// covers the previous and current months, but once aggregation runs and deletes raw
 52-	// data, the summary table is the only source. Using min(origin, previousMonthStart)
 53-	// ensures we don't skip the previous month even when origin is set to the current month.
 54-	effectiveOrigin := opts.Origin
 55-	if opts.Origin.After(previousMonthStart) {
 56-		effectiveOrigin = previousMonthStart
 57-	}
 58-
 59-	// If effective origin is in or after current month, no historical data to fetch
 60-	if !effectiveOrigin.Before(currentMonthStart) {
 61+	// If origin is in the previous month or later, raw data covers it — no summary to fetch.
 62+	if !opts.Origin.Before(previousMonthStart) {
 63 		return nil, nil
 64 	}
 65 
 66 	where := ""
 67-	args := []interface{}{opts.UserID, effectiveOrigin, currentMonthStart}
 68+	args := []interface{}{opts.UserID, opts.Origin, currentMonthStart}
 69 	argIdx := 4
 70 	if opts.Host != "" {
 71 		where = "AND host = $" + fmt.Sprintf("%d", argIdx)
 72@@ -953,14 +958,28 @@ func mergeHosts(historical, current []*db.VisitUrl) []*db.VisitUrl {
 73 }
 74 
 75 func (me *PsqlDB) visitReferer(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
 76-	historical, err := me.visitRefererFromSummary(opts)
 77-	if err != nil {
 78-		return nil, fmt.Errorf("query summary referers: %w", err)
 79-	}
 80+	var historical, current []*db.VisitUrl
 81+	var histErr, rawErr error
 82 
 83-	current, err := me.visitRefererFromRaw(opts)
 84-	if err != nil {
 85-		return nil, fmt.Errorf("query raw referers: %w", err)
 86+	var wg sync.WaitGroup
 87+	wg.Add(2)
 88+
 89+	go func() {
 90+		defer wg.Done()
 91+		historical, histErr = me.visitRefererFromSummary(opts)
 92+	}()
 93+	go func() {
 94+		defer wg.Done()
 95+		current, rawErr = me.visitRefererFromRaw(opts)
 96+	}()
 97+
 98+	wg.Wait()
 99+
100+	if histErr != nil {
101+		return nil, fmt.Errorf("query summary referers: %w", histErr)
102+	}
103+	if rawErr != nil {
104+		return nil, fmt.Errorf("query raw referers: %w", rawErr)
105 	}
106 
107 	return mergeTopReferers(historical, current), nil
108@@ -972,18 +991,13 @@ func (me *PsqlDB) visitRefererFromSummary(opts *db.SummaryOpts) ([]*db.VisitUrl,
109 	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
110 	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
111 
112-	effectiveOrigin := opts.Origin
113-	if opts.Origin.After(previousMonthStart) {
114-		effectiveOrigin = previousMonthStart
115-	}
116-
117-	// If effective origin is in or after current month, no historical data to fetch
118-	if !effectiveOrigin.Before(currentMonthStart) {
119+	// If origin is in the previous month or later, raw data covers it — no summary to fetch.
120+	if !opts.Origin.Before(previousMonthStart) {
121 		return nil, nil
122 	}
123 
124 	// Clamp origin to month boundary for summary table lookup
125-	originMonthStart := time.Date(effectiveOrigin.Year(), effectiveOrigin.Month(), 1, 0, 0, 0, 0, time.UTC)
126+	originMonthStart := time.Date(opts.Origin.Year(), opts.Origin.Month(), 1, 0, 0, 0, 0, time.UTC)
127 
128 	where := ""
129 	args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
130@@ -1059,14 +1073,28 @@ func (me *PsqlDB) visitRefererFromRaw(opts *db.SummaryOpts) ([]*db.VisitUrl, err
131 }
132 
133 func (me *PsqlDB) visitUrl(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
134-	historical, err := me.visitUrlFromSummary(opts)
135-	if err != nil {
136-		return nil, fmt.Errorf("query summary urls: %w", err)
137-	}
138+	var historical, current []*db.VisitUrl
139+	var histErr, rawErr error
140 
141-	current, err := me.visitUrlFromRaw(opts)
142-	if err != nil {
143-		return nil, fmt.Errorf("query raw urls: %w", err)
144+	var wg sync.WaitGroup
145+	wg.Add(2)
146+
147+	go func() {
148+		defer wg.Done()
149+		historical, histErr = me.visitUrlFromSummary(opts)
150+	}()
151+	go func() {
152+		defer wg.Done()
153+		current, rawErr = me.visitUrlFromRaw(opts)
154+	}()
155+
156+	wg.Wait()
157+
158+	if histErr != nil {
159+		return nil, fmt.Errorf("query summary urls: %w", histErr)
160+	}
161+	if rawErr != nil {
162+		return nil, fmt.Errorf("query raw urls: %w", rawErr)
163 	}
164 
165 	return mergeTopUrls(historical, current), nil
166@@ -1078,18 +1106,13 @@ func (me *PsqlDB) visitUrlFromSummary(opts *db.SummaryOpts) ([]*db.VisitUrl, err
167 	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
168 	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
169 
170-	effectiveOrigin := opts.Origin
171-	if opts.Origin.After(previousMonthStart) {
172-		effectiveOrigin = previousMonthStart
173-	}
174-
175-	// If effective origin is in or after current month, no historical data to fetch
176-	if !effectiveOrigin.Before(currentMonthStart) {
177+	// If origin is in the previous month or later, raw data covers it — no summary to fetch.
178+	if !opts.Origin.Before(previousMonthStart) {
179 		return nil, nil
180 	}
181 
182 	// Clamp origin to month boundary for summary table lookup
183-	originMonthStart := time.Date(effectiveOrigin.Year(), effectiveOrigin.Month(), 1, 0, 0, 0, 0, time.UTC)
184+	originMonthStart := time.Date(opts.Origin.Year(), opts.Origin.Month(), 1, 0, 0, 0, 0, time.UTC)
185 
186 	where := ""
187 	args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
188@@ -1170,14 +1193,28 @@ func (me *PsqlDB) VisitUrlNotFound(opts *db.SummaryOpts) ([]*db.VisitUrl, error)
189 		limit = 10
190 	}
191 
192-	historical, err := me.visitUrlNotFoundFromSummary(opts, limit)
193-	if err != nil {
194-		return nil, fmt.Errorf("query summary 404 urls: %w", err)
195-	}
196+	var historical, current []*db.VisitUrl
197+	var histErr, rawErr error
198 
199-	current, err := me.visitUrlNotFoundFromRaw(opts, limit)
200-	if err != nil {
201-		return nil, fmt.Errorf("query raw 404 urls: %w", err)
202+	var wg sync.WaitGroup
203+	wg.Add(2)
204+
205+	go func() {
206+		defer wg.Done()
207+		historical, histErr = me.visitUrlNotFoundFromSummary(opts, limit)
208+	}()
209+	go func() {
210+		defer wg.Done()
211+		current, rawErr = me.visitUrlNotFoundFromRaw(opts, limit)
212+	}()
213+
214+	wg.Wait()
215+
216+	if histErr != nil {
217+		return nil, fmt.Errorf("query summary 404 urls: %w", histErr)
218+	}
219+	if rawErr != nil {
220+		return nil, fmt.Errorf("query raw 404 urls: %w", rawErr)
221 	}
222 
223 	return mergeTopUrls(historical, current), nil
224@@ -1189,18 +1226,13 @@ func (me *PsqlDB) visitUrlNotFoundFromSummary(opts *db.SummaryOpts, limit int) (
225 	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
226 	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
227 
228-	effectiveOrigin := opts.Origin
229-	if opts.Origin.After(previousMonthStart) {
230-		effectiveOrigin = previousMonthStart
231-	}
232-
233-	// If effective origin is in or after current month, no historical data to fetch
234-	if !effectiveOrigin.Before(currentMonthStart) {
235+	// If origin is in the previous month or later, raw data covers it — no summary to fetch.
236+	if !opts.Origin.Before(previousMonthStart) {
237 		return nil, nil
238 	}
239 
240 	// Clamp origin to month boundary for summary table lookup
241-	originMonthStart := time.Date(effectiveOrigin.Year(), effectiveOrigin.Month(), 1, 0, 0, 0, 0, time.UTC)
242+	originMonthStart := time.Date(opts.Origin.Year(), opts.Origin.Month(), 1, 0, 0, 0, 0, time.UTC)
243 
244 	where := ""
245 	args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
246@@ -1277,14 +1309,28 @@ func (me *PsqlDB) visitUrlNotFoundFromRaw(opts *db.SummaryOpts, limit int) ([]*d
247 }
248 
249 func (me *PsqlDB) visitHost(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
250-	historical, err := me.visitHostFromSummary(opts)
251-	if err != nil {
252-		return nil, fmt.Errorf("query summary hosts: %w", err)
253-	}
254+	var historical, current []*db.VisitUrl
255+	var histErr, rawErr error
256 
257-	current, err := me.visitHostFromRaw(opts)
258-	if err != nil {
259-		return nil, fmt.Errorf("query raw hosts: %w", err)
260+	var wg sync.WaitGroup
261+	wg.Add(2)
262+
263+	go func() {
264+		defer wg.Done()
265+		historical, histErr = me.visitHostFromSummary(opts)
266+	}()
267+	go func() {
268+		defer wg.Done()
269+		current, rawErr = me.visitHostFromRaw(opts)
270+	}()
271+
272+	wg.Wait()
273+
274+	if histErr != nil {
275+		return nil, fmt.Errorf("query summary hosts: %w", histErr)
276+	}
277+	if rawErr != nil {
278+		return nil, fmt.Errorf("query raw hosts: %w", rawErr)
279 	}
280 
281 	return mergeHosts(historical, current), nil
282@@ -1341,24 +1387,44 @@ func (me *PsqlDB) visitHostFromRaw(opts *db.SummaryOpts) ([]*db.VisitUrl, error)
283 }
284 
285 func (me *PsqlDB) VisitSummary(opts *db.SummaryOpts) (*db.SummaryVisits, error) {
286-	visitors, err := me.visitUnique(opts)
287-	if err != nil {
288-		return nil, err
289-	}
290+	var (
291+		visitors    []*db.VisitInterval
292+		urls        []*db.VisitUrl
293+		refs        []*db.VisitUrl
294+		notFound    []*db.VisitUrl
295+		visitorsErr error
296+		urlsErr     error
297+		refsErr     error
298+		nfErr       error
299+	)
300 
301-	urls, err := me.visitUrl(opts)
302-	if err != nil {
303-		return nil, err
304-	}
305+	var wg sync.WaitGroup
306+	wg.Add(4)
307 
308-	notFound, err := me.VisitUrlNotFound(opts)
309-	if err != nil {
310-		return nil, err
311-	}
312+	go func() {
313+		defer wg.Done()
314+		visitors, visitorsErr = me.visitUnique(opts)
315+	}()
316+	go func() {
317+		defer wg.Done()
318+		urls, urlsErr = me.visitUrl(opts)
319+	}()
320+	go func() {
321+		defer wg.Done()
322+		refs, refsErr = me.visitReferer(opts)
323+	}()
324+	go func() {
325+		defer wg.Done()
326+		notFound, nfErr = me.VisitUrlNotFound(opts)
327+	}()
328 
329-	refs, err := me.visitReferer(opts)
330-	if err != nil {
331-		return nil, err
332+	wg.Wait()
333+
334+	// Return the first error encountered
335+	for _, err := range []error{visitorsErr, urlsErr, refsErr, nfErr} {
336+		if err != nil {
337+			return nil, err
338+		}
339 	}
340 
341 	return &db.SummaryVisits{

1@@ -0,0 +1,7 @@
2+-- Composite index for visitUniqueFromSummary: filters by (user_id, host, visit_date)
3+CREATE INDEX IF NOT EXISTS idx_monthly_visits_user_host_date
4+ON analytics_monthly_visits (user_id, host, visit_date);
5+
6+-- Covering index for visitHostFromRaw: filters by (user_id, created_at), groups by host, counts distinct ip_address
7+CREATE INDEX IF NOT EXISTS idx_analytics_visits_user_created_host_ip
8+ON analytics_visits (user_id, created_at, host, ip_address);