#​494 — February 22, 2023

Web Version

Together with  Percona logo

Postgres Weekly

Creating Entity Relatonship (ER) Diagrams with SQL and MermaidMermaid is a powerful diagramming tool that turns a basic text format (here’s how it works for ER diagrams) into fully rendered visual diagrams. Can you dynamically generate such markup from SQL for Mermaid to render? Yep.

Pavlo Golub

💡 Mermaid is an interesting and useful tool even separate from SQL. You can play with it 'live' here or learn more at its homepage. Reader Ashley Peacock also has a new book out with Pragmatic Bookshelf all about it called 📗 Creating Software with Modern Diagramming Techniques.

Expert Support to Architect, Build and Deploy HA Postgres — Whether you need a pre-build consultation, want us to build your HA infrastructure for you, or are looking for ongoing, post-build support, Percona can help you meet your HA goals.

Percona sponsor

Type Constraints in 65 Lines of SQL — Creating validated data types in Postgres isn’t particularly difficult and can come in handy for enforcing your specific schema. Oliver demonstrates how to create a type to represent semantic version numbers.

Oliver Rice (Supabase)

Unlogged Tables: Faster, But Less Reliable, Tables — Unlogged tables in Postgres can give you a big performance improvement if your use case can deal with the downsides, as Greg explores here. Maybe think of them as persistent temporary tables?

Greg Sabino Mullane

Object Ownership and Default Privileges — Covers the concept of ownership and default privileges, explaining how they can be used for effective management of permissions and security in a database by giving just the right amount of access to certain objects.

Ryan Booz

IN BRIEF:

▶  The Ever-Growing Ecosystem of Postgres with Álvaro Hernandez — A 35-minute podcast with Álvaro, the founder of OnGres, a Postgres services company, well known for his contributions to the community. He talks about running Postgres on Kubernetes, tooling complexity, and Postgres’s wire protocol as the heart of an ecosystem.

Screaming in the Cloud podcast

▶  Postgres System Columns Explained — A 25-minute video digging into ctid, xmin and xmax columns. These are system columns that can be used when investigating the behaviors of transactions. Fun digging!

Hussein Nasser

Improve Logical Replication Performance in Amazon Aurora with the New Write‑Through Cache
Susan Douglas and Scott Mead (AWS)

Using an Oracle Database Gateway to Connect Amazon RDS Custom for Oracle to Postgres
Fernando da Cruz and Harper (AWS)

UUIDs vs Serials for Keys
Christophe Pettus

🛠 Code and Tools

PgManage 1.0a: GUI Administration Tool for Postgres — A new fork of the seemingly abandoned OmniDB that's a cross-platform command-line tool for managing Postgres 9.6–15. GitHub repo. (Slightly painful to run on macOS due to not being signed, but it is in alpha.)

CommandPrompt Inc.

TimescaleDB 2.10: The Time Series Database Extension — Now with full Postgres 15 support, reworking of how compression works (with notable improvements to INSERT rates), and you can now use joins in continuous aggregates.

Timescale

Beginner’s Guide to DevOps Tools (GitHub, Jenkins, Terraform, Ansible)

Akamai Connected Cloud sponsor

SQLite Foreign Data Wrapper for Postgres — Supports Postgres 11-15 and recent SQLite versions. Requires a Linux or POSIX-compliant system.

PGSpider

pg-semver: A Semantic Version Data Type — An implementation of the version number format specified by the Semantic Versioning 2.0.0 Specification.

David E. Wheeler, et al.

😆 It's a total coincidence this popped up on our radar the same week as the type constraints in 65 lines of SQL article (above).

🐦 Seen on Twitter