CREATE_TABLESPACE(7pg14)

💅 CREATE_TABLESPACE — define a new tablespace (PostgreSQL 14.24, besties) 💅

Okay so picture this: PostgreSQL 14.24 docs, and we're about to carve out a whole new tablespace. No cap, this is the glow-up your data files deserve. ✨

---

🌸 SYNOPSIS 🌸

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

Like, that's the whole recipe right there. Chef's kiss. 👑

---

🦋 DESCRIPTION 🦋

So CREATE TABLESPACE literally registers a brand-new cluster-wide tablespace. The name has to be totally distinct from any existing tablespace in the database cluster — no duplicates, periodt. 🚫

What even is a tablespace? Bestie, it lets superusers define an alternative location on the file system where the data files containing database objects (think tables and indexes) can live. It's giving real estate energy. 🏡

A user with appropriate privileges can pass tablespace_name to CREATE DATABASE, CREATE TABLE, CREATE INDEX, or ADD CONSTRAINT to have the data files for those objects stored inside the specified tablespace. Iconic. 💫

⚠️ Warning, bestie, listen up: a tablespace cannot be used independently of the cluster in which it is defined. See Section 23.6. Like, it's not a lone wolf, okay? It needs its cluster. 😭

---

💖 PARAMETERS 💖

• tablespace_name — the name of the tablespace you're creating. The name cannot begin with pg_, because those are reserved for system tablespaces. That's system territory, don't touch. 🙅‍♀️
• user_name — the name of the user who will own the tablespace. If you omit it, it defaults to the user executing the command. Only superusers can create tablespaces, but they can assign ownership to non-superusers. Sharing is caring, slay. 🤝
• directory — the directory that will be used for the tablespace. The directory must exist (CREATE TABLESPACE will not create it for you — do that yourself, babe), should be empty, and must be owned by the PostgreSQL system user. It must be specified by an absolute path name. No relative paths, no vibes-only paths. 📁
• tablespace_option — a tablespace parameter to set or reset. Currently the only available parameters are seq_page_cost, random_page_cost, effective_io_concurrency, and maintenance_io_concurrency. Setting these for a particular tablespace overrides the planner's usual cost estimate for reading pages from tables in that tablespace, plus the executor's prefetching behavior — as established by the configuration parameters of the same name (see seq_page_cost, random_page_cost, effective_io_concurrency, maintenance_io_concurrency). Super useful if one tablespace sits on a disk that's faster or slower than the rest of the I/O subsystem. The way that disk-speed discrimination is real. 🐢💨

---

🎀 NOTES 🎀

• Tablespaces are only supported on systems that support symbolic links. If your system doesn't do symlinks, this whole thing is a no-go. 💀
• CREATE TABLESPACE cannot be executed inside a transaction block. Not negotiable. Don't try it. 🙈

---

🌈 EXAMPLES 🌈

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 the tablespace owned by a different database user, use a command like this:

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

Genevieve really said my tablespace, my rules. 💅

---

⭐ COMPATIBILITY ⭐

CREATE TABLESPACE is a PostgreSQL extension. That's it. That's the note. 🫶

---

🔥 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))

Go read them, they're all linked up like a cute little friend group. 📚✨

---

This whole beautiful manual page is from PostgreSQL 14.24, dated 2026, and it's literally CREATE TABLESPACE(7). Slay, you made it to the end. 🎉👑💖