Hacker News Reader

Comment by gfodor | Hacker News Reader

I noticed hash indexes are now crash proof and replicated -- this seems to make them actually usable in production. In other words, this release effectively "adds" a new index type. That seems like a much bigger deal than is being talked about, is there any reason to believe that new databases shouldn't be using hash indexes for columns that won't be supporting range queries? (In other words, pretty much all keys.) I've seen mixed reports about hash indexes being beneficial.

View on HN

I think it's a neat feature. But note that orderedness isn't just useful for range queries, it also helps to satisfy ORDER BY and to allow for merge joins without a sort steps. There's also no yet support for index-only scans (probably never), no constraints, and no multi-column index support.

There's also still some performance kinks to work out with the current hash index performance - large when growing the index quickly or under very high concurrency.

Do you know how that'd work out for those of us with UUID primary keys rather than integer primary keys?

Not quite sure I understand exactly what you're getting at? Are you wondering if those constraints are less important because you less frequently order by UUIDs? If so, I don't think it makes a huge difference - merge joins are still relevant, and you'll often ORDER BY anyway just to get stable resultsets. If the question is if there's bigger benefits to hash joins for largely unordered data? Then yes, that's true.

Stop it! You're giving away all possible answers to one of my favorite interview questions.

Edit: not seriously asking you to stop, but the interview question part is true

I hope those are interview questions for dbas because it's way outside common knowledge for backend developers.

Why are back-end developers not supposed to have a deep understanding of their tools? I hear this sort of statement all the time, it’s infuriating. I once even had a dev team lead claim that it was not a dev’s responsibility at all if the app generates bad SQL; the DBA is supposed to make the queries fast (this person no longer works for me, but I consistently hear similar claims from consultants and others in the dev community).

DBAs are expected to understand the crappy inefficient row-at-a-time ORM in use by the devs, which implies understanding a particular programming language, but not the other way around? There’s nothing magical about SQL databases that a decent developer can’t understand.

> Why are back-end developers not supposed to have a deep understanding of their tools?

You mean all their tools? Like how does modern processors handle branch predictions in the face of out of order operations, or what differences in server cooling will affect throughput or peak performance?

Do you have a deep understanding of all the tools you use? Because I honestly doubt so.

I think there is a very large gap between being able to answer that interview question effectively and having decent understanding of how to not misuse your ORM.

Specifically with ORMs, It does seem that a lot of devs don't understand or care about the generated SQL... but that question was way above the knowledge level I would expect/want out of someone IMO.

Not for DBAs, but I also don't expect candidates to get all the answers in that comment. It's a decent data structure question to ask why databases generally use binary trees for indexes when hash lookups are faster. Non-DBAs usually have to think a little bit.

Databases do not use binary trees for indexes and tables, they use B-trees. B-trees are a family of self balancing tree data structures which usually are not binary because in a database you want the tree to have few levels.

Thank you. I remembe having to balance a B-Tree in my DB final. 5 nodes deep. Had to show all of the work. I think it ended up with 20 some revisions based on all the insert and delete statements.

Thanks! The merge join issue seems like one that probably would affect many workloads. Ordering by key less so but good to realize.

I guess it would be helpful for this feature to also include some documentation about if/when to use hash indexes, since up to this point the db has issued a warning basically saying "don't use these." Right now I'd imagine that there will be a lot of confusion around it given the feature's history so people will just not consider it.

Equality operations are also cheaper with hash indexes than btree because less data pages need to be fetched, so this can be a gain for certain columns with a high cardinality. You should really look at if those could be used or not, you may gain in performance with a switch.

Reply on news.ycombinator.com