#​449 — April 6, 2022

Web Version

Postgres Weekly

How Postgres Chooses Which Index to Use for a Query — Whenever a blog post is dated April 1st, I’m immediately on edge, but there’s no fooling around in Lukas’s deep yet direct look at how Postgres’s query planner gets to work. This goes right down into Postgres’s source code(!) but also covers a practical example of why this is useful knowledge to have in your back pocket.

Lukas Fittl

Mapping Timestamps to 'Bins' with date_bin — Somehow I missed this neat addition in Postgres 14 till now. date_bin is a function to place a supplied timestamp into a bin/bracket/zone defined within a set of intervals from a specified origin. It provides a finer level of control to the long standing date_trunc.

Hans-Jürgen Schönig

App Performance Blocked By Your Database? — Scaling Postgres databases can mean cost and complexity. PolyScale provides a plug-and-play serverless edge cache for Postgres. Scale globally in minutes without writing code or deploying infrastructure.

PolyScale.ai sponsor

In brief:

Postgres Container Apps: Easy Deployment of Apps in Postgres — An interesting development. Postgres Container Apps provide a mechanism for spinning up a container-based app from within Postgres that itself runs directly alongside your Postgres database. This is Crunchy specific but it’s interesting to think of the use cases for this.

Craig Kerstiens (CrunchyData)

Identify Postgres Performance Bottlenecks with pg_stat_statementspg_stat_statements comes up time and time again as one of the most used Postgres extensions and for good reason: it’s great for debugging performance issues. It’s now available on Timescale Cloud by default, but you can use it on your non Timescale systems too, of course.

Ryan Booz (Timescale)

Quick Trick: Search in Every Field in a Table — You want to find a value in a table and you don’t care what column it’s in! What to do? You could select a row as a single value and cast it to a string..

Hubert depesz Lubaczewski

Keep Up with the Latest in Startups, Tech, & Programming in Just 5 Min

TLDR Newsletter sponsor

Tidying Up with VACUUM — Look, we can’t go a month without covering vacuuming in some way or another. It’s not as fun as extreme ironing but this walk through ensuring the vacuuming process is properly configured for your database’s requirements is a lot more useful.. and Shaun covers it here.

Shaun Thomas (EDB)

How to Enable SSL for Postgres Connections
Frits Hoogland (Yugabyte)

🔧 Code and Tools:

Postico: A Native Postgres Client for macOS — It’s a commercial product (with a trial) but has an elegant native macOS aesthetic. We first linked to it seven years ago, but love checking in to see projects still doing well.

Egger Apps

A Postgres Wire-Compatible SQLite Proxy — What if you could use Postgres client tools.. but to work with SQLite databases on remote servers? That’s the idea put forward by this interesting experiment.

Ben Johnson