Re: Separate volumes

Hi Steve,

Coming from oracle land, tablespaces play a bigger role than they do in
PG land.  In PG land, they can control the mapping of tables/indexes to
faster or slower devices. By separating a table's tablespace from its
index tablespace, you may get more parallel I/O.  They also allow for
flexibility in setting pg config parameters per tablespace:

|alter tablespace mytablespace set ( seq_page_cost=0.5,
random_page_cost=0.5 ); |

But they also can be a headache in managing stuff.   For instance, all
replicas must have the same directory structure and symlinks.

Regards,
Michael Vitale

Steve Midgley wrote on 4/6/2020 1:11 PM:
>
>
> On Mon, Apr 6, 2020 at 9:42 AM Erik Brandsberg <erik(at)heimdalldata(dot)com
> <mailto:erik(at)heimdalldata(dot)com>> wrote:
>
> With SSD and it's random IO performance, I doubt that this advice
> would apply as much, and adds complexity to your configuration and
> management.  In particular if you use any filesystem level
> snapshotting (like with ZFS), splitting the filespaces will make
> it harder to do restores and using snapshots.
>
> On Mon, Apr 6, 2020 at 10:40 AM Ed Behn <ed(dot)behn(at)collins(dot)com
> <mailto:ed(dot)behn(at)collins(dot)com>> wrote:
>
> I was once told that it's best practice to store tables and
> indexes in separate tablespaces located on separate physical
> drives. It seemed logical that this should improve
> performance because the read-head wouldn't need to jump back
> and forth between a table and its index.
>
> However, I can't seem to find this advice anywhere online. Is
> it indeed best practice? Is it worth the hassle?
>
>
>
> As a general and practical matter I 100% agree with Erik -- the advice
> is a bit out of date, and for SSDs it probably makes no meaningful
> difference. However for extremely high, sustained workloads, you might
> find splitting tables, indices, and transaction logs onto separate
> disk _disk arrays and controllers_ could yield improvements,
> particularly for certain RAID setups. But maxing out a disk controller
> is pretty hard to do (impossible afaik with a single drive), so you'd
> want to have some strong metrics to show this is worth it. At that
> point, you'd probably be better off getting commercial disk array
> solutions into the mix rather than rolling your own anyway..
>
> Steve