#​424 — September 29, 2021

Web Version

Postgres Weekly

Postgres 14 Release Candidate 1 Released — Progress towards the next major version of Postgres takes a big leap with the first release candidate, meaning the team are ready for you to try it out and report those last bugs. A final release is due in the next week if nothing big rears its head. Draft release notes.

PostgreSQL News

JSON in Postgres: How to Use It Right — Postgres has had solid JSON support for several major versions now and I use it heavily myself without any negative incidents, but there are ‘bad uses’ for it, claims the author.

Laurenz Albe

A Better Way to Index Your Postgres Database: pganalyze Index Advisor — After many months of effort we are launching our index advisor. We looked into ML-based Postgres indexing but found that relying on the Postgres planner and using a deterministic model was better. See how we built the advisor and try it for free.

Pganalyze sponsor

Editorial note: As per the above, it’s always great to see a new tool get released, and Lukas has done a great job of explaining just why and how a tool that determines what indexes you should create to improve specific queries can be useful. Try it here.

Zero-Downtime Postgres Schema Migrations Need lock_timeout and Retries — Deploying schema changes in heavily loaded systems is challenging because of the locking involved which can rapidly become more complicated than you’d assume. Instead of waiting around, failing fast and retrying can avoid some of the problems.

Nikolay Samokhvalov

Using Recursive Queries to Get Distinct Elements from a Table — Working through a query problem and coming up with a creative solution that’s far quicker than the first attempt.

Hubert depesz Lubaczewski

SQLite FDW 2.1.0 Released — The SQLite Foreign Data Wrapper for Postgres now supports Postgres 14 and makes it possible to use SQLite database files as if they were Postgres tables (with some limitations).


Download Prepared OSM Data in PostGIS Enabled Postgres Databases For Free

CYBERTEC PostGIS OSM Downloader sponsor

Building an Oracle to Postgres Migrator: Lessons Learned — “A lot has been written about Oracle to PostgreSQL migration; today, I want to share some of the lessons learned when it comes to actually building a migration tool”.

Hans-Jürgen Schönig

PGLoader: Data Loading and Continuous Migration from Other Databases to Postgres — Can load data in from files (e.g. CSV) or migrate an entire database into Postgres. Written in Lisp and open source.

Dimitri Fontaine

Restarting A Sequence: How Hard Could It Be?
Luca Ferrari

Pgpool-II Configuration Parameters: enable_shared_relcache
Bo Peng