Subject: Re: Separate volumes
To: Steve Midgley <science@misuse.org>
Cc: Erik Brandsberg <erik@heimdalldata.com>, Ed Behn <ed.behn@collins.com>,
 pgsql-sql@lists.postgresql.org
References: 
 <CAE1kc7VfQVz0VGf4cyjaY5KYSZxrPQjPWMoEsqFTJ41-LkMOHg@mail.gmail.com>
 <CAFcck8FF4T3_PL7Fa+iW5YBkA4iZ3aR2V_jka=GpV9taPph9NQ@mail.gmail.com>
 <CAJexoSLSHRVOKzVGD-pDHE=KY9un7bPsxGO6fSKZVgeGsj2t6g@mail.gmail.com>
From: MichaelDBA <MichaelDBA@sqlexec.com>
Message-ID: <5b60ae44-33e0-d9ea-d6e9-c6db9b4c7ef1@sqlexec.com>
Date: Mon, 6 Apr 2020 15:30:57 -0400
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 PostboxApp/7.0.12
MIME-Version: 1.0
In-Reply-To: 
 <CAJexoSLSHRVOKzVGD-pDHE=KY9un7bPsxGO6fSKZVgeGsj2t6g@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------035C223B60F7377070FF2437"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------035C223B60F7377070FF2437
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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@heimdalldata.com 
> <mailto:erik@heimdalldata.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.behn@collins.com
>     <mailto:ed.behn@collins.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


--------------035C223B60F7377070FF2437
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: base64

