On Wed, 2020-02-19 at 05:42 +0100, Nicolas PARIS wrote: > I have both hdd and ssd disk on the postgres server. The cluster is > right now created on the hdd only. I am considering using a tablespace > to put some highly used postgres object on the ssd disk. Of course the > ssd is small compared to the hdd, and I need to choose carefully what > objects are stored on that side. > > I am wondering what kind of object (indexes, data) would benefit from > ssd. The database primary/foreign keys are highly used and there is > almost no sequencial scan. However the server has a large amount of ram > memory and I suspect all of those indexes are already cached in ram. > > I have read that tablespaces introduce overhead of maintenance and > introduce complication for replication. But on the other hand I have > this ssd disk ready for something. > > Any recommandation ? Put "pg_stat_statements" into "shared_preload_libraries" and restart the server. Set "track_io_timing" to on. Let your workload run for at least a day. Install the "pg_stat_statements" extension and run SELECT blk_read_time, query FROM pg_stat_statements ORDER BY blk_read_time DESC LIMIT 20; That will give you the 20 queries that spent the most time reading from I/O. Examine those queries with EXPLAIN (ANALYZE, BUFFERS) and see which tables or indexes cause the I/O. Then you have a list of candidates for the fast tablespace. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
