Hi there,

Welcome to the July 2022 issue of our pganalyze newsletter - we hope you've had a good month!

Today, one of our main focus points is Lock Monitoring. Lock Monitoring is an important aspect of operational best practices for Postgres, and you may also be familiar with "blocking sessions" from databases such as Oracle. We recently talked about the importance of lock monitoring and took a closer look at the most important things to know.

Behind the scenes, we've been working on a better way to represent lock information in the Connections view in pganalyze to help you quickly identify the root cause of active queries being blocked by a lock.

In this newsletter you will also find our latest 5mins of Postgres episodes as well as details around the new Enterprise Server release.

- - -

Upcoming Lock Monitoring feature in pganalyze

We've been busy working on new functionality that helps improve the reliability and overall operations of your Postgres database.

To start with, we've been developing a new Lock Monitoring feature that utilizes a combination of the pg_blocking_pids() function and pg_locks view in Postgres to give you details on which active queries are blocking other queries, including following a chain of pending lock requests. Note that this will be most helpful for an active ongoing production incident (or when reviewing such an incident after the fact), where you may have had to resort to complex queries from the Postgres wiki pages before.

We're not ready to share this new feature publicly just yet, but would be happy to hear from you if you have thoughts on this topic, or would like to try an early version.

- - -

New pganalyze Enterprise Server release (v2022.08.0)

We've had many requests for the new pganalyze Index Advisor and pganalyze Indexing Engine to be available for our Enterprise Server customers. The wait is over, and we're excited to share this new release with you. When upgrading, make sure to run the database migrations. You will find per-table index recommendations on the new "Index Advisor" page within 48 hours after upgrading.

Read the full pganalyze Enterprise Server v2022.08.0 release notes→

- - -

We are hiring a Postgres Solutions Engineer at pganalyze - join our team:

Are you or one of your peers interested in helping pganalyze customers make effective use of the Postgres observability and automatic tuning recommendations provided by pganalyze? The work involves having a deep understanding of Postgres itself, and being able to jump into the details to figure out how Postgres behaves in certain situations and to guide customers towards a comprehensive solution. You will also help showcase new functionalities added to the pganalyze product, such as developing demos for the new automatic indexing functionality provided by the pganalyze Indexing Engine. We also welcome referrals to someone you think could be a good fit:

Open position: Postgres Solutions Engineer→

- - -

5mins of Postgres - episodes from July:

E25: Postgres lock monitoring, LWLocks and the log_lock_waits setting→

We talk about Postgres locking. When it is concerning, what lightweight locks are, as well as the log_lock_waits setting in Postgres.. (share this episode here)

E26: New in Postgres 15 - NULL handling in UNIQUE constraints & Parallel DISTINCT→

We walk through the new unique constraint NULLS NOT DISTINCT option, and the SELECT DISTINCT performance improvements in Postgres 15. (share this episode here)

E27: Waiting for Postgres 16 - Make subquery alias optional in FROM clause→

We talk about Postgres 16 removing the alias requirement for subqueries in FROM, enabling easier migrations from Oracle to Postgres. (share this episode here)

E28: Row Level Security in Postgres, security invoker views and why LEAKPROOF functions matter→

We're talking about Postgres Row Level Security, the BYPASSRLS attribute, security invoker views, and Postgres performance problems when using LEAKPROOF functions and Row Level Security policies. (share this episode here)

Subscribe to the pganalyze YouTube channel to stay up to date about our weekly videos→

- - -

Product changes in July:

• Index Advisor
  • Initial support for PostGIS
    • This resolves issues around query analysis failing with data type errors
    • The Indexing Engine can now determine scans based on queries involving PostGIS data types and functions
    • We don't support index recommendations for PostGIS just yet, but other columns on such tables will now receive recommendations
  • Filter out duplicate clauses in scans
    • This did not affect the recommendations themselves, but lead to confusing "column_a = $1 AND column_a = $1" expressions
  • Track extensions that are installed on each database and load schema definitions based on it
    • This was previously handled via heuristics based on data types / index opclasses, this new approach is more accurate
  • Handle sub-SELECTs inside cast expressions

  • Handle "(SubPlan 1)" clauses correctly

• Single Sign-On: Allow entering SAML IdP metadata manually instead of via metadata URL
  • This enables easier configuration for providers that list out the configuration instead of providing the metadata URL
• Log Insights: Filter out log_statement=all and log_duration=on log lines
  • This replaces the previous behavior that prevented all log collection for servers that had either log_statement=all or log_duration=on enabled.
  • With the new logic, we continue ignoring these high-frequency events (which would cause downstream problems), but accept all other log events, including threshold-based auto_explain events.
• Add support for Google AlloyDB for PostgreSQL
  • This adds new options to specify the AlloyDB cluster ID and instance ID
  • Special cases the log parsing to support AlloyDB's [filename:line] prefix
  • Supports AlloyDB's modified autovacuum log output
• Add support for Aiven for PostgreSQL
  • Support was previously available via the self-managed instructions, but this adds explicit support and improved setup instructions
  • Existing Aiven servers that were detected as self-managed will be automatically updated to be recognized as Aiven servers
• Self-managed servers: Allow using pg_read_file to read log files (instead of log tail / syslog)
  • This relies on the built-in Postgres function pg_read_file to read log files and return the log data over the Postgres connection.
  • This requires superuser (either directly or through a helper) and thus does not work on managed database providers, with the exception of Crunchy Bridge, for which this is already the mechanism to fetch logs.
  • Additionally, this carries higher overhead than directly tailing log files, or using syslog, and thus should only be used when necessary.
  • Set db_log_pg_read_file = 1 / LOG_PG_READ_FILE=1 to enable the logic
• Self-managed servers: Support disk statistics for software RAID devices
  • These statistics are summarized across all component disk devices and then tracked for the parent software RAID device as one. Note that this is only done in case these statistics are not yet set (which is the case for the typical Linux software RAID setup).
• pganalyze Enterprise Server v2022.08.0
• New pganalyze Index Advisor, streamlined onboarding experience & more
• See full release notes
• pganalyze collector 0.45.0
  
  • Changes included in overall list, see full release notes
• Improved Get Help experience
• Bugfixes
  • Fix storing VACUUM+ANALYZE events that have the exact same timestamp on the same table
    • Previously the ANALYZE even would not have been recorded in such situations, potentially leading to inaccurate "stale stats" EXPLAIN insights

  • Organization settings

    • Only count servers with recent data for usage statistics

    • Fix inaccurate member counts for member roles tab
  • EXPLAIN plans: Add information for Incremental Sort node
  • Crunchy Bridge: Fix collection of system metrics
  • Heroku Postgres: Fix blank log line parsing
  • Fully qualify constraint definitions, to support non-standard schemas

 - - -

As always, I'm happy to hear from you and am personally reading all of your responses. Looking forward to your thoughts!

Have a nice day!

Lukas & the pganalyze team

PS: If you're interested in giving pganalyze a try you can learn more about it here. Of course, I'd be happy to walk you through a demo on a video call. You can book a 30 minute meeting with me here. Or, if you have general requests, feel free to get in touch with us here.