Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm confused about tablespaces in PostgreSQL. Is it something like LVM? I mean when disk is getting full, can we add another disk, format and then CREATE TABLESPACE tblspace LOCATION /media/disk2/data? Is it enough? Or we should manually ALTER databases, tables or indexes to take benefit of it?

share|improve this question

2 Answers 2

up vote 5 down vote accepted

Historically, PostgreSQL stored tables and indexes in individual files. Tablespaces are a means of placing multiple tables/indexes into a single file or group of related files in the same directory. Other database management systems use similar techniques, although tney can store multiple objects within a file. On PostgreSQL a tablespace is implemented as a directory that individual files are placed in, but the files are still one-per-database-object.

A table must reside in a single tablespace unless it is partitioned, in which case each partition must reside on a given tablespace. On some DBMS platforms you could add files to a tablespace as you suggest, although in the case case of PostgreSQL a tablespace is essentially just a directory for grouping files together. On systems where this is possible it could result in suboptimal I/O performance as individual disks could contain I/O hot spots. Probably, you would be better off setting multiple disks up in a RAID volume and placing the tablespace on that.

Depending on your workload you may wish to set up individual disk volumes with tablespaces on each volume and distribute parts of your database over the different volumes. The most trivial case is separating database logs onto a separate volume, but you could distribute data over multiple volumes for various reasons.

EDIT: Here is a link on PostgreSQL tablespaces.

share|improve this answer
    
So a single flat table or index can not expand on multiple tablespaces? –  Majid Azimi Mar 7 '12 at 12:43
    
@MajidAzimi not without partitioning it, although you can add files (e.g. multiple partitions) to a tablespace. –  ConcernedOfTunbridgeWells Mar 7 '12 at 12:58

I mean when disk is getting full, can we add another disk, format and then CREATE TABLESPACE tblspace LOCATION /media/disk2/data? Is it enough?

No, that is not enough - it is true that 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.", but current objects will not automatically spill over into the new tablespace in the sense you mean when you refer to LVM - they need to be moved, eg with alter table:

alter table foo set tablespace bar

The full documentation for tablespaces can be found here and details what their purpose is, including the quote above about running out of space and, for example:

...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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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