Welcome to the August 2023 edition of the pganalyze newsletter!
We start today's issue with the news around the Postgres Language Server project which is actively being worked on in the open by the Supabase team.
A language server adds features like auto-complete, go-to-definition, documentation on hover, and others to IDEs. There have been attempts at adding Postgres support to code editors before, but Supabase's approach is different than previous ones. It uses the actual Postgres parser to do the heavy-lifting. This is done via pganalyze's libpg_query, a C library for accessing the PostgreSQL parser outside of the server.
The Supabase team have verified that their approach works, but most of the work is still ahead. They're making the project public so that they can develop it in the open with input from the community. We're excited to see where this is going and are happy to see libpg_query be used in such great ways.
In this newsletter you will further find pganalyze product updates and our 5mins of Postgres episodes.
We show when good correlation is not enough. Using a BRIN index can actually be slower than not using an index at all. We show how the new multi minmax operator class helps find outliers in BRIN indexes.
We talk about optimizing bulk load performance in Postgres and how the COPY ring buffer works. We're comparing different methods of INSERTs and show why COPY is the fastest option. We're also looking at pgbench and pgbuffercache to show client side vs server side performance.
Here, we walk through how partition-wise joins can improve your Postgres query performance. We also look at partitionwise-aggregates and their impact on query planning time and query execution time.
Subscribe on YouTube
We publish weekly videos on Postgres, our conference talks, tutorials, product walk-throughs, and more.
This release introduces the pganalyze VACUUM Advisor
Added inserted rows graph to VACUUM Activity of the Schema Statistics page
Changes to Index Advisor like improved handling of check constraints and supporting queries containing COALESCE and other expressions in the LIMIT clause
Updated bundled collector version to release 0.51.1 with improvements for Autovacuum handling, self-managed servers, and more.
Several further performance improvements and bug fixes you can read about here.
VACUUM Advisor
Improve performance of loading xmin horizon data
track how many inserts happened since the last vacuum (n_ins_since_vacuum)
Move issue indicator on bloat tab table listing to settings columns
Previously, tables in this list that have an open VACUUM Advisor bloat issue would be flagged with an info icon in the 'Est. Bloat' column, and other tables would be flagged with a check mark to indicate there is no open issue. However, this could be misconstrued to mean that the bloat on these tables is not a problem. This is not necessarily the case: The "Insufficient VACUUM Frequency" check only looks for growth in bloat, not the absolute level of bloat. Tables could still have a problematic amount of bloat without an open issue. Moving the icons to the settings columns makes it clearer that the check is focused on verifying whether these VACUUM settings are adequate, and not on checking the absolute level of bloat.
Index Advisor
Fix sorting of unused indexes for which an index write overhead could not be calculated
Fix column finding problem due to the table referenced wrongly
This fixes errors like `column "column_1" does not exist`, when there are several tables with the same name and one table does not have "column_1".
Improve handling of check constraints
Allow Index Advisor to process schemas containing more CHECK constraints, including constraints marked as NOT VALID.
Log Insights
Fix log details rendering error with logs contain index vacuum information
Alerts & Checkup
Fix addition of more matching PIDs after connection-related alerts are triggered
Due to a bug in the handling of connection related alerts (Active Queries, Idle Transactions or Blocking Queries), the list of references for each issue which highlights the specific PIDs that were matched by the alert was not correctly updated if additional PIDs were found that matched the condition after the issue was first detected. This did not affect the state of the issue overall, just the display of connections and their PIDs on the issue page, which may have sometimes been incomplete, or empty due to this bug.
Query performance
Fix loading time issue for servers with a lot of roles
Query analysis
Support queries containing COALESCE and other expressions in the LIMIT clause
Further updates and improvements
Support setting a default role for new members onboarded via SAML
Previously, "View & Modify (All Servers)" role was assigned to any new members onboarded via SAML and there was no way to change this behavior. With this change, the default role can be set during the integration setup. It also can be updated to any member roles of the organization from the Integrations tab in the Settings page."
Add index scans per minute stats in the Indexes tab in Schema Statistics page
This scans per minute stats is calculated using last 24 hours data. You can view the data for index scans per minute over time on the index details page.
Add inserted rows graph to VACUUM Activity of the Schema Statistics page
Starting from Postgres 13, autovacuum is triggered based on the number of rows inserted since a table was last vacuumed. Currently, the VACUUM/ANALYZE Activity tab of the Schema Statistics page shows the graph illustrating the number of dead rows along with its corresponding autovacuum threshold, as well as the oldest unfrozen XID age along with its corresponding autovacuum threshold. These graphs have been useful to correlate the VACUUM activity over time. The introduction of the graph of inserted rows (since a table was last vacuumed) along with its corresponding autovacuum threshold provides a comprehensive overview of how autovacuum is triggered.
Improve time range selection handling on charts
If selecting a time range right up against the edge of a chart, it was easy to lose the selection previously, since it would be cleared immediately when the mouse left the chart area. Now we give a small grace period before clearing the selection, making it easier to select right up to the available data.
Review us on G2
Please leave your feedback (good or bad!) in this survey - we appreciate it!
