Welcome to the January 2023 edition of the pganalyze newsletter!
I hope you had a good start to the year and that you and your loved ones are safe and healthy. And to our friends in Ukraine, I want to once again express my full support and hope for a better future.
At pganalyze, we've been busy working on new autovacuum tuning functionality - and we've just added new per-table metrics on why Postgres started a particular anti-wraparound VACUUM, more below. We are also starting a new early access program for autovacuum tuning - if you're interested to join, let us know!
Today, we're also introducing the improved pganalyze documentation with a new navigation sidebar and redesigned overview page. Further, we've started off with season 2 of our "5mins of Postgres" video series on YouTube, and share that we've successfully passed our SOC 2 Type 2 audit (compliance teams, rejoice!).
If you are a current pganalyze customer (or planning to be), feel free to request our SOC 2 Type 2 report by responding to this newsletter - and soon available in-app.
All the best,
Lukas
New Autovacuum Metrics & Upcoming Features
Behind the scenes, we've been making steady progress towards new metrics and upcoming features for configuration tuning - starting with autovacuum. The start may seem subtle, but it leads to the bigger picture: With this month's collector releases (0.48.0 is the latest), we are now tracking more detailed TXID wraparound metrics.
These new metrics are available immediately on the per-table VACUUM page (below the "Dead Rows" graph), showing exactly when an anti-wraparound started because too many new transaction IDs were assigned, reaching the autovacuum_freeze_max_age threshold.
We have a lot more coming! If you would like to get early access to more autovacuum metrics, and be first in line when new tuning recommendations are available for testing, we invite you to our early access program*:
* Our program is focused on existing pganalyze customers willing to schedule a short 45min interview with us to learn about your VACUUM challenges, and who would like to give us feedback as we improve the new functionality.
The pganalyze documentation got a major refresh! Providing clear and concise instructions for pganalyze and explaining the inner workings of Postgres is important to us - we see our docs as a place not exclusively for our users, rather anybody that might run into a common Postgres error or question.
But, we started to outgrow the simple, one-column design. We implemented:
1) A sticky navigation sidebar that helps with keeping an overview of related topics and makes it easier to navigate
2) An overhaul of our overview page to highlight better where to start reading
3) Additional feature documentation, to learn more on how pganalyze works (starting with the Connections page for our launch of the new Lock Monitoring feature last month)
We'd love to hear your feedback, and learn what we can improve further with the pganalyze docs!
Over the last year we've spoken with dozens of data platform teams, small startups, and large enterprises migrating from legacy databases to Postgres in the cloud.
As we've reflected on how our product vision fits the bigger picture, we've decided to refresh the pganalyze website, and our messaging. We've moved on from individual insights, to focusing on the overall goal: Achieving consistent database performance and availability, for whichever workload you are using Postgres for.
We want to thank each and everyone of you who took the time to speak with us and walk us through their Postgres (and pganalyze) setups, and we look forward to continue the conversation!
We look at generate_series and pgebench, and its initialization options, to generate test data for Postgres. We also show the new random_normal function in PG16.
We look at the Memoize plan node and how enable_memoize can speed up performance by up to 1000x. We also walk through cases where memoize won't have much effect.
We talk about the createrole_self_grant option in Postgres 16, which lets us create a role that can administer the database but not break out of it. We are also looking at the pg_use_reserved_connections role.
Subscribe on YouTube
We publish weekly videos on Postgres, our conference talks, tutorials, product walk-throughs, and more.
Send additional Postgres table stats (relpages, reltuples, relallvisible) and additional Postgres transaction ID / freezing stats
Server level: current TXID and next MXID
Database level: age of datfrozenxid and datminmxid, also xact_commit and xact_rollback
Table level: age of relfrozenxid and relminmxid
Amazon RDS and Aurora:
Support IAM token authentication, enabled via db_use_iam_auth (Thanks Alexandre Viau for the contribution!)
Avoid DescribeDBInstances call for log downloads (this should reduce rate limiting errors with this AWS API call)
Cache failures in DescribeDBClusters calls for up to 10 minutes
Improve RDS log processing for large log file sections
Aiven: Improve Service ID and Project ID detection from hostname
Heroku: Ignore non-Postgres URIs in environment (Thanks Théo Bougé for the contribution!)
Azure CosmosDB for PostgreSQL, and open-source Citus extension: Collect Citus distributed index sizes
Log Insights:
Add support for timezones specified by number, such as "-03"
Warn if log lines will be ignored due to verbose logging settings
Fix error handling when fetching stats
The missing checks could previously lead to incomplete snapshots, possibly resulting in tables or indexes temporarily disappearing in pganalyze
Index Advisor
Fix cost improvement calculation with existing indexes (due to this bug, you may have seen exaggerated cost improvements in some situations)
Fix processing of servers with tables involving simple sequential scans (due to this bug you may have seen recommendations missing on some servers)
Fix issues with extensions and non-public search_path
Better handle complex relationships involving table inheritance and foreign keys
VACUUM monitoring & tuning
Add Frozen XIDs graph to per-table VACUUM page (highlights why new anti-wraparound VACUUMs started)
For servers without much activity, fix setting of end time of "VACUUM runs" in pganalyze
Behind the scenes we're now storing the additional data from the new collector releases (see above) and are testing new per-database and per-server overview pages to monitor TXID Wraparound
Other improvements
Lock monitoring: Add link from blocking query alert page to Connections overview (makes it easier to see the individual blocked queries)
Fix issue that caused system information to be out of date (e.g. not updating the instance class of an Amazon RDS instance on the System page)
Default empty query text to '<unknown query text>' (this can happen if the pg_stat_statements query text file gets too large and the query text cannot be loaded)
We're now using 64-bit query fingerprints for all queries (as introduced by the pganalyze-collector 0.38.0 almost two years ago)
If you are on a very old collector release you may have seen a change in query statistics, as we're now transforming the old ID format on the server side (upgrading to a newer collector is recommended)
Review us on G2
Please leave your feedback (good or bad!) in this survey - we appreciate it!
