Strangely quiet around here

My colleague Bob Pacheco asked me to help with a strange problem he was witnessing for a client. A new Postgres cluster was getting created on a Kubernetes

Today we're excited to release a big update to our Crunchy Bridge CLI: a new interactive menu for psql! Now when connecting to your Crunchy Bridge database with cb psql you'll have a :menu option. The cb menu is an easy to navigate collection of insights about your database. All of these insights are powered by data already contained in Postgres system catalogs. We have these same database insights in the dashboard, so this feature extends that to those working directly from the command line.

Before we added this new Bridge CLI, you had to find your own system catalog queries. You had to know which catalogs existed, where to find some community queries, and had to construct things basically from scratch. Now with a simple command you can get the insights you need and stay in the flow of developing or debugging:

Cache
  1 – Cache and index hit rates
Size Information
  2 – Database sizes
  3 – Table sizes
Query Performance
  4 – Queries consuming the most system time
  5 – Queries running over 1 minute
  6 – Slowest average queries
Connection Management
  7 – Connection count by state
  8 – Connection count by user and application
Indexes
  9 – Duplicate indexes
  10 – List of indexes
  11 – Unused indexes
Locks
  12 – Blocking queries
Extensions
  13 – Available extensions
  14 – Installed extensions

At PostGIS Day 2023, one of our speakers showed off a really cool demo for getting JSON and SVGs in and out of Postgres / PostGIS and into Google Sheets. Brian Timoney put together several open source projects in such a cool way that I just had to try it myself. If you want to see his demo video, it is on YouTube

On the Customer Success Engineering team at Crunchy Bridge, we run across customers with lock issues on their Postgres database from time to time. Locks can have a cascading effect on queries. If one process is locking a table, then a query can be waiting on the process before it, and the process before that one. Major lock issues can quickly take down an entire production Postgres instance or application.

In this post let’s look at why locks happen, and more importantly how to get to the bottom of a lock issue and the one process blocking everything else. That one process that blocks them all! Once you find the source of the lock, I’ll give you the options for terminating the process that created all your troubles in the first place.

Finding the source of the lock

In my role as a Solutions Architect at Crunchy Data, I help customers get up and running with Crunchy Postgres for Kubernetes (CPK). Installing and managing a Postgres cluster in Kubernetes has never been easier. However, sometimes things don't go as planned and I’ve noticed a few major areas where Kubernetes installations go awry. Today I want to walk through some of the most common issues I see when people try to get up and running with Postgres in Kubernetes and offer a list of basic troubleshooting ideas to get started. Now sure, your issue might not be in here, but if you’re just trying to diagnose a bad install or a failing cluster, here’s my go to list of where to get started

The Order of Things: CRD, Operator, Cluster, Pod

You may have noticed a file called postmaster.pid inside your data directory. This file gets created when Postgres first starts up, and gets removed on a clean shutdown. It seems to contain some random numbers and strings, but what do they all mean?

The file will look like this:

2757
/home/greg/pg/17/data
176540940
5432
/tmp
*
   8675309        12
ready

I'm excited to announce support for the Citus extension for Postgres on Crunchy Bridge. This means you can have a fully managed Citus experience on any cloud (AWS, Azure, or GCP) managed by the Postgres experts at Crunchy Data. If you're unfamiliar with Citus

I've always found distributed systems to be the most fascinating branch of computer science. I think the reason is that distributed systems are subject to the rules of the physical world just like we are. Things are never perfect, you cannot get everything you want, you’re always limited by physics, and often by economics, or by who you can communicate with. Many problems in distributed systems simply do not have a clean solution, instead there are different trade-offs you can make.

While at Citus Data, Microsoft, and now Crunchy Data, the focus of my work has been on distributed PostgreSQL architectures. At the last PGConf.EU

If you’ve ever dug under the hood of Postgres a bit, you’ve probably heard about the page. This is the on-disk storage mechanism and it's limited to an 8kb size. But what happens when you have data bigger than that 8kb? TOAST is made. Postgres TOASTs data by splitting it up into smaller chunks. TOAST stands for The Oversized Attribute Storage Technique.

TOAST happens automatically, you don’t set up anything, it just comes with Postgres out of the box. So why should you care?

Well TOAST can impact your query performance quite a bit. It adds some extra hurdles for Postgres to jump through when delivering data to your end users. So knowing how TOAST is made, when, and in some cases how to avoid it, is important for optimizing Postgres.

Rows bigger than your page size

Since its launch, Ruby on Rails has been a preferred open source framework for small-team B2B SaaS companies. Ruby on Rails uses a conventions-over-configuration mantra. This approach reduces common technical choices, thus elevating decisions. With this approach, the developers get an ORM (ActiveRecord), templating engine (ERB), helper methods (like number_to_currency