From the PostgreSQL docs:

Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects.

By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at least two ways. First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.

Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.

As you can see, while not as powerful as tablespaces in, say, Oracle, they do still have their uses in PostgreSQL. You can use them to make use of different filesystems, or different mount options, or different disk types and, in doing so, intelligently apply performance characteristics to subsets of your data. For example, you could put your highest volume tables in a tablespace that is mounted from SSDs while the rest of your db is mounted from spinning rust.

Sounds decent, right? Now you before you go off and be “clever” and create an SSD-backed mountpoint for your new tablespace, understand that there are places you should not create the tablespace. You shouldn’t create tablespaces on any kind of ephemeral storage, for example on a tmpfs or a ramfs or similar. You also should not create your new tablespaces under $PGDATA. Yes, I’m aware there is $PGDATA/pg_tblspc but that directory is not for you. The system will auto-populate that directory with pointers to the real location of your tablespaces!

So what happens when you create a tablespace inside $PGDATA? Let’s find out. First, we’ll create the directory for the tablespace:

$ mkdir $PGDATA/tablespaces
$ cd $PGDATA/tablespaces
$ pwd
/Users/doug.hunley/pgdata/tablespaces

And we see that nothing bad has happened yet. So, let’s pop over into psql and actually create the tablespace:

> CREATE TABLESPACE ts1 LOCATION '/Users/doug.hunley/pgdata/tablespaces';
WARNING:  42P17: tablespace location should not be inside the data directory
LOCATION:  CreateTableSpace, tablespace.c:295
CREATE TABLESPACE
Time: 7.797 ms

We get a warning (not an error, for some reason) but it works and all appears fine. Now you can spend minutes/days/months/years using your new tablespace and never notice that you’ve got a problem. So where does the problem come in?

Let’s try to make a backup of our cluster:

$ pg_basebackup -D pgdata2 -Fp -R -Xs -c fast -l 'clone for slave' -P -v
transaction log start point: 2/17000028 on timeline 1
pg_basebackup: directory "/Users/doug.hunley/pgdata/tablespaces" exists but is not empty

There it is.

When creating the backup, it tries to ensure the tablespace location is the same, but then it won’t write to a non-empty directory. My example is two different $PGDATA locations on the same box, but the issue is the same when using different machines because pg_basebackup backs up everything in $PGDATA which means your tablespace directory gets cloned before it gets to the actual cloning of the data in the tablespace so you end up with “stuff” in the dir, making it non-empty. Which gives you the same error and output.

OK, so it breaks backups. I can work around that by using another backup method. What else?

How about using pg_upgrade to do an upgrade? No matter if you run in link mode or not, pg_upgrade will not move your tablespace location. So you may have ~/pgdata95 and ~/pgdata96 after the upgrade, but your tablespaces are still in ~/pgdata95/tablespaces. So, as per the docs:

Once you are satisfied with the upgrade, you can delete the old cluster’s data directories by running the script mentioned when pg_upgrade completes.

And boom you’ve just deleted your tablespaces off disk. Congratulations!

So there you have it. Two very good reasons to not create tablespaces inside $PGDATA. Please, don’t do this. Everyone who admins that cluster going forward will thank you.