3 ms·

That seems amazing, thanks for sharing. Do anyone on hn happen to know a similar guide / article explaining how data locality can help a database? I've been lo

That seems amazing, thanks for sharing. Do anyone on hn happen to know a similar guide / article explaining how data locality can help a database? I've been looking for the topic, both spatial and temporal locality, and to my surprise I've found very few discussions about it, most of which led me to believe it wouldn't matter much shaping my indexes to have good locality between them. I believe there would be some optimization but I still hadn't the time to benchmarking.

haimez 9y ago
It's significant, especially when locality is related to temporal locality (ie: most reads will be relatively recent writes). The biggest upside is serving more reads from the in memory portion of indexes after things start having to live on disk. Occasionally, locality also nets a win on relevant query results residing in contiguous storage blocks- but fast queries should be reading tons of data in the first place.
j_s 9y ago
Here's a recent discussion recommending a specific site that might help you as a resource, but I don't see much there regarding locality. How far can you go with MySQL or MariaDB? | https://news.ycombinator.com/item?id=14892184 https://news.ycombinator.com/item?id=14892184 (Aug 2017) > dmux This is a great resource RE indexing: http://use-the-index-luke.com/ http://use-the-index-luke.com/ There is a detailed PostgreSQL optimization comment there that may be more what you had in mind: https://news.ycombinator.com/item?id=14896154 https://news.ycombinator.com/item?id=14896154 > olavgg: Use multi-column/functional/partial indexes, as this will help you design a database where most of your 'hot' indexes can fit in RAM. If you have too many indexes, you will do index scan on disk, which is painfully slow. I[n] many cases a full table scan (sequential read) would be faster instead.

haimez 9y ago

It's significant, especially when locality is related to temporal locality (ie: most reads will be relatively recent writes). The biggest upside is serving more reads from the in memory portion of indexes after things start having to live on disk. Occasionally, locality also nets a win on relevant query results residing in contiguous storage blocks- but fast queries should be reading tons of data in the first place.

j_s 9y ago

Here's a recent discussion recommending a specific site that might help you as a resource, but I don't see much there regarding locality. How far can you go with MySQL or MariaDB? | https://news.ycombinator.com/item?id=14892184 https://news.ycombinator.com/item?id=14892184 (Aug 2017) > dmux This is a great resource RE indexing: http://use-the-index-luke.com/ http://use-the-index-luke.com/ There is a detailed PostgreSQL optimization comment there that may be more what you had in mind: https://news.ycombinator.com/item?id=14896154 https://news.ycombinator.com/item?id=14896154 > olavgg: Use multi-column/functional/partial indexes, as this will help you design a database where most of your 'hot' indexes can fit in RAM. If you have too many indexes, you will do index scan on disk, which is painfully slow. I[n] many cases a full table scan (sequential read) would be faster instead.