clusterdb

Cluster a specific database

$ clusterdb [database_name]

$ clusterdb [[-a|--all]]

$ clusterdb [[-t|--table]] [table_name] [database_name]

clusterdb [OPTION]... [--dbname=]DBNAME [TABLE [TABLE...]]

-a, --all
    Cluster all tables in all databases

-d DBNAME, --dbname=DBNAME
    Database to cluster

-e, --echo-errors
    Echo failed SQL commands

-h HOSTNAME, --host=HOSTNAME
    Database server host or socket directory

-l, --list
    List available tables for clustering

-p PORT, --port=PORT
    Database server port number

-q, --quiet
    Suppress all output messages

-t TABLE, --table=TABLE
    Cluster only specific table(s)

-U NAME, --username=NAME
    Connect as specified user

-v, --verbose
    Enable verbose output

-V, --version
    Display version information

-W, --password
    Force password prompt

-?, --help
    Show help and exit

-H CONNINFO, --conninfo=CONNINFO
    PostgreSQL connection string

clusterdb is a command-line utility in PostgreSQL that reorganizes tables by clustering rows based on a specified index. This process physically sorts heap tuples in index order, improving query performance for index scans and reclaiming space from deleted or updated rows (dead tuples). Unlike VACUUM, which only marks space as reusable, CLUSTER compacts the table.

It wraps the SQL CLUSTER command, allowing clustering of one or more tables in a database, or all tables across all databases. clusterdb acquires an exclusive lock on each table, making it suitable for maintenance windows rather than live production use on large tables.

Specify a database with -d or --dbname, tables with -t, or use -a for all. It supports standard PostgreSQL connection options like host, port, and user. Output includes progress unless suppressed with -q. Ideal after bulk deletes or updates to optimize storage and speed.

Acquires exclusive table locks, blocking writes; avoid on large production tables without downtime. Requires index on table; skips tables without suitable indexes. Can be I/O intensive.

clusterdb mydb clusters all tables in mydb.
clusterdb -h localhost -U postgres -t users -t orders mydb clusters specific tables.
clusterdb -a clusters all tables in all databases.

Each table clusters on its primary index unless specified via SQL. Combine with ANALYZE for full optimization.

Introduced in PostgreSQL 7.3 (2002) alongside the CLUSTER SQL command (from 7.1). Evolved with libpq enhancements; current in PostgreSQL 16+ for optimized maintenance.

vacuumdb(1), reindexdb(1), psql(1), pg_dump(1)