PostgreSQL & tablespaces - it's not so scary ...

Having spent the last week with Oracle DBAs eagerly peeking towards PostgreSQL, I saw that besides the usual beginner questions one of the Postgres concepts that sounded kind of familiar for Oracle folks (but still brought some confusion, as it's not quite the same thing) are tablespaces.

From higher (user) level everything is pretty similar to Oracle and other systems though – tablespaces "house" database object like tables and indexes. The biggest differences compared to Oracle might be that Postgres tablespaces are cluster level objects and there's no possibility to restrict tablespace (thus cluster/database) size. You don't have to deal with single datafiles but folders instead.

And before going to the details - why would one need tablespaces at all? Well, for two reasons mostly – first to keep the database running when the disk space is running out on the current partition and there's no easy way to extend it (no LVM for example) ... and second, to optimize performance and enable more parallel IO, by for example having indexes on fast SSD disks to benefit OLTP workloads. Just "for fun" I wouldn't recommend setting up tablespaces as it has some implications for replication scenarios, see end of article for more.

So what is exactly a tablespace in Postgres?

As a reminder – by default a Postgres cluster is "self-contained", meaning all the data files will be residing inside the initialized "data directory" (called also PGDATA as one can set the similarly named environment variable to simplify working with clusters via pg_ctl), making things very easy and portable.

But about tablespaces - actually it's very simple, a tablespace is just another name for a folder where the Postgres server process owner (typically "postgres" system user) is the owner and can write some files. The folder can be located anywhere, Postgres must just be able to create symlinks to that certain path. The symlinks are created in the "pg_tblspc" subfolder of the cluster (for example /var/lib/postgresql/9.6/main/pg_tblspc on Ubuntu) and can be inspected using the usual file system commands like "ls -l". After that, users can explicitly say that they want to store some specific tables/indexes in that new "linked folder" or one can also decide to make it the default for new objects or even move all old objects over to the new tablespace.

How to create one?

In the simplest form something like that will work:

When using tablespaces to tune IO though, something like that would make more sense:

NB! Just declaring a tablespace won't help normal unprivileged users – they all need a CREATE grant to create tables/indexes. Something like that will alleviate the situation. Note that for databases with high security requirements one should of course give out such grants on a per user basis.

Moving existing tables to a new tablespace

When plagued by disks running out of space, then after creating a new tablespace the first thing should be setting it also to serve as a default for new objects. This can be done by modifying the default_tablespace parameter and reloading the server config via pg_reload_conf() or "pg_ctl -D mydatadir reload". After that one could/should move also fast growing tables/indexes to the new tablespace. This can be done per table or starting with Postgres 9.4 also en masse.

NB! Using the later syntax locks all tables/indexes (use NOWAIT for fast failing when unable to get all the locks) and will then start to copy the data files one by one, so basically a downtime is required. For "online" moving one can though use tools like pg_repack or pg_squeeze.

Backup & replication considerations

For logical (pg_dump, pg_dumpall) backups nothing changes, but for physical backups (or when building up a replica), pg_basebackup will by default require the exact same directory layout on the replica for things to work. When this is not possible or feasible then one can "re-map" the tablespaces though using the --tablespace-mapping parameter or just use the "tar" format that creates again a "self-contained" blob. See pg_basebackup for more details. Also for doing manual basebackups with cp or rsync or such, one should make sure the "dereference" option is used so that the datafiles behind the symlinks get copied.

Hope it helped. Questions/corrections are welcome as usual! For more information - subscribe to our newsletter.