#​451 — April 20, 2022

Web Version

Postgres Weekly

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

▶  Advanced INT to BIGINT Conversions — Integer overflows have caused all manner of bugs, problems, and outages over the years, so should we just move to BIGINT for everything? Not so fast, says Robert, who digs deeper into the dilemma in this 20-minute talk.

Robert Treat

In brief:

  • The latest PostgreSQL Person of the Week interview is with Bharath Rupireddy who works on Postgres's internals at Microsoft. He has some advice on how to work more closely with Postgres, if you wish to.

  • Not Postgres specific, but I found it interesting: if you're an Apple user, you can use SQL to analyze your iMessage history.

  • AWS RDS PostgreSQL recently had a security issue you might want to know about. Numerous Postgres versions were deprecated as a result.

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

How to Generate Postgres Credentials with Hashicorp Vault and Go
Yandry Pozo

🔧 Code and Tools:

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.


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