PostgreSQL: Extending database storage when disk is full

PostgreSQL doesn't support spanning a single table over multiple tablespaces directly.

You can use table partitioning to create a "master" table, with child tables on different tablespaces. It's a bit tricky and it means you can't use foreign keys or enforce a proper unique constraint on the primary key, so it's not generally the preferred option.

A more practical option is usually to use a logical volume manager, RAID system, or similar to span multiple physical disks into a single volume, with a single file-system on it. Then PostgreSQL just uses it like anything else and doesn't need to care. On Linux, this might mean LVM, the md software RAID driver in raid0 mode, btrfs, or all sorts of other options. Most (all) of these require you to build a new filesystem then move the PostgreSQL database onto it, you can't convert a regular filesystem that's directly on top of a partition into a raid volume / logical volume / etc.

Note that neither of these options are safe if the additonal storage is removable. If you remove a disk containing a tablespace, or remove part of a logical volume or spanned raid, your entire database will become inacessible. If you lose the disk/volume or its data, the entire DB will be unrecoverable.

You are correct, tablespaces only allow moving entire tables to new drives / file systems, not extending existing the space for existing ones.

In theory you could muck around with symlinks to move individual postgresql data files to other drives manually, but that could be problematic (and must be done while postgresql is not running).

You can run vacummn analyze command on the table so that it release the stale memory(not in use or space required by table for rows which are deleted) which can be used to add new row into table.