-
Postgres SQL Lessons From Advent of Code Challenges
2022-02-17
Note: This post was originally published on heap’s blog and was co-written with Amanda Murphy We did something odd for Advent of Code this year: We solved a few challenges in javascript and then in PostgreSQL. We learned a few interesting things about SQL that we’d like to share here. Disclaimer: We did not complete all 25 days in SQL (judging by the links from this HN thread, it looks like pretty much no one did), but we still think the things we learned about SQL are useful and worth sharing, especially for non-experts.…
-
Optimizing Postgres Queries at Scale
2021-12-28
Note: This post was originally published on heap’s blog Heap is a product analytics tool that automatically captures web and mobile behavior like page views, clicks, and taps. We’re operating at a scale of billions of events per day, which we store across a distributed Postgres cluster. Heap’s thousands of customers can build queries in the Heap UI to answer almost any question about how users are using their product.…
-
How Postgres Audit Tables Saved Us From Taking Down Production
2021-11-08
Note: This post was originally published on heap’s blog Audit tables record changes that occur to rows in another table. They’re like commit logs for database tables, and they’re typically used to figure out who made what changes when. But surprisingly, we’ve found them useful for keeping our distributed Postgres cluster stable. To convince you of the stability-related value of audit tables, we’ll cover how audit tables helped us avoid a serious incident.…
-
Lessons from Deploying 1 million Postgres Indexes
2021-10-13
Note: This post was originally published on heap’s blog Heap is a product analytics tool that automatically captures web and mobile behavior like page views, clicks, and taps. We’re operating at a scale of billions of events per day, which we store across a distributed Postgres cluster. Our cluster has over a million tables of events. Recently, we discovered an index that makes our new Effort Analysis feature faster, and we attempted to roll out that index across the cluster.…
-
Working Around a Case Where the Postgres Planner Is "Not Very Smart"
2021-08-10
Note: This post was originally published on heap’s blog Heap is a digital insights platform that automatically captures web and mobile behavior like page views, clicks, and taps. We recently shipped Effort Analysis, a way for Heap customers to see the median number of interactions and seconds engaged between each step within a funnel. Here’s what it looks like: To build this feature, we needed to write a query that could quickly scan more than a billion rows of event data.…