Performance¶

This page reports how pistachio scales as the number of tables grows. The numbers come from measuring the plan, dump and fmt commands against synthetic schemas of increasing size.

What is measured¶

Each run parses the desired schema from SQL, reads the current schema from the PostgreSQL system catalogs, and computes the DDL diff. All three costs grow with the number of objects, so the table count is the main driver of runtime.

Five cases are measured:

create plan: plan of the full schema against an empty database. Every table is a create, so this covers parsing and diff generation with no catalog to read.
noop plan: plan of the full schema against a database that already matches it. This is the common case in CI. It runs the full path (parse, read the catalog, diff) and prints -- No changes.
modify plan: plan of a changed schema against a matching database. Each table gets a new column, a column type change, and a new index, so the diff is three ALTER/CREATE statements per table. This measures diff generation and SQL output.
dump: dump of the full schema. This reads the catalog and serializes it back to SQL, with no parsing or diffing. The output goes through the formatter, the same one fmt runs.
fmt: fmt of the schema file, rewritten in place. This parses the file and lays it out again, and reads no database.

Environment¶

Linux x86_64, Intel Xeon @ 2.80GHz (4 vCPUs), 15 GB RAM
PostgreSQL 16.15 (connected over localhost)
pistachio built from main after v1.84.0

The database runs on the same host, so client/server latency is negligible. Times over a network connection will be higher because reading the catalog adds round trips.

Schema shape¶

Each table has 9 columns of mixed types (bigint identity primary key, text, varchar, numeric, integer, boolean, and two timestamps) and two secondary indexes. Every table but the first also has a parent_id column with a foreign key to the previous table. A schema of N tables therefore has N tables, N primary keys, 2N secondary indexes, and N-1 foreign keys.

Results¶

Each value is the median of eleven runs, in seconds.

Tables	create plan	noop plan	modify plan	dump	fmt
10	0.087	0.116	0.122	0.105	0.034
50	0.144	0.170	0.191	0.164	0.058
100	0.183	0.241	0.242	0.212	0.080
250	0.305	0.495	0.522	0.379	0.147
500	0.592	0.893	0.918	0.720	0.247
1,000	1.077	1.547	1.607	1.290	0.462

The modify plan emits three DDL statements per table, so its output grows from 30 lines at 10 tables to 3,000 lines at 1,000 tables. The create plan output grows the same way, from 160 to 16,000 lines.

Repeated runs of the same case vary by up to about 10% on this machine, so a difference smaller than that between two cells is not significant.

Analysis¶

Runtime grows almost linearly with the number of tables. Going from 100 to 1,000 tables takes about 6 times as long.

Every command that reads the database spends about 85 ms on starting the process and connecting to PostgreSQL. This is why the smallest schemas are not much faster than medium ones. fmt does not connect, and its fixed cost is about 35 ms.

At 1,000 tables, the create plan (parse and diff, no catalog read) takes 1.08s and dump (catalog read, no parse) takes 1.29s. The noop plan does both and takes 1.55s. That is less than the sum, because the noop plan writes no DDL and does not format its output.

The modify plan is only 60 ms slower than the noop plan at 1,000 tables. Reading the catalog and parsing the schema take most of the time, and writing 3,000 DDL statements adds little.

Every case takes less than 1.7s at 1,000 tables. At 2,000 tables, the create, noop and modify plans take 1.9s, 3.2s and 3.6s. Larger schemas were not measured.

fmt is the fastest command. It parses the file and writes it out again, and does not connect to the database. At 1,000 tables it takes 0.46s. For files of 100 to 2,000 tables it takes 0.08s to 0.96s.

Formatting takes 0.55s of the 1.29s that dump takes at 1,000 tables. dump --no-format skips it and takes 0.74s.

Reproducing¶

Generate a schema of N tables and time the commands against a local database:

# Generate N tables, each with columns, two indexes, and a foreign key.
gen() {
  for i in $(seq 1 "$1"); do
    printf 'CREATE TABLE t_%d (\n' "$i"
    printf '  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,\n'
    printf '  name text NOT NULL,\n  code varchar(64) NOT NULL,\n'
    printf '  amount numeric(12,2) NOT NULL DEFAULT 0,\n  qty integer NOT NULL DEFAULT 0,\n'
    printf '  is_active boolean NOT NULL DEFAULT true,\n  note text,\n'
    printf '  created_at timestamptz NOT NULL DEFAULT now(),\n'
    printf '  updated_at timestamptz NOT NULL DEFAULT now()'
    [ "$i" -gt 1 ] && printf ',\n  parent_id bigint REFERENCES t_%d (id)' "$((i-1))"
    printf '\n);\n'
    printf 'CREATE INDEX idx_t_%d_code ON t_%d (code);\n' "$i" "$i"
    printf 'CREATE INDEX idx_t_%d_created_at ON t_%d (created_at);\n' "$i" "$i"
  done
}

gen 1000 > schema.sql
psql -c 'DROP SCHEMA public CASCADE; CREATE SCHEMA public;'

time pista plan schema.sql   # create plan (empty database)
psql -f schema.sql
time pista dump              # dump (loaded database)
time pista plan schema.sql   # noop plan (matching database)

# modify plan: change every table (type change, new column, new index),
# then plan against the loaded database.
sed -e 's/qty integer/qty bigint/' \
    -e 's/note text,/note text,\n  extra text,/' \
    -e 's/\(CREATE INDEX idx_t_\([0-9]*\)_created_at ON t_\2 (created_at);\)/\1\nCREATE INDEX idx_t_\2_name ON t_\2 (name);/' \
    schema.sql > modified.sql
time pista plan modified.sql # modify plan (diff on every table)

# fmt: lay the schema file out again. Copy it first, since fmt rewrites the
# file it is given and a second run on the formatted file does no work.
cp schema.sql copy.sql
time pista fmt copy.sql      # fmt (no database)

The foreign key has to sit on the column. pistachio does not read a standalone ALTER TABLE ... ADD COLUMN ... REFERENCES. Written that way the foreign keys never reach the desired schema, and the noop plan proposes dropping them.

Dropping 1,000 tables in one statement needs more locks than the default allows, so raise max_locks_per_transaction before resetting between runs. 4096 is enough.