#​463 — July 13, 2022

Web Version

Postgres Weekly

How Postgres 15 Improves UNIQUE and NULL — The author took a look at the latest Postgres 15 beta and dug into an improvement to UNIQUE constraints on columns with NULL values where it’s possible to make Postgres treat nulls as either distinct values or not. Peter Eisentraut, who developed this feature, popped up on Hacker News to note: “The background here is that the SQL standard was ambiguous about which of the two ways an implementation should behave. So in the upcoming SQL:202x, this was addressed by making the behavior implementation-defined and adding this NULLS [NOT] DISTINCT option to pick the other behavior.”

Ryan Lambert

Scale Your Postgres Instance Globally, in Minutes — PolyScale.ai is a plug-and-play database edge cache that keeps your database queries fast, everywhere. Run low-latency, multi-region applications or serverless functions, without the cost and complexity of traditional database scaling.

PolyScale.ai sponsor

Column Order in Postgres Does Matter — You might think that the order of columns in a database table is just a trivial consequence of that table's history and doesn’t matter much at a technical level. But it can, says Hans-Jürgen, though his example does lean on a particularly extreme example(!)

Hans-Jürgen Schönig

💡 GitLab has a handy article on why they require their developers order columns in a specific way in Ordering Table Columns in PostgreSQL – if you want to learn more about this matter.

Initial Findings from the State of PostgreSQL 2022 Survey — It takes a while to crunch the data to find insights from large community surveys, so Timescale is taking a dip in the shallow end first with a look at demographics (notably, most respondents were not from North America) and a few answers to open ended questions.

Ryan Booz

QUICK BITS:

  • 🎤 Postgres is getting its own podcast! Postgres.fm features two developers, responsible for Postgres AI and pgMustard respectively, discussing all things Postgres on a weekly basis. Only one episode for now, but we'll feature the show again as it develops. Listen to episode 1.

  • Azure PostgreSQL Flexible Server now supports the Pgrouting and PLV8 extensions if you want to get your geographic routing or JavaScript powered Postgres functions on.

  • We mentioned it a couple of weeks ago, but on social everyone seems to have suddenly noticed Amazon Aurora now supports Postgres 14 and we didn't want anyone to think we missed it ;-)

Some Notes on GitLab’s Postgres Schema Design — It’s been a worrying long time since we’ve seen a real world database design article doing the rounds, so it’s fun to see Shekhar poking around in Gitlab’s (seemingly rather well designed) database a little.

Shekhar Gulati

Data to Go: Postgres Logical Replication — Need your data to be copied to a new location on the regular for further processing, consolidation, or more? Postgres 10+’s logical replication is for you. This is a quick explainer that also compares it to streaming replication.

Elizabeth Christensen

Tracing Gorm Queries with OpenCensus and Google Cloud Tracing — GORM is a powerful ORM for Go and the team at Incident use it for working with their Postgres database. Here’s how they trace what’s going on in order to keep an eye on performance.

Alex Russell-Saw

Free eBook: Understanding Databases

Linode sponsor

Enabling and Enforcing SSL/TLS for Postgres Connections
Agustín (Percona)

RELEASES:

FerretDB 0.5 – A MongoDB alternative on top of Postgres.
Good Job 3.1 – Postgres-based background job backend for Ruby on Rails.
norm 2.5 – An ORM for the Nim language.
usql 0.11 – Universal CLI tool for SQL databases.
libpqxx 7.7.4 – Official C++ client API for Postgres.
pgsync 0.7.1 – Sync data from one Postgres database to another.