A veritable treasure trove of assorted how-to recipes for PostgreSQL, stored as a Github repository, has been started by Nikolay Samokhvalov, well known in the PostgreSQL world.

While PostgreSQL docs are awesome, they often lack practical pieces of advice, a situation that these how-tos try to remedy.

Given his 20+ years of database experience, Samokhvalov is well-positioned to share his advice to help out other PostgreSQL users and also aims to use the how-tos as a constant feed to his employer's Postgres.ai system.

Looking at the recipes themselves and as evident by the following sample listing, we find that they are addressed to PostgreSQL admins, who with their aid can find out how to:

• tell EXPLAIN ANALYZE from EXPLAIN (ANALYZE, BUFFERS)?
• tune Linux parameters for OLTP Postgres
• change ownership of all objects in a database
• find redundant indexes
• add a CHECK constraint without downtime
• convert a physical replica to logical
• monitor transaction ID wraparound risks
• NOT get screwed as a DBA (DBRE)
• deal with long-running transactions (OLTP)
• check btree indexes for corruption
• compile Postgres on Ubuntu 22.04
• use pg_restore

At the moment of writing there's 92 recipes, but the listing is being continuously updated with new ones, the most recent written 4 months ago. Nikolay's goal is to create at least 365 posts.

Going through some of Nikolay's notes you can sense his years of experience being infused into his writings. For instance in "How to find redundant indexes", he first explains what such an index is :

• Redundant indexes refer to multiple indexes on a table that serve the same purpose or where one index could satisfy the queries that all others do. Two basic examples:

  1.Duplicate Indexes: Two or more indexes that have exactly the same columns in the same order are clearly redundant.
   
  2. Overlapping Indexes: When you have a multi-column index, for example on (a, b), and then another index just on (a). For queries that only filter on column a, the index on (a, b) could suffice, making the index on (a) potentially unnecessary.

Then goes on to explain why you should even attempt to clean them up before showing how to find them.

Or in "How to add a CHECK constraint without downtime" he first explains why adding CHECK constraints can be helpful:

• to maintain better data quality
• to define a NOT NULL constraint without downtime in PG12+

before going adding them. The rest of the recipes follow along this line.

However in case you don't feel like going through the listing manually but instead want to ask questions, there's now a custom GPT made called #PostgresMarathon that is fine tuned on this recipes based knowledge base.

In any case, no matter which option you'll go for, what's certain is that at some point in time you'll find the trove very handy.

More Information

Postgres How-tos

#PostgresMarathon

Related Articles

The PostgreSQL Extension Repo By Pigsty

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