#389 — January 20, 2021

Web Version

Postgres Weekly

pspg 4.0 Released: A Pager for Postgres Tables and More — pspg is a Unix pager for working with Postgres query results as well as CSV or TSV data. 4.0 adds support for exporting content to file or clipboard. GitHub repo.

Pavel Stěhule

Working with Hierarchical Structures in Postgres — Trees and similar hierarchical structures are often required when modelling data and there are multiple ways to implement them in Postgres. This post looks at materialized views with recursive CTEs and using ltree columns.

Ana Hobden

Close-To Zero Downtime Migration & Automatic Code Rewrite — CYBERTEC Migrator is a powerful, but yet easy-to-use software product for fast database migration from Oracle to PostgreSQL. It supports a wide range of data structures and Oracle concepts and is fully able to handle complex setups and environments.

CYBERTEC sponsor

How to Audit a Postgres Database — Auditing is required for many security regulations and is also useful if you want to know what happened in your database, and when and who was responsible. Here’s a basic look at what PGAudit can do for you.

Sebastian Insausti (SeveralNines)

A Few Postgres Tips and Tricks to Kickstart 2021 — You know we love the tips and tricks around here (yes, Tip of the Week is back next week) and Kaarel has a few things to share here around partial indexes, Postgres upgrades, and inspecting query result metadata in psql.

Kaarel Moppel

How to Run Some Tasks Without User Intervention At Specific Times? — If cron is your first answer, you’re right, but there can be more to it when dealing with Postgres. Hubert also touches on pgAdmin and pg_timetable briefly here.

Hubert depesz Lubaczewski

Are There Limits to Partition Counts? — Probably not a very restrictive one, as Hubert discovers here. “I would probably discourage people from making 5000+ partitions of single table, but Pg can definitely handle it.”

Hubert depesz Lubaczewski

Learn How a 138-Year-Old Shipping Company Ships with Serverless

New Relic sponsor

SQL Optimizations in Postgres: IN vs EXISTS vs ANY/ALL vs JOIN — An item from last year we didn’t get around to covering. It demonstrates how different approaches to the same problem can yield different levels of performance despite Postgres’s optimizations.

Jobin Augustine

How to Upgrade from Postgres 12 to 13 with Homebrew — Could be useful if you’re running a Homebrew-based Postgres install on your local macOS machine.

Nick Quaranto

How Postgres Executes Sequential Scans with the Help of Table Access Methods APIs — Another low level ‘under the hood’ article from the HighGo folks. Only dive in if you care how Postgres works internally.

Cary Huang

A New Oracle to Postgres Migration Guide for Azure Users — Microsoft and Amazon’s zeal for getting Oracle users to switch to, well, anything that isn’t Oracle makes me think Larry Ellison refused to take part in the tech billionaire Secret Santa. Nonetheless, there’s now a new, detailed migration guide for moving Oracle workloads to Azure Database for PostgreSQL, if that’s something you need to do.

Arun Kumar Thiagarajan