PGh0bWwgdGhlbWU9ImRlZmF1bHQtbGlnaHQiPjxoZWFkPg0KPG1ldGEgaHR0cC1lcXVpdj0i
Q29udGVudC1UeXBlIiBjb250ZW50PSJ0ZXh0L2h0bWw7IGNoYXJzZXQ9dXRmLTgiPg0KPC9o
ZWFkPjxib2R5IHRleHQ9IiMwMDAwMDAiPkhpIFN0ZXZlLDxicj4NCjxicj4NCkNvbWluZyBm
cm9tIG9yYWNsZSBsYW5kLCB0YWJsZXNwYWNlcyBwbGF5IGEgYmlnZ2VyIHJvbGUgdGhhbiB0
aGV5IGRvIGluIA0KUEcgbGFuZC7CoCBJbiBQRyBsYW5kLCB0aGV5IGNhbiBjb250cm9sIHRo
ZSBtYXBwaW5nIG9mIHRhYmxlcy9pbmRleGVzIHRvIA0KZmFzdGVyIG9yIHNsb3dlciBkZXZp
Y2VzLiBCeSBzZXBhcmF0aW5nIGEgdGFibGUncyB0YWJsZXNwYWNlIGZyb20gaXRzIA0KaW5k
ZXggdGFibGVzcGFjZSwgeW91IG1heSBnZXQgbW9yZSBwYXJhbGxlbCBJL08uwqAgVGhleSBh
bHNvIGFsbG93IGZvciANCmZsZXhpYmlsaXR5IGluIHNldHRpbmcgcGcgY29uZmlnIHBhcmFt
ZXRlcnMgcGVyIHRhYmxlc3BhY2U6PGJyPg0KPGJyPg0KPHByZSBzdHlsZT0iYm94LXNpemlu
ZzogYm9yZGVyLWJveDsgb3ZlcmZsb3c6IGF1dG87IGZvbnQtZmFtaWx5OiBNZW5sbywgTW9u
YWNvLCBDb25zb2xhcywgJnF1b3Q7Q291cmllciBOZXcmcXVvdDssIG1vbm9zcGFjZTsgZm9u
dC1zaXplOiAxM3B4OyBkaXNwbGF5OiBibG9jazsgcGFkZGluZzogOS41cHg7IG1hcmdpbjog
MHB4IDBweCAxLjVlbTsgbGluZS1oZWlnaHQ6IDEuNDI4NTc7IHdvcmQtYnJlYWs6IGJyZWFr
LWFsbDsgb3ZlcmZsb3ctd3JhcDogYnJlYWstd29yZDsgY29sb3I6IHJnYig1MSwgNTEsIDUx
KTsgYmFja2dyb3VuZC1jb2xvcjogcmdiKDI0NSwgMjQ1LCAyNDUpOyBib3JkZXI6IDFweCBz
b2xpZCByZ2IoMjA0LCAyMDQsIDIwNCk7IGJvcmRlci1yYWRpdXM6IDRweDsgZm9udC1zdHls
ZTogbm9ybWFsOyBmb250LXZhcmlhbnQtbGlnYXR1cmVzOiBub3JtYWw7IGZvbnQtdmFyaWFu
dC1jYXBzOiBub3JtYWw7IGZvbnQtd2VpZ2h0OiA0MDA7IGxldHRlci1zcGFjaW5nOiBub3Jt
YWw7IG9ycGhhbnM6IDI7IHRleHQtYWxpZ246IHN0YXJ0OyB0ZXh0LWluZGVudDogMHB4OyB0
ZXh0LXRyYW5zZm9ybTogbm9uZTsgd2lkb3dzOiAyOyB3b3JkLXNwYWNpbmc6IDBweDsgLXdl
YmtpdC10ZXh0LXN0cm9rZS13aWR0aDogMHB4OyB0ZXh0LWRlY29yYXRpb24tc3R5bGU6IGlu
aXRpYWw7IHRleHQtZGVjb3JhdGlvbi1jb2xvcjogaW5pdGlhbDsiPjxjb2RlIGNsYXNzPSJs
YW5ndWFnZS10ZXh0IiBkYXRhLWxhbmc9InRleHQiIHN0eWxlPSJib3gtc2l6aW5nOiBib3Jk
ZXItYm94OyBmb250LWZhbWlseTogTWVubG8sIE1vbmFjbywgQ29uc29sYXMsICZxdW90O0Nv
dXJpZXIgTmV3JnF1b3Q7LCBtb25vc3BhY2U7IGZvbnQtc2l6ZTogMTRweDsgcGFkZGluZy10
b3A6IGluaGVyaXQ7IHBhZGRpbmctcmlnaHQ6IDBweDsgcGFkZGluZy1ib3R0b206IGluaGVy
aXQ7IHBhZGRpbmctbGVmdDogMHB4OyBjb2xvcjogaW5oZXJpdDsgYmFja2dyb3VuZC1jb2xv
cjogdHJhbnNwYXJlbnQ7IGJvcmRlci1yYWRpdXM6IDBweDsgd2hpdGUtc3BhY2U6IHByZS13
cmFwOyI+YWx0ZXIgdGFibGVzcGFjZSBteXRhYmxlc3BhY2Ugc2V0ICggc2VxX3BhZ2VfY29z
dD0wLjUsIHJhbmRvbV9wYWdlX2Nvc3Q9MC41ICk7DQo8L2NvZGU+PC9wcmU+DQpCdXQgdGhl
eSBhbHNvIGNhbiBiZSBhIGhlYWRhY2hlIGluIG1hbmFnaW5nIHN0dWZmLsKgwqAgRm9yIGlu
c3RhbmNlLCBhbGwgDQpyZXBsaWNhcyBtdXN0IGhhdmUgdGhlIHNhbWUgZGlyZWN0b3J5IHN0
cnVjdHVyZSBhbmQgc3ltbGlua3MuPGJyPg0KPGJyPg0KUmVnYXJkcyw8YnI+DQpNaWNoYWVs
IFZpdGFsZTxicj4NCjxicj4NCjxicj4NCjxicj4NCjxzcGFuPlN0ZXZlIE1pZGdsZXkgd3Jv
dGUgb24gNC82LzIwMjAgMToxMSBQTTo8L3NwYW4+PGJyPg0KPGJsb2NrcXVvdGUgdHlwZT0i
Y2l0ZSIgDQpjaXRlPSJtaWQ6Q0FKZXhvU0xTSFJWT0t6VkdELXBESEU9S1k5dW43YlBzeEdP
NmZTS1pWZ2VHc2oydDZnQG1haWwuZ21haWwuY29tIj4NCiAgPG1ldGEgaHR0cC1lcXVpdj0i
Y29udGVudC10eXBlIiBjb250ZW50PSJ0ZXh0L2h0bWw7IGNoYXJzZXQ9dXRmLTgiPg0KICA8
ZGl2IGRpcj0ibHRyIj48ZGl2IGRpcj0ibHRyIj48YnI+PC9kaXY+PGJyPjxkaXYgY2xhc3M9
ImdtYWlsX3F1b3RlIj48ZGl2DQogZGlyPSJsdHIiIGNsYXNzPSJnbWFpbF9hdHRyIj5PbiBN
b24sIEFwciA2LCAyMDIwIGF0IDk6NDIgQU0gRXJpayANCkJyYW5kc2JlcmcgJmx0OzxhIGhy
ZWY9Im1haWx0bzplcmlrQGhlaW1kYWxsZGF0YS5jb20iIA0KbW96LWRvLW5vdC1zZW5kPSJ0
cnVlIj5lcmlrQGhlaW1kYWxsZGF0YS5jb208L2E+Jmd0OyB3cm90ZTo8YnI+PC9kaXY+PGJs
b2NrcXVvdGUNCiBjbGFzcz0iZ21haWxfcXVvdGUiIHN0eWxlPSJtYXJnaW46MHB4IDBweCAw
cHggMC44ZXg7Ym9yZGVyLWxlZnQ6MXB4IA0Kc29saWQgcmdiKDIwNCwyMDQsMjA0KTtwYWRk
aW5nLWxlZnQ6MWV4Ij48ZGl2IGRpcj0ibHRyIj5XaXRoIFNTRCBhbmQgDQppdCdzIHJhbmRv
bSBJTyBwZXJmb3JtYW5jZSwgSSBkb3VidCB0aGF0IHRoaXMgYWR2aWNlIHdvdWxkIGFwcGx5
IGFzIA0KbXVjaCwgYW5kIGFkZHMgY29tcGxleGl0eSB0byB5b3VyIGNvbmZpZ3VyYXRpb24g
YW5kIG1hbmFnZW1lbnQuwqAgSW4gDQpwYXJ0aWN1bGFyIGlmIHlvdSB1c2UgYW55IGZpbGVz
eXN0ZW0gbGV2ZWwgc25hcHNob3R0aW5nIChsaWtlIHdpdGggWkZTKSwNCiBzcGxpdHRpbmcg
dGhlIGZpbGVzcGFjZXMgd2lsbCBtYWtlIGl0IGhhcmRlciB0byBkbyByZXN0b3JlcyBhbmQg
dXNpbmcgDQpzbmFwc2hvdHMuPC9kaXY+PGJyPjxkaXYgY2xhc3M9ImdtYWlsX3F1b3RlIj48
ZGl2IGRpcj0ibHRyIiANCmNsYXNzPSJnbWFpbF9hdHRyIj5PbiBNb24sIEFwciA2LCAyMDIw
IGF0IDEwOjQwIEFNIEVkIEJlaG4gJmx0OzxhIA0KaHJlZj0ibWFpbHRvOmVkLmJlaG5AY29s
bGlucy5jb20iIHRhcmdldD0iX2JsYW5rIiBtb3otZG8tbm90LXNlbmQ9InRydWUiPmVkLmJl
aG5AY29sbGlucy5jb208L2E+Jmd0Ow0KIHdyb3RlOjxicj48L2Rpdj48YmxvY2txdW90ZSBj
bGFzcz0iZ21haWxfcXVvdGUiIHN0eWxlPSJtYXJnaW46MHB4IDBweCANCjBweCAwLjhleDti
b3JkZXItbGVmdDoxcHggc29saWQgcmdiKDIwNCwyMDQsMjA0KTtwYWRkaW5nLWxlZnQ6MWV4
Ij48ZGl2IA0KZGlyPSJsdHIiPjxkaXY+SSB3YXMgb25jZSB0b2xkIHRoYXQgaXQncyBiZXN0
IHByYWN0aWNlIHRvIHN0b3JlIHRhYmxlcyANCmFuZCBpbmRleGVzIGluIHNlcGFyYXRlwqB0
YWJsZXNwYWNlcyBsb2NhdGVkwqBvbiBzZXBhcmF0ZcKgcGh5c2ljYWwgZHJpdmVzLg0KIEl0
IHNlZW1lZCBsb2dpY2FsIHRoYXQgdGhpcyBzaG91bGQgaW1wcm92ZSBwZXJmb3JtYW5jZcKg
YmVjYXVzZSB0aGUgDQpyZWFkLWhlYWQgd291bGRuJ3QgbmVlZCB0byBqdW1wIGJhY2sgYW5k
wqBmb3J0aCBiZXR3ZWVuIGEgdGFibGUgYW5kIGl0cyANCmluZGV4LsKgPC9kaXY+PGRpdj48
YnI+PC9kaXY+PGRpdj5Ib3dldmVyLCBJIGNhbid0IHNlZW0gdG8gZmluZCB0aGlzIA0KYWR2
aWNlIGFueXdoZXJlIG9ubGluZS4gSXMgaXQgaW5kZWVkIGJlc3QgcHJhY3RpY2U/IElzIGl0
IHdvcnRoIHRoZSANCmhhc3NsZT88L2Rpdj48ZGl2Pjxicj48L2Rpdj48ZGl2PsKgIDxicj48
L2Rpdj48L2Rpdj48L2Jsb2NrcXVvdGU+PC9kaXY+PGJyPjwvYmxvY2txdW90ZT48ZGl2PkFz
DQogYSBnZW5lcmFsIGFuZCBwcmFjdGljYWwgbWF0dGVyIEkgMTAwJSBhZ3JlZSB3aXRoIEVy
aWsgLS0gdGhlIGFkdmljZSBpcyBhDQogYml0IG91dCBvZiBkYXRlLCBhbmQgZm9yIFNTRHMg
aXQgcHJvYmFibHkgbWFrZXMgbm8gbWVhbmluZ2Z1bCANCmRpZmZlcmVuY2UuIEhvd2V2ZXIg
Zm9yIGV4dHJlbWVseSBoaWdoLCBzdXN0YWluZWQgd29ya2xvYWRzLCB5b3UgbWlnaHQgDQpm
aW5kIHNwbGl0dGluZyB0YWJsZXMsIGluZGljZXMsIGFuZCB0cmFuc2FjdGlvbiBsb2dzIG9u
dG8gc2VwYXJhdGUgZGlzayANCl9kaXNrIGFycmF5cyBhbmQgY29udHJvbGxlcnNfIGNvdWxk
IHlpZWxkIGltcHJvdmVtZW50cywgcGFydGljdWxhcmx5IGZvcg0KIGNlcnRhaW4gUkFJRCBz
ZXR1cHMuIEJ1dCBtYXhpbmcgb3V0IGEgZGlzayBjb250cm9sbGVyIGlzIHByZXR0eSBoYXJk
IHRvDQogZG8gKGltcG9zc2libGUgYWZhaWsgd2l0aCBhIHNpbmdsZSBkcml2ZSksIHNvIHlv
dSdkIHdhbnQgdG8gaGF2ZSBzb21lIA0Kc3Ryb25nIG1ldHJpY3MgdG8gc2hvdyB0aGlzIGlz
IHdvcnRoIGl0LiBBdCB0aGF0IHBvaW50LCB5b3UnZCBwcm9iYWJseSANCmJlIGJldHRlciBv
ZmYgZ2V0dGluZyBjb21tZXJjaWFsIGRpc2sgYXJyYXkgc29sdXRpb25zIGludG8gdGhlIG1p
eCANCnJhdGhlciB0aGFuIHJvbGxpbmcgeW91ciBvd24gYW55d2F5Li48L2Rpdj48ZGl2Pjxi
cj48L2Rpdj48ZGl2PlN0ZXZlPGJyPjwvZGl2PjwvZGl2PjwvZGl2Pg0KPC9ibG9ja3F1b3Rl
Pg0KPGJyPg0KPC9ib2R5PjwvaHRt
bD4=
--------------035C223B60F7377070FF2437--