Re: Disk Groups/Storage Management for a Large Database in PostgreSQL

Thanks Olivier and Scot for your inputs!

Another data point I would like to share is that VMs will be built in Azure
Cloud with Azure Managed Storage and Locally redundant storage (LRS) option
with a remote DR as well.

LVM or ZFS would still be a good option to allow easy storage/disk
management like add, resize or remove disks while PostgreSQL services are
up?
Is equal data distribution a challenge on LVM/ZFS disks?

Thanks
Amit

On Tue, Jan 23, 2024 at 9:49 AM Scot Kreienkamp <
Scot(dot)Kreienkamp(at)la-z-boy(dot)com> wrote:

> El lun, 22 ene 2024 18:44, Amit Sharma <amitpgsql(at)gmail(dot)com> escribió:
>
> Hi,
>
>
>
> We are building new VMs for PostgreSQL v15 on RHEL 8.x For a large
> database of 15TB-20TB.
>
>
>
> I would like to know from the experts that is it a good idea to create
> LVMs to manage storage for the database?
>
>
>
> Or are there any other better options/tools for disk groups in PostgreSQL,
> similar to ASM in Oracle?
>
>
>
> Thanks
>
> Amit
>
>
>
> Simple question that requires a somewhat more complex answer. There are
> actually 3 metrics to consider:
>
>
>
> 1) Capacity
>
> Your database doesn't fit on a single disk, so you need to distribute your
> data across several disks. LVM would indeed be an option (as well as ZFS or
> RAID disk arrays)
>
>
>
> 2) Safety
>
> If you loose 1 disk, your data is at risk, as you're likely to loose all
> tables partially loaded on that disk. LVM is still an option as long as it
> is configured on a RAID array. ZFS can do that natively.
>
>
>
> 3) Performance
>
> Oracle ADM ensures performance by automatically controlling the
> distribution of the tables. I would need to see on a real case how it is
> actually done. For sure, LVM and ZFS won't have this type of granularity.
>
>
>
> On the other hand, you can distribute your data in table partitions to
> help this distribution. It is not automatic but will surely help you to
> distribute your workload.
>
>
>
>
>
> As he is building VM’s I’m assuming the hardware level has all the
> redundancy for RAID/ZFS/etc. If that is the case then you don’t want to
> run RAID/ZFS/etc on top of that, let the hardware do its thing. If my
> assumption is wrong then ignore everything I’m saying.
>
>
>
> One thing I found that helps with speed of reads/writes… you can spread
> your read/write load across multiple SCSI controllers/disks using LVM. For
> example, I’m assuming VMWare which allows 4 SCSI controllers. Set the OS
> disk on SCSI controller 0, then spread your database disks in sets of 3
> across SCSI controllers 1-3, IE 3 disks of 5TB each, one on each SCSI
> controller. Then when you create your LVM partition specify the option to
> stripe it with 3 stripes. That gives you a setup where you are
> multiplexing reads/writes across all 3 SCSI controllers and disks instead
> of bottlenecking them all through 1 SCSI controller and disk at a time.
>
> *Scot Kreienkamp | Applications Infrastructure Architect | La-Z-Boy
> Corporate*
> One La-Z-Boy Drive | Monroe, Michigan 48162 | ( (734) 384-6403 | | )
> 1-734-915-1444 | * Scot(dot)Kreienkamp(at)la-z-boy(dot)com
> www.la-z-boy.com | facebook.com/lazboy | twitter.com/lazboy |
> youtube.com/lazboy
> [image: Smaller LZB Only Logo for Sign.png]
>
> This message is intended only for the individual or entity to which it is
> addressed. It may contain privileged, confidential information which is
> exempt from disclosure under applicable laws. If you are not the intended
> recipient, you are strictly prohibited from disseminating or distributing
> this information (other than to the intended recipient) or copying this
> information. If you have received this communication in error, please
> notify us immediately by e-mail or by telephone at the above number. Thank
> you.
>