Issue 451 - A Postgres 9 to 13 upgrade story (13)

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.

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..

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).

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.

▶  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.

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).

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).

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.

'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.

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.

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.”