CREATE_TABLESPACE(7pg18)

💖 CREATE_TABLESPACE(7) 💖

okay bestie, sit down, we're learning CREATE TABLESPACE — it's the PostgreSQL 18.6 Documentation version, so you know it's fresh. this is a 7-section manual page and we are reading all of it. no skimming. i will know. 👀

---

✨ NAME ✨

CREATE_TABLESPACE — define a new tablespace 🎀

---

🌸 SYNOPSIS 🌸

CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

look at her. iconic structure. square brackets mean optional, bestie, don't forget. 💅

---

🦋 DESCRIPTION 🦋

so CREATE TABLESPACE registers a new cluster-wide tablespace. and the name you pick? it must be distinct from the name of any existing tablespace in the database cluster. no duplicates, no copycats, we don't do that here. 🙅

a tablespace is basically letting superusers define an alternative location on the file system where the data files holding database objects (think tables, think indexes, think the whole crew) get to live. it's giving "i want my data over there actually." ✨

and if you're a user with appropriate privileges, you can pass tablespace_name to CREATE DATABASE, CREATE TABLE, CREATE INDEX or ADD CONSTRAINT and boom — the data files for those objects get stored inside the tablespace you picked. chef's kiss. 😌

⚠️ Warning ⚠️ a tablespace cannot be used independently of the cluster in which it is defined. like, it's not a standalone girlie. see Section 22.6. periodt.

---

💜 PARAMETERS 💜

• tablespace_name — the name of the tablespace you're creating. it cannot begin with pg_, because those names are reserved for system tablespaces. that's a hard no, bestie. 🚫
• user_name — the name of the user who will own the tablespace. if you omit it, it defaults to the user executing the command. and here's the tea: only superusers can create tablespaces, but they can totally hand ownership of tablespaces over to non-superusers. sharing is caring. 🫶
• directory — the directory that'll be used for the tablespace. okay so listen carefully:
  • the directory must exist — CREATE TABLESPACE will not create it for you, she is not your assistant 💀
  • it should be empty
  • it must be owned by the PostgreSQL system user
  • and it must be specified by an absolute path name
• tablespace_option — a tablespace parameter to be set or reset. currently the only available parameters are seq_page_cost, random_page_cost, effective_io_concurrency and maintenance_io_concurrency. setting these values for a particular tablespace overrides the planner's usual estimate of the cost of reading pages from tables in that tablespace, plus how many concurrent I/Os are issued — the usual behaviour comes from the configuration parameters of the same name (see seq_page_cost, random_page_cost, effective_io_concurrency, maintenance_io_concurrency). this is lowkey useful when one tablespace sits on a disk that's faster or slower than the rest of the I/O subsystem. main character disk energy. 💫

---

🎀 NOTES 🎀

CREATE TABLESPACE cannot be executed inside a transaction block. that's the rule. don't try it. 🙈

---

🌈 EXAMPLES 🌈

okay so you wanna create a tablespace called dbspace at file system location /data/dbs? first, make the directory using operating system facilities and set the correct ownership:

mkdir /data/dbs
chown postgres:postgres /data/dbs

then issue the tablespace creation command inside PostgreSQL:

CREATE TABLESPACE dbspace LOCATION '/data/dbs';

and if you want a tablespace owned by a different database user, use a command like this:

CREATE TABLESPACE indexspace OWNER genevieve LOCATION '/data/indexes';

slay. look at you go. 👑

---

⭐ COMPATIBILITY ⭐

CREATE TABLESPACE is a PostgreSQL extension. she's homegrown, she's original, she's ours. 💖

---

🧚 SEE ALSO 🧚

• CREATE DATABASE (CREATE_DATABASE(7))
• CREATE TABLE (CREATE_TABLE(7))
• CREATE INDEX (CREATE_INDEX(7))
• DROP TABLESPACE (DROP_TABLESPACE(7))
• ALTER TABLESPACE (ALTER_TABLESPACE(7))

---

and that's the whole page bestie!! 🎉 this was PostgreSQL 18.6, dated 2026, and yes the footer literally says CREATE TABLESPACE(7) again because the manpage said so and we respect the manpage. go forth and tablespace responsibly, i love you, bye!! 🌸💅✨🦋