It's also tricky with a COW filesystem like ZFS in which the order of data on-disk may not match the logical order of the file. So your database may decide to dispatch a bunch of sequential 1MB reads to the filesystem, only for the filesystem to translate those large sequential reads into many small random 8kB reads. It depends on how fragmented the file is, so you can start off with great performance which degrades significantly over time.

Luckily SSDs have a much lower penalty for random reads compared to spinning disks, so in future this will be less of an issue.

In the meantime all you can do is use a different filesystem, hide the problem with cache, or manually defragment your data files from time to time.

Actually SSD themselves implement COW filesystem since NAND cannot be written without erase step.

That's true but not particularly relevant to database performance. As I mentioned, random reads on an SSD do not suffer from a head seek penalty so it doesn't matter how the blocks are allocated. On the other hand with spinning disk there is a huge difference in throughput for large sequential vs small random reads.

What about TRIM?

TRIM is like the free() equivalent to malloc(). It releases a NAND page back to free state for other uses. It dones't require any NAND access or writes per-se but if persistent TRIM is needed (vs best effort,) it gets much more complicated.

However mostly it is.

Consider the example with the ID's if he would have an index with all the fields he would need, example he would only need the id and the email he could create a index with them and the query would be faster, that would be a index only scan (https://wiki.postgresql.org/wiki/Index-only_scans)

Sure, I guess with index-only scans the picture does shift a bit, right? And quite a few cases are reducible to those, so this can't be ignored.

This is a good point, what the article really should be discussing is the tradeoff between sequential throughput and random disk access, which is especially important on HDDs, for those unfortunate enough to still be working with things that spin :)

True - that's what I had to battle with as well - disk seek cost vs. read speeds - Postgres does collect stats on that IIRC (in special tables), but might still need to advise it on that. And do lots of EXPLAIN ANALYZE (incl. BUFFERS sometimes) to check what happens. Re. buffers etc., I also had this nagging idea that the way Postgres sorts sets sometimes takes into account cache misses (i.e. it may use sort algo which compares elements that are not that far away - but not sure on this - also, perhaps this is to be expected anyway).

AFAICT pgsql controls this with the random_page_cost and seq_page_cost variables [1], which are relative and seem to default to 4 and 1 respectively. It doesn't look like these are automatically adjusted though based on the hardware.

Some searching and I found some blog posts suggesting lowering them both to closer to one on SSDs [2]. Indeed, the documentation says you can set them both to 1 if you are sure that the database will be fully cached in RAM.

[1] http://www.postgresql.org/docs/current/static/runtime-config... [2] http://www.cybertec.at/2013/01/better-postgresql-performance...

Aha, yeah I recall reading suggestions akin to those for SSDs. Nice!

There's also `ALTER TABLE SET STATISTICS` and default_statistics_target config parameter (for supposedly smart query planning on the fly?) (though would have to re-read to recall how these things work - off to bed soon): http://www.postgresql.org/docs/current/static/runtime-config... (edit ah you included the same link - lots of good stuff here - ahh I like Postgres documentation)

Actually, further to your point, the documentation states this explanation of the default for random/seq_page_cost:

"The default value can be thought of as modeling random access as 40 times slower than sequential, while expecting 90% of random reads to be cached

If you believe a 90% cache rate is an incorrect assumption for your workload, you can increase random_page_cost to better reflect the true cost of random storage reads. Correspondingly, if your data is likely to be completely in cache, such as when the database is smaller than the total server memory, decreasing random_page_cost can be appropriate. Storage that has a low random read cost relative to sequential, e.g. solid-state drives, might also be better modeled with a lower value for random_page_cost."

In other words, cache misses are taken into account to the extent that the model above is reflective of your workload and hw environment.

> In other words, cache misses are taken into account to the extent that the model above is reflective of your workload and hw environment.

Fair enough. Though I think they have in mind cache misses in the sense of page faults here? - i.e. "it's not in memory - have to read from disk". (i.e. cache as in Postgres internal cache (+ OS filesystem cache, actually/IIRC). The variable name `random_page_cost` seems to suggest this (load a memory page into memory - from disk).

Whereas I had in mind cache as in CPU cache[1] - where sort operation happens which does lots of cache misses (between CPU cache and RAM) - and needs to read from rest of RAM (i.e. random memory access is not uniformly random). It's a nice rabbit hole..

[1]: https://en.wikipedia.org/wiki/CPU_cache

> If you believe a 90% cache rate is an incorrect assumption for your workload

For people wondering as I did about how you might monitor for this, it looks like the data you need is in pg_statio_all_indexes