Previous Entry
kova98/Barelytics

Optimizing Queries

I ran the load test last night at 2000 concurrent users to test things out and fill up the database a bit more. Unfortunately I couldn't wake my screen up for some reason so I don't know the results of the load test, but I can see that stats requests are timing out now 😂

The throughput is fine for now. I have a fairly good idea of what the bottleneck is and I'm ready to optimize if I get traction.

Time to optimize the queries!

The stats request takes 54 seconds. Let's analyze the queries.

I'll run an EXPLAIN ANALYZE on each query and write down the current times.

Query | Execution Time --------------------------|---------------- Bounce Rate | 1100.119 ms Page Views & Visitors | 4831.051 ms Session Duration | 1123.891 ms Top Paths | 9745.457 ms Top Sources | 1175.566 ms Top Device Types | 6318.436 ms Top Operating Systems | 6180.373 ms Top Browsers | 6252.123 ms Top Countries | 6193.282 ms Total | 42920.30 ms (42.92 seconds)

I'll tackle the low hanging fruit first. All of my queries are filtering by timestamp and event type, so I'll create an index for that.

Created an index but see no change. In the ANALYZE output, I see it's still using the Seq Scan. Did some digging, and I found out that a seq scan will actually be faster than an index scan if it's returning more than 10% of all rows.

Since I created 5 million rows in a day and I'm trying to calculate data from them, the reality is that these queries will take some time.

I'll do the same test, but with a query that won't aggregate that many rows.

Query | Execution Time --------------------------|---------------- Bounce Rate | 621.558 ms Page Views & Visitors | 702.489 ms Session Duration | 742.828 ms Top Paths | 752.930 ms Top Sources | 748.793 ms Top Device Types | 6306.759 ms Top Operating Systems | 693.866 ms Top Browsers | 728.921 ms Top Countries | 719.091 ms Total | 12017.23 ms (12.02 seconds)

Okay, that's much faster. But still too slow. I'll add an index for timestamp and type

Query | Execution Time --------------------------|---------------- Bounce Rate | 1060.564 ms Page Views & Visitors | 1132.591 ms Session Duration | 1125.753 ms Top Paths | 1108.842 ms Top Sources | 1119.390 ms Top Device Types | 7012.807 ms Top Operating Systems | 1042.941 ms Top Browsers | 1104.105 ms Top Countries | 1086.599 ms Total | 15793.59 ms (15.79 seconds)

Wow, even worse! How's that possible?

Looking at the explain, I can see that it's doing a bitmap heap scan where it's filtering by the domain. Of course, I'm now filtering by domain so it should be in the index.

->  Parallel Bitmap Heap Scan on events  (cost=93574.67..426861.18 rows=163 width=41) (actual time=712.792..1001.253 rows=2 loops=3)
"                                      Recheck Cond: ((type = 'page_view'::text) AND (""timestamp"" >= '2025-11-10 00:00:00+00'::timestamp with time zone) AND (""timestamp"" <= '2025-11-18 00:00:00+00'::timestamp with time zone))"
		Rows Removed by Index Recheck: 556479
		Filter: (domain = 'barelytics.io'::text)
		Rows Removed by Filter: 1954351
		Heap Blocks: exact=13065 lossy=79692

Okay, I'll try adding the domain to the index.

Looking at the analyze now, I can see that it's using the index scan. It went from 1060 to 26 ms! Awesome! I'll run the full test with the new domain filter.

Query | Execution Time --------------------------|---------------- Bounce Rate | 43.327 ms Page Views & Visitors | 42.227 ms Session Duration | 46.059 ms Top Paths | 46.528 ms Top Sources | 44.063 ms Top Device Types | 18.245 ms Top Operating Systems | 54.251 ms Top Browsers | 36.521 ms Top Countries | 41.477 ms Total | 372.70 ms (0.37 seconds)

Alright! That's much like it!

I guess I can live with this. The massive queries really can't be improved while keeping this schema, but if I ever get to the scale where users are querying millions of events, I'll explore my options further.

Some ideas:

  • parallelizing queries (quick and easy)
  • caching requests (quick and easy)
  • pre-calculating stats each day (more work)
  • migrating to an olap database (much more work)

For now, this will do. I'll make sure to always include domains in requests (I'll add this once I add domain setup), and add some loading indicators.

This seems to be it for the backend. I'll now move on to building out the landing page. I need to go through the checklist for MVP and figure out what I'm still missing. I expect a lot of work to pop up when I start trying to go through the flow of creating a new user -> starting free trial -> onboarding.