Elevation Profiles and Flightlines with PostGIS — I’m always impressed what geographic work you can do with PostGIS and Postgres. This post demonstrates turning a variety of points a flight took into an actual flight path.

Paul Ramsey

How to Shape Sample Data with generate_series() and SQL — The last in a series of posts about generating large sets of test data with Postgres itself in order to benchmark, test workloads, or play with database features. This stage moves on to manipulating such generated data to fit a required profile.

Ryan Booz (Timescale)

Free eBook: Effective Indexing in Postgres — Learn how to create the best Postgres index for your queries. We provide a deep dive into index types, operators, data types and more. Creating the right indexes can often improve your query performance.

Pganalyze sponsor

Database Lab Engine: A Way to Clone Postgres Databases Quickly — Promises ‘blazing fast’ database cloning (claiming an ability to clone a 1TB database in 10 seconds thanks to its copy-on-write approach) particularly for building dev or QA/staging environments based on full-size production-esque databases.

Postgres.ai

Postgres Indexes for Newbies — If you understand the differences between B-Tree, GIN, and GIST indexes, scroll on, but otherwise this gives you a very high level overview. If you then want to go (a lot) further you could read more detailed guides like these on B-trees or BRIN indexes.

Elizabeth Christensen

A Few Lessons from Doing Advent of Code Challenges with Postgres — Advent of Code is an annual set of programming challenges that takes place each December. Commonly languages like Python or C# are used, but some folks set out to use things like SQL. While the authors didn’t complete all 25 days with SQL, they learnt some interesting things through the attempt.

Matt Dupree and Amanda Murphy

An Overview of Security Best Practices for AWS's Postgres Platforms — An AWS-oriented look at good practices to follow when using either RDS for PostgreSQL or the Postgres flavor of Aurora. Security groups, forcing SSL, IAM authentication, and encryption all appear.

Baji Shaik and Divya Sharma (AWS)

Accelerating VACUUM in Postgres — It’s been a while since we had a flurry of articles about vacuuming, but here’s a few quick tips to tweaking Postgres’s settings to speed up the autovacuuming process in particular.

Alexei Kozlov

Going Into 'Read Only' Mode with Postgres — If you have a need to temporarily put a Postgres instance into a ‘read only’ state, default_transaction_read_only can be used to prevent write transactions.

Jonathan Katz

Use Flux and PostgreSQL to Enrich Data for Real-Time Apps

InfluxData sponsor

Tips for Installing Citus and Postgres Packages — Citus is well known as an extension for horizontally scaling Postgres and this post answers some questions as to how it’s packaged and how it’s installed and managed.

Gürkan İndibay (Citus Data)

Distributed Locking with Postgres Advisory Locks — Postgres’s advisory locks feature can provide your app with a useful and trustworthy locking mechanism that doesn’t even have to involve data stored in Postgres itself. This post (from 2020 – we missed it at the time) demonstrates how one company uses them.

Richard Clayton

How to Create an Amazon CloudWatch Dashboard to Monitor Postgres on AWS — Goes into quite some depth on creating a dynamic dashboard for monitoring RDS or Aurora including things like slow queries, the error log, vacuuming processes, and more.

Shunan Xiang and Li Liu (AWS)

Reshape: A Zero-Downtime Schema Migration Tool for Postgres — An experimental tool written in Rust to perform complex migrations on Postgres 12 and higher in a gradual way so that no downtime is required.

Fabian Lindfors

Que 1.0: A Ruby Job Queue That Uses Postgres's Advisory Locks — We mentioned advisory locks above and here’s a perfect use for them bringing more reliable job queue functionality to Ruby by using locks to protect the state of the jobs.

Chris Hanks

Pgweb: A Web Based Postgres Database Browser/Client — A long established client written in Go, so zero-dependency binaries are easy to build where need be. GitHub repo.

Dan Sosedoff