Review PostgreSQL query plans with a least-privilege fixture

Review a PostgreSQL query plan with a small, least-privilege fixture before treating an agent's recommendation as a production change. This guide uses recorded local observations to separate planner estimates, executed queries, index applicability and database permissions.

Understand what the skill package provides

Microsoft PostgreSQL & Azure Guidance contains the original routing file and all 23 of its references. It covers generic PostgreSQL topics as well as separate Azure workflows. Our download preserves the pinned instructions and license, along with setup and data-access context.

It is an instruction package. It does not start postgres-mcp, connect a database, install an extension or enable a cloud service. Live database actions described by the source need separately configured tools and a user-selected connection. The pinned Microsoft repository distinguishes installing skills alone from installing its larger plugin.

Start by identifying your database version, query and expected result. Read the relevant reference, then check its proposal against the operation you can actually inspect. A source review establishes where the instructions came from; a narrow database experiment answers a different question. The skill selection guide explains how to compare those kinds of evidence.

Build a disposable fixture with a separate reader

Our experiment used PostgreSQL 17.11 and 20,000 generated users in a temporary database. The database and client shared an internal Docker network without external access. The client ran as a non-root user with a read-only root filesystem; database data lived in temporary memory-backed storage. It did not mount or query a production database.

The following SQL reproduces the table shape and distribution used in the experiment. Run setup as the owner of a new disposable database, not as the review account. The schema creation deliberately fails if that schema already exists; do not replace existing objects to make the example run.

CREATE SCHEMA bb_fixture;
CREATE TABLE bb_fixture.users (
  id integer PRIMARY KEY,
  email text NOT NULL,
  active boolean NOT NULL,
  filler text NOT NULL
);
INSERT INTO bb_fixture.users
SELECT i,
       'user' || i || '@example.test',
       i % 4 = 0,
       repeat('fixture', 12)
FROM generate_series(1, 20000) AS i;

CREATE INDEX idx_active_users
  ON bb_fixture.users (email) WHERE active = true;
CREATE INDEX idx_users_lower_email
  ON bb_fixture.users (lower(email));
ANALYZE bb_fixture.users;

For the reader, create a dedicated login without elevated attributes or membership in the owner role. In the disposable database, grant schema usage and table SELECT, with no schema creation or data write privileges. The recorded fixture also revoked PUBLIC's permission to create temporary tables. PostgreSQL's GRANT documentation describes these separate privileges.

Use a hidden password prompt when configuring that login. Keep credentials out of copied SQL, prompts and public evidence. Open a separate connection as the reader and verify current_user, role membership and effective privileges. A read-only tool profile is useful, but its label does not replace database permissions.

Read an estimate before choosing to execute

First inspect a plan without executing the query:

EXPLAIN (FORMAT JSON)
SELECT id, email
FROM bb_fixture.users
WHERE active = true
  AND email = '[email protected]';

In our fixture, the plan used idx_active_users and estimated one row. It did not contain actual row observations. Those are planner estimates based on statistics and configuration, not a measurement of this query's execution.

If the specific statement is appropriate to execute in your fixture, a separate observation can use:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON, TIMING OFF)
SELECT id, email
FROM bb_fixture.users
WHERE active = true
  AND email = '[email protected]';

That run reported one actual row and buffer observations. ANALYZE executes the statement; it is not simply a more detailed preview. Planner costs use cost units, while execution timing fields describe measured time. Do not convert a lower estimated cost into a promised latency improvement. See the PostgreSQL EXPLAIN reference.

Even a SELECT can invoke functions with effects. Do not add ANALYZE to an unknown statement merely because it begins with SELECT. A rollback also does not undo every possible external effect. For this experiment, we reviewed the exact query and ran it only against generated data.

Test whether the index applies to the query

The partial index includes active users. Changing the query to request an inactive user changes that condition:

EXPLAIN (FORMAT JSON)
SELECT id
FROM bb_fixture.users
WHERE active = false
  AND email = '[email protected]';

Our observation did not use idx_active_users; its plan contained a sequential scan. The important finding is that this partial index does not cover that query's predicate. It is not a rule that every sequential scan needs another index.

The expression index gives a second useful comparison. A query on lower(email) used idx_users_lower_email in this fixture. A query on upper(email) did not. Compare the indexed expression and the actual query rather than assuming that two text transformations are interchangeable.

We also observed a sequential scan under an aggregate over all 20,000 rows. That query returned the expected identity sum of 200,010,000. Reading a large share of a table is a different workload from selecting one indexed email. The Using EXPLAIN guide explains how selectivity and plan choices relate.

These are observations for this dataset, version and configuration. Another distribution or statistics state can produce a different plan. Before changing a real index, review its storage, write costs, predicates and workload. The CREATE INDEX reference describes expression and partial indexes as well as the constraints on concurrent builds.

Check the permission boundary with negative cases

The reader could inspect the fixture table, but it could not change data or create an index. We tested actual denied operations as well as inspecting privilege metadata.

Fixture operation	Observed SQLSTATE	What this observation establishes
Reader INSERT	42501	This role lacked the required write privilege
Reader UPDATE	42501	This role could not update the fixture table
Reader CREATE INDEX	42501	SELECT access did not grant table ownership for indexing
DELETE in an explicit read-only transaction	25006	The transaction rejected that write
Owner CREATE INDEX CONCURRENTLY inside a transaction block	25001	That concurrent build was not allowed in the transaction block
Deliberately slow reader query with a 50 ms statement timeout	57014	The configured timeout canceled that statement

After these cases, the fixture still had 20,000 rows, 5,000 active users and the expected identity sum. A permission error is a boundary to explain. It is not a reason for an agent to silently reconnect as the owner or choose another database.

The concurrent-index case tested a rejection, not a successful online index build. It does not measure lock behavior or production availability. Use the exact operation's documentation rather than applying a blanket instruction to add CONCURRENTLY to all index work.

Keep local PostgreSQL and Azure decisions separate

The downloaded references include Azure-specific connection, provisioning, recovery, extension and AI-service workflows. Our local fixture did not exercise them. The service flavor, server version, account access and cost requirements matter before those instructions can be acted on.

Likewise, we did not test row-level security, replication, vector search, extensions or a whole application's tenant isolation. A SELECT-only reader test does not establish an RLS design. Check the effective role, policy and function behavior for that separate task rather than reusing this result as proof.

The instruction files are free under MIT. An AI client, model plan, Azure resource or hosting service may have separate charges. This guide starts none of those services and makes no paid model call. Treat cloud resource creation as a separate, explicit decision with a reviewed target and cost.

Record the result and its limits

The published scenario for Microsoft PostgreSQL & Azure Guidance contains six package/source-integrity observations and 22 local PostgreSQL observations. Its record includes the package version and checksum, server and client image identities, environment, plans and rejected SQL states. The package review continues to report that full skill/agent runtime evaluation has not been performed.

This is an original BB Skills guide, written with AI assistance and checked against the actual fixture evidence and primary PostgreSQL documentation. We did not execute an AI client or postgres-mcp, connect Azure, test every reference, or benchmark a production workload. The recorded plan behavior is a concrete starting point for review, not a complete performance or security certification.

For your own evaluation, retain the exact query, relevant data distribution, role, server version, plan and expected result. Describe what you executed and what remains untested. Review a proposed production change separately from the local experiment, using the application's real workload and operational constraints.