When to use tablespaces

Craig Ringer
Craig Ringer

Most deployments do not need tablespaces, but there are cases where they're useful. Tablespaces in PostgreSQL are quite different to tablespaces in some other products and little direct influence on performance.

Splitting across tablespaces can allow for multiple file systems to be used. This permits parallel fsck during OS crash recovery, possibly improving recovery times. But fsck should rarely be necessary or even useful with modern crash-safe journalling file systems, to the point where if it is, something has probably already gone badly wrong and you should be looking at failover, not repair. Avoiding fsck is desirable, especially since widely used Linux file systems lack support for any sort of online fsck.

Tablespaces are not particularly useful for organising data into levels of durability. You might expect that if there are tables or indexes you can tolerate losing, you could put them on tablespaces backed by less reliable storage. But crash recovery redo will refuse to proceed past a missing table or index file, even if you don't think it's vital, so that unimportant index will hold up the whole system's startup. At time of writing PostgreSQL does not support ignoring lost tables or indexes and continuing redo. There's limited support in the form of the rather heavy-handed form of the zero_damaged_pages option, but it may not handle all possible issues from lost tablespaces.

Tablespaces do not influence PostgreSQL's I/O parallelism. Though you can potentially fine tune your underlying storage access a little. For example, if you know that queries that are major sources of load tend to (say) join two huge tables, storing them on separate tablespaces backed by independent storage may gain you a small performance benefit.

The main utility of tablespaces is organising data into service classes based on storage performance. Putting hot, rapidly changed data on high performance storage, and cold or append-only data on lower performance storage.

Was this article helpful?

0 out of 0 found this helpful