3 ms·
Until you hit the problem of needing to iterate over your table (batches). As far as I can tell, the only real way to do efficient iteration over postgres tabl
by bpicolo 10y ago
Until you hit the problem of needing to iterate over your table (batches).
As far as I can tell, the only real way to do efficient iteration over postgres tables is to continually bound ID at either end and then take an offset. (SELECT * from <foo table> where id > <largest id from last batch> limit 10). With btrees on UUID you get lexicographic ordering here I imagine, so it'll work. With hash indexes no dice.
Cursors don't end up working out because if you kill long-running queries or idle connections (which most websites should, in production) you'll kill applications using cursors. With pgbouncer, at least, reading from cursors doesn't seem to bump idle timeouts.
- throwaway91111 10y agoSurely hash indexes have some stable ordering you can order by, even if its meaning is obtuse.
- bpicolo 10y agoHash indexes don't support any operations other than = as far as I can tell, so they won't even be considered for >.
- deathanatos 10y agoPresumably, hash indexes are based on hash tables underneath, which don't generally have a stable ordering, even an obtuse one. (Any write could cause the underlying table to get resized, which will cause the contents to get re-ordered.) Perhaps if you can guarantee that writes aren't happening, they might, but I don't think this is generally applicable to most people's use case. (but range queries / being able to iterate are generally why I prefer btrees, unless I have a good reason. Keep in mind that the O(log n) on B-trees is log with a huge base — it's not power of two. It takes very few disk reads for a b-tree to find its query; it's just not the O(1) of a hash table.)
- throwaway91111 10y agoAh, yes I see now.