Hi there,

Welcome to the March 2022 issue of the pganalyze newsletter!

Today, we take an in-depth look at how Postgres chooses which index to use for a query, share our new integration with Crunchy Bridge, and walk through this month's episodes of our weekly video series "5mins of Postgres", published every Thursday.

In only a few weeks we've reached over 200 subscribers for "5mins of Postgres". If you want to join, feel free to subscribe to our channel on YouTube. All "5mins" videos have English subtitles available and also get published on our blog.

- - -

Have you ever wondered why Postgres chooses a certain index?

We've often times found ourselves staring at an EXPLAIN plan, seeing a Sequential Scan, and being puzzled as to why Postgres isn't doing what we are expecting. Why is Postgres choosing a particular index over another one, or not choosing an index altogether?

In this blog post, we take an in-depth look at the Postgres source, in a search for answers:

How Postgres Chooses Which Index To Use For A Query→

- - -

5mins of Postgres - episodes from March:

E7: Autovacuum, dead tuples not yet removable, and the Postgres xmin horizon→

We explain what 'dead tuples not yet removable' means and why autovacuum might be unable to remove dead rows from a table. We also look at the xmin horizon. (share this episode here).

E8: Postgres Auditing: Table triggers with supabase vs the pgAudit extension→

In this episode, we look at two different approaches to audit Postgres. First, we are using triggers in Postgres with supa_audit, then we are looking into the pgAudit extension. (share this episode here).

E9: PostGIS vs GPUs, tuning parallel queries, and automating Citus extension benchmarks with HammerDB→

We look at PostGIS versus GPU performance for spatial queries and also take a look at using HammerDB and custom automation scripts to benchmark the Citus extension. (share this episode here).

E10: max_wal_size, Postgres full page writes and UUID vs BIGINT primary keys→

In episode 10, we walk through max_wal_size, full-page writes and why you might want to choose BIGINT instead of a UUID for your primary key column (20x performance difference). (share this episode here).

E11: MERGE in Postgres 15, and how it compares to INSERT ON CONFLICT→

We talk about the MERGE command in Postgres 15, how it differs from the INSERT ON CONFLICT command, and why you might opt for one over the other. INSERT ON CONFLICT *guarantees* that you either get an INSERT or an UPDATE, even if there are concurrent inserts. (share this episode here).

As mentioned previously, we publish a new episode every Thursday. The episodes are quick and bite sized and we hope you like them as much as this person on YouTube:

"Five minutes never went by so quickly. These are great 'peeps behind the curtain' for me. You deserve so many more views / subs."

Subscribe to the pganalyze YouTube channel here→

- - -

Product changes in March:

• A lot of work on the new database-wide Index Advisor, currently in early access
• New integration with Crunchy Bridge
  • We've teamed up with the team over at Crunchy Data, to launch our new pganalyze integration built on the newly released Postgres Container Apps
  • This integration is the easiest yet - just a single command to launch the collector directly on your database server hosted by Crunchy Bridge
  • Read more on the Crunchy Data blog, or explore the documentation
• pganalyze-collector 0.43.0
  • Apart from the Crunchy Bridge integration, this release contains:
  • Bugfix for distributed Postgres environments using the Citus extension with citus.shard_replication_factor > 1
  • Bugfix to filter out vacuum records we cannot match to a table name, avoiding processing errors in the backend
  • Docker image: Add tzdata package for correct timezone parsing during log line handling
• Improved Weekly Report email rendering in Outlook, and other bugfixes
  
• Behind the scenes:
  • Our new Rust-based statistics processing has started to roll out
    
  • We're in the early stages of adopting Tailwind CSS for some of our UI
  • And we've updated our own Postgres instances to Postgres 14 - we're excited to be running the latest release!

- - - 

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.