Postgresql – Best practice for creating tablespaces in Postgresql

configurationpostgresqltablespaces

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, the DB 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 PostgreSQL production deployment.

I will have the following file system 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 this: 

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

…and place business objects in these tablespaces like this:

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.

Best Answer

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.

Related Question