PostgreSQL gives developers and DBAs many tools for investigating database performance, but the difficult part is often not collecting the information.
The difficult part is connecting the information.
A slow query may be related to an index. An index recommendation may depend on table statistics. A bad execution plan may be caused by inaccurate row estimates. A configuration recommendation may look useful but make little sense for a small production server.
This is where automated database analysis can help.
pgAssistant is an open-source PostgreSQL analysis tool that combines database introspection, workload analysis, specialized advisors, and implementation planning. Its core advisors are deterministic and can operate without an LLM.
With pgAssistant 3.8, the workflow goes beyond generating recommendations. Workload Insights compares historical collections so teams can see changes in execution time, call volume, query mix, PostgreSQL configuration, and recommendations over time.
The important point is that automation does not remove the DBA from the process.
It gives the DBA better evidence to work with.
What Does pgAssistant Actually Automate?
A PostgreSQL performance investigation can involve several different sources of information:
SQL queries
↓
Execution plans
↓
Table statistics
↓
Indexes
↓
Schema design
↓
Configuration
↓
Maintenance
↓
Workload behavior
Checking all of these manually for every database can become time-consuming.
pgAssistant brings these areas together and produces findings that can be prioritized and turned into an implementation plan. Its documented workflow is:
Observe
↓
Diagnose
↓
Prioritize
↓
Plan
↓
Implement
↓
Collect again
↓
Measure
That continuous loop is a major focus of version 3.8.
Automation Does Not Mean Automatic Changes
This distinction is important.
When a database tool identifies a possible problem, there are two very different approaches:
Approach A
Detect problem
↓
Automatically change database
and:
Approach B
Detect problem
↓
Explain evidence
↓
Recommend action
↓
DBA reviews
↓
DBA approves change
↓
Change is implemented
↓
Result is measured
The second approach is much easier to control in a production environment.
A recommendation such as:
CREATE INDEX ...
does not automatically mean that the index should be created immediately.
The DBA still needs to consider:
Write workload
Existing indexes
Storage
Table size
Query frequency
Deployment timing
Maintenance overhead
Application behavior
Automation should reduce investigation effort, not remove engineering judgment.
How pgAssistant Analyzes PostgreSQL
pgAssistant can inspect several parts of a PostgreSQL environment.
These include:
Database configuration
System catalogs
Statistics
Schemas
Relations
Query plans
Workload activity
Indexes
Maintenance information
The tool's documented advisors cover areas such as SQL, schema design, indexes, configuration, autovacuum, and maintenance.
This means a DBA can move from a general finding to the underlying evidence instead of treating the database as a black box.
Query Analysis Starts With the Execution Plan
Consider this query:
SELECT
order_id,
customer_id,
order_date,
total_amount
FROM orders
WHERE customer_id = 42
AND status = 'Completed'
ORDER BY order_date DESC
LIMIT 50;
Looking only at the SQL text does not tell us whether an index is needed.
PostgreSQL may already have an appropriate index.
It may decide that a sequential scan is cheaper.
It may use an existing index but still filter thousands of rows afterward.
The execution plan provides the missing information.
For example:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
order_id,
customer_id,
order_date,
total_amount
FROM orders
WHERE customer_id = 42
AND status = 'Completed'
ORDER BY order_date DESC
LIMIT 50;
PostgreSQL's EXPLAIN shows the selected execution plan, while EXPLAIN ANALYZE executes the statement and adds runtime information. PostgreSQL also notes that EXPLAIN ANALYZE introduces profiling overhead.
pgAssistant's Query Advisor uses execution-plan information when analyzing index opportunities rather than relying only on the SQL text.
Why SQL Parsing Alone Is Not Enough
Imagine a query contains:
WHERE customer_id = 42
A simple analyzer might conclude:
customer_id needs an index
But PostgreSQL may already have:
Index Scan using idx_orders_customer
and the table may contain only a few hundred rows.
In that case, adding another index may provide little value.
pgAssistant considers execution plans, planner estimates, table statistics, predicates, and existing indexes when evaluating index opportunities.
This is a better model:
SQL
+
Execution Plan
+
Statistics
+
Existing Indexes
+
Workload
=
Recommendation
Detecting Residual Filtering
One useful example is an index that is technically being used but does not filter enough rows.
Suppose PostgreSQL produces:
Index Scan using idx_orders_customer on orders
Index Cond: (customer_id = 42)
Filter: (employee_id = 7)
Rows Removed by Filter: 24000
The index is not useless.
It is helping PostgreSQL find rows for the customer.
But the database is still retrieving many rows that are subsequently discarded because of employee_id.
A better composite index may be:
CREATE INDEX idx_orders_customer_employee
ON orders (customer_id, employee_id);
Whether this index is actually useful still needs to be validated against the workload.
The important point is that pgAssistant can reason about the existing access path rather than simply seeing customer_id in the query and stopping there. Its Query Advisor documentation specifically describes analyzing Index Cond, Filter, Recheck Cond, and rows removed by filtering.
Using PostgreSQL Statistics
Execution plans are only as useful as the information available to the planner.
PostgreSQL maintains statistics that help estimate how many rows will match a condition.
For example:
Estimated rows: 100
Actual rows: 125000
That is a significant difference.
The planner expected a small result but the operation actually produced a much larger one.
This can affect decisions involving:
Join strategies
Index usage
Sorts
Aggregations
Memory usage
PostgreSQL recommends keeping planner statistics current, normally through autovacuum and ANALYZE; after substantial changes to table contents, a manual ANALYZE may sometimes be appropriate.
pgAssistant incorporates PostgreSQL statistics into its analysis. Its Query Advisor documentation describes using values such as n_distinct, null_frac, most-common values, and histogram information when evaluating index candidates.
Finding the Difference Between a Finding and a Decision
Suppose pgAssistant identifies:
Potential index improvement
That is a finding.
It is not automatically a deployment decision.
A DBA should ask:
Is this query important?
Is the table large?
How frequently does the query run?
Does another index already cover the workload?
What is the write rate?
How much storage will the new index require?
Will the index help production traffic?
Only after answering those questions should the team decide whether to implement the recommendation.
This distinction is one of the most important concepts when introducing automation into database operations.
Prioritizing Problems Instead of Reading Everything
A large PostgreSQL database can generate many findings.
For example:
12 possible index improvements
8 configuration findings
5 maintenance findings
3 schema findings
2 high-impact queries
Reading them in arbitrary order wastes time.
pgAssistant's workflow includes prioritization based on factors such as urgency, confidence, effort, and workload impact. Its Executive Plan then consolidates recommendations into an ordered remediation plan.
A practical priority model might look like:
High workload impact
+
High confidence
+
Reasonable implementation effort
↓
Investigate first
This does not mean every recommendation at the top of a list should be implemented immediately.
It means the team has a more useful starting point.
The Executive Plan
One of the useful parts of pgAssistant's workflow is the Executive Plan.
Instead of receiving a disconnected collection of recommendations, related findings can be organized into an ordered remediation plan.
The documented plan can include:
Priority
Dependencies
Confidence
Impact
Estimated effort
DEV/OPS ownership
Implementation guidance
Maintenance constraints
Executable SQL
For a development team, this can turn a database finding into a concrete engineering task.
For example:
Finding
↓
High-impact query
↓
Potential composite index
↓
Implementation guidance
↓
DEV/DBA review
↓
Deployment
The DBA remains responsible for deciding whether the recommendation is appropriate.
Why Ownership Matters
Database performance problems are not always DBA problems.
Consider a query that is generated incorrectly by an application.
The database may be performing exactly what it was asked to do.
The solution might require a developer to change:
Application query
rather than:
PostgreSQL configuration
pgAssistant's planning workflow distinguishes work for DEV, OPS, or both.
That can make remediation more practical in larger organizations.
For example:
Finding | Likely Owner |
|---|
Inefficient SQL | Development |
Missing index | DBA / Development |
PostgreSQL parameter | DBA / Operations |
Autovacuum issue | DBA / Operations |
Application-generated N+1 queries | Development |
Schema design issue | Development / DBA |
The exact ownership depends on the organization's architecture and responsibilities.
Using Workload Ranking
A query taking two seconds is not necessarily the biggest performance problem.
Consider:
Query A
2 seconds × 20 calls
= 40 seconds
Query B
20 milliseconds × 500,000 calls
= 10,000 seconds
The second query may deserve much more attention.
Workload analysis helps identify this difference.
pgAssistant's workload analysis and query ranking are designed to help teams identify queries with significant workload impact.
This changes the investigation from:
Which query looks slow?
to:
Which query is consuming meaningful database capacity?
That is a much more useful production question.
Automating the Collection Process
For one database, manual analysis may be enough.
For many databases, it becomes difficult.
Consider an organization with:
Development
├── 20 databases
Staging
├── 30 databases
Production
├── 200 databases
Running a complete analysis manually across every database is not realistic.
pgAssistant's companion Collector project can automate and centralize selected analyses across multiple PostgreSQL databases, while pgAssistant Grafana provides fleet-wide visibility.
A simplified architecture looks like:
PostgreSQL databases
↓
pgAssistant Collector
↓
Central repository
↓
pgAssistant / Grafana
↓
Prioritized findings
This is where automation becomes particularly valuable.
The tool is not just helping one DBA investigate one query.
It can help establish a repeatable database improvement process.
Security Should Be Part of the Automation
Database analysis requires access to database metadata, statistics, schemas, and execution plans.
That does not mean the analysis account should automatically receive broad administrative privileges.
pgAssistant documents separate roles for analysis and maintenance:
pgassistant_analyze
↓
Read-only analysis
pgassistant_maintain
↓
Explicit maintenance operations
The documented recommendation is to use the read-only analysis role for normal sessions and keep maintenance credentials separate for trusted operators.
For example, an analysis role can be configured with safeguards such as:
ALTER ROLE pgassistant_analyze
IN DATABASE my_database
SET default_transaction_read_only = on;
ALTER ROLE pgassistant_analyze
IN DATABASE my_database
SET statement_timeout = '5min';
ALTER ROLE pgassistant_analyze
IN DATABASE my_database
SET lock_timeout = '5s';
These settings provide additional protection, but they should not be treated as a replacement for proper PostgreSQL privileges.
What Happens After a Recommendation?
Suppose the DBA approves an index recommendation.
The process should not stop at:
CREATE INDEX ...
The next question is:
Did the workload actually improve?
This is where pgAssistant 3.8 adds an important capability.
Workload Insights compares consecutive collections and shows changes in:
Execution time
Call volume
Query mix
High-impact queries
PostgreSQL version
Important configuration
Executive Plan findings
The workflow becomes:
Recommendation
↓
Implementation
↓
New collection
↓
Workload comparison
↓
Performance evidence
↓
Next decision
This is more useful than treating database tuning as a one-time exercise.
Example: Measuring an Index Change
Suppose a query originally shows:
Calls: 150,000
Average time: 38 ms
Total execution: High
The DBA reviews the execution plan and approves a composite index.
After deployment, another workload collection shows:
Calls: 148,000
Average time: 12 ms
Total execution: Significantly lower
That is useful evidence.
But it should not be interpreted as automatic proof that the index caused every improvement.
Other changes may have occurred between collections.
pgAssistant explicitly distinguishes correlation from causation, and a recommendation disappearing from a later analysis does not automatically prove that the recommended change was implemented.
This is exactly where human review remains important.
What pgAssistant Should Not Replace
Automation is useful, but there are areas where traditional database engineering remains necessary.
Real-Time Monitoring
pgAssistant's Workload Insights is based on collected historical measurements.
It is not a replacement for real-time monitoring and alerting. The project explicitly distinguishes its improvement workflow from monitoring systems that show what is happening right now.
A production environment should still have monitoring for:
CPU
Memory
Disk
Connections
Lock waits
Replication
Query latency
Errors
I/O
Database availability
Application Performance Monitoring
A database query may be fast while the application is still slow because of:
Network latency
Serialization
Application processing
Connection pooling
External APIs
Database analysis cannot explain every application-level bottleneck.
Business Context
A recommendation may technically improve a query but have little business value.
A DBA still needs to understand which workloads are important.
A Practical DBA Workflow
A good workflow with pgAssistant can look like this:
Step 1: Collect Evidence
Start with database structure, configuration, statistics, and workload information.
Step 2: Review Findings
Look at the most important findings rather than trying to fix everything.
Step 3: Validate the Evidence
Inspect the query, execution plan, statistics, and existing indexes.
Step 4: Prioritize
Consider:
Impact
Confidence
Effort
Risk
Business importance
Step 5: Assign Ownership
Determine whether the change belongs to:
Development
DBA
Operations
Development + DBA
Step 6: Implement Carefully
Test the change in an appropriate environment before production deployment.
Step 7: Collect Again
Capture another workload measurement.
Step 8: Compare
Use Workload Insights to understand what changed.
Step 9: Continue
If the workload improved, move to the next important issue.
If it did not, investigate the evidence again.
Common Mistakes
Automatically Applying Every Recommendation
A recommendation is not a deployment command.
Review it first.
Optimizing Only Individual Queries
Database performance is a workload problem as much as it is a query problem.
Ignoring Existing Indexes
A new index may duplicate an existing access path.
Ignoring Write Performance
Every additional index can add maintenance work to data modifications.
Treating Historical Correlation as Proof
A performance change occurring after a deployment does not automatically prove that the deployment caused it.
Giving Analysis Accounts Excessive Permissions
Use the least privilege necessary for the analysis workload.
Replacing Monitoring With Historical Analysis
Historical workload analysis and real-time monitoring answer different questions.
Best Practices
When using pgAssistant in a production PostgreSQL environment:
Start with read-only analysis.
Prioritize findings using workload impact and confidence.
Review execution plans before approving index changes.
Check existing indexes before creating new ones.
Verify PostgreSQL statistics when planner estimates look suspicious.
Assign findings to the correct development or operations team.
Test significant changes before production deployment.
Make one meaningful change at a time when investigating performance.
Collect workload data after an intervention.
Compare before and after measurements.
Keep maintenance credentials separate from normal analysis credentials.
Continue using real-time monitoring alongside pgAssistant.
Treat recommendations as evidence-based guidance, not automatic truth.
Document why a production change was approved.
Revisit persistent findings instead of assuming every recommendation must be fixed immediately.
Advantages and Limitations
Advantages
Automates much of the initial database investigation.
Combines multiple PostgreSQL information sources.
Uses execution plans and statistics for query and index analysis.
Helps prioritize findings instead of presenting only raw data.
Provides an Executive Plan for remediation work.
Supports separation between development and operations responsibilities.
Version 3.8 adds historical Workload Insights.
Can be extended to larger PostgreSQL fleets through Collector and Grafana.
Limitations
Recommendations still require human validation.
It does not replace real-time monitoring.
Database changes still carry operational risk.
Historical correlation does not prove causation.
Application-level problems may require application profiling.
A technically correct recommendation may not always be the highest business priority.
Summary
pgAssistant 3.8 is useful because it automates the difficult first part of PostgreSQL performance work: collecting evidence, analyzing database behavior, finding potential problems, and organizing those findings into a practical improvement workflow.
Its Query Advisor can use execution plans and PostgreSQL statistics to reason about query and index problems instead of relying only on SQL syntax. Its broader advisors cover areas such as schema, configuration, maintenance, and workload analysis.
Version 3.8 adds another important piece: Workload Insights. Teams can collect the database again after making a change and compare execution time, call volume, query mix, recommendations, and environment changes over time.
The best way to use this automation is not to remove the DBA from the process.
It is to give the DBA better evidence.
The practical model is simple:
Automate the investigation
↓
Review the evidence
↓
Make the engineering decision
↓
Implement carefully
↓
Measure the result
That balance makes database automation useful without turning production PostgreSQL changes into a black-box process.
Join the conversation! Your thoughts help the community grow.