Postgres Weekly Issue 413: July 7, 2021

#413 — July 7, 2021

Simulating Temporal Tables with dblink and Replication Delay — A curious look at using delayed streaming replication and dblink (a way to execute queries on remote databases via another SQL query) to simulate temporal tables (i.e. to look back in time). This is such an odd but clever approach.

Kaarel Moppel

A Look at Row-Level Locks — A practical demo of how row-level locks are managed in Postgres and can be used together with object-level locks.

Egor Rogov

🌟 Time-Series 101: Get 10+ Tutorials, Sample Datasets & More — Explore 10+ tutorials - complete with sample datasets and SQL queries - to get up and running with time-series data analysis. Topics range from analyzing crypto trends to DevOps & IoT monitoring, building awesome visualizations & beyond. 🚀

Timescale sponsor

Understanding pg_repack: What Can Go Wrong and How to Avoid It — pg_repack is a tool to remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes, without holding an exclusive lock over said tables, but it’s not without its pain points.

Jobin Augustine

Ora2PG Now Supports oracle_fdw to Increase Data Migration Speed — Ora2Pg is an open source Oracle to Postgres migration tool and it can now use oracle_fdw for faster performance, particularly on tables using BLOB columns. This is due to the new versions v22.0 and 22.1 having just been released (with more features than just the above).

Gilles Darold

IN BRIEF:

Postgres has two new committers. Welcome to Daniel Gustafsson and John Naylor.
Daniel Westermann is the latest PostgreSQL Person of the Week and has been questioned on his favorite Postgres tools, extensions, and events.
Fly, an app deployment platform, has written about how they enable the use of globally-replicated Postgres for both reads and writes on their platform.

Postgres Permissions and Materialized Views — Materialized views let you persist the result of a query, but keeping them suitably refreshed by a non-owner can require some work on the permissions.

Ryan Lambert

Instant Realtime APIs on Postgres with Authorization | Get Started for Free in 30s

Hasura sponsor

▶ New Ways to Get Started with Azure Database for PostgreSQL — A snappy 20-minute introduction to running Postgres on Azure, particularly in its Citus-flavored ‘Hyperscale’ form. Claire is one of the most prolific suppliers of links to Postgres Weekly, by the way, so it’s neat to see her talking about Postgres live.

Scott Hanselman and Claire Giordano

How JOINs Between Local and Distributed Postgres Tables Work with Citus — Citus makes it possible to distribute tables across multiple nodes in a cluster and even to JOIN between them. This post is a basic introduction to how it works.

Sait Talha Nisanci

Paranoid SQL Execution on Postgres — Jeremy thinks of some ideas to execute SQL with a “paranoid” level of safety, by using timeouts, fully qualifying the names of things, using hot standbys, and more.

Jeremy Schneider