HorizonDB separates storage from compute: PostgreSQL compute runs in front of remote storage for high availability, while a local SSD cache reduces remote reads for low latency.

In a previous post, when HorizonDB was in preview, I used EXPLAIN (ANALYZE, BUFFERS) and I/O timings to estimate whether PostgreSQL reads were coming from the local SSD cache or remote storage.

This is now directly visible in the execution plan with the SSDCACHE option:

EXPLAIN (ANALYZE, BUFFERS, SSDCACHE) SELECT * FROM large; 

For example, here is a sequential scan of a 56 GB table:

 QUERY PLAN --------------------------------------------------------------------------- Seq Scan on large (actual time=0.138..207836.006 rows=7372800.00 loops=1) Buffers: shared read=7372800 dirtied=12800 written=1 I/O Timings: shared read=200058.305 write=0.001 SSD cache: hits=7372799 misses=1 Planning Time: 0.022 ms Execution Time: 208112.478 ms 

There are two levels of information here.

  • BUFFERS tells us what happened from PostgreSQL's point of view: Buffers: shared read=7372800. The scan didn't find these pages in PostgreSQL shared buffers, so it had to read 7,372,800 × 8 KiB = 56.25 GiB into them.

  • SSDCACHE tells us where those reads were satisfied: SSD cache: hits=7372799 misses=1. Of those reads, 7,372,799 pages—essentially the entire 56.25 GiB—were found in the local SSD cache. Only one 8 KiB page missed the cache and had to be read from the lower storage tier.

The important point is that an SSD-cache hit is still a PostgreSQL shared read. These are not two independent counters:

PostgreSQL needs a page | +-- shared hit --> already in shared buffers | +-- shared read | +-- SSD cache hit --> read locally | +-- SSD cache miss --> lower storage tier 

This makes SSDCACHE a useful complement to BUFFERS: BUFFERS shows whether PostgreSQL had to perform a read, while SSDCACHE shows whether the local SSD cache satisfied it.

Here's another example where the cache starts missing:

Seq Scan on large Buffers: shared hit=1786023 read=6917977 I/O Timings: shared read=307554.308 SSD cache: hits=6740355 misses=177074 

Here, PostgreSQL performs 6,917,977 shared reads (52.78 GiB). Of these, 6,740,355 pages (51.42 GiB) hit the local SSD cache, while 177,074 pages (1.35 GiB) missed it.

Previously, I had to infer this distinction from I/O timings. Now, EXPLAIN (ANALYZE, BUFFERS, SSDCACHE) shows directly how PostgreSQL reads are distributed between the local SSD cache and the lower storage tier.

This cache hierarchy meets the needs of most OLTP applications:

  • a small, hot working set in PostgreSQL shared buffers, including current transactions, reference tables, and internal index pages. You expect Buffers: shared hit in EXPLAIN (ANALYZE, BUFFERS) for them.
  • a larger, warm working set in the local SSD cache, including recent transactions, pages awaiting vacuuming, and less frequently accessed data. You expect SSD cache: hits in EXPLAIN (ANALYZE, SSDCACHE) for them.
  • cold data served through the page servers or, when necessary, from blob object storage, where throughput and low cost matter more than low latency. You see SSD cache: misses in EXPLAIN (ANALYZE, SSDCACHE) for them.

Both SSD cache: hits and SSD cache: misses correspond to Buffers: shared read in EXPLAIN (ANALYZE, BUFFERS): the page wasn't already in shared buffers and had to be read into one. SSDCACHE adds the missing information about where that read was served from, which helps explain its I/O performance.