Welcome to the November 2022 issue of the pganalyze newsletter!
Whilst it's most certainly December now, we wanted to make sure to include a roundup of new Amazon RDS and Aurora announcements from AWS re:invent last week.
In pganalyze news, we've launched our new lock monitoring feature, including alerting that helps you catch blocked queries quickly.
Looking ahead, we're continuing work on our new VACUUM monitoring and tuning recommendation features, and wanted to thank everyone who joined our webinar on autovacuum tuning! (you can watch the re-run here). As always, you can also find our 5mins of Postgres episodes in this newsletter.
All the best,
Lukas
What's new
š New pganalyze Lock Monitoring feature launched!
Have you ever encountered a Postgres locking problem, and ended up copy and pasting a very large query from the Postgres wiki to find the root cause of queries waiting for locks?
With the new pganalyze Lock Monitoring feature you can immediately see which query is holding locks, as well as identify which queries are caught up in the situation due to the way the lock queues work in Postgres.
pganalyze now walks the lock tree for you across multiple levels, shows you the root cause on the Connections page, and can alert you by email, Slack or PagerDuty when a certain number of queries is waiting for a lock for too long.
For those of you who made it out to AWS re:invent in Las Vegas last week, we hope you made it safely back home, and had a good time! Here are our Top 3highlights of Postgres-related news from last week:
1. New Trusted Language Extensions (pg_tle) capability
Postgres 14+ on RDS and Aurora now lets you run "CREATE EXTENSION" with a custom extension definition
This initial announcement is quite basic, but we can see the potential here once more hooks are accessible (personally, I'm wishing for a way to extend the Postgres planner!)
This new feature makes it easy to spot suspicious logins to your RDS databases through the GuardDuty system
If you want more ongoing details about your connection activity, we can recommend using pganalyze Log Insights - it can highlight all connection events (be they suspicious or not!)
In general, Postgres needs to be operated with "full_page_writes = on" for safety reasons, to protect against torn page writes
EBS volumes can now protect against torn page writes, ensuring 16 KiB write operations are atomic (note this is not yet available on RDS for Postgres, but we're hoping that'll change soon)
Learn about a surprising case of a very large integer causing a Sequential Scan in Postgres. There are cases when Postgres decides to not use an index, but instead opts for a Sequential Scan. This can cause some very real performance problems.
We talk about logical replication improvements in Postgres 15. In particular, we talk about improvements to data filtering, why the REPLICA IDENTITY is important, and how logical replication deals with errors.
Learn more about reducing replication lag by setting the maintenance_io_concurrency setting in Postgres 15. We're also talking about true asynchronous I/O in Postgres and how to use "recovery prefetch".
We talk about the difference regarding security and performance between the ANY and the IN operator in Postgres and explain when you might use one over the other.
In this episode we take a deep dive on a recent announcement from AWS re:Invent. pg_tle lets you create trusted language extensions for Amazon RDS and Aurora.
Subscribe on YouTube
We publish weekly videos on Postgres, our conference talks, tutorials, product walk-throughs, and more.
