How Retool Upgraded Its 4TB Postgres Database to v13 — 4TB might not be “big data” but it’s in a common zone for SaaS primary database size, so Retool’s story and tips learned along the way during an upgrade from Postgres 9.6 to 13 may prove of interest.

Peter Johnston (Retool)

Faster Geospatial Enrichment: Postgres Versus.. — Mark is well known for producing some fantastic data analysis posts over the years (such as his 1.1 billion taxi rides series) and now he puts Postgres up against ClickHouse and BigQuery for converting a large set of coordinates into H3 identifiers. Postgres didn’t win, but it also didn’t lose..

Mark Litwintschik

The Postgres You Know and Love, with Time-Series Superpowers — TimescaleDB is the open-source relational database for time-series and analytics, built by developers for developers. It brings together the familiarity of PostgreSQL with speed and petabyte scale. Try it for free (no credit card required).

Timescale sponsor

Find and Fix a Missing Postgres Index — “Missing indexes are a key ingredient if you are looking for a perfect recipe to ruin performance in the most efficient way possible,” says Hans-Jürgen, but luckily the order of the day here is how to avoid that problem, fix missing indexes, and improve performance.

Hans-Jürgen Schönig

Two Sizes Fit Most: Postgres and ClickHouse? — An opinion piece from GitLab’s CEO arguing that Postgres and Clickhouse cover most of the OLAP and OLTP bases businesses require (although arguably Postgres is entering the OLAP world more itself, in the shape of HTAP).

Sid Sijbrandij

Formatting SQL Code with pgFormatter within Emacs — If Emacs is your operating system editor of choice, this might be of use, though pgFormatter is a Perl-based SQL beautifier that can be used at the command line too (or even on the web).

Luca Ferrari

Securing Your PostgreSQL Database: An Overview of Best Practices

Teleport sponsor

Connecting to Postgres over SSL in Rust with Self-Signed Certificates — It’s for YugabyteDB really, but it’s wire-compatible with Postgres so we’re promised it’ll work on both.

Fits Hoogland

'Our Experience with Postgres on ZFS' — ZFS is a file system with lots of useful advanced features (but with higher hardware requirements, too) including snapshots and transparent compression – both quite useful for database use cases.

Aycan Gulez

A SQLite to PostgreSQL Migration Story — You don’t often hear about SQLite to Postgres migrations, but I’ve done a few myself when moving small Rails apps to cloud platforms (SQLite and containers make for sad times). There’s not much to this particular story but it’s nice to see people’s reasoning.

Danny Xu

Orafce: Oracle Compatibility Functions for PostgreSQL — Orafce implements some of the functions from Oracle Database that might be useful for users migrating to Postgres. v3.21.0 backports regexp_replace and regexp_instr from the future Postgres 15.

Orafce

PostgresML: Train Models and Make Predictions Using Only SQL — “You train models using simple SQL commands, and you get the predictions in your apps via a mechanism you’re already using: a query over a standard Postgres connection.”

Lev Kokotov and Montana Low