PostgreSQL Creating Tablespaces

A tablespace is a location on disk where PostgreSQL stores data files containing database objects e.g., indexes., tables, etc. PostgreSQL uses a tablespace to map a logical name to physical location on disk.

PostgreSQL comes with two default tablespaces:

  • pg_default tablespace stores all user data.
  • pg_global tablespace stores all global data.

The tablespaces allows you to control the disk layout of PostgreSQL. There are two main advantages of using tablespaces:

  • First, if a partition on which the cluster was initialized is out of space, you can create a new tablespace on a different partition and use it until you reconfigure the system.
  • Second, you can use the statistics of database objects’ usages to optimize the performance of databases. For example, the you can place the frequent access indexes or tables on devices that perform very fast e.g., solid state devices, and put the tables containing archive data which is rarely used on a slower devices.


Naming DB and Owner

Two tablespaces are automatically created by initdb. The pg_global tablespace is used for shared system catalogs. The pg_default tablespace is the default tablespace of the template1 and template0 databases (and, therefore, will be the default tablespace for other databases as well, unless overridden by a TABLESPACE clause in CREATE DATABASE).

Deifine Definitions

-- Database: pgsdemo1

-- DROP DATABASE pgsdemo1;

WITH OWNER = manas237
TABLESPACE = pg_default
LC_COLLATE = 'English_United States.1252'
LC_CTYPE = 'English_United States.1252'
GRANT ALL ON DATABASE pgsdemo1 TO public;
GRANT ALL ON DATABASE pgsdemo1 TO manas237;