Storage inspection

afpsql inspect storage --dsn env:DATABASE_URL returns one finite AFDATA result with code: storage_report and report_schema: afpsql-storage-v1. The same command is available through afpsql-readonly, including locked profiles. It uses the ordinary direct, SSH, or container SQL transport. Every metadata query runs read-only; it reads no application rows or vector values, and never runs maintenance, deletion, filesystem discard, or reclamation commands.

Read the reconciliation first

database_before and database_after use pg_database_size for the connected database across tablespaces. This excludes cluster WAL and shared cluster catalogs. relations sums each non-shared physical relation’s main/FSM/VM/init forks exactly once, including system catalogs, sequences, TOAST, and indexes. Other backends’ temporary relations are excluded from this catalog subtotal; visible temporary files may still occur in database directory observations.

When database-directory inspection completes, reconciliation compares the end database total with file groups. Otherwise it compares with the complete catalog-relation subtotal. Partial file observations remain available but are not added to the relation subtotal, which would count relation files twice. unattributed_logical_size_bytes and attribution_excess_bytes are nonnegative; the latter preserves observations larger than the database total. An unavailable basis produces null, never a fabricated zero. A residual provides a next step, not an automatic assertion that extension storage caused the whole difference.

Each query section has status (complete, partial, or unknown), source, start/end epoch milliseconds, rows, reason, and SQLSTATE where applicable. Top-level partial still exits successfully and preserves the report. An initial connection/database-size failure returns a terminal error with exit 1; invalid arguments use exit 2, and output failures use exit 4.

Collection is sequential and live. Catalog transactions do not freeze file lengths. Endpoint totals help show changes but cannot bound changes between observations; the measurement error bound is unknown. Even zero residual does not establish a frozen or perfectly synchronized filesystem snapshot.

File and extension coverage

Function EXECUTE privileges are checked before file/WAL queries. Runtime path restrictions, revocations, timeouts, and missing functions still produce a section reason without discarding other sections. No extra privileges are requested or granted. pg_stat_file(..., true) preserves disappeared files as missing counts. A missing tablespace database directory means that database has no observed directory there, rather than an SQL permission error.

Inspection covers base/<database_oid> and database directories beneath each catalogued custom tablespace’s server-version directory in pg_tblspc. Each observed directory entry is grouped by PostgreSQL relation filename convention, PGroonga pgrn* prefix when installed, or other database files. Directory entries are measured but not recursively traversed, matching the database-size function’s immediate-entry scope. Filename groups describe file classes, not proven ownership by individual catalog objects. Other extension formats remain unrecognized.

PGroonga uses database-wide files outside ordinary relation size accounting. pgroonga_indexes lists these indexes independently of the top-20 relation-size ranking. Their extension_logical_size_bytes is null: file totals are not divided among indexes. inspect indexes also reports size_coverage and a storage note. Zero relation bytes do not mean zero extension storage. Usage counters alone do not justify deleting indexes.

largest_tables and largest_indexes show up to 20 objects. Table total bytes include indexes and TOAST; TOAST’s total is a component of table bytes, not an additional amount. Relation-size fields exclude separately stored extension files. The full relation subtotal is independent of these display limits.

Vector summary

vector_tables detects extension-owned pgvector vector, halfvec, and sparsevec columns and reports declared type and dimensions, estimated rows, table/TOAST/index sizes, and every attached index’s method and definition. It also lists direct foreign-key neighbors in either direction and the constraint metadata establishing those links. Table names do not establish association. For application-only links without foreign keys, declare the table explicitly:

afpsql inspect storage --dsn env:DATABASE_URL \
  --vector-related-table public.embedding_link

Repeat --vector-related-table for multiple related tables. Each row’s inclusion_sources identifies vector_column, declared_foreign_key, and/or caller_declared; caller declarations do not assert a database constraint. Names use PostgreSQL identifier resolution, including schema qualification and quoted identifiers. Physical tables and materialized views are accepted; select partition leaves individually. Foreign-server storage is outside this report. Names are bound as parameters, not interpolated into SQL. Duplicate declarations and different names resolving to the same OID count once, including tables already found through vector columns or foreign keys. Explicit selections do not recursively expand their neighbors.

