The popular PostgreSQL explainer series is celebrating its 100th release and beyond. Let's take a look at what it makes it so special.

The '5mins of Postgres' series run by PgAnalyze, the platform that analyzes PostgreSQL instances for bottlenecks to offer troubleshooting advice, is very popular amongst PostgreSQL DBA's and developers.

The series which is run on a weekly basis, walks viewers through interesting articles about Postgres that occurred in the previous weeks, adding the context and personal perspective of the PgAnazlyze personnel on top. The topics are not constrained to troubleshooting and optimization resources, but span a wide array of topics, just about anything Postgres-related.

In the past two years over 100 episodes have been released, the last one very recently, which have now been gatherered together in one massive Youtube playlist. In it you'll find material on topics like query optimization, troubleshooting, PostgreSQL new features, tips for developers and dbas alike, and so on and we've picked out a few representative videos of the series.

[Editor: Just to make things confusing, two sets of numbering are used in the playlist. The E(pisode) numbers which are appended at the end of the title of each episode are ascending and run out at E64. However the list number on the left is descending so that the very latest is always numbered 1. This means the list numbers of all the recommended videos will change. To find the list number of those recommended use 1+X-E where X is the number of the final video in the list and E is the number given. The numbers at publication are given below in square brackets.]

A must-watch is E1 [ #105] and, yes, the very first one, about "Using Postgres statistics to improve bad query plans"; self-explanatory really.

E5 [#101] is a hands-on tutorial on debugging a 100% CPU utilization spike through Postgres EXPLAIN and perf on Linux.

E8 [#98] is another very handy one about Auditing by utilizing two different approaches; triggers vs the pgAudit extension.

For devs, a good watch would be E10 [#96]: "max_wal_size, Postgres full page writes and why you might want to choose BIGINT instead of a UUID for your primary key column", together with E11 [#65] which looks into the new MERGE SQL construct introduced in PostgreSQL 15, comparing it to INSERT ON CONFLICT.

In E45 [#61] "IN lists vs ANY operator: Bind parameters and performance" the talk is about the difference between the ANY and the IN operator in Postgres, how they differ in performance, and why you might use one or the other.

E48 [#48] is about optimizing the use of indexes, benchmarking and contrasting multi column, covering and hash indexes, and whether B-Tree or hash indexes are a better fit.

Fast forward to up to 8 months ago, E74 [#32] talks about vectors in Postgres, claiming that vectors are the new JSON in Postgres, and introducing pgvector.

Then 5 months ago, E82 [#24] takes a quick and high level overview of new version of PostgreSQL v16.

Episode 100 [#6] discusses how to optimize the row size in Postgres in order to reduce the overall table size. Specifically, the talk is on how column ordering affects storage size and the big benefits that array-based storage can yield for some workloads.

And we cap it with E104 [E2] which discusses a recently committed change to the Postgres 17 development branch that enables configuring the size of the Postgres SLRU caches, as well as improvements to LWLock contention on SLRU related locks. It gives the background on what SLRU caches do in Postgres, and historic performance challenges experienced at scale.

So as you can see the playlist has something of everything and for everyone with even a remote relation to Postgres. It's a list that doesn't just showcase useful tips and tricks that will make your life easier and help you squeeze every little bit of performance out of your database, but is also essential in keeping you informed on Postgres' latest developments.

And all that in just about 5 minutes.

More Information

Youtube playlist

Related Articles

It's 2024. Why Does PostgreSQL Still Dominate? 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info