回复: [External] Re: Separate volumes

On Apr 6, 2020, at 16:36, Ed Behn <ed.behn@collins.com> wrote:

 

 

That makes sense. The person who told me this was very experienced with Oracle but was a PG novice. 

     -Ed

Ed Behn | Senior Systems Engineer | Avionics

COLLINS ÆROSPACE

2551 Riva Road, Annapolis, MD 21401 USA

Tel: +1 410 266 4426 | Mobile: +1 240 696 7443

ed.behn@collins.com | collinsaerospace.com

CONFIDENTIALITY WARNING: This message may contain proprietary and/or privileged information of Collins Aerospace and its affiliated companies. If you are not the intended recipient, please 1) Do not disclose, copy, distribute or use this message or its contents. 2) Advise the sender by return email. 3) Delete all copies (including all attachments) from your computer. Your cooperation is greatly appreciated.

 

 

 

On Mon, Apr 6, 2020 at 3:33 PM MichaelDBA <MichaelDBA@sqlexec.com> wrote:

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> 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> 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