Evan Todd Profile picture
19 Jul, 11 tweets, 2 min read
Another story from the trenches. This one is about a server performance problem that strangely only happened when the data was small.
Our web app has a page that gives you a glimpse of a pretty hefty stream of events - you can see the most recent day or two of activity. The database table driving this was many GB.
Clicking through the pages was adding significant load to the entire database and we couldn't figure out why.
- it only queried about 20 items sorted by time
- there was an index on the timestamp
There was a pruner that trimmed the table down to 48 hours of history or so, but it didn't seem to be helping.
We eventually found a huge disparity between users of this table - some had zero entries and some had hundreds of thousands.
Weirdly, the page loaded instantly for users with hundreds of thousands of rows, while users with zero rows had to wait 12 seconds for their totally empty page to load.
We had two indexes on the table - one for customer ID and one for timestamp. The Postgres query planner correctly chose the timestamp index since the results needed to be sorted by time. Then it would filter by customer ID.
For heavy users, the filter had practically nothing to do, since their entries took up most of the table. So our "SELECT * LIMIT 20" query would quickly pick up 20 entries and happily exit.
For users with 1 or 2 entries, it would dutifully churn through hundreds of thousands of rows, helpfully sorted by the timestamp index, searching for the needle in the haystack.
We solved it by having the pruner cull not only old rows, but also rows that caused any one customer ID to go over a certain limit, so no single customer's entries could dominate the table.
If this kind of problem sounds interesting to you, we're hiring software engineers! strongdm.com/careers

• • •

Missing some Tweet in this thread? You can try to force a refresh

Keep Current with Evan Todd

Evan Todd Profile picture

Stay in touch and get notified when new unrolls are available from this author!

Read all threads

This Thread may be Removed Anytime!


Twitter may remove this content at anytime! Save it as PDF for later use!

Try unrolling a thread yourself!

how to unroll video
  1. Follow @ThreadReaderApp to mention us!

  2. From a Twitter thread mention us with a keyword "unroll"
@threadreaderapp unroll

Practice here first or read more on our help page!

More from @etodd_

19 Dec 20
Story from the trenches today.
An ops person deployed an AWS “reaper” script. Pretty straightforward concept: delete everything on AWS older than X days, minus a whitelist. What could possibly go wrong?
The reaper runs for the first time. It’s deleting things! Joy fills our hearts with the anticipation of a lower AWS bill.
Read 8 tweets
5 Sep 20
It's been almost three years. I finally collected the energy to write a few words about @DeceiverGame. Lots more to unpack, but this is a start. Thread
Hey folks! I do pop on here every once in a while! I ran out of money and got burned out on game development. Thank you for your kind words and support for the game. I can't tell you how fun it was to see people get so passionate about it 🙂
Unfortunately the game is canceled. Since it's all over now, let me explain the original idea and why it was called Deceiver.
Read 18 tweets

Did Thread Reader help you today?

Support us! We are indie developers!

This site is made by just two indie developers on a laptop doing marketing, support and development! Read more about the story.

Become a Premium Member ($3/month or $30/year) and get exclusive features!

Become Premium

Too expensive? Make a small donation by buying us coffee ($5) or help with server cost ($10)

Donate via Paypal Become our Patreon

Thank you for your support!

Follow Us on Twitter!