Date: Wed, 19 Feb 2020 07:08:38 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Nicolas PARIS <nicolas.paris@riseup.net>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: tablespace to benefit from ssd ?
Message-ID: <20200219130838.GI31889@telsasoft.com>
References: <87ftf7fari.fsf@riseup.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <87ftf7fari.fsf@riseup.net>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

On Wed, Feb 19, 2020 at 05:42:41AM +0100, Nicolas PARIS wrote:
> Hi
> 
> 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.

To start with, you can:
ALTER SYSTEM SET temp_tablespaces='ssd';

That will improve speed of sorts which spill to disk (if any).

+1 to using LVM for purposes of instrumentation.

You can also:
ALTER TABLESPACE ssd SET (random_page_cost=1.0);

It'd be difficult to suggest anything further without knowing about your
workload or performance goals or issues.

-- 
Justin