0

PostgreSQL version : 11.2
OS : RHEL or Oracle Linux 7.6 (Yet to be decided)

I am at the design stage of setting up a production database. In production, this DB size will be around 300GB to 400GB in size.

This is what I have in mind. Please let me know if this is a good idea for a Postgres production deployment.

I will get the below filesystems mounted with the following sizes.

/db ----> 50 GB
/pgdata ---> 500 GB

I will initialize the database cluster in the custom location /db/postgres/pg11/data .
And right from the start I will start creating tablespaces like below

CREATE TABLESPACE orders_tbs LOCATION '/pgdata/<db_name>/orders_tbs';

and place business objects in these tablespaces like below

CREATE TABLE orders (id int, order_item text) tablespace orders_tbs;

Thank You Arkhena, CL

In the above case, I thought of creating a separate filesystem for datafiles (/pgdata) and keeping the config files and logs in /db. So, my idea was bad.

Since I am in RHEL/Oracle Linux , by default, my $PGDATA will be /var/lib/pgsql/11/data .
But, I prefer to have my $PGDATA in a custom location like /db/postgres/pg11/data

Since the datafiles reside in $PGDATA/base directory , how about creating a disk layout like below using LVM ? A 50GB filesystem for $PGDATA's top parent directory /db and a separate 500 GB filesystem for $PGDATA/base directory ?

[root@localhost ~]# df -Ph
Filesystem                      Size  Used Avail Use% Mounted on
<output snipped>
.
.
/dev/mapper/VolGroup1-LogVol02  50G    23M  49.9G   1% /db
/dev/mapper/VolGroup1-LogVol04  500G  2.7M  499.9G  1% /db/postgres/pg11/data/base

I need to check with our Linux Admin on how the above disk layout can be optimally created without causing any storage bottlenecks.

  • Why do you want to separate the table data files from the rest of the database? – CL. Feb 26 at 8:10
1

Tablespaces in PostgreSQL exist for some really particular needs (and I doubt a less than 500 GB is in that case) and for SQL compliance. If you plan to create tablespaces to store eventually everything on the same disk, please don't. If you plan to create tablespaces inside $PGDATA, please don't.

Tablespaces lead to more complex recovery operations (if you need one). You'll curse yourself later, trust me.

You'll find a lot of excellent advices in Christophe Pettus slides (PostgreSQL when it's not your job). The slide 27 is about tablespaces and why not using them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.