Comment by patio11 | Hacker News Reader

Comment by patio11 | Hacker News Reader

Parent Full thread

Oh, also, offer some consulting for clients on their database woes.

We do this at the day job. It runs about $X00 an hour, with a minimum of Y hours, if our customers need it. Heroku, on the other hand, has a lot of customers who are wondering how far their $50 a month is going to get them. For these customers, many of whom are Rails types who don't quite grok indexes, it might be a better solution to say "Um, look, rather than us teaching you a core engineering skill that you're manifestly unwilling to pay for, how about we suggest a technology stack that makes this skill unnecessary".

Previously, one of the Rails hosts (Dreamhost or Heroku, can't remember) released stats saying something like 97% of customers create no indexes. I totally understand how that can happen, too -- you expect ActiveRecord to be magic, and with what it does it is very powerful magic, but it is not magic that totally obviates your need to think about database design. (Edited to add: My business runs on Rails, I consider myself to have low to intermediate SQL ability, and if you contact my day job to get consulting on your database woes you won't get handed off to me anytime soon.)

If there are Rails developers who work with applications of any size at all and are not familiar with indexes, the problem isn't scaling - it's lack of knowledge of one's application's stack. The mentality that one's app should magically scale without any idea of what's under the hood is toxic.

Anyway, it doesn't get much simpler than: add_index :users, :account_id

Right, but it gets much more complicated.

Near trivial example: what index or indices do you need to support the business requirement "I want to know how long users stay active after they sign up, and I want you to be able to slice that data by signup date and by whether they're paying customers or not."

So programmer Bob goes off and does this.

"Oh, Bob, the screen only lets me slice the data by signup date and by customer type, but I want to slice by both at once."

So Bob makes a one line tweak to his controller (to use both conditions, instead of one or the other)... and BOOM, down goes the poor server.

Well this is a requirement in the Business Intelligence domain, so you should create a reporting database (probably a star schema) and put an analytics package on top of it. You'll get easy sub second queries.

EXPLAIN SELECT is your friend. Rails does let you view the SQL it generates, right?

Oh definitely - sorry, I didn't mean to be flippant or to suggest that dropping indexes on everything were some sort of magic scaling powder. I once worked on an app a client brought in from an outside company that had slapped indexes on every single column in the database (including longtext - it was Postgres). I've never seen something so broken.

But yeah. It's complicated. "Software is hard." But the best thing you can do is become aware of your ignorance, then try to eliminate what you can.

Such business requirements often fall under the "I do this 20 times a month not 20 times a second" so they don't need a full index. But, name one of the technology's that "scales" that handles this better than a modern SQL DB.

Could be that 97% of customers have no need for indexes. I have none on my personal site; with rows numbering in the dozens and pageviews/day numbering about 20, I could probably brute-force search over the database in Python and it'd still be fast enough...

I have some sites that don't have indexes. At <1000 views/day it's not necessary. Caching generated HTML is as easy and more effective in making it faster.

Reply on news.ycombinator.com