0

I learned how to create tablespaces, rename it, assign an owner to it, and drop it. But I don't understand why I would even need it. A tablespace is simply a storage area on disk assigned to a postgresql object. It sounds no different from the data directory initialized with initdb. You can assign a data directory anywhere on your disk. So what makes a tablespace different and why would you need a tablespace?

Improve this question

1 Answer 1

Reset to default
3

initdb initializes the whole server data directory and every file will be in the same disk. With tablespaces you can distribute objects onto different disks when needed. The reasons usually are performance related. For example, you may want to have some rarely used tables on slower disks and high throughput tables on super fast disks.

Another benefit is to store indexes on separate disks so they have separate I/O from the data.

Naturally also if the volume on which the cluster is on runs out of space, tablespaces can be used to allow the databases to grow to other disks. This requires the tables to be moves to the new tablespace, it will not happen automatically.

Improve this answer
3
  • Are you suggesting you can separate different tables within the same databases to different disks by using tablespaces?
    – Donato
    Commented May 27, 2015 at 6:10
  • @Donato Yes, and also indexes.
    – Sami Kuhmonen
    Commented May 27, 2015 at 6:16
  • 2
    You can also have indexes for a table on a different disk than the table, which can have huge performance benefits, and lets you use faster but less reliable storage for indexes (since you can re-create them if they are lost).
    – Craig Ringer
    Commented May 27, 2015 at 6:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.