5 ms·
When I wrote Reddit Gold (now called Reddit Premium) I intentionally left the paying-user table index-free, looking forward to the day it would become necessary
by raldi 3y ago
When I wrote Reddit Gold (now called Reddit Premium) I intentionally left the paying-user table index-free, looking forward to the day it would become necessary.
It hadn’t happened by the time I left the company, even though sales were exceeding expectations.
- dmak 3y agoWow really? This seems really surprising to me.
- twoodfin 3y agoSpeculating, but presumably this table only needed consultation during creation of a new user session. That’s probably a pretty heavyweight operation to begin with, so adding a scan of a few KB (user IDs being modestly sized integers) for every thousand currently paying users is NBD.
- hardware2win 3y agoWhat was the reasoning?
- raldi 3y agoI had read a Joel Spolsky post about how they used to get an email every time they made a sale (“Ding!”) and the day they finally turned it off was full of pride. (I did that too, originally not even filtered out of my inbox.)
- cactusplant7374 3y agoIndexes are such an easy thing to add. I don't get it. Seems like under optimization when you consider the tradeoffs.
- SoftTalker 3y agoThey add overhead to updates and inserts. If you don't need them, why take that hit. Know your data.
- toast0 3y agoIf the table is small enough that indexes aren't critical for reads, they probably don't impact the rate of writes either. Of course, if the object is large scale bulk inserts, with only very occasional selects, then yes.
- EGreg 3y agoWait a second, the whole thing is you don't mind O(N) overhead in searching on every request, but you mind O(log N) overhead for updates and inserts?
- titaniczero 3y agoWell, my guess is that updates and inserts are much more frequent than searches in their use case. You're assuming a balanced frequency for these operations and it hardly ever happens.
- bcrosby95 3y agoSo no primary key or uniqueness constraints?
- ComodoHacker 3y agoModern DB engines can enforce Unique or PK constraints without indexes. Yes, they perform scans.
- tomnipotent 3y agoEvery major RDBMS requires an index for both constraints.
- hinkley 3y agoFK constraints however are a pretty common gotcha. You have a table that allows deletes, and every table that has a column pointing to that table gets scanned on each delete operation. So you have to add an index on that column, or start talking about tombstoning data instead of deleting it, but in which case you may still need the FK index to search out references to an old row to replace with a new one.
- tomnipotent 3y agoAn FK also adds the burden of keeping a copy of each related row during the lifespan of a transaction. This means small-but-frequently-updated tables that are joined to a lot can be a source of unexpected headaches.
- ComodoHacker 3y agoAfter checking the docs, you are right, I stand corrected. I was pretty sure Oracle and DB2 don't.
- Macha 3y agoUnlikely, given Reddit's past schema design. One table of "things", and then another table of attributes of those things in a entity,key,value format. https://kevin.burke.dev/kevin/reddits-database-has-two-tables/ https://kevin.burke.dev/kevin/reddits-database-has-two-table...
- eterm 3y agoThat goes against my intuition, because the performance would be more impacted (beneficial to have an index) where you have very few bits set on a boolean field. If you have 10m users and 100 have "IsPremium = 1" then an index massively speeds up anything with WHERE IsPremium = 1, compared to if the data is fairly evenly spread between IsPremium = 1 and IsPremium = 0 where an index won't be much benefit. So low sales would increase the need for an index. That said, I'm assuming searching for "Users WHERE IsPremium = 1" is a fairly rare thing, so an index might not make much difference either way.
- pc86 3y agoYou wouldn't need any of these WHERE clauses if you have all the premium users in one table. Even managing the users in both tables in pretty trivial when you just add someone as they pay and remove them as the premium account expires.
- deleted 3y ago[deleted]
- abtinf 3y ago> paying-user table index-free Presumably, that means they created a new table intended to be straight-joined back to the user table. No need to search a column.
- Vvector 3y agoJoining a table on a column without an index will require a linear scan of the column. You almost always want an index on JOIN columns
- lazide 3y agoThe primary key (userid?) will almost always be indexed implicitly. You’d usually have to go out of your way to avoid it. So it was probably being joined by an indexed column, but without an explicitly defined index.
- 3y ago
- BeefWellington 3y agoIt's not really index-free if a separate table has constraints of any kind.