Database tuning usually does not end after finding one slow query.
A PostgreSQL database changes continuously. Query patterns change, data grows, indexes are added, PostgreSQL is upgraded, configuration parameters are modified, and application workloads move in different directions.
That makes one-time database analysis less useful over time.
pgAssistant 3.8.0 takes a more historical approach. The release adds Workload Insights, improves Executive Plan history, and records PostgreSQL environment changes alongside workload measurements. The goal is to help teams compare what changed in PostgreSQL with what happened to the workload afterward.
This article looks at the important changes in pgAssistant 3.8 and how they can fit into a practical PostgreSQL tuning workflow.
What Is pgAssistant?
pgAssistant is an open-source PostgreSQL analysis tool aimed at developers, DBAs, and operations teams.
It combines database inspection, workload analysis, advisors, and implementation planning in one interface. Its core advisors can work without an LLM, while AI assistance is available for scenarios where additional reasoning is useful.
A simplified workflow looks like this:
PostgreSQL
|
v
Collect Information
|
v
Analyze
|
v
Find Problems
|
v
Prioritize
|
v
Apply Changes
Earlier versions were already useful for identifying database issues.
Version 3.8 adds another important step:
Analyze
|
v
Change
|
v
Collect Again
|
v
Compare
|
v
Measure
That historical comparison is the main idea behind the release.
What Changed in pgAssistant 3.8?
The 3.8 release focuses on closing the loop between recommendations and actual workload results.
The main changes include:
Workload Insights
Improved Executive Plan history
PostgreSQL version and configuration tracking
Better comparison between collections
Safer pgTune recommendations for small instances
Support for PostgreSQL pre-release version strings
PostgreSQL 19 parameter generation
The release was announced on September 16, 2026 through the PostgreSQL project news site.
Workload Insights
The biggest addition is Workload Insights.
Instead of looking at one database snapshot in isolation, pgAssistant can compare consecutive measurements collected over time.
For example:
Collection 1
|
v
Database Changes
|
v
Collection 2
|
v
Compare
The comparison can show changes in:
Execution time
Call volume
Query mix
Important queries
Recommendations
PostgreSQL version
Tuning parameters
This gives database teams more context when evaluating a performance change.
Why Historical Data Matters
Consider a query that takes:
Before:
850 ms
A developer adds an index.
The query later runs at:
After:
120 ms
A traditional database dashboard can show the current 120 ms value.
But a historical analysis can help connect the two observations:
850 ms
|
| Index added
v
120 ms
That is much more useful when teams are trying to understand whether a change had a measurable effect.
The tool deliberately does not claim that a correlation proves causation. A query can become faster because of several changes at the same time.
What Workload Insights Compares
pgAssistant 3.8 compares consecutive Collector measurements.
The comparison includes several types of information.
Execution Time
Teams can see how query execution time changes between measurements.
For example:
Collection A:
Total execution time = 4.2 hours
Collection B:
Total execution time = 2.7 hours
This provides a high-level view of workload behavior.
Call Volume
A query may become more expensive simply because the application starts calling it more often.
For example:
Execution time per call:
50 ms -> 48 ms
Calls:
100,000 -> 900,000
The query itself became slightly faster, but the overall workload impact could still increase substantially.
Looking at execution time and call volume together gives better context.
Query Mix
Application behavior can change.
A new application release might introduce a new query pattern, while an older query becomes less common.
Workload Insights can show changes in the query mix between measurements.
This can help answer:
Did the database become slower?
Or did the application start doing different work?
That distinction matters when investigating performance regressions.
Finding the Queries With the Biggest Impact
A database can have thousands of SQL statements.
You usually do not want to investigate every query equally.
Workload Insights highlights queries responsible for significant gains or regressions in the workload.
For example:
Query A
+20% impact
Query B
-5% impact
Query C
-35% impact
The exact interpretation depends on the measurements, but the idea is to focus investigation on queries that materially affect the workload.
This is more useful than simply sorting queries alphabetically or looking only at the slowest individual statement.
Executive Plan History
pgAssistant already provides an Executive Plan that combines recommendations into a prioritized view.
Version 3.8 improves the historical view of that plan.
Teams can compare consecutive collections and identify findings that are:
New
Persistent
No longer detected
For example:
Collection 1
Missing index
Duplicate index
Configuration issue
Later:
Collection 2
Duplicate index
Configuration issue
The missing-index finding is no longer detected.
That is useful information, but it should not automatically be interpreted as proof that somebody created the index.
The workload may have changed.
The schema may have changed.
The query may no longer be present.
pgAssistant deliberately keeps that distinction clear.
New Findings vs Persistent Findings
Historical classification makes database maintenance easier to organize.
Imagine:
New
----
Missing foreign-key index
Persistent
----------
High sequential scan workload
No longer detected
------------------
Duplicate index
The database team can then prioritize its work.
A persistent problem may deserve more attention than something that appeared only once.
A new problem may need investigation before it becomes a production issue.
Tracking PostgreSQL Configuration Changes
Performance changes are often related to configuration.
Suppose a team changes:
shared_buffers
work_mem
max_connections
and later observes a workload improvement.
Without historical context, it can be difficult to remember exactly when the configuration changed.
pgAssistant 3.8 stores PostgreSQL version information and important tuning settings as part of the collected evidence.
The timeline can therefore look like:
Collection A
PostgreSQL Version: X
Configuration: A
Workload: A
|
| Change
v
Collection B
PostgreSQL Version: Y
Configuration: B
Workload: B
This provides better context during performance investigations.
PostgreSQL Version Changes
A PostgreSQL upgrade can affect query performance.
For example:
PostgreSQL 17
|
| Upgrade
v
PostgreSQL 18
The workload may behave differently after the upgrade.
pgAssistant 3.8 records PostgreSQL version information alongside workload measurements, making version changes visible when comparing collections.
This is particularly useful during upgrade analysis.
It does not prove that the PostgreSQL upgrade caused a particular performance change.
It simply gives the team another piece of evidence to investigate.
pgAssistant and PostgreSQL 18
This release is also relevant for teams running PostgreSQL 18.
PostgreSQL 18 introduced several performance-related capabilities, including asynchronous I/O, planner improvements, and optimizer statistics preservation during pg_upgrade.
When a database is upgraded, teams should not rely only on whether the application starts successfully.
A better process is:
Before Upgrade
|
v
Collect Workload
|
v
Upgrade PostgreSQL
|
v
Collect Again
|
v
Compare
|
v
Investigate Changes
Historical workload data can make that process easier.
Safer pgTune Recommendations
pgAssistant 3.8 also changes how pgTune handles worker-related recommendations on small instances.
The release notes state that pgTune now clamps worker and parallel-worker settings to the CPU count supplied to the calculation. This is particularly relevant for small PostgreSQL instances with one or two CPUs.
For example, suppose an environment has:
CPU:
2
A generic tuning calculation should not recommend worker settings that assume substantially more CPU resources are available.
The basic principle is:
Available CPU
|
v
Calculate Settings
|
v
Respect Resource Limit
This makes recommendations more appropriate for small instances.
Why Small PostgreSQL Instances Need Care
A configuration that makes sense for a large database server may be inappropriate for a small development environment.
For example:
Development
2 CPU
4 GB RAM
is very different from:
Production
32 CPU
128 GB RAM
Database tuning needs to consider the resources actually available.
A recommendation that ignores CPU constraints can create unnecessary contention.
PostgreSQL Pre-Release Version Support
pgAssistant 3.8 also improves version parsing for PostgreSQL pre-release versions.
Examples include:
19beta3
and other beta, release-candidate, and development version formats.
This is useful for teams testing upcoming PostgreSQL releases.
However, pre-release software should not automatically be treated like a production release.
pgAssistant treats unreleased PostgreSQL branches as review-only rather than presenting them as already released production versions.
PostgreSQL 19 Parameter Generation
The release also adds PostgreSQL 19 parameter generation.
This matters for teams evaluating the next PostgreSQL release before moving production systems.
A practical workflow could be:
Current PostgreSQL
|
v
Collect Configuration
|
v
Evaluate New Version
|
v
Review Parameters
|
v
Test Workload
The important part is testing.
Parameter generation should provide a starting point for configuration review, not a reason to copy settings directly into production.
pgAssistant Is Not a Real-Time Monitoring Tool
This distinction is important.
pgAssistant 3.8 adds historical workload analysis, but it is not intended to replace real-time monitoring.
Real-time monitoring answers:
What is happening right now?
Historical analysis answers:
What changed between two measurements?
You generally need both.
A production environment might therefore use:
Real-Time Monitoring
+
Historical Analysis
+
Database Logs
+
Application Metrics
Each provides a different view of the system.
pgAssistant's own documentation explicitly distinguishes Workload Insights from real-time monitoring.
A Practical PostgreSQL Tuning Workflow
The new features make more sense when used as part of a repeatable workflow.
Step 1: Collect Baseline Data
Before making a change, collect PostgreSQL and workload information.
Database
|
v
Collection A
Step 2: Analyze
Look for:
Slow queries
Index problems
Configuration issues
Schema problems
Maintenance issues
Step 3: Prioritize
Do not fix everything at once.
Start with changes that have a clear expected benefit.
Step 4: Make One Controlled Change
For example:
CREATE INDEX CONCURRENTLY idx_orders_customer_id
ON orders(customer_id);
The goal is to make the change measurable.
Step 5: Collect Again
After the system has enough representative workload:
Database
|
v
Collection B
Step 6: Compare
Now compare the two measurements.
Collection A
|
v
Change
|
v
Collection B
|
v
Workload Insights
Step 7: Investigate
If the workload improved, determine why.
If it became worse, investigate the regression.
Step 8: Repeat
Database optimization is not a one-time task.
It is a continuous process.
Example: Measuring an Index Change
Suppose an application has this query:
SELECT id, customer_id, total_amount
FROM orders
WHERE customer_id = 1001;
Before creating an index, PostgreSQL may use a sequential scan depending on table size, statistics, and other factors.
You can inspect the query plan with:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total_amount
FROM orders
WHERE customer_id = 1001;
If the workload justifies it, you might create:
CREATE INDEX CONCURRENTLY idx_orders_customer_id
ON orders(customer_id);
Then run the workload again and collect another measurement.
The important part is not simply:
Index created = success
The useful question is:
Did the workload actually improve?
That is the type of question Workload Insights is designed to help answer.
Do Not Create Every Suggested Index
Database tuning tools can identify potential improvements, but every recommendation still needs engineering judgment.
An index has costs.
It can increase:
Storage requirements
Write overhead
Maintenance work
Vacuum work
Planning complexity
Before creating an index, check:
Query frequency
Query performance
Table size
Write volume
Existing indexes
Storage cost
A recommendation is a reason to investigate, not an instruction to execute blindly.
Use Query Plans Before Changing SQL
A query that looks slow from the application side may have several possible causes.
Use:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
The execution plan can show whether PostgreSQL is using:
Sequential scans
Index scans
Bitmap scans
Nested loops
Hash joins
Sort operations
pgAssistant's Query Advisor also uses execution-plan information when analyzing index recommendations.
This is useful because query syntax alone does not tell you whether an index is actually needed.
Separate Analysis From Maintenance
Database analysis and database maintenance should not always use the same credentials.
pgAssistant's security guidance recommends separating a read-only analysis role from a more privileged maintenance role.
A typical model is:
pgassistant_analyze
|
+-- Read statistics
+-- Analyze schema
+-- Review query plans
+-- Generate recommendations
pgassistant_maintain
|
+-- Maintenance operations
+-- VACUUM
+-- ANALYZE
+-- Statistics reset
The normal analysis workflow should use the lower-privileged account, while maintenance access should be reserved for trusted operators.
This is a good practice for production database tooling in general.
Common Mistakes
Treating a Recommendation as a Command
A tuning recommendation still needs review.
Looking Only at Current Metrics
A current metric does not tell you whether the database improved or regressed compared with last week.
Changing Multiple Things at Once
If you change indexes, SQL, configuration, and PostgreSQL versions simultaneously, it becomes harder to determine which change mattered.
Ignoring Query Volume
A query that runs ten times a day is different from one that runs ten million times.
Ignoring Writes
Indexes can improve reads while increasing write overhead.
Treating Correlation as Proof
If performance improves after a configuration change, that does not automatically prove the configuration change caused the improvement.
Replacing Monitoring With Historical Analysis
Historical comparison and real-time monitoring solve different problems.
Best Practices
Establish a Baseline
Collect workload information before making major changes.
Change One Important Thing at a Time
This makes measurement easier.
Compare Consecutive Measurements
Use historical data to understand how the workload changed.
Track Configuration Changes
Record PostgreSQL upgrades and important tuning changes.
Keep Production Access Restricted
Use read-only analysis credentials where possible.
Validate Recommendations
Review the query plan, workload, and operational impact before implementing changes.
Measure After Deployment
Do not stop after applying the recommendation.
Collect new evidence and compare it with the baseline.
Advantages of pgAssistant 3.8
Capability | Practical Benefit |
|---|
Workload Insights | Compare workload changes over time |
Executive Plan history | See how findings evolve |
Environment tracking | Connect workload changes with PostgreSQL changes |
Small-instance pgTune handling | More resource-aware recommendations |
Pre-release version support | Easier testing of upcoming PostgreSQL versions |
PostgreSQL 19 parameters | Helps evaluate newer PostgreSQL configurations |
These capabilities are most useful when teams already have a regular database analysis process.
Things to Keep in Mind
pgAssistant does not eliminate the need for database expertise.
You still need to understand:
The tool can organize evidence and recommendations, but engineers still need to validate changes.
Summary
pgAssistant 3.8 is focused less on producing another list of PostgreSQL recommendations and more on helping teams understand what happens after those recommendations are acted on.
The main addition is Workload Insights, which compares consecutive measurements and brings together execution time, call volume, query mix, recommendation changes, PostgreSQL versions, and important configuration changes.
The overall workflow is:
Observe
|
v
Diagnose
|
v
Prioritize
|
v
Plan
|
v
Implement
|
v
Collect Again
|
v
Measure
|
+--------> Repeat
That approach is useful because PostgreSQL performance is not static. A query can become slower because data grew, application traffic changed, an index was removed, configuration changed, or the workload itself shifted.
Version 3.8 gives database teams more historical context for understanding those changes.
The most practical way to use it is to establish a baseline, make controlled changes, collect new workload data, and compare the results rather than assuming that a recommendation automatically produced the expected improvement.
Join the conversation! Your thoughts help the community grow.