22.6. Tablespaces (資料表空間)1

Tablespaces inPostgreSQLallow 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 aPostgreSQLinstallation. 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.


Even though located outside the main PostgreSQL data directory, tablespaces are an integral part of the database cluster and_cannot_be treated as an autonomous collection of data files. They are dependent on metadata contained in the main data directory, and therefore cannot be attached to a different database cluster or backed up individually. Similarly, if you lose a tablespace (file deletion, disk failure, etc), the database cluster might become unreadable or unable to start. Placing a tablespace on a temporary file system like a RAM disk risks the reliability of the entire cluster.

To define a tablespace, use theCREATE TABLESPACEcommand, for example::

CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';

The location must be an existing, empty directory that is owned by thePostgreSQLoperating system user. All objects subsequently created within the tablespace will be stored in files underneath this directory. The location must not be on removable or transient storage, as the cluster might fail to function if the tablespace is missing or lost.


There is usually not much point in making more than one tablespace per logical file system, since you cannot control the location of individual files within a logical file system. However,PostgreSQLdoes not enforce any such limitation, and indeed it is not directly aware of the file system boundaries on your system. It just stores files in the directories you tell it to use.

Creation of the tablespace itself must be done as a database superuser, but after that you can allow ordinary database users to use it. To do that, grant them theCREATEprivilege on it.

Tables, indexes, and entire databases can be assigned to particular tablespaces. To do so, a user with theCREATEprivilege on a given tablespace must pass the tablespace name as a parameter to the relevant command. For example, the following creates a table in the tablespacespace1:

CREATE TABLE foo(i int) TABLESPACE space1;

Alternatively, use thedefault_tablespaceparameter:

SET default_tablespace = space1;
CREATE TABLE foo(i int);

Whendefault_tablespaceis set to anything but an empty string, it supplies an implicitTABLESPACEclause forCREATE TABLEandCREATE INDEXcommands that do not have an explicit one.

There is also atemp_tablespacesparameter, which determines the placement of temporary tables and indexes, as well as temporary files that are used for purposes such as sorting large data sets. This can be a list of tablespace names, rather than only one, so that the load associated with temporary objects can be spread over multiple tablespaces. A random member of the list is picked each time a temporary object is to be created.

The tablespace associated with a database is used to store the system catalogs of that database. Furthermore, it is the default tablespace used for tables, indexes, and temporary files created within the database, if noTABLESPACEclause is given and no other selection is specified bydefault_tablespaceortemp_tablespaces(as appropriate). If a database is created without specifying a tablespace for it, it uses the same tablespace as the template database it is copied from.

Two tablespaces are automatically created when the database cluster is initialized. Thepg_globaltablespace is used for shared system catalogs. Thepg_defaulttablespace is the default tablespace of thetemplate1andtemplate0databases (and, therefore, will be the default tablespace for other databases as well, unless overridden by aTABLESPACEclause inCREATE DATABASE).

Once created, a tablespace can be used from any database, provided the requesting user has sufficient privilege. This means that a tablespace cannot be dropped until all objects in all databases using the tablespace have been removed.

To remove an empty tablespace, use theDROP TABLESPACEcommand.

To determine the set of existing tablespaces, examine thepg_tablespacesystem catalog, for example

SELECT spcname FROM pg_tablespace;

Thepsqlprogram's\dbmeta-command is also useful for listing the existing tablespaces.

PostgreSQLmakes use of symbolic links to simplify the implementation of tablespaces. This means that tablespaces can be used_only_on systems that support symbolic links.

The directory$PGDATA/pg_tblspccontains symbolic links that point to each of the non-built-in tablespaces defined in the cluster. Although not recommended, it is possible to adjust the tablespace layout by hand by redefining these links. Under no circumstances perform this operation while the server is running. Note that in PostgreSQL 9.1 and earlier you will also need to update thepg_tablespacecatalog with the new locations. (If you do not,pg_dumpwill continue to output the old tablespace locations.)

1.  PostgreSQL: Documentation: 10: 22.6. Tablespaces

results matching ""

    No results matching ""