The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Create a tablespaceCREATE TABLESPACE fastspace LOCATION '/srv/postgresql/fastspace';View examples
List tablespacesSELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace;View examples
Grant create privilegeGRANT CREATE ON TABLESPACE fastspace TO app_owner;View examples
Place a new tableCREATE TABLE app.events (id bigint, payload jsonb) TABLESPACE fastspace;View examples
Place a new indexCREATE INDEX events_id_idx ON app.events (id) TABLESPACE fastspace;View examples
Move a tableALTER TABLE app.events SET TABLESPACE fastspace;View examples
Set heap fillfactorALTER TABLE app.sessions SET (fillfactor = 80);View examples
Reset heap fillfactorALTER TABLE app.sessions RESET (fillfactor);View examples
Set index fillfactorALTER INDEX app.events_tenant_time_idx SET (fillfactor = 80);View examples
Repack an index concurrentlyREINDEX INDEX CONCURRENTLY app.events_tenant_time_idx;View examples
Prefer external storageALTER TABLE app.events ALTER COLUMN payload SET STORAGE EXTERNAL;View examples
Set TOAST autovacuum scaleALTER TABLE app.events SET (toast.autovacuum_vacuum_scale_factor = 0.05);View examples
Set a vacuum scale factorALTER TABLE app.job_queue SET (autovacuum_vacuum_scale_factor = 0.02);View examples
Reset table overridesALTER TABLE app.job_queue RESET (autovacuum_vacuum_scale_factor);View examples
Show a relation tablespaceSELECT reltablespace FROM pg_class WHERE oid = 'app.events'::regclass;View examples
Measure a tablespaceSELECT pg_size_pretty(pg_tablespace_size('fastspace'));View examples
Drop an empty tablespaceDROP TABLESPACE fastspace;View examples

Tablespaces map database objects to administrator-managed filesystem locations, while storage parameters tune individual heap, index, TOAST, and autovacuum behavior. These controls affect physical layout and maintenance—not logical backup boundaries—and poor choices can trade one bottleneck for another.

Step by step

Detailed examples

01

Provision tablespaces as cluster infrastructure

Only a superuser can create a tablespace. The absolute directory must already exist, be empty, and be owned by the PostgreSQL operating-system account; CREATE TABLESPACE cannot run inside a transaction block. The path is a symlink-backed part of the same cluster, not portable independent storage.

Register a prepared tablespace directory
CREATE TABLESPACE fastspace
OWNER app_owner
LOCATION '/srv/postgresql/fastspace'
WITH (random_page_cost = 1.1, effective_io_concurrency = 200);

Note: Provision and secure the directory at the OS layer first; run outside a transaction in an approved maintenance window.

Back to quick reference ↑
02

Place heaps and indexes according to measured I/O needs

TABLESPACE clauses choose initial placement; defaults come from default_tablespace and temp_tablespaces. Moving an existing relation rewrites or copies physical data and holds strong locks, so budget capacity on source, destination, replicas, and backups. A partitioned parent has no storage; place each leaf.

Separate a large index from its heap
CREATE INDEX CONCURRENTLY orders_created_idx
ON app.orders (created_at)
TABLESPACE fastspace;

Note: CONCURRENTLY reduces write blocking but takes longer, performs extra work, and cannot run inside a transaction block.

Back to quick reference ↑
03

Reserve heap page space for update-heavy rows

Heap fillfactor ranges from 10 to 100 and affects how future INSERT and rewrite operations pack pages. Lower values leave room for same-page updates, improving the chance of HOT updates when indexed columns are unchanged, at the cost of a larger table and more scan I/O. Existing pages are not repacked merely by changing the setting.

Reserve page space, then schedule a rewrite if justified
ALTER TABLE app.sessions SET (fillfactor = 80);
-- Apply to existing pages later with VACUUM FULL, CLUSTER, or a rewrite tool.

Note: VACUUM FULL and CLUSTER require strong locks; do not run them casually in production.

Back to quick reference ↑
04

Tune index fillfactor for write patterns

B-tree fillfactor controls initial leaf-page packing: the default is 90, while lower values can reduce early page splits on insert-heavy indexes. Overly low values waste cache and storage. Index storage parameters differ by access method, so verify support and rebuild if existing pages must be repacked.

Build a write-heavy B-tree with headroom
CREATE INDEX CONCURRENTLY events_tenant_time_idx
ON app.events (tenant_id, occurred_at)
WITH (fillfactor = 80);
Back to quick reference ↑
05

Control large-value storage with TOAST-aware settings

TOAST stores compressible or oversized values out of line when needed. Column STORAGE and COMPRESSION preferences affect future writes, not existing values, and MAIN only prefers inline storage rather than guaranteeing it. Disabling autovacuum on a table does not automatically protect its TOAST table from bloat.

Prefer LZ4 for future payload writes when available
ALTER TABLE app.events ALTER COLUMN payload SET COMPRESSION lz4;

Note: The cluster must support the method; unchanged existing values keep their prior compression until rewritten.

Back to quick reference ↑
06

Override autovacuum only for exceptional tables

Per-table thresholds can make high-churn or append-heavy relations maintain statistics and dead tuples sooner. Overrides multiply operational complexity and should be based on observed modification rates and table size. Setting autovacuum_enabled=false does not prevent wraparound vacuum and creates serious bloat and statistics risks.

Tune one high-churn queue table
ALTER TABLE app.job_queue SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01
);
Back to quick reference ↑
07

Inventory placement before changing or dropping storage

Use supported size functions and catalogs to find relations in each tablespace. DROP TABLESPACE requires it to be empty across every database in the cluster, and the directory should be removed only after PostgreSQL unregisters it. Logical dumps preserve tablespace declarations unless options suppress them, but do not copy tablespace files.

Inventory relation placement and size
SELECT n.nspname, c.relname, t.spcname, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind IN ('r', 'm', 'i')
ORDER BY pg_total_relation_size(c.oid) DESC;
Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupTablespacespostgresql.org
  2. PostgreSQL Global Development GroupCREATE TABLESPACEpostgresql.org
  3. PostgreSQL Global Development GroupALTER TABLESPACEpostgresql.org
  4. PostgreSQL Global Development GroupDROP TABLESPACEpostgresql.org
  5. PostgreSQL Global Development GroupCREATE TABLE and Storage Parameterspostgresql.org
  6. PostgreSQL Global Development GroupALTER TABLEpostgresql.org
  7. PostgreSQL Global Development GroupCREATE INDEXpostgresql.org
  8. PostgreSQL Global Development GroupTOASTpostgresql.org
  9. PostgreSQL Global Development GroupRoutine Vacuumingpostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback