Ever since we got the fast new database server with SSDs, I’ve been monitoring which tables are getting heavy traffic and should go live on the SSDs. We have two tablespaces, “fast” which is faster but smaller, and “slow” which is bigger but slower. I’ve been using this query to determine which indexes should live in which tablespace. There are different forms of this query around the web, but I needed to see the tablespaces, too.

SELECT
    i.idx_scan,
    i.idx_tup_read,
    i.idx_tup_fetch,
    i.indexrelname AS index,
    it.spcname AS index_tablespace,
    i.relname AS table,
    tt.spcname AS table_tablespace,
    pg_size_pretty(pg_relation_size(i.indexrelname::text)) as index_size
FROM pg_stat_all_indexes i
    INNER JOIN pg_class ic ON (i.indexrelid = ic.oid)
    LEFT OUTER JOIN pg_tablespace it ON (ic.reltablespace = it.oid)
    INNER JOIN pg_class tc ON (i.relid = tc.oid)
    LEFT OUTER JOIN pg_tablespace tt ON (tc.reltablespace = tt.oid)
ORDER BY 1 desc, 2 desc, 3 desc

The output looks like this (in \x mode because of the width):

-[ RECORD 1 ]----+----------------------------------------------------
idx_scan         | 395974172
idx_tup_read     | 432974893
idx_tup_fetch    | 426070104
index            | testbook_pkey
index_tablespace | fast
table            | testbook
table_tablespace | fast
index_size       | 289 MB
-[ RECORD 2 ]----+----------------------------------------------------
idx_scan         | 133416135
idx_tup_read     | 133441801
idx_tup_fetch    | 133413399
index            | lists_listid_custid
index_tablespace | fast
table            | lists
table_tablespace | fast
index_size       | 7096 kB
-[ RECORD 3 ]----+----------------------------------------------------
idx_scan         | 50310975
idx_tup_read     | 1286116
idx_tup_fetch    | 742639
index            | listdetail_bkkey_listid_where_ctr2_is_zero
index_tablespace | fast
table            | listdetail
table_tablespace | fast
index_size       | 682 MB

I have one case where a heavily-trafficked table is still staying on the slow tablespace. It’s a log of user login history that is only ever appended to, and is searched only a few times a day. SSDs are great at random reads, but not much faster than physical spindles on sequential writes. Therefore, my login history would not benefit much from moving to the SSD tablespace, and I can allocate that precious space to another table or index instead.