Re:Understanding caching in Postgres – An in-depth guide (2016)
by napsterbr on Sunday October 01, 2017 @09:44PM
(#15381145)
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.
> 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.
Re:Understanding caching in Postgres – An in-depth guide (2016)
by haimez on Monday October 02, 2017 @01:12AM
(#15381834)
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.
Re:Understanding caching in Postgres – An in-depth guide (2016)
by madhusudhan000 on Monday October 02, 2017 @08:40AM
(#15383332)
Author here.
> I was surprised to learn Postgres doesn't use direct read/write (O_DIRECT) to bypass kernel caches.
It is more related to the database being platform neutral. The community is very sensitive to putting in performance improvement/hacks which applies only to certain systems. There was a discussion back then - https://www.postgresql.org/message-id/50E4AAB1.9040902@optio.... There were several more, you can search around the mail archives, but the general tendency is not to put changes which is not universally applicable to all platforms.
With that said, a lot is changing, as their community grows we can expect optimizations like these.
> The article doesn't go into the details, but maybe someone can point me to some resources that explain the benefits of this double cache layer?
The article was already a bit long so I didn't go into much details. Perhaps ill write on it in another article.
Re:Understanding caching in Postgres – An in-depth guide (2016)
by madhusudhan000 on Monday October 02, 2017 @09:00AM
(#15383428)
It hasn't changed much. But new OLAP features have come up such as parallel queries and for those, the existing cache architecture doesn't make sense since OLAP is full of sequential scans.
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.
How far can you go with MySQL or MariaDB? | https://news.ycombinator.com/item?id=14892184 (Aug 2017)
> dmux This is a great resource RE indexing: 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
> 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.
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.
The article doesn't go into the details, but maybe someone can point me to some resources that explain the benefits of this double cache layer?
All I can think of is PHK (author of Varnish) arguing for exactly the opposite: use only kernel caches with clever mmap.
> I was surprised to learn Postgres doesn't use direct read/write (O_DIRECT) to bypass kernel caches.
It is more related to the database being platform neutral. The community is very sensitive to putting in performance improvement/hacks which applies only to certain systems. There was a discussion back then - https://www.postgresql.org/message-id/50E4AAB1.9040902@optio.... There were several more, you can search around the mail archives, but the general tendency is not to put changes which is not universally applicable to all platforms.
With that said, a lot is changing, as their community grows we can expect optimizations like these.
> The article doesn't go into the details, but maybe someone can point me to some resources that explain the benefits of this double cache layer?
The article was already a bit long so I didn't go into much details. Perhaps ill write on it in another article.
You can read the doc for the same - https://www.postgresql.org/docs/10/static/when-can-parallel-...