| PG_LICHT_MCP(1) | General Commands Manual | PG_LICHT_MCP(1) |
pg_licht_mcp —
read-only PostgreSQL catalog exploration server for the
Model Context Protocol
pg_licht_mcp |
[-c file.ini]
[database_url] |
pg_licht_mcp |
-h | -V |
pg_licht_mcp is a Model Context Protocol
server that exposes the PostgreSQL system catalog to an MCP client over
JSON-RPC 2.0 on standard input and standard output. It is meant to be
launched by the client, not run interactively.
pg_licht_mcp queries the catalog only:
schemas, tables, columns, functions, types, roles, and the statistics views.
It does not read user table data, with two narrow exceptions that return
counts and never rows: checkKey, which tests a
primary key for existence, and rowScatter, which
counts the pages the rows of a value sit on. Every call to a database runs
inside its own ‘READ ONLY’
transaction, and the pooler tools send PgBouncer's admin console nothing but
‘SHOW’ commands; see
SECURITY
CONSIDERATIONS.
The options are as follows:
-c
file.ini, --config
file.ini-h,
--help-V,
--versionAny other argument beginning with
‘-’ is refused with exit status 2. No
connection string begins with one, and through 4.3.3 an unknown option was
taken for the connection string instead.
A single positional database_url argument is
used as the connection string when neither a configuration file nor
DATABASE_URL is present.
When a configuration file defines more than one connection, every
operation accepts an optional connection argument
naming one of its sections; omitting it uses the default section.
listConnections reports what is configured. An
operation may instead take instance,
replication_group or group and
run once per member; see
Topology:
instances, replication groups, and groups.
A connection string is handed to libpq unchanged, so both accepted forms work:
postgresql://user:pass@host:5432/dbname?sslmode=require host=localhost port=5432 dbname=mydb user=daniel
A single database needs nothing beyond
DATABASE_URL.
To reach several databases from one server, list them as INI sections:
[default] port = 6432 dbname = pglicht user = daniel sslmode = prefer ; no password here — use ~/.pgpass or a service file [prod] ; a libpq service name, resolved from ~/.pg_service.conf, ; $PGSERVICEFILE, or $PGSYSCONFDIR/pg_service.conf service = db01_ro [staging] service = db01_ro ; the service supplies the defaults dbname = db01_staging ; and explicit keys override them
Pass a section name as the connection argument of any operation to run it against that database; omit it to use [default], or, if there is no [default], the first section in the file that is not a PgBouncer console, since a call naming no connection is a database question.
A section with ‘kind =
pgbouncer’ is a PgBouncer admin console rather than a
database, and only the operations in
Connection poolers run against
it; every other operation refuses it, as those refuse a database. Its
dbname is always pgbouncer and
may be left out, and its user should be one listed in
PgBouncer's stats_users, which may run
‘SHOW’ and nothing else.
pg_licht_mcp cannot tell which list a user is in
without trying a command only an admin may run, so that choice is the
operator's. Its statement_timeout_ms bounds each
‘SHOW’ from this side, since the
console has no statement_timeout to set. It may carry
group labels, so a group sweep can ask every pooler at
once, but not instance or
replication_group, which describe PostgreSQL servers;
and it is never inferred into an instance, though a database reached through
the same pooler shares its host and port.
[pooler_prod] kind = pgbouncer host = pooler01 port = 6432 user = pglicht_stats group = poolers
A database section reached through such a
console says so with ‘pooler =
section’. Nothing else in the file can:
through PgBouncer,
inet_server_addr()
is the pooler's own connection to PostgreSQL, so the pooled database cannot
be told from its address. The key is declared, never inferred, and checked:
verifyTopology reads the console's
‘SHOW DATABASES’ and reports the route
beside the connection. It must name a section with
‘kind = pgbouncer’ that loaded, or the
section declaring it is skipped.
[orders_pooled] host = pooler01 port = 6432 dbname = orders user = pglicht_ro instance = pg-orders-01 pooler = pooler_prod
It buys three things. verifyTopology
checks the route: a name the pooler routes to another database or port is an
error, an alias is noted, and the direct connection that is the same
database — one postmaster, one database, one user — is named.
The pooler operations accept the database's own name as
connection and answer from its console, about its
pool, with pooler naming the console in the answer.
And a sweep of a per-database operation answers that database once: the
pooled member is skipped when a direct member is declared the same
instance, connects as the same user, and the console
routes the pooled name to the direct member's database without forcing
another user. The skip names the twin. Anything less — an alias to
another database, another user, a console that cannot be read — keeps
both, since their answers may differ.
Three optional keys per section say how the configured databases relate. They are deliberately separate, because only the first two license a conclusion:
[billing_prod] service = billing-prod instance = pg-prod-01 ; one postmaster replication_group = billing-ha ; a primary and its replicas group = prod, billing ; an operator label [billing_ro] service = billing-replica instance = pg-prod-02 replication_group = billing-ha group = prod, billing, reporting
There is no cluster
key. PostgreSQL uses that word for the first sense and RDS and Aurora for
the second, so it means opposite things to the two people most likely to
read the file. There is no role key either: primary or
replica is observed on every connection through
pg_is_in_recovery()
and never cached, because failover swaps it and failover is exactly when
this server gets used.
An instance is inferred when two sections
share a host and port, and reported as
‘inferred’ rather than
‘declared’. Behind a pooler one
endpoint can front several instances and one instance can be reached through
several pooler ports, so an inferred instance groups output without
licensing a contention claim; verifyTopology settles
it. A section using service is never inferred, since
the service file is deliberately not expanded and its host and port are
therefore unknown.
Passing instance, replication_group or group in place of connection runs the operation once per member and returns one result per member, in file order. A member that cannot be reached is reported in place rather than failing the sweep.
Which of the three an operation accepts depends on where its
answer actually varies, and its input schema says which. Catalog and bloat
readings vary between the databases of one instance but not between members
of a replication group, because a physical replica is byte-identical.
currentActivity,
currentLocks, statementStats
and the buffer cache operations are the other way round: they report the
whole instance from any one of its databases, so sweeping them across it
would repeat one answer, and that is refused with
-32602.
duplicateIndexes,
indexBloat and tableIOStats
are the case worth knowing: they carry idx_scan, which
is each server's own. An index that reads as unused on the primary may be
carrying a replica's entire reporting workload, and only that replica's scan
counts show it. role set to
‘primary’ or
‘replica’ narrows a sweep to one side.
connection, instance,
replication_group, group and
role are read by pg_licht_mcp
itself out of a call's arguments. An operation that declares an argument of
its own by one of those names keeps it —
roleDependencies takes a role,
naming the role to ask about — and cannot be targeted by that
selector. The tool's argument wins, in the published schema and in the call
alike.
A sweep is sequential — the server is a single-threaded
loop — so its width is wall-clock time.
listTopology reads the configuration file and opens
no connection, so it is cheap to call first.
A worked example covering all of this is shipped as cpp/test/connections.example.ini in the source tree.
Keys are passed through to libpq, so anything libpq accepts works,
including service, which may be used on its own in
place of host, port,
dbname, and user. A service name
is resolved from ~/.pg_service.conf,
PGSERVICEFILE, or
$PGSYSCONFDIR/pg_service.conf.
Each section must set either service or at least dbname; a section with neither is reported by name at startup rather than failing later with an obscure libpq error.
pg_licht_mcp adds one key of its own
unless the section sets it: application_name, as
‘pg-licht-cpp/version’.
A section that sets application_name replaces that
name, version included. Where several people or assistants connect as one
database role, a different application_name in each
one's connections file tells them apart in
pg_stat_activity, in the server log
(‘%a’) and in
currentActivity. It is a name the client chooses and
not authentication: nothing keeps two files from carrying the same one. The
key has to be in the section itself; a value set in a service file is
overridden by the default.
Every other key reaches libpq as written, among them sslmode, connect_timeout, target_session_attrs and passfile. None of them lifts a guard, because the read-only transaction and the statement ceiling are set per transaction, after the connection is made. Without connect_timeout, a server that does not answer is waited for as long as the operating system waits, which can be minutes; the statement ceiling does not cover it.
The key options is rejected. PgBouncer
refuses GUCs in the startup packet, so pg_licht_mcp
sets what it needs per transaction instead — the read-only guard
always, and statement_timeout from
statement_timeout_ms below; see
SECURITY
CONSIDERATIONS.
Every transaction sets statement_timeout, so no single call can run without an upper bound. The default is 120000 milliseconds. Set it per section:
[prod] service = billing-prod statement_timeout_ms = 300000 [wan] service = analytics-eu statement_timeout_ms = 15000
A value of 0 removes the ceiling and leaves the server's own
statement_timeout untouched, which is the behaviour of
3.2.0 and earlier. With a single DATABASE_URL, use
PG_LICHT_STATEMENT_TIMEOUT_MS instead.
Most operations are catalog reads that finish in milliseconds and
never approach it. The ones that can are those whose cost scales with the
server rather than with the query: tableBloat with
exact true, indexBloat on a
btree or hash index, and bufferCacheContents, which
scans every buffer in shared_buffers. Raise it for the
connection when such a scan is the point of the call.
A statement that reaches the ceiling is reported as the ceiling being reached, with the value and the key to change, rather than as an execution error: nothing went wrong, the answer simply needs longer than the connection allows.
Like the read-only guard, the ceiling is transaction-scoped rather than session-scoped, which is what makes it survive a transaction-mode pooler. It cannot be carried in the conninfo: statement_timeout is a GUC rather than a libpq keyword, and the options keyword that could carry it is the one PgBouncer rejects.
Total RAM and vCPU count belong to the machine, not to the
cluster. PostgreSQL has no catalog for them, and a backend could not read
them portably in any case — it would read the client's host, which is
the wrong machine. They are therefore injected, and
hostCapacity correlates them with
shared_buffers, work_mem, and
the rest. Five optional keys per section carry them:
[prod] service = db01_ro host_ram_mb = 65536 host_vcpus = 16 host_reserved_mb = 8192 ; e.g. a ZFS ARC capped at 8 GB host_storage = local nvme ; free text, never interpreted host_note = shared with the app ; free text, never interpreted
host_reserved_mb is memory the operating
system holds outside the page cache and does not give back on request: a ZFS
ARC of a fixed size, huge pages reserved for something else, another service
on the machine. PostgreSQL cannot see it, and
hostCapacity adds it to the committed worst case, as
its own line. Where it is not declared the answer says so rather than
counting zero: on a 32 GB server with an 8 GB ARC, a worst case reported as
82% of RAM was in fact more than all of it. The page cache itself must not
be put here; it shrinks under pressure, and
effective_cache_size already describes it.
These keys are consumed by pg_licht_mcp
and never reach libpq, which would reject the whole connection string over
an unrecognised keyword. host_ram_mb and
host_vcpus must be positive integers; anything else is
rejected at startup naming the section and the key, because
‘64GB’ where megabytes are meant would
be wrong by three orders of magnitude and would silently invalidate every
derived ratio.
There are two other ways in. The environment variables under
ENVIRONMENT apply to the
single-connection DATABASE_URL form only, where
there is no question which host they describe; with a connections file each
section declares its own, since the sections may well live on different
machines. And an agent that inspects the host at run time — over SSH,
say — can pass ram_mb and
vcpus straight to
hostCapacity, which takes precedence over both.
With several databases on one postmaster, a reserved [instance:name] section carries the same keys once and its members inherit them; an explicit per-connection value still wins:
[instance:pg-prod-01] host_ram_mb = 65536 host_vcpus = 16 [app] service = app_ro instance = pg-prod-01
An [instance:name] section is not a connection:
it never appears in listConnections and never
becomes the default. It accepts the four host capacity keys and nothing
else, and is rejected at startup if no section declares that instance.
Inheritance follows instance only, never
replication_group: replicas routinely run on smaller
machines, and inheriting the primary's RAM would give every replica a
confidently wrong shared_buffers ratio.
Limits pg_licht_mcp applies to itself live
in an optional budgets.ini. There are two: what
explainQuery may execute when
analyze is combined with explicit
settings, as shares of the host capacity declared for
the connection; and how large one answer may be.
[analyze] memory_percent = 10 ; worst-case plan memory, % of host_ram_mb vcpus_per_worker = 4 ; one parallel worker per this many host_vcpus forbidden_functions = ; schema.name, ... never analyzed allowed_functions = ; schema.name, ... analyzed although volatile [payload] max_kb = 0 ; largest answer returned whole; 0, the default, is off
Those are the built-in values, used when no file is found.
forbidden_functions and
allowed_functions correct the gate that keeps
explainQuery from analyzing a statement whose plan
calls a volatile function. That gate reads what a function declares and not
what it does, and only the operator knows the functions of their own
database. A function named under forbidden_functions
is never analyzed, even though it is declared STABLE
or IMMUTABLE; the answer names it under
forbidden_functions. A function named under
allowed_functions is analyzed although it is
VOLATILE, which is what a function is when nobody
declared otherwise. Each entry is
schema.name,
as the catalog holds them, without quotes or parameters: the plan the gate
reads does not say which signature was meant, so every function of that name
is. A name in both lists is forbidden. The lists apply to
analyze only: a function PostgreSQL evaluates while it
builds the plan has run before any list can be consulted.
memory_percent takes 1 to 100,
vcpus_per_worker 1 to 1024, and
max_kb 0 to 1048576.
With max_kb set, an answer larger than that
many kilobytes, as the compact JSON a client receives, is refused with an
error whose hint names the arguments that operation declares to make its
answer smaller, and for a sweep, that fewer members would. It is off by
default, and it is a ceiling an operator chooses rather than a protection
against loss: Claude Code moves a tool result over its own limit
(MAX_MCP_OUTPUT_TOKENS, 25,000 tokens by default)
into a file the model reads back, so such an answer reaches it either way.
The limit is in bytes, not tokens, and how many bytes of these answers make
a token has not been measured, so set it from what a client is observed to
accept. The first of these is read:
PG_LICHT_BUDGETS, which must then exist;
budgets.ini in the same directory as the connections
file in use; and ~/.config/pg_licht/budgets.ini. The
last two are used only if they exist.
The file is strict: an unknown section or key, or a value out of range, stops the server at startup rather than leaving a limit in force that the operator did not write. It holds no credentials and may be readable by anyone, but it must not be writable by anyone but its owner, since a file another user can edit is a limit another user can raise. Each refusal and each permitted run reports the values and the file they came from under execution_budget.
The connection source is resolved in this order:
--config
filePG_LICHT_CONFIGDATABASE_URLThe first that applies is used alone. Where
~/.config/pg_licht/connections.ini exists,
DATABASE_URL and the positional argument are not
read, and a call that names no connection goes to that
file's default.
Because sections may carry passwords, the configuration file must
not be group- or world-accessible. pg_licht_mcp
refuses to load it otherwise, the same rule libpq applies to
~/.pgpass:
chmod 600 ~/.config/pg_licht/connections.ini
Note that libpq enforces that rule on ~/.pgpass but not on pg_service.conf, so if a service file holds a password its permissions are yours to manage.
An edit to the connections file, or to the budgets file in use at
startup, takes effect at the next request, without restarting the server or
the client. A budgets file created after startup is read at the next start,
and one that disappears leaves the limits in force as they were. Before each
request pg_licht_mcp compares the file's
modification time, size and inode with the version it loaded, which also
catches an editor that replaces the file rather than writing it in place.
SIGHUP forces a reload whatever the file looks like,
for an editor that keeps the timestamp or a filesystem that does not update
it:
pkill -HUP pg_licht_mcp
The new file is read in full before anything changes. If it fails
as a whole — unreadable, group- or world-accessible, malformed, or
with no usable section — the running configuration stays in use,
listConnections carries
reload_error, verifyTopology
reports it as a configuration finding, and the file
is read again when it next changes. A section that does not validate is
skipped, as at startup. The swap happens between requests, which are handled
one at a time, so no call sees part of each configuration. Connections whose
section changed or was removed are closed; the rest are kept. Only the file
named at startup is ever read.
If a file changes while it is being read, nothing is applied, and the next request reads it again.
The tool list and the resource list both depend on the
configuration — the default connection, the only pooler, one set of
resources per connection — so after a reload
pg_licht_mcp sends
notifications/tools/list_changed and
notifications/resources/list_changed, and declares
listChanged for both at initialisation. A client on
the stateless revision, which never sends
notifications/initialized, is sent them too, and
tools/list is marked cacheable for one minute rather
than an hour. A client that ignores them keeps working, with descriptions
that may name the old default for at most that minute. A new
pg_licht_mcp binary still needs the client to
restart the server.
Before 4.5.0, SIGHUP took the default
action and ended the process. It no longer does in any mode; with a
connection string instead of a file there is nothing to reload, and the
signal is ignored.
Every operation below except
listConnections also accepts an optional
connection argument naming a section of the
configuration file; omit it to use the default connection. Arguments shown
as name are required.
Operations backed by an extension
(statementStats,
explainQuery, tableBloat,
indexBloat,
waitEventProfile,
statementKernelStats,
predicateStats and
suggestIndexes) locate its objects through
pg_extension and reference them schema-qualified.
The extension therefore does not have to be installed in
public, and does not have to be on any
search_path: ‘CREATE EXTENSION
pgstattuple SCHEMA extensions’ works exactly as an
installation into public does.
This cannot be solved with a search_path
instead. pg_licht_mcp sets no session state, because
the target deployment is a transaction-mode pooler that discards it, and
PgBouncer rejects a GUC passed through the options
connection keyword outright.
The location of each extension is resolved once per configured
connection and remembered for the life of the process, since one connection
addresses one database. It is looked up again after any failure to find the
object, so an extension installed (or moved with
‘ALTER EXTENSION ... SET SCHEMA’)
while pg_licht_mcp is running is picked up on the
next call. An extension that is absent is reported as not installed, with
the hint naming the setup steps; see
DIAGNOSTICS.
Two arguments narrow an answer by a string, and they match differently.
_’ and
‘%’ match themselves, so
‘user_’ finds
user_x and not users.
Nothing is stemmed or split into words. Taken by
listSchemas, listTables,
listTableStats,
listTableSizes,
listFunctions,
listSequences, listRoles,
serverSettings,
poolerConfig,
listConnections and
listTopology.websearch_to_tsquery()
with the English configuration. Words are stemmed, so
‘order’ finds
orders; names are split into words at underscores
and capitals before they are indexed, so
customer_id contributes
‘customer’; a phrase in double
quotes must match in order, ‘or’
between words means either, and
‘-word’ excludes. A fragment of a
word matches nothing: to find part of a name, use a listing's
pattern instead. Taken by
searchTables,
searchFunctions and
searchEnums, which each say what they search.
searchFunctions also matches the language name as
a literal substring, so ‘sql’ finds
plpgsql functions.Through 4.3.3, pattern on
listRoles and serverSettings
was an ILIKE, where
‘_’ matches any character; it is now
the literal substring its description always said it was.
checkPrivilegesMost of this server works for any role that can connect,
because the catalog is world-readable. Measured on PostgreSQL 18: a bare
LOGIN role with no grants runs 59 of the 73
database operations at full fidelity when every extension is present,
and the monitoring role takes that to 67. What remains for the
monitoring role is the four that read row data — the
tableStats column histograms,
checkKey, rowScatter and
explainQuery — plus
evaluateIndex, which plans against tables and so
needs SELECT on them, and the replication-origin
half of replicationStats, which no predefined
role can read.
Worth calling first against an unfamiliar connection or a
restricted role. The alternative is discovering the limits one operation
at a time, and the discovery misleads:
tableStats under a role without
SELECT returns every column present with null
statistics, which is what a table that was never analyzed looks like,
and statementStats returns rows whose query text
is <insufficient privilege> with no count
of what was hidden.
Operations named in neither list are fully available;
available is a count rather than a list, because
the exceptions are the answer. denied means the
operation cannot answer at all, degraded that it
answers less than a privileged role would see. The three that read row
data are always degraded and never
denied, because privilege there is per object: a
role without blanket read access may still hold
SELECT on some tables and not others.
extensions reports the four extensions
that need shared_preload_libraries —
pg_stat_statements,
pg_wait_sampling,
pg_stat_kcache and
pg_qualstats — as three separate facts
each: library_loaded,
extension_created in this database, and
functional, whether its view answers a read, with
the reason when it does not. A probe that fails for a reason of its own,
such as a timeout, leaves functional null and says
why under probe_error. The state that goes
unnoticed is a mismatch between them: a library preloaded whose
extension was never created needs only CREATE
EXTENSION, no restart, and the extension operations say so in
that case rather than calling it not installed.
An absent extension is distinguished from a missing privilege, because the two send an operator to different fixes.
No role memberships and no GRANT
statements appear in the output. Which predefined role gates an
operation is PostgreSQL's business; the caller needs to know what works.
Naming a grant in the hint of an operation that failed is diagnosis;
listing grants beside every unavailable operation would be a standing
recommendation to escalate privilege, which is not this server's to
make.
checkRoleAccess
grantee schema
objecthas_table_privilege()
and its relatives rather than reconstructed from ACLs, so inheritance,
grants to PUBLIC, ownership and superuser fold in
the way the server folds them. Needs no grant of its own: these functions
and the catalog are world-readable, so any role that can connect may ask
about any other. The argument is grantee because
role is reserved server-wide for narrowing a sweep
to a primary or a replica.
Schema USAGE and database
CONNECT come back beside the object privileges,
since a grant on the table is inert without them and the resulting error
names the table. Where a table-level privilege is absent but individual
columns carry it, those columns are listed. A routine name reports every
overload with its signature and
security_definer.
Row-level
security is not folded in, because
has_table_privilege()
does not consider it: a true here can still return no rows. The
row_level_security block carries whether RLS is
enabled, whether it is forced, whether this particular role is subject
to it — the owner is exempt unless forced — and every
policy with whether it applies to the role. Enabled with no applicable
permissive policy denies everything.
Not to be confused with
checkPrivileges, which reports which of this
server's own operations the connecting role can run.
roleDependencies
rolepg_shdepend.
by_kind separates owner,
which blocks DROP ROLE and is cleared by
REASSIGN
OWNED, from acl and
policy, which DROP OWNED clears.
pg_shdepend is
shared across the cluster, so the counts cover every database; an object
id resolves only from the database it lives in, so
objects names this database and the shared
catalogs while others appear as counts. Tables, functions, schemas and
types are named as
schema.name; every other
class, a policy or a default privilege included, by
pg_identify_object(),
so a policy reads as ‘p on
public.t’.
defaultPrivileges
[schema]global
overrides the hard-wired defaults; per-schema entries are added to them. A
default applies only to objects created by
granted_by. Naming a schema
returns its entries and the global ones, since both decide what the next
object there gets; a schema that does not exist is an error.listSchemas
[pattern]listTables
schema [pattern]tableDetails, not
here; this operation is deliberately light for browsing a schema. Carries
no statistics and no sizes: see listTableStats and
listTableSizes.tableDetails
schema tablenull if the table has no
toastable columns), primary key as an ordered column list, index
definitions with valid, constraints, foreign keys,
inbound foreign keys (referenced_by), triggers
(timing, events, when condition, language, enabled),
rules (excluding the implicit view _RETURN rule),
row-level security status and policies, view definition, and role grants.
Two fields report an object that is not doing what its
definition suggests. An index with valid false is
what a CREATE INDEX CONCURRENTLY that failed
leaves behind: it occupies disk and is maintained on every write, and
the planner never uses it. A trigger with enabled
‘disabled’ still carries its whole
definition, because ALTER TABLE ... DISABLE
TRIGGER changes no text;
‘replica’ and
‘always’ are the
session_replication_role states, which is how a
trigger can be off for the application and on for a replication apply
worker.
Everything here changes only when someone issues DDL, and none
of it is sampled row data. For row counts, scan counters, autovacuum
state and the pg_stats histograms see
tableStats; for measured sizes see
tableSize.
tableStats
schema tablerelpages
* block_size
with the estimated_from
timestamp that produced it),
block_size is the server's compile-time
BLCKSZ, 8192 unless it was built otherwise.
seq_scan and idx_scan, live
and dead tuples, the cumulative write counters
(n_tup_ins, n_tup_upd,
n_tup_del, n_tup_hot_upd),
mods-since-analyze, inserts-since-vacuum, the four
pg_stat_user_tables timestamps
(last_vacuum, last_autovacuum,
last_analyze,
last_autoanalyze), per-index scan counts, and the
per-column pg_stats histograms
(null_frac, avg_width,
n_distinct, physical order
correlation, most_common_vals
and their frequencies). Reads the catalog and the
statistics collector only: no relation is opened and no file is measured.
most_common_vals is literal values sampled from the column, up to default_statistics_target of them. This is the only operation that returns them.
The manual and automatic timestamps are separate, and mean
what the catalog means by them. A recent
last_autovacuum says autovacuum is reaching the
table; a recent last_vacuum beside a null
last_autovacuum says the opposite —
somebody is keeping the table alive by hand and the cron job is hiding
the finding rather than being it. Through 4.1.1 both were returned
merged under last_vacuum, which made those two
indistinguishable. estimated_from remains the
latest of all four, since any of them refreshes
relpages and
reltuples.
cumulative_statistics_missing is true where the catalog counts rows in the table and the cumulative statistics have none: no live tuples and no vacuum or analyze on record. On a server whose statistics are intact that does not last, since loading a table counts its rows as live. It is what crash recovery and an immediate shutdown leave behind, what a restore or an upgrade starts with, and what every table on a standby looks like always, because vacuum and analyze are recorded on the primary and never replicated. A note says which of those the server is. Read without it, each is a table nobody maintains. It is null for a partitioned parent or a view, whose live tuple count is zero by construction, and it ends with the first insert the counters see.
columnHistogram
schema table
columntableStats carries three points off that histogram
— low, mid and high — which is enough to pick parameters to
re-plan a statement with; this is for when the shape of the distribution
is itself the question.
The two halves are complements, not alternatives.
ANALYZE puts the most frequent values in
most_common_vals and builds the histogram only
from what is left, so a value in the MCV list never appears in the
bounds however common it is, and reading either alone misdescribes the
column. The bounds are equal-frequency: consecutive entries delimit
buckets holding roughly the same number of rows, so bounds bunched
together are a dense region and a wide gap a sparse one.
statistics_target says why the histogram is the
width it is, and is the knob that changes it.
Costs nothing to read, and returns
more literal column values than any other operation here: the bounds and
the most common values are rows sampled out of the table.
pg_stats filters on
has_column_privilege(),
so a role without SELECT on the column gets
nulls rather than data.
listTableStats
schema [pattern]tableStats for those.tableSize
schema tablelistTableSizes
schema [pattern]listPartitions
schemanull, not
zero, when the default partition has never been analyzed:
reltuples stays at -1 until then even after rows
arrive, and has_default says whether a default
exists at all. rows and
size_estimate cover only the leaves that have been
analyzed; never_analyzed says how many of the
leaf_partitions they could not see, and both are
null when none has been measured. A
sub-partitioned child holds no rows itself, so its leaves are what is
summed. Reads reltuples and
relpages, so no relation is opened and no lock is
taken.
A growing default partition is the finding: rows land there when they match no bound, so it is a missing partition that has not failed loudly yet.
partitionDetails
schema, table
[limit, order_by]null for a partition that has never been analyzed,
rather than a zero that would read as empty.
Autovacuum runs per
partition,
so a parent has no vacuum state of its own and ranking parents finds
nothing while one child falls behind. Bounds are verbatim rather than
parsed: a bound carries whatever types the key columns have, and a
misparsed boundary is worse than an unparsed one. For a range parent,
the highest upper bound against now() says
whether the next period's partition exists.
At most limit partitions are returned, 100 by default and at most 10,000, with partition_count and partitions_truncated beside them: each costs about 400 bytes, so a table partitioned by day for three years is about 430 kB. order_by decides which are kept and in what order: name (the default), dead_tuples, mod_since_analyze, oldest_vacuum (never vacuumed first), or size. The partition falling behind is found by ranking, not by reading names. The DEFAULT partition is always kept, since a growing default is a missing partition that has not failed loudly yet, and by name it sorts last — where a cap would cut it.
largeObjectsdiskUsage counts their
bytes. No sizes: the bytes are in pg_largeobject,
which is not publicly readable.listFunctions
schema [pattern]functionDetails
schema functionlistEnums
schemaenumDetails
schema enumlistTypes
schematypeDetails
schema typelistSequences
schema [pattern]SERIAL and IDENTITY
columns. pattern, when given, keeps only the
sequences whose name contains it, case-insensitively.listExtendedStatistics
schemaCREATE STATISTICS)
for schema with target table, columns, statistics
kinds (ndistinct, dependencies, mcv), and description.searchTables
web_searchlistTables plus roles.
Structure only, and there is no statistics counterpart: a text search is
how a table is found, not how a counter is read. Name a match to
tableStats or tableSize
for those.searchFunctions
web_search [schema,
include_system]pg_catalog and
information_schema, are left out unless
include_system is true or one of those schemas is
named as schema: they were 97 to 99 percent of a
typical answer, and enough of them to bury the database's own functions. A
schema that does not exist is an error rather than
an empty answer.searchEnums
web_searchlistRoleslistTablespaceslistEventTriggerslistLanguagesplpgsql or plpython3u,
with owner, trusted and procedural flags, handler function, and
description.listAccessMethodslistCastslistExtensions
[available]pg_available_extensions; every entry then
carries installed and
default_version, the version CREATE
EXTENSION would install. Whether an extension a plan needs is one
command away, or needs a package and perhaps a restart first, changes the
plan. checkPrivileges draws the same line for the
extensions this server itself uses: an operation denied because its
extension is missing says whether that extension is available on the
server.listCollations
schemalistOperators
schemalistOperatorClasses
schemalistTextSearchConfigs
schemalistForeignTables
schemalistForeignServerslistPublicationslistSubscriptionsCREATE SUBSCRIPTION declared, changing
on DDL and not otherwise. For whether the subscriber is keeping up, call
subscriptionStats.
The connection string is never exposed, as it may contain credentials. PostgreSQL agrees: the catalog revokes subconninfo from public and grants every one of its other seventeen columns, so selecting it would fail for any non-superuser as well as leaking a password.
subscriptionStatsAnswers whether a subscriber is keeping up and,
if not, whether it is stuck copying a table or failing to apply. Reports
no byte lag, because a subscriber cannot measure it:
received_lsn and
latest_end_lsn track each other rather than the
publisher, so their difference is zero even when the subscriber is far
behind. For lag in bytes call replicationSlots
on the
publisher
and read retained_wal_bytes.
When a table is still copying, the worker pid is a real
backend pid: pass it to progressStats for the
byte and tuple counts of that exact copy.
bytes_total is zero there, and so
bytes_percent is null: a table sync streams from
the publisher rather than reading a file of known size.
errors is absent entirely on PostgreSQL 14, which has no pg_stat_subscription_stats, and its conflicts are PostgreSQL 18. An absent key means the server cannot answer, which is not the same as no errors.
replicationSlotswal_status is the verdict that retained
WAL bytes only hint at: ‘extended’
means the slot is already past max_wal_size, and
‘lost’ means the WAL it needs is
gone and the slot is unusable. safe_wal_size is
how much more WAL may be written before that happens.
The spill and stream counters come from
pg_stat_replication_slots, a different view:
they show logical decoding spilling large transactions to disk, which is
invisible in the slot's own row and is a common, silent throughput
cliff. PostgreSQL 16 adds conflicting, and 17 adds
invalidation_reason and
inactive_since.
replicationStatsTwo readings that are routinely misread. The view is
security restricted per
row rather than refused, so a role without
pg_read_all_stats sees the senders exist with
columns null, which looks like an idle replica and is a permission
answer. And the lag columns revert to NULL a
short time after a standby has entirely caught up and WAL activity
stops, so a null lag there means caught up while a non-null one is the
last measurement rather than the current state.
databaseSizeserverSettingspg_settings) grouped by
category, each with current value, unit, description, context, type,
source, and pending_restart flag.currentActivity
[pid, query_id,
min_duration_s, state]pg_stat_activity) across all databases: pid,
database, user, application_name, backend_type, state, wait event, query
text, transaction and query duration, leader pid for parallel workers, and
the backend's xid and xmin.
query_id is the join key to
statementStats and
explainQuery, which is what turns “this
is running now” into “this is its plan”. It is
returned as text, since a 64-bit value does not survive JSON number
precision, and is null unless compute_query_id is
enabled; the default ‘auto’
enables it when pg_stat_statements is loaded. On
PostgreSQL 17 and newer each wait event also carries its prose
description from pg_wait_events.
All four filters are optional and combine; with none of them the whole view is returned, which on a busy server is mostly idle connections and internal processes.
statementStats is traced to whoever is running
it now.active’ or
‘idle in transaction’.currentLocks
[pid]pg_locks) joined with the holding
backend's query and user, plus which pids are blocking each waiting lock.
Use to diagnose lock contention.
pg_blocking_pids().
Each row is tagged with chain_depth: 0 is the
backend asked about, and the largest depth is the one at the root of
the pile-up, which is the one to look at first. Omit it for every lock
in the cluster.databaseStatspg_stat_database) for
every database in the cluster: connections, commits and rollbacks, block
hit ratio inputs, tuple counts, conflicts, deadlocks, temp file usage, and
checksum failures.
The session counters (session_time, active_time, idle_in_transaction_time, sessions, sessions_abandoned, sessions_fatal, sessions_killed) distinguish a database that is busy from one merely holding transactions open, which the commit and rollback counts alone cannot tell apart. PostgreSQL 18 adds parallel_workers_to_launch and parallel_workers_launched; a shortfall between them means queries planned for parallelism ran without it.
statementStats
[limit, query_id,
order_by, min_calls]pg_stat_statements), with calls, timing, row
counts, buffer usage, temporary block I/O, and WAL volume, under
statements, alongside an info
block from pg_stat_statements_info. Returns a
clear error with setup instructions if the extension is not installed.
Each statement carries explainable.
pg_stat_statements tracks utility statements
too, and after a bulk load, a migration or a maintenance window they
head the ranking: ALTER TABLE,
CREATE INDEX, VACUUM,
COPY. They have no plan, so
explainable is false for them and true for a
statement explainQuery will take, by the same
first-word rule that operation applies; it is null where the query text
is hidden from the connecting role. Nothing is left out: a review after
a migration wants to see that building one key took two hours.
info.dealloc counts how often the extension has evicted its least-used entries because pg_stat_statements.max was exceeded. A non-zero value means this list is not the slowest queries in the cluster but the slowest of those that survived eviction — a distinction that cannot be drawn from the rows themselves, which is why it is returned beside them.
query_id is text, not a number: it is a
64-bit value that a client parsing JSON numbers as doubles would round,
and the rounded value would then not be found by
explainQuery.
pg_stat_statements keeps one entry per user
and database, so a queryid can match more than one row.waitEventProfile
[group_by, query_id,
limit, include_idle]pg_wait_sampling, one entry per wait
event with its sample count, its percent of the samples counted, and
estimated_ms.
currentActivity is one sample of what is waiting
now; this is what the server has been waiting on, which a single sample
cannot say. IO/DataFileRead dominating and
LWLock/WALWrite dominating are opposite stories
that look identical in a throughput figure.
A sample is one backend seen in one wait at one tick of the
collector, every settings.profile_period_ms, so
estimated_ms is backend time summed over every
backend, not wall-clock time. The counts accumulate from the last reset
or server start, and pg_licht_mcp never resets
them: a window is the difference between two readings. An entry whose
event_type and event are
null is a backend sampled while not waiting at all — running,
which usually means on CPU. Client/ClientRead
includes idle connections waiting for their next statement, so on a
pooled server it measures connections times time as much as
anything.
Requires pg_wait_sampling at version
1.1 or later, in shared_preload_libraries.
statementStats. query_id
is null for samples with no statement, and always null when
pg_wait_sampling.profile_queries is off.Activity samples of background
processes idling in their main loops, which are left out by default
and counted in excluded_idle_samples. On a quiet
server they outnumber everything else.statementKernelStats
[limit, query_id,
order_by]pg_stat_kcache: user and system CPU time, bytes
actually read from and written to storage, page faults and context
switches, separately for planning and execution, per
query_id.
PostgreSQL cannot see this itself. A shared_blks_read is a request to the kernel, which may answer it from its page cache or from the device; exec.reads_bytes is what came off storage. Compare it with shared_blks_read times block_size, both returned. A high nivcsws (involuntary context switches) is CPU contention; majflts is memory pressure reaching the disk.
What these counters mean depends on the platform the server runs on, which the answer names as platform. cpu_time_s, user plus system, is the kernel's own total, checked on Linux and FreeBSD. On Linux the user and system split and the byte counts are given as measured. Elsewhere those four are null and unavailable says why, because they are not what they look like. FreeBSD splits a thread's CPU time into user and system by statistical sampling, about every 7.9 ms, so a short statement is reported as all user time: 567,895 short updates measured 9.0 s of user time and 0.000 s of system time. It also charges a process only the storage I/O it performed synchronously, not the buffered writes flushed later: three sorts that spilled 1.08 GB of temporary files measured 0.47 MB written, where Linux charged 257 MB. Other platforms are not verified, and are treated the same way.
Read from the
pg_stat_kcache()
function, which is per statement — not the view of the same name,
which is summed per database — and joined to
pg_stat_statements through
pg_stat_statements(false),
which skips the query text file. nswaps,
msgsnds, msgrcvs and
nsignals are not reported: Linux does not maintain
them, so they are always zero. For a role without
pg_read_all_stats,
pg_stat_statements hides the queryid of other
roles' statements, so their calls,
total_exec_ms and
shared_blks_read are null beside kernel counters
that are complete.
Requires pg_stat_kcache at version 2.2
or later, in shared_preload_libraries after
pg_stat_statements.
progressStats
[pid, relation]VACUUM, ANALYZE,
CREATE INDEX, CLUSTER,
COPY, and base backups. Each carries its phase,
blocks or tuples done against the total, a completion percentage, and how
long it has been running. Use it to decide whether a
VACUUM will finish before wraparound — the
natural companion to wraparoundStatus — or
whether a CREATE INDEX is stuck waiting on a
locker.
All six categories are always present, empty when nothing is running, so an absent key never has to be read as either “idle” or “unsupported here”. PostgreSQL 17 renamed the vacuum dead-tuple columns from counts to bytes; both are reported under their own names, with dead_tuple_unit saying which the server produced, because they measure different things.
currentActivity.ioStats
[pid, backend_type,
object, context]pg_stat_io): reads, writes, extends, hits,
evictions, reuses, fsyncs, their timings, and a hit percentage. This is
where buffers written directly by backends, vacuum's ring-buffer reuse,
and bulk read and write I/O become visible separately from the aggregate
counters in checkpointStats. Rows with no activity
at all are omitted, since the view is a dense matrix of combinations most
of which are structurally impossible. Byte counters are reported on
PostgreSQL 18 and newer, where they replaced the former
op_bytes column.
Requires PostgreSQL 16 or newer; older servers get a clear error naming the alternatives.
pg_stat_get_backend_io(),
together with its WAL volume from
pg_stat_get_backend_wal():
a backend can be quiet in I/O and still be generating WAL heavily.
pg_stat_io has no pid column at all, so this
is a different source rather than a filter, and it requires PostgreSQL
18.client
backend’ or ‘autovacuum
worker’.relation’ or
‘temp relation’.normal,
‘vacuum,
‘bulkread’’’,
or ‘bulkwrite’.wraparoundStatus
[schema, limit]age(datfrozenxid),
age(datminmxid), and each as a percentage of its
own freeze_max_age and of the limit of 2144483647,
at which the cluster stops accepting commands that assign a new
transaction id or multixact. Both axes carry both budgets: multixacts have
the same three-million stop limit and the same forty-million warning as
transaction ids, and members-space exhaustion is its own incident. That
figure is
not 2^31:
PostgreSQL refuses new transaction ids three million short of wraparound
(xidStopLimit = xidWrapLimit - 3000000), and
begins warning in the log forty million short of it. Both are reported
— wraparound_limit and
wraparound_warn_limit under
limits, with
xids_until_wraparound_limit and
xids_until_warn_limit per database. The warning
threshold is the one an operator has usually already seen fire. For
tables: the oldest by age(relfrozenxid), with the
effective autovacuum_freeze_max_age and whether it
came from the table's storage parameters or the server, plus last vacuum
times and dead tuple counts.
TOAST tables are included, labelled with the table they belong
to. They carry their own relfrozenxid, do not
appear in pg_stat_user_tables, and are
frequently the relation actually holding the horizon back.
On PostgreSQL 18 and newer each table also reports
frozen_percent
(pg_class.relallfrozen) and its cumulative
vacuum and autovacuum times. Those turn an age into an estimate of the
work remaining: an old relfrozenxid on a table
that is already almost entirely frozen is a different problem from the
same age with nothing frozen, and the timings say whether autovacuum has
been trying and failing to keep up or has simply never run.
diskUsageHow much room is left is not knowable from SQL. PostgreSQL exposes no function for total or free space, so this reports what is consuming space and how it divides, never the headroom. A tablespace also carries its location, which matters because the database that is growing and the volume that is full need not be the same device.
Every size is in logical bytes, as PostgreSQL counts them. On a filesystem that compresses, deduplicates or shares blocks with a snapshot — ZFS, btrfs — the space occupied differs, usually downwards, by a ratio PostgreSQL cannot see: a 106 GB database was measured occupying 68 GB on ZFS at its default compression. Freeing 10 GB as counted here may return less than that to the volume.
A climbing .ready count in the archive status is a failing archive_command retaining every segment it has not archived — indistinguishable from an abandoned replication slot by WAL size alone, and a different fix.
The
pg_ls_(*)
sections need pg_monitor. Each section is guarded
on its own savepoint, so one refusal returns an error in that key and
leaves the rest answered:
pg_ls_logdir()
raises rather than returning empty when
logging_collector is off.
The four directory sections are
trivial. The tablespace and database sizes are the whole cost: they walk
the tree and stat every segment file, so they scale with file count
rather than with bytes, and the two overlap because
pg_tablespace_size()
on pg_default covers the same subtree the
per-database sizes do. Measured at 314 ms for the whole call against a
cluster of roughly a terabyte, where a bare
databaseSize is already 163 ms. Nothing is read,
no relation is opened and no lock is taken — this is metadata
rather than I/O — but that was measured with directory entries
warm in the page cache, and a filling disk is when they are not.
statement_timeout bounds it, so the failure mode
is a timeout that names itself.
checkpointStatspg_stat_wal record,
full-page-image, and byte counters, and the settings that govern all of it
(checkpoint_timeout,
max_wal_size,
checkpoint_completion_target,
the bgwriter
knobs).
The source views differ by major version: PostgreSQL 17 split
the checkpointer counters into
pg_stat_checkpointer and moved the
backend-written buffer counts to pg_stat_io,
while earlier versions keep everything in
pg_stat_bgwriter. Field names are normalized
across both, so a caller never branches on the version; the
source field names the views that produced the
numbers.
tableIOStats
schema [table,
limit]pg_statio_all_tables):
heap_blks_read against
heap_blks_hit, idx_blks_read
against idx_blks_hit, the TOAST and TOAST-index
pairs, and a combined ratio, with relation size and scan counts for
context.
A ratio is null, not zero, for an object that has seen no reads at all, so “no traffic” is never mistaken for “every read missed the cache”.
pg_statio_all_indexes; over a whole schema it
would multiply the payload by the index count without making the
table-level ratios any easier to read.bufferCacheSummarypg_buffercache.
tableIOStats counts only what
shared_buffers served, so a miss there may still
have come from the operating system page cache at RAM speed; this is the
only in-core view of that split. Read the histogram rather than a hit
ratio: mass at usage count 2 to 5 is a stable working set, everything at
0 to 1 with no unused buffers is clock-sweep churn, and those are the
same ratio with opposite diagnoses. A single sample is weak evidence;
two samples minutes apart are the method.
Requires the pg_buffercache extension
at version 1.4 or later — the summary functions arrived there
— and a role with pg_monitor. Version 1.4
ships with PostgreSQL 16; on 14 and 15 there is no 1.4 to update to, and
the operation says so and points at
bufferCacheContents, which reads the view and
works on every version. On 16 and newer an out-of-date extension is
reported as such rather than as absent, and a missing grant names the
grant.
bufferCacheContents
[limit]Answers which relation drives checkpoint writeback (pair with
checkpointStats), whether the visibility map
fork is resident enough for index-only scans to pay off, and which
database's working set is displacing the others.
Only buffers belonging to this database and to the shared catalogs are resolved to names. Buffers held by other databases on the same instance are visible to PostgreSQL but not reported, because they cannot be resolved locally and unnamed rows would invite being read as this database's.
hostCapacity
[ram_mb, vcpus,
storage]A hash table is allowed work_mem times hash_mem_multiplier, which is 2 by default since PostgreSQL 15, so hash_mem_multiplier is reported with the settings and two figures are derived from it: work_mem_times_hash_mem_multiplier_bytes, the allowance of one hash table in one process, and one_hash_node_all_processes_bytes, that allowance times max_parallel_workers_per_gather plus one, which is what one hash node of a parallel query may take. A statement with several hash nodes may take it for each. Neither is part of the worst case, which counts one work_mem per connection, and both are computed from this session's hash_mem_multiplier: a session that raises it, or work_mem, is beyond what any figure here describes.
Total RAM and vCPU count are properties of the host, not of the cluster: no catalog holds them, so they have to be injected. See Host capacity. The reported source says which of the three paths supplied them. Every ratio that needs RAM is null when none was supplied, together with a hint naming the three ways to provide it. Nothing is ever guessed: a wrong memory figure would silently invalidate every number derived from it.
duplicateIndexes
schema [table]identical groups indexes whose key
columns, operator classes, collations, sort order,
INCLUDE columns, and partial predicate all
match. redundant reports an index whose key
columns are a leading prefix of a wider index that also covers its
INCLUDE columns.
Comparison is by column expression rather than attribute
number, so two different expression indexes are not confused with each
other, and (a DESC) is not treated as covered by
(a, b). A unique index is never reported as
redundant for being a prefix: dropping it would drop a constraint the
wider index does not enforce. Each entry carries size,
idx_scan, the name of any backing constraint, and
the replica identity and validity flags, since those decide whether the
index can be dropped at all.
evaluateIndex
sql [create,
hide]hypopg. create takes
CREATE INDEX statements to plan against without
building them; hide takes the names of existing
indexes to plan without, which is how to ask whether an index is safe to
drop — the question duplicateIndexes raises
and cannot settle, since neither redundancy nor an unused
idx_scan proves the planner would not miss it.
Nothing is built, no lock is taken, no catalog row is written,
and the statement is never executed: hypopg
cannot serve EXPLAIN ANALYZE, so this is
plan-only and safer than explainQuery with
analyze. Reports the plan and total cost before
and after, and for each index whether the planner actually used it
— a proposed index the planner ignores is the common case, and a
cost figure alone hides it. The cost is the planner's estimate, not a
measurement.
A hypothetical index lives in backend-local memory for the
whole session and is cleared by neither
ROLLBACK, a new transaction, nor
DISCARD ALL, PgBouncer's default
server_reset_query. A transaction-mode pooler
cannot be asked to help either: it runs no reset query at all unless
server_reset_query_always is set, and even then
DISCARD ALL does not clear it. This operation
therefore resets explicitly on the way in and on the way out, so that
behind a transaction pooler one caller's hypothetical index cannot
reshape the next caller's plans.
hide requires
hypopg 1.4.0 or later, gated on the extension
version.
Takes settings and
plan_as_role exactly as
explainQuery does, and for a reason of its own:
the whole output is a before/after cost comparison, so an environment
that does not match production makes both halves answer a different
question. Both plans are built under the same environment, and
planning_environment reports it.
predicateStats
[limit, query_id,
order_by]WHERE and join predicates
pg_qualstats has sampled in this database, one
entry per relation, column, operator and way of evaluating it: how often
it ran, how many rows it was evaluated against, how many it filtered out,
and how far the planner's row estimate missed.
evaluated_as “filter” is a
predicate applied to rows after they were fetched, so a high
filtered_pct is rows read only to be thrown away
— what an index would avoid. “index” means an index
already serves it. mean_err_estimate_ratio is the
factor by which the planner's estimate missed — 166 when 60 rows
were expected and 10,000 arrived — and 0 when it did not;
mean_err_estimate_num is the same miss in rows. A
large factor is fixed by ANALYZE, a larger
statistics target or extended statistics, not by an index.
No constant is ever returned.
A predicate reads ‘table.column
op’?, the form the extension's own advisor uses;
pg_qualstats records every predicate's literal
in constvalue, and neither that column nor
pg_qualstats_example_query()
is read. query_ids holds at most ten per
predicate, with queries the full count.
pg_qualstats samples one statement in
pg_qualstats.sample_rate, by default one in
max_connections, so counts are samples rather than
totals.
Requires pg_qualstats at version 2.0
or later, in shared_preload_libraries. Without the
preload the extension does not fail: it reports only the calling
session's own predicates. That is detected from the extension's own
settings, which any role can read, and refused, rather than returned as
an empty answer that looks like a real one.
suggestIndexes
[min_filter, min_selectivity,
forbidden_am]pg_qualstats_index_advisor()
for this database: each a complete CREATE INDEX
statement with the query_ids of the statements it
would serve, and not_indexable, the predicates that
filter heavily but use an operator no access method supports.
Nothing is built; the advisor only reads
pg_qualstats. A suggestion is a hypothesis. Pass
its ddl to evaluateIndex
as create, with the statement
explainQuery recovers for one of its
query_ids, to see whether the planner would use
it. On PostgreSQL 14 and 15 that statement keeps its
$n placeholders, which only 16's
‘EXPLAIN (GENERIC_PLAN)’ can plan;
substitute representative values first. Constants are never returned, as
with predicateStats. Requires
pg_qualstats 2.1 or later, the first whose
advisor names the statements each suggestion serves, preloaded as for
predicateStats, and refuses in the same
case.
hash.tableBloat
schema table
[exact]pgstattuple
or pgstattuple_approx):
table size, live and dead tuple counts and percentages, free space and
percentage. More accurate than the ANALYZE-time estimates in
listTableStats and
tableStats. Returns a clear error with setup
instructions if the extension is not installed.
false.indexBloat
schema indexpgstattuple function fits its access method. The
access method is resolved from the catalog, so the caller does not need to
know it in advance.
btreepgstatindex:
version, tree_level,
root_block_no,
internal_pages,
leaf_pages, empty_pages,
deleted_pages,
avg_leaf_density and
leaf_fragmentation. Reads the whole index.ginpgstatginindex:
version, pending_pages and
pending_tuples, reported together with the
fastupdate setting and the effective
pending_list_limit_kb that bound them. Reads
only the metapage, so it is cheap on any size of index.hashpgstathashindex:
version, bucket_pages,
overflow_pages,
bitmap_pages,
unused_pages, live_items,
dead_items and
free_percent. Reads the whole index.gistpgstattuple:
there is no page-level function for GiST, so these are the tuple-level
figures table_len,
tuple_count, tuple_len,
tuple_percent,
dead_tuple_count,
dead_tuple_len,
dead_tuple_percent,
free_space and
free_percent. A WITHOUT
OVERLAPS key is a GiST index. Reads the whole index.source names the function that answered.
The metrics are deliberately not normalized across access
methods, unlike tableBloat's exact and
approximate pair: leaf_fragmentation and
pending_tuples are not two spellings of one
quantity. access_method names which set was
returned.
index_size and
idx_scan accompany every result, because they are
what the metrics are weighed against: a fragmented index that nothing
has scanned since the last statistics reset is a candidate for dropping
rather than for ‘REINDEX’.
spgist and
brin are read by no
pgstattuple function and are reported as
unsupported by name; their page-level detail is in the
pageinspect extension instead. A partitioned
index has no storage of its own and is reported as such.
rowScatter
schema table
column [value,
max_rows, sample_percent]tableStats returns
physical_order_correlation, which says a column is
out of physical order; this says what that costs. On a 73-million-row
table whose city column had a correlation of 0.09, one city's 157,058 rows
were on 140,257 pages where about 3,300 would hold them.
With value, the count is exact: rows that match, the distinct pages they sit on, pages_if_packed — what those rows would need at the table's average rows_per_page — and scatter_ratio, pages over that. A ratio of 1 is already packed; 40 means a query for that value reads forty times the pages it needs to. At most max_rows matching rows are read, 200,000 unless given and never more than 5,000,000, and capped says whether that limit was reached, in which case the counts are lower bounds. A value of null measures the rows where the column is null.
Without value, the reading is sampled by
whole pages (TABLESAMPLE SYSTEM) for the values
pg_stats lists as most common in the column, up
to ten: for each, share_of_pages_percent, the
share of the table a query for it touches,
estimated_rows,
estimated_pages and its
scatter_ratio. That answers which column is worth
clustering on without naming a value. About ten thousand pages are read
unless sample_percent says otherwise, and the
whole table when it is smaller than that; at 100 percent the sampled
figures equal the exact ones.
It reads the table's rows, through
ctid, so it needs SELECT
on the table and holds an AccessShareLock for
the read. The exact form returns counts and echoes nothing back; the
sampled form returns only values tableStats
already returns. A partitioned table has no pages of its own: name one
partition.
checkKey
schema table
valuesexplainQuery
[queryid | sql]EXPLAIN (FORMAT JSON)’ plan for
a statement, recovered from pg_stat_statements by
queryid or supplied directly as
sql. Runs in a read-only transaction bounded by
statement_timeout. Returns the plan verbatim plus
generic, analyzed, and
read_only flags and the
pg_stat_statements row. There are no heuristics
and no generated DDL; the plan is yours to interpret.
pg_stat_statements queryid, as a decimal
string. It is a 64-bit value and does not survive JSON number
precision. Mutually exclusive with sql. Two
kinds of recorded statement cannot be planned from their text, and the
answer says which: one cut short at
track_activity_query_size, and one that had a
typed literal. pg_stat_statements records
‘DATE '2026-09-14'’ as
‘DATE $6’, keeping the type name
without its string, which is not SQL at any length. Both have to be
passed through sql instead.SELECT,
INSERT, UPDATE,
DELETE, MERGE,
WITH, TABLE, or
VALUES statement. Utility statements are
rejected. Mutually exclusive with queryid.pg_stat_statements are normalized, so
without params they are planned with
‘GENERIC_PLAN’; supplying params
has the statement PREPAREd and planned with
real values.EXPLAIN (ANALYZE,
BUFFERS)’, which really executes the statement. Honoured
only after the plan is proven free of any
ModifyTable node, so data-modifying
statements, including data-modifying CTEs, are never executed. Nor is
a statement whose plan calls a function PostgreSQL marks
VOLATILE: such a function may change the
server or the session in ways a read-only transaction does not prevent
— end another session, reload the configuration, reset
statistics, take a session lock. The plan is then returned with
analyzed false and the functions named in
volatile_functions. A few volatile functions
with no effect outside the statement are let through:
random(),
random_normal(),
clock_timestamp(),
timeofday(),
gen_random_uuid(),
uuidv4(),
uuidv7(),
pg_sleep(),
pg_sleep_for()
and
pg_sleep_until(),
which return a different value each time or wait; and those that only
read the state of the server,
pg_relation_size(),
pg_total_relation_size(),
pg_table_size(),
pg_indexes_size(),
pg_database_size(),
pg_tablespace_size(),
pg_lock_status()
(the pg_locks view),
pg_blocking_pids(),
pg_safe_snapshot_blocking_pids(),
pg_is_in_recovery(),
pg_current_wal_lsn(),
pg_last_wal_replay_lsn(),
pg_last_committed_xact(),
pg_stat_get_io(),
currval()
and
lastval().
The functions that read files, and
query_to_xml(),
are not among them. Requires timeout_ms. Default
false.{ work_mem: 512MB }’. Applied
with
set_config(name,
value, true), so they
revert with the transaction and cannot leak to another session.
Allowlisted to the settings that change a plan:
work_mem,
hash_mem_multiplier, the cost and parallelism
knobs, every enable_*, the join-search limits,
constraint_exclusion,
plan_cache_mode and the jit knobs. An unknown
name is refused and nothing is planned, rather than ignored.
search_path is excluded on purpose: it changes
which objects the statement resolves to rather than how they are
joined.
With analyze the statement is executed under these settings only within a budget taken from the host capacity declared for the connection, as host_ram_mb and host_vcpus. The worst case the plan's memory limits permit must stay within a share of the declared RAM, 10% unless budgets.ini says otherwise: each sort-like node at work_mem, each hash node at work_mem times hash_mem_multiplier, times the processes running it. The plan may use at most one parallel worker per four declared vCPUs, again unless budgets.ini says otherwise; see Budgets. With no declared capacity, no settings change is executed at all. A refused plan is still returned with analyzed false, and execution_budget under planning_environment carries the arithmetic. The read-only guard and the timeout bound what the statement writes and how long it runs, but not what it allocates, and an out-of-memory kill of one backend restarts every connection on the instance.
pg_db_role_setting, filtered to the same
allowlist. A per-database entry (ALTER ROLE ... IN
DATABASE) overrides the role-wide one, as the server itself
applies them. Entries that are not planner settings, such as
search_path or
statement_timeout, are reported under
skipped_from_role rather than dropped silently.
Explicit settings win over what the role
carries. On its own it is not held to the execution budget, because it
applies what the role already runs with in production.When either is given the result carries
planning_environment, saying what was applied and,
for plan_as_role, what was skipped. The plan's own
Settings block reports what the server saw.
listTopology
[pattern]inferred’ was derived from a shared
host and port rather than declared. Takes no
connection argument.
where does billing_prod live’
and ‘show me the ha group’ are
the same argument. An unlabelled connection is matched by its own
name. A pattern matching nothing returns empty lists rather than an
error.verifyTopologyThe whole operation turns on one fact: a system identifier names a replication lineage, not a postmaster. A physical replica began as a copy of its primary and carries the same value forever, so the identifier alone cannot separate the two axes and the postmaster's start time is read alongside it. The same identifier and the same start time is one instance, however it was reached; the same identifier with a different start time is another server of the same lineage, which is a replication group.
The address a connection reports
cannot decide this, and through a pooler it is wrong: behind PgBouncer,
inet_server_addr()
is the pooler's own connection to PostgreSQL —
127.0.0.1 for a pooler on the database's machine
— so one server reached directly and through its pooler reports
two addresses. Through 4.4 that was read as two servers, and a correct
instance declaration was reported as an error.
Reports declarations the servers contradict, connections that
share an identifier without being declared together, a replication group
with no reachable primary, and split brain — where it names every
primary it found and chooses none. Logical replication cannot be
verified this way, because a logical replica has its own identifier, and
is reported as ‘cannot verify’
rather than as a mismatch.
A connection declaring pooler is also
checked against that console's routing, and its
route is reported: the pooler database name, the
backend host, port and database, any force_user,
and the pool mode and size. PgBouncer's ‘SHOW
DATABASES’ never lists a
‘*’ fallback, only explicit
entries and the names a fallback has created — which it drops
again once their pool is empty — so a name it does not list may
still be routed. When the connection through the pooler works, that is
reported as information; when it fails, as a warning that names the
connection's own error, which is what says whether the name is routed at
all. Takes no connection argument.
listConnections
[pattern]listConnections,
listTopology and
verifyTopology are the only operations that do not
take a connection argument.
These read a PgBouncer admin console, named by a
‘kind = pgbouncer’ section of the
connections file — or by a database section that declares
‘pooler =
console’, answered by that console about
the database's own pool. With no connection named they
read the only console configured, never the default connection, which is a
database; with several configured, one must be named or swept by
group. The console is not PostgreSQL: it refuses
‘BEGIN’ and
‘SET’, so these run in no transaction
and send only ‘SHOW’ commands fixed in
the server, as the user the section names. Swept by
group only, and without role,
since a pooler is neither primary nor replica. No resources are offered for
a console.
Each ‘SHOW’ is bounded by
the section's statement_timeout_ms, cancelled on the
client side when it expires. A command this PgBouncer refuses — an
older version that does not know it — leaves its part of the answer
empty and names it under unavailable, and the rest is
answered; only a console that answers none of them is an error. Like an
absent extension on a database, an unreachable console or a refused login is
an answer carrying error and
hint, not a failed call.
poolerStatus
[database]SHOW
POOLS’, ‘SHOW STATS’
and ‘SHOW DATABASES’ rows are
returned beside the summary, keyed by the columns this PgBouncer version
reports, and ‘SHOW LISTS’ as totals.
The admin database pgbouncer is left out unless
database names it.poolerConnections
[database, state,
limit]waiting has no server yet. No
statement text is here — PgBouncer does not keep it — but
client addresses and ports, user and database names and
application_name are; see
What reaches the caller.
limit defaults to 100, at most 1000, for clients and
servers each.poolerConfig
[pattern, all]A client that negotiated MCP revision
2025-06-18 or later receives its answer as
structuredContent. Every client, whatever it
negotiated, also receives the text block.
Sending both is what the specification advises, and 4.0.0 did not:
it sent the structured payload instead of the text block, reasoning that a
client which cannot read structuredContent would not
ask for a revision that has it. A client can advertise a revision it does
not fully implement, and this server cannot tell — Claude Code
negotiates 2025-06-18 and reads
content, so every call came back appearing to have no
content at all. The token saving from sending one format is real, but not at
the price of an answer that looks empty.
Two environment variables change this, both read once at startup.
PG_LICHT_STRUCTURED_ONLY2025-06-18 or later, which is what
4.0.0 and 4.1.0 did by default. Worth setting once you have confirmed your
client reads structured content: it removes 27-46% of the bytes on a
typical call.PG_LICHT_MAX_PROTOCOLinitialize answers with the revision the client
asked for only when it appears in the list this server will speak, and
with the highest it will speak otherwise, so capping the list is how an
operator forces an older response shape from the server side. This governs
the handshake only: the stateless revision carries its version per request
and is not negotiated.The text block is serialised compactly. Indentation measured 18-39% of the payload across real catalog reads and no consumer reads it; every one parses the text as JSON.
Errors remain a text block in both eras: structuredContent is the format for a tool's answer, and an error is a message about why there is no answer.
Two kinds of failure are kept apart, and never share a message. An answer carrying error and hint is a fact about the database: an extension that is absent, too old, preloaded but not created, or not preloaded; a grant that is missing. A statement that fails for any other reason — a timeout, a lock, a lost connection, a query this server got wrong — is reported as the failure it is, a call result with isError set and the server's own message, and is never turned into a statement about what the database lacks. An empty result is an answer; a failed statement is not.
Every operation declares an outputSchema to
clients that negotiated the revision defining it. The schemas type the keys
that are unconditional, mark nothing required, and permit additional
properties. This is deliberate: payloads are version-conditional, and any
operation may answer {error, hint} when an extension
is absent or a grant is missing, so a schema that enumerated leaf fields
would reject correct answers.
Results that revision 2026-07-28 marks
cacheable — server/discover,
tools/list, prompts/list,
resources/list,
resources/templates/list and
resources/read — carry
ttlMs and cacheScope. Both are
emitted only alongside resultType, so a legacy result
is unchanged.
Anything compiled in or read from the configuration file at
startup is hinted at one hour: none of it can change while the process
lives, and no listChanged capability is declared, so
there is no mechanism by which it could. Anything read from a live catalog
is hinted at one minute. This matters most for
tools/list, which is roughly 170 kB once 76
operations carry descriptions, annotations, titles and output schemas, and
which a client would otherwise re-fetch whenever it needed the list.
tools/list is scoped
private rather than public.
Its content varies by negotiated revision, while the cache key is the method
and its parameters and the revision travels in _meta;
and its connection property names the configured
default connection. Neither belongs in a cache a shared gateway may serve to
another caller. A private cache still serves the calling client, which is
where the saving lands.
The list operations accept an opaque cursor
and return nextCursor when more results remain; an
unusable cursor is refused with -32602. The page
size is larger than any list this server currently produces, so a client
that ignores cursors still receives every operation.
resources/list is the one that grows, being
connections multiplied by schemas.
Startup opens no connection. The registry is parsed and validated, so a malformed conninfo still fails immediately, but reachability is left to the first call that needs it — otherwise one unreachable database prevents the server from starting at all, taking every other database with it.
A connection is held between calls and closed after 60 seconds
idle, one per configured connection, keyed on the connection name. Every
registry entry carries its own user, so a reused connection never crosses
identities. Idle connections belonging to
pg_licht_mcp therefore appear in
pg_stat_activity for up to a minute after use.
A cached connection can be closed by the server underneath
— idle_session_timeout, a restart, a firewall
that forgot the flow. There is no way to know but to use it, so a failure
discards it and reconnects once; the retry is safe because only
BEGIN and the setup batch have run at that
point.
A fan-out sweep visits up to 16 members at once. This is safe where general concurrency would not be: members of a replication group are distinct servers by definition, and a group sweep already collapses members that would answer identically, so the width is one connection per machine rather than several onto one. The payload stays in configuration order however the members finish.
Running behind a connection pooler remains supported and tested,
and is still worth it across a WAN: a pooler amortises the TLS handshake
across restarts of this process. Note that PgBouncer reads neither
.pgpass nor a libpq service
definition, so its [databases] section duplicates
whatever the connections file already says; and in transaction mode it does
not run server_reset_query at all unless
server_reset_query_always is set.
Every session on a database runs inside a transaction opened with
SET TRANSACTION READ ONLY and ends in
ROLLBACK. Nothing is ever committed: there is
nothing a commit could preserve, and rolling back leaves the server provably
as it was found.
Rollback does not undo backend-local state set by an extension. A
hypopg hypothetical index survives
ROLLBACK, the next transaction, and
DISCARD ALL, which is why
evaluateIndex resets it explicitly rather than
relying on the transaction ending.
Structure is served as resources as well as operations — documents a client may browse and pin into context without a call. The division is volatility: a resource changes only when someone issues DDL, while a reading changes continuously and the model must decide when to take it.
pglicht://conn/schemaspglicht://conn/schema/schemapglicht://conn/schema/schema/table/tablepglicht://conn/schema/schema/functionspglicht://conn/schema/schema/enumspglicht://conn/schema/schema/typespglicht://conn/server/rolespglicht://conn/server/extensionspglicht://conn/server/settingsresources/list is bounded. It enumerates
connections and their schemas and reaches tables, functions, enums and types
through resources/templates/list instead: a database
with 10 000 tables would otherwise produce a 10 000-entry response on a call
clients make eagerly. Enumerating schemas costs one connection per
configured connection; one that cannot be reached is skipped rather than
failing the list.
The topology axes are absent from the URI space by design. A resource names exactly one object in exactly one database, and partial failure across members can only be reported per member, which belongs to operations.
Eight templates, each encoding an order of investigation that is
easy to get wrong. Every one whose tools are privilege-gated opens by
calling checkPrivileges: a restricted role receives
null statistics rather than an error, so a plan built on operations that
will not answer looks like it worked. They are static text with argument
substitution and touch no database until the model acts on them.
diagnose-slow-query
[query_id, min_duration_s]statementStats to a plan to a fix, reading
info.dealloc first so a truncated list is not
mistaken for the whole picture.triage-lock-contention
[connection]diagnose-deadlock.diagnose-deadlock
deadlock_log [connection]Recurrence is established in three stages, where the statement
merely appearing in a view is the finding. Still present in
statementStats on a cluster that is evicting
means it runs often enough to survive eviction, which is what a deadlock
needs; absent settles nothing, since it may have been evicted, reset, or
never tracked. Present in currentActivity means
it is running now, so the next occurrence can be watched rather than the
last one inferred. Live and slow earns
currentLocks, because the chains forming now are
the near-miss version of the one that closed — at which point
triage-lock-contention takes over.
triage-active-sessions
[connection]Begins by distrusting the word “active”:
pg_stat_activity calls a backend active while it
executes a statement, and one waiting on a lock, on a disk read or on a
parallel sibling is executing a statement. Only backends with no wait
event compete for CPU, and that is the count compared against the core
count — a server with fifty active sessions of which forty-five
are lock waits has one blocker and a queue, not a shortage of cores.
Parallel workers are collapsed into their leaders first, since one query
with four workers is five rows and one unit of user concurrency.
The split by wait_event_type then
decides which investigation this is:
triage-lock-contention for locks,
buffer-cache-review for I/O, the parallelism
settings for IPC, the write path for LWLock.
Where the server really is CPU-bound, concurrency is arrival
rate multiplied by duration, so it rises when statements get slower even
though nothing about the load changed.
statementStats says which term moved: more calls
is growth and a capacity answer, the same calls taking longer is a
regression and belongs to diagnose-slow-query. A
connection pooler bounds the damage and makes no statement faster.
triage-disk-space
[connection]Four consumers in order of how fast each can be undone: WAL
pinned by a slot (the usual answer, and the only one that frees a lot at
once), temporary files (the fastest, and the space returns the moment
the statement ends), bloat, and genuine growth. Notes the cause it
cannot see — a failing archive_command
retains WAL and pg_stat_archiver is not read
here — and that a stuck archiver and an abandoned slot look
identical from the size alone.
Says plainly that VACUUM FULL is not a
disk-full action: it needs free space equal to the table and its indexes
before it releases any, and holds an
AccessExclusiveLock throughout. Ranks what can
be freed by reversibility, with dropping a slot marked irreversible for
its consumer.
bloat-and-vacuum-review
[schema]replicationSlots before concluding anything
about bloat: an inactive slot holds back the xmin horizon cluster-wide,
and no autovacuum tuning fixes it.buffer-cache-review
[connection]capacity-check
[connection]replication-slot-review
[connection]subscriptionStats separates one that is stuck from
one that is gone, and disable_on_error in
listSubscriptions identifies the third case, a
subscriber that switched itself off on an apply error and is one command
from resuming. An unconsumed slot holds back the xmin horizon, so vacuum
cannot remove dead tuples anywhere in the cluster, and pins WAL until the
disk fills; both get diagnosed as bloat or as a disk problem. Reads
wal_status, establishes whose slot it is before
proposing anything, weighs max_slot_wal_keep_size
against the disk, and treats dropping a slot as irreversible for its
consumer.plan-schema-change
change [schema,
table]It sends the model to measure instead, and to measure every
object the change touches rather than only the one named: a foreign key
locks the table it references as well as the table it is added to, a
partitioned table means the parent and every partition, and the object
not named is often the busier one. The readings are rows and
most_common_vals from
tableStats, what a rewrite would move from
tableSize, the indexes and constraints that
multiply it from tableDetails, the oldest
running transaction from currentActivity
— then asks for the change to be classified as metadata only, a
scan, or a full rewrite, and for the size at which the answer would
flip. Lock level alone settles nothing: a strong lock held for a
millisecond is safe and a weak one held for an hour may not be, and
duration comes from the measurements.
review-clustering
schema tabletableStats for which columns are out of order,
predicateStats or
statementStats for which of them queries filter
on, rowScatter for what the scatter costs a query,
the write counters for how fast the order drifts, and
tableSize for the space and the exclusive lock a
CLUSTER needs. It asks for the fill factor to be
justified by what it buys: an update that changes an indexed column cannot
stay on its page whatever room is left, so there a fill factor buys
locality and not cheaper updates. "Do not cluster" is a result,
and the more common one.check-role-access
role object
[privilege, connection]USAGE on the
schema, whose error names the table and sends people to the wrong object.
Row-level security is a second
gate, opening only after the grants have already said yes. Enabled with
no applicable permissive policy it is deny-all — every grant
checks out,
has_table_privilege()
would say yes, and the table returns nothing. The owner bypasses it
unless FORCE ROW LEVEL SECURITY is set, which is
why checking as the owner proves nothing about anybody else.
Distinct from checkPrivileges, which
reports which of this server's own operations the connecting role can
run. This prompt is about another role's access to a database
object.
explain-and-fix
sql [params,
schema]Whether params is supplied decides which
plan comes back, and the difference between the two is itself a
diagnosis. Without them a statement carrying $n
placeholders is planned with GENERIC_PLAN, which
is what a prepared statement settles on — frequently the whole
answer to “fast by hand, slow from the application”. The
prompt asks for the comparison in both directions.
Reads estimates against actuals, then buffers
(shared_read is a cold cache or
an oversized working set, not a bad plan), then
Heap Fetches on an index-only scan (a stale
visibility map, fixed by vacuum and not by an index), then time no node
accounts for (it waited). Grounds all of it in
tableStats: row count first, since a sequential
scan of a few thousand rows needs no fix at all, then the analyze
timestamps, then most_common_vals for the skew
that makes one plan right for a common value and wrong for a rare
one.
Ranks fixes by what they cost — analyze, rewrite,
configuration, DDL — and treats “already as fast as it can
be” as a legitimate outcome. An index proposal is shaped
deliberately: existing coverage checked with
duplicateIndexes, equality columns before range,
INCLUDE for an index-only scan, a partial
predicate where the constant is always the same, and the operator class
where the default will not be used.
Verifies before recommending: a rewrite is re-planned and
compared, an index is put to evaluateIndex so
the planner says whether it would take it. Creation is handed to
plan-schema-change, since whether an index is
correct and whether it is safe to build are different questions.
Both this and diagnose-slow-query
close by choosing the kind of fix rather than defaulting to DDL: a query
rewrite, a statistics fix, a configuration change, or DDL, preferred in
that order because that is the order of what they cost — an
ANALYZE is free, a rewrite costs a deploy and
nothing in the database, configuration changes every other query too,
and an index is a write cost paid by every
INSERT and UPDATE for as
long as it exists. A rewrite can then be verified on the spot by
explaining it; an index cannot, so its benefit is presented as the
prediction it is, and its creation handed to
plan-schema-change.
completion/complete offers values for the
conn, schema and
table variables that appear in prompt arguments and
resource templates. A completion is a convenience: an argument it does not
recognise, or a catalog it cannot read, yields an empty list rather than an
error.
DATABASE_URLPG_LICHT_CONFIG--config and before
~/.config/pg_licht/connections.ini.PG_LICHT_BUDGETSPG_LICHT_HOST_RAM_MBhostCapacity. Honoured only with the
single-connection DATABASE_URL form; with a
connections file, use host_ram_mb in the section
instead. See Host capacity.PG_LICHT_HOST_RESERVED_MBPG_LICHT_HOST_VCPUSPG_LICHT_HOST_STORAGEPG_LICHT_HOST_NOTEPG_LICHT_STATEMENT_TIMEOUT_MSDATABASE_URL form; with a connections file, use
statement_timeout_ms in the section instead. See
The statement
ceiling.PGSERVICEFILEPGSYSCONFDIRPGPASSFILEHOMEAny other libpq environment variable is honoured as usual, since connection strings are passed through unchanged.
The pg_licht_mcp utility exits 0 on
success, and >0 if an error occurs.
pg_licht_mcp exits non-zero when no
connection source is given, when the configuration file cannot be parsed or
has unsafe permissions, or when the default connection cannot be reached at
startup, and with status 2 on an unknown option.
Run against a single database given by the environment:
DATABASE_URL="postgresql://user:pass@host/dbname" pg_licht_mcp
The libpq key-value form works too, as does a positional argument or a configuration file:
DATABASE_URL="host=localhost port=5432 dbname=mydb" pg_licht_mcp pg_licht_mcp "postgresql://user:pass@host/dbname" pg_licht_mcp --config ~/.config/pg_licht/connections.ini
Register the server with claude mcp
add:
claude mcp add --transport stdio pg-licht \ -e DATABASE_URL="postgresql://user:pass@host/dbname" \ -- /usr/local/bin/pg_licht_mcp
The --scope flag controls where the
configuration is written:
| Scope | Flag | Stored in |
| Local (default) | --scope
local |
~/.claude.json |
| Project | --scope
project |
.mcp.json |
| User | --scope
user |
~/.claude.json |
Local scope is personal and limited to the current project, project scope is shared with the team through version control, and user scope applies across all of your projects.
A project-scoped server lives in .mcp.json at the repository root; commit it to share it with the team:
{
"mcpServers": {
"pg-licht": {
"command": "/usr/local/bin/pg_licht_mcp",
"args": [],
"env": {
"DATABASE_URL": "postgresql://user:pass@host/dbname"
}
}
}
}
Local- and user-scoped servers live in
~/.claude.json instead. Edit that file with
claude mcp add or claude mcp
add-json rather than by hand, since it also holds per-project
state.
Add the same JSON block under mcpServers in claude_desktop_config.json.
Grok Build reads .mcp.json and ~/.claude.json for compatibility, so a server already configured for Claude Code is picked up automatically. To configure it directly:
grok mcp add pg-licht -- /usr/local/bin/pg_licht_mcp
Environment variables for stdio servers are set in the
configuration file rather than on the command line.
--scope project writes to
.grok/config.toml in the project; omit it for the
user-level ~/.grok/config.toml:
[mcp_servers.pg-licht]
command = "/usr/local/bin/pg_licht_mcp"
env = { DATABASE_URL = "postgresql://user:pass@host/dbname" }
Verify with grok mcp list or
grok mcp doctor pg-licht.
SIGHUP
arrived, and the new configuration is in use; see
Reloading.listConnections and
verifyTopology list it under
invalid with the reason, and a call naming it
returns the reason. Only a file in which no section validates stops the
server.statementStats
and the queryid path of
explainQuery require the extension. The returned
hint gives the setup steps. An equivalent error is returned for
pgstattuple by tableBloat
and indexBloat.pg_wait_sampling,
pg_stat_kcache and
pg_qualstats keep their counters in shared memory,
which exists only when the library is preloaded, and
CREATE EXTENSION succeeds without it. Affects
waitEventProfile,
statementKernelStats,
predicateStats and
suggestIndexes. The hint names the setting, and
for pg_stat_kcache that it must follow
pg_stat_statements.pg_wait_sampling 1.1,
pg_stat_kcache 2.2,
pg_qualstats 2.0, or for
suggestIndexes 2.1. Run
‘ALTER EXTENSION ... UPDATE’.pgstattuple function is installed with
EXECUTE revoked from PUBLIC and granted to
pg_stat_scan_tables, so a role with every read
privilege on the data can still be refused the statistics. The hint names
the grant. Affects tableBloat and
indexBloat.gist,
spgist or brin, which the
extension does not cover. The hint names the supported access methods and
points at pageinspect.ALTER EXTENSION pgstattuple
UPDATE’.25006pg_licht_mcp supports PostgreSQL 14 and
newer, and is verified on 14 through 18.
One feature degrades gracefully below 16.
explainQuery can only plan a normalized
pg_stat_statements statement by using
‘EXPLAIN (GENERIC_PLAN)’, which
requires PostgreSQL 16. On 14 and 15 that path returns an actionable hint
instead; supplying params, which prepares and plans
the statement with real values, works on every supported version.
ioStats requires PostgreSQL 16, where
pg_stat_io was introduced, and returns an error
naming the alternatives on older servers.
bufferCacheSummary requires the
pg_buffercache extension at version 1.4 or later,
where pg_buffercache_summary() and
pg_buffercache_usage_counts() were added. The gate
is on the extension version rather than the server version: an extension
created before 1.4 and never updated is a live catalog entry missing exactly
those two functions, and is reported as out of date rather than absent.
Version 1.4 first shipped with PostgreSQL 16, so on 14 and 15 the operation
reports that instead — there is no 1.4 to update to there —
and points at bufferCacheContents, which reads the
view and is available in every version of the extension.
bufferCacheContents reads the view, which exists in
every version the extension has had. Both need a role with
pg_monitor.
The three shared-memory extensions are gated the same way, on
their own version rather than the server's, because each is packaged
separately for every major: waitEventProfile needs
pg_wait_sampling 1.1, the first with a
queryid in its profile;
statementKernelStats needs
pg_stat_kcache 2.2, the first to split planning from
execution, and reports stats_since from 2.3;
predicateStats needs
pg_qualstats 2.0, and
suggestIndexes 2.1, before which the advisor
returned bare strings rather than suggestions with the statements they
serve. An older installation is reported as out of date, with the
‘ALTER EXTENSION ... UPDATE’ that
fixes it.
Elsewhere, a field is simply omitted where the underlying column does not exist, and normalized where it was renamed. Every operation returns its full result on every supported version; only these individual fields vary:
pg_subscription.subtwophasestate).pg_stat_user_indexes.last_idx_scan); table
n_tup_newpage_upd and
last_seq_scan; slot
conflicting.pg_wait_events); slot
invalidation_reason and
inactive_since; statement
stats_since and
minmax_stats_since.pg_stat_io byte counters.Two views changed shape upstream and are normalized rather than
passed through. checkpointStats reads
pg_stat_checkpointer on PostgreSQL 17 and newer and
pg_stat_bgwriter before it, reporting one set of
field names either way and naming its sources in
source; it also drops the four
pg_stat_wal timing columns that PostgreSQL 18
removed. progressStats reports the PostgreSQL 17
vacuum dead-tuple columns under their own names and labels the unit, since
the old and new columns count different things.
On a Citus worker the shards are ordinary tables that Citus hides
from any client whose application_name does not match
citus.show_shards_for_app_name_prefixes.
pg_licht_mcp therefore sees only the empty
distributed shells there, and the size and statistics operations report a
worker holding gigabytes as holding nothing. It cannot change this itself,
since the setting needs a superuser; on each worker, for the role it
connects as:
ALTER ROLE pglicht_ro SET citus.show_shards_for_app_name_prefixes = 'pg-licht';
pg_licht_mcp first appeared in 2025.
Version 2.0 extended it to full pgAdmin-shaped catalog parity. Version 2.1
made the read-only guard per transaction rather than per session, added
named multi-database connections, and added
explainQuery. This manual page first appeared in
version 2.2.
Daniel Cristian <danielcristian@gmail.com>
Every catalog query is parameterized, using
$1 and $2 bindings or
::regnamespace and
::regclass casts. No schema, table, or search-term
argument is ever concatenated into SQL text.
The one deliberate exception is
explainQuery, which must place the statement under
analysis into the EXPLAIN text because
EXPLAIN cannot take a bind parameter. It is guarded
by a leading-keyword whitelist, a single-statement check,
PREPAREd parameter binding with every value escaped
through libpq, a ModifyTable gate that refuses to
execute anything that writes, a gate that refuses to execute a plan calling
a volatile function, and a bounded statement_timeout.
Whether the statement succeeds, fails or times out, nothing of the call
stays on the connection: its prepared statement is deallocated and
session-level advisory locks are released inside the transaction. The
connection outlives the call — pg_licht_mcp
keeps it for the next one, and a pooler hands it to another client —
so what a statement left on it would otherwise stay.
Three things about explainQuery are limits
and not guards, and the role pg_licht_mcp connects
as is what bounds them. The volatile gate reads function names from the plan
and trusts what each function declares: a function declared
STABLE or IMMUTABLE that has
an effect anyway, a volatile function behind an operator or a cast, and
whatever a function calls inside itself are not seen. Planning alone runs
functions: PostgreSQL evaluates an IMMUTABLE
function of constant arguments, and may evaluate a
STABLE one, while it builds the plan, so they run
without analyze. And EXPLAIN
checks privileges as the statement itself would, with or without
analyze: to have an INSERT,
UPDATE, DELETE or
MERGE explained, the role needs that privilege on
the target table, which pg_monitor and
pg_read_all_data do not give. Granting it is the
operator's decision; without it PostgreSQL refuses the plan of a write.
A request is refused before it is parsed when it is nested deeper
than 128 levels or its line is longer than 16 MiB, with JSON-RPC error
-32600 and a null id, and the server goes on to the
next request.
Every tool call to a database runs inside its own
‘READ ONLY’ transaction, so even a bug
that let a query attempt a write would fail with SQLSTATE
25006 rather than succeed silently.
That guard is deliberately transaction-scoped rather than
session-scoped. Under a connection pooler in transaction mode, such as
PgBouncer with pool_mode = transaction, the server
connection is returned to the pool at commit and the next call may be handed
a different one, so a SET issued once at startup
silently stops applying to later calls. Whether the pooler also resets the
connection it takes back (server_reset_query,
which transaction mode skips by default) changes
nothing here: session state cannot be relied on either way. Transaction
scope is the scope a pooler preserves. The setting cannot be pushed into the
connection string either: PgBouncer rejects GUCs in the startup packet
outright, which is why the options configuration key
is refused.
statement_timeout is set on the same transaction and for the same reason, so no call can occupy a backend indefinitely. See The statement ceiling for the value and how to change it.
pg_licht_mcp never writes, and it never
returns rows from your tables. Every call to a database is a read-only
transaction, so nothing it runs can change data or schema. A PgBouncer
console refuses transactions, so the pooler tools rest on two other guards:
they send only ‘SHOW’ commands, which
are constants in the server with nothing from the caller in them; and the
connection's user should be one of PgBouncer's
stats_users, which may run
‘SHOW’ and is refused
‘PAUSE’,
‘RELOAD’,
‘SET’ and every other command —
so the pooler would refuse a write even if one were sent. The second guard
is the operator's to set up: pg_licht_mcp does not
verify it, and a user from admin_users would work and
would not be refused. Three operations read a user table, and none returns
its rows: checkKey answers only whether a row with
the given primary key exists, though that answer is itself a fact about the
table; rowScatter answers how many rows hold a value
the caller names and how many pages they are on, which is a fact about the
table in the same way; and explainQuery with
analyze executes a statement the caller supplied, only
once its plan is proven not to write and to call no volatile function, and
returns the plan with row counts and timings rather than the rows.
Values that came from your data can still reach the caller, in these places:
currentActivity,
currentLocksWHERE clause or a
statement carries — a password in ALTER ROLE ...
PASSWORD among them. Statements of other roles are hidden unless
the connecting role holds pg_read_all_stats.statementStats,
explainQuerypg_stat_statements, in
which the constants of a planned statement are replaced by
$n placeholders: up to 500 characters in a list,
whole when one queryid is named. Statements of other roles read
‘<insufficient privilege>’
without pg_read_all_stats.tableStats,
columnHistogrampg_stats: the most common
values with their frequencies, and histogram bounds, which are sample
values from the column. PostgreSQL returns them only for columns the
connecting role may SELECT.rowScatterpg_stats lists them, the same ones
tableStats returns, each beside its page counts.
With a value it returns counts alone.explainQuery,
evaluateIndexexplainQueryinvalid input syntax for type
integer’ followed by the value — so for every other
error only the SQLSTATE and its standard name are returned.
With analyze no row is returned, and a
caller can still confirm a value it proposes: the plan's actual row
counts say how many rows met a condition, and a statement can be written
to fail or not depending on a value. That is inherent to executing a
statement the caller wrote and is not prevented. What reaches the caller
reaches whoever operates the assistant, so a table holding personal data
is protected by the connecting role not being able to
SELECT it, and by nothing less.
functionDetails,
tableDetailspoolerStatus,
poolerConnections,
poolerConfigserverSettingsNo operation returns a password
pg_licht_mcp itself holds:
listConnections never returns one and never expands
a service file, listForeignServers returns server
options but never user mappings, and
listSubscriptions omits each subscription's
connection string.
Nor does any operation return a constant recorded by
pg_qualstats, which keeps the literal of every
predicate it samples. predicateStats and
suggestIndexes read neither its
constvalue column nor its example-query functions, and
render a predicate as ‘table.column
op’?, which is why neither appears in the list above.
The connecting role decides the rest. A role without
SELECT on a column gets none of its statistics, and
a role without pg_read_all_stats sees only its own
statements. checkPrivileges reports what the current
role can reach, so the narrowest role that answers the question is the one
to connect as.
| October 13, 2026 | pg-licht |