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

El lun, 22 ene 2024 18:44, Amit Sharma <amitpgsql(at)gmail(dot)com<mailto: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<http://www.la-z-boy.com> | facebook.com/lazboy<http://facebook.com/lazboy> | twitter.com/lazboy<http://twitter.com/lazboy> | youtube.com/lazboy<http://youtube.com/lazboy>
[cid:smallerlzbonlylogoforsign_b8ca06bf-75b5-4619-8093-c9418c455597.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.