<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Tablespaces</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Managing Databases" HREF="managing-databases.html"><LINK REL="PREVIOUS" TITLE="Destroying a Database" HREF="manage-ag-dropdb.html"><LINK REL="NEXT" TITLE="Client Authentication" HREF="client-authentication.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="manage-ag-dropdb.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="managing-databases.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 18. Managing Databases</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="managing-databases.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="client-authentication.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="MANAGE-AG-TABLESPACES" >18.6. Tablespaces</A ></H1 ><A NAME="AEN19820" ></A ><P > Tablespaces in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > allow 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. </P ><P > By using tablespaces, an administrator can control the disk layout of a <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > installation. 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. </P ><P > 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. </P ><P > To define a tablespace, use the <A HREF="sql-createtablespace.html" >CREATE TABLESPACE</A > command, for example:<A NAME="AEN19829" ></A > </P><PRE CLASS="PROGRAMLISTING" >CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';</PRE ><P> The location must be an existing, empty directory that is owned by the <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > system user. All objects subsequently created within the tablespace will be stored in files underneath this directory. </P ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > 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, <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > does 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. </P ></BLOCKQUOTE ></DIV ><P > Creation of the tablespace itself must be done as a database superuser, but after that you can allow ordinary database users to make use of it. To do that, grant them the <TT CLASS="LITERAL" >CREATE</TT > privilege on it. </P ><P > Tables, indexes, and entire databases can be assigned to particular tablespaces. To do so, a user with the <TT CLASS="LITERAL" >CREATE</TT > privilege 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 tablespace <TT CLASS="LITERAL" >space1</TT >: </P><PRE CLASS="PROGRAMLISTING" >CREATE TABLE foo(i int) TABLESPACE space1;</PRE ><P> </P ><P > Alternatively, use the <A HREF="runtime-config.html#GUC-DEFAULT-TABLESPACE" >default_tablespace</A > parameter: </P><PRE CLASS="PROGRAMLISTING" >SET default_tablespace = space1; CREATE TABLE foo(i int);</PRE ><P> When <TT CLASS="VARNAME" >default_tablespace</TT > is set to anything but an empty string, it supplies an implicit <TT CLASS="LITERAL" >TABLESPACE</TT > clause for <TT CLASS="COMMAND" >CREATE TABLE</TT > and <TT CLASS="COMMAND" >CREATE INDEX</TT > commands that do not have an explicit one. </P ><P > The tablespace associated with a database is used to store the system catalogs of that database, as well as any temporary files created by server processes using that database. Furthermore, it is the default tablespace selected for tables and indexes created within the database, if no <TT CLASS="LITERAL" >TABLESPACE</TT > clause is given (either explicitly or via <TT CLASS="VARNAME" >default_tablespace</TT >) when the objects are created. If a database is created without specifying a tablespace for it, it uses the same tablespace as the template database it is copied from. </P ><P > Two tablespaces are automatically created by <TT CLASS="LITERAL" >initdb</TT >. The <TT CLASS="LITERAL" >pg_global</TT > tablespace is used for shared system catalogs. The <TT CLASS="LITERAL" >pg_default</TT > tablespace is the default tablespace of the <TT CLASS="LITERAL" >template1</TT > and <TT CLASS="LITERAL" >template0</TT > databases (and, therefore, will be the default tablespace for other databases as well, unless overridden by a <TT CLASS="LITERAL" >TABLESPACE</TT > clause in <TT CLASS="COMMAND" >CREATE DATABASE</TT >). </P ><P > 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. </P ><P > To remove an empty tablespace, use the <A HREF="sql-droptablespace.html" >DROP TABLESPACE</A > command. </P ><P > To determine the set of existing tablespaces, examine the <TT CLASS="STRUCTNAME" >pg_tablespace</TT > system catalog, for example </P><PRE CLASS="SYNOPSIS" >SELECT spcname FROM pg_tablespace;</PRE ><P> The <A HREF="app-psql.html" ><SPAN CLASS="APPLICATION" >psql</SPAN ></A > program's <TT CLASS="LITERAL" >\db</TT > meta-command is also useful for listing the existing tablespaces. </P ><P > <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > makes extensive use of symbolic links to simplify the implementation of tablespaces. This means that tablespaces can be used <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >only</I ></SPAN > on systems that support symbolic links. </P ><P > The directory <TT CLASS="FILENAME" >$PGDATA/pg_tblspc</TT > contains 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. Two warnings: do not do so while the postmaster is running; and after you restart the postmaster, update the <TT CLASS="STRUCTNAME" >pg_tablespace</TT > catalog to show the new locations. (If you do not, <TT CLASS="LITERAL" >pg_dump</TT > will continue to show the old tablespace locations.) </P ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="manage-ag-dropdb.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="client-authentication.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Destroying a Database</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="managing-databases.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Client Authentication</TD ></TR ></TABLE ></DIV ></BODY ></HTML >