vector_related_table_requests records resolution status and reasons for each requested name. A missing, inaccessible, invalid, or unsupported object preserves other metadata, makes the overall report partial, and leaves vector_related_tables_total_bytes null. The same applies if a requested table disappears before its size can be collected. Partial table sizes remain visible. vector_related_tables_coverage states the discovery boundary, even when every query completed. Application-only links are never discovered automatically. Domains over vector types are not detected; declared unconstrained dimensions remain null. Partitioned parents may have zero local storage; physical leaves carry the bytes.

vector_related_tables_total_bytes sums distinct reported table totals once, including their indexes. It measures whole related tables, not vector-column payload alone. estimated_row_count comes from pg_class.reltuples (including PostgreSQL’s -1 unknown estimate); inspection never counts application rows.

Logical lengths, allocation, and WAL

All size fields use integer bytes. Divide by 1,073,741,824 for GiB or 1,000,000,000 for GB. SQL file metadata reports logical lengths. allocated_size_bytes, host_image_allocated_size_bytes, and reclaimable_size_bytes remain null: SQL cannot determine container filesystem allocated blocks, host sparse-image allocation, or guaranteed reclaimable space. A container’s FITRIM capability is a different boundary from SQL privileges.

wal uses pg_ls_waldir when permitted and measures cluster-wide WAL segment file logical lengths. It is never added to the connected database total and does not claim per-database WAL attribution or cover all cluster disk usage.

Regression evidence

The compact recorded observation used in unit tests is PostgreSQL 18 with PGroonga 4.0.8 and vector 0.8.6:

Six PGroonga indexes have zero relation bytes but unknown individual extension bytes. Recorded vector table totals are 42,794,876,928 and 4,344,307,712 bytes; their combined 47,139,184,640 bytes already include indexes. The HNSW component is 5,424,971,776 bytes and is not added again. The observed 52,988,776,448 allocated PGroonga bytes are external filesystem evidence, not SQL output. These numbers are fixtures, not a current database measurement.

Run the fixed entrypoints scripts/test.sh unit storage_ and scripts/test.sh integration storage_. Integration tests cover real catalog queries, ordinary-role permission degradation, and the readonly CLI. The native fixture also verifies a custom tablespace. Recorded-data tests cover attribution, vector arithmetic, permission revocation after preflight, and disappearing files; they do not substitute for live PGroonga/pgvector extension compatibility tests. To inspect an explicitly selected existing database, use the separate scripts/test.sh storage-live mode. Set AFPSQL_STORAGE_LIVE_ARGS_JSON to a JSON array of connection arguments, AFPSQL_STORAGE_LIVE_DATABASE to the expected database name, and AFPSQL_STORAGE_LIVE_REPORT_PATH to a new local report file. Use file credential sources instead of embedding secrets in that argument list. The test first verifies database identity and transaction_read_only = on, then executes inspect storage through the freshly built readonly binary, saves the report, validates the output and file-group arithmetic, and prints a compact summary. It creates no database fixtures and grants no privileges. Connection environment defaults are removed so an unrelated DSN cannot override the chosen target. Ordinary test modes never opt into this existing-database test. AFPSQL_STORAGE_LIVE_VECTOR_RELATED_TABLES_JSON optionally supplies the related table names as a JSON array. The test checks their inclusion sources and ensures unique table OIDs and nonduplicated totals. AFPSQL_STORAGE_LIVE_EXPECTED_VECTOR_TOTAL_BYTES can assert a known regression total when the selected database is stable.

A successful complete collection establishes the report’s measurement coverage, not that application-only relationships were discovered. A vector table with no declared foreign keys requires --vector-related-table to include a separate application link table in the vector-related total. Compare that coverage with the caller’s known schema before claiming the total covers the whole workflow.

No full-size database copy is needed. Report persistence/comparison is not yet implemented; callers can retain the finite JSON report themselves.

Sources for the storage model: PostgreSQL size and file functions, PostgreSQL file layout, PGroonga database file layout, pgvector type definitions.