#325 — October 2, 2019

Read on the Web

Postgres Weekly

Postgres 12 RC 1 Released, Final Due This Week? — The first release candidate of PostgreSQL 12 is now available for download. The final release is expected tomorrow, but we didn’t feel it was 100% enough to delay this issue but it will be on postgresql.org as soon as it’s out.

PostgreSQL Global Development Group

On Implementing Transparent Data Encryption in Postgres — A brief update on a long term discussion over whether (and how) Postgres should implement TDE. In short, work is now underway, with work on this TODO list hoping to be in place for Postgres 13.

Bruce Momjian

Free eBook: How to Get a 3x Performance Improvement on Your Postgres Database — Learn our best practices for optimizing Postgres query performance for customers like Atlassian and how to reduce data loaded from disk by 500x.

pganalyze sponsor

Hacker News Discusses.. Postgres or MySQL? — There’s little more that can provoke a discussion on Hacker News than asking readers to choose between two technologies. Perhaps unsurprisingly, Postgres seems to be strongly preferred.

Hacker News

Introducing phpPgAdmin 7.12.0 — phpPgAdmin is a Web-based Postgres administration tool (in a similar vein to MySQL’s popular phpMyAdmin). v7.12.0 moves to PHP 7 and supports all current versions of Postgres (including 12).

xzilla

PostGIS 3.0 Beta 1 Released — Best used with Postgres 12, PostGIS is a popular Postgres extension that opens up the world of working with geospatial data at an advanced level from right within your database.

PostGIS Developers

How to Run a Quick ALTER TABLE Without Long Locking Concurrent Queries — An interesting workaround to a confusing problem.

Hubert depesz Lubaczewski

How to Work with Postgres in Go(lang) — An attempt at ‘arming you with experience’ of using PostgreSQL from Go (with pgx, specifically), covering tools, drivers, logging, monitoring, and quirks to look out for.

Artemiy Ryabinkov

Fixing track_activity_query_size in postgresql.conf — By default, Postgres reserves up to 1024 bytes to track each query run which might not be enough for your monitoring or logging preferences. Here’s how to tweak the setting involved.

Hans-Jürgen Schönig

Is Synchronous Replication a Trap? — “I think that a lot of people are using this technology and getting little or no benefit out of it, and some are actively hurting themselves.”

Robert Haas

Take the Guesswork Out of Improving Query Performance — Based on the query plan, pgMustard offers you tips to make your query faster. Try it for free.

pgMustard sponsor

How Much Faster is Redis at Storing a Blob of JSON Compared to Postgres? — If you’re familiar with Redis, the in-memory data structure store, it won’t surprise you that it’s fast(!) but the use cases and pros and cons between Redis and Postgres are hugely different too.

Peter Bengtsson

Understanding Heroku Postgres Log Statements and Common Errors

Heroku

supported by Datadog

💡 Tip of the Week

The INTERSECT clause on SELECT

Relational algebra and set theory form much of the basis of SQL and inspire many of its features. One such feature is the ability to intersect the results of two queries against each other in the way that you might intersect two sets mathematically:

The intersection of A and B is made up of what A and B have in common.

As an example, let's say you have a customers table which has a address_id column pointing to rows in an addresses table. We want to fetch all address IDs that are present in both tables (that is, a customer record is pointing at an address and that address exists):

SELECT address_id FROM customers
  INTERSECT SELECT id FROM addresses;

We can also do the converse if we want to return the address IDs that are referenced in the customers table but are NOT present in the addresses table itself. We do this using EXCEPT instead of INTERSECT:

SELECT address_id FROM customers
  EXCEPT SELECT id FROM addresses;

Note that order is important here. We're selecting address IDs from customers and then the ones that exist in addresses are being removed by EXCEPT. If we wanted to find the IDs of any addresses that are not being referenced from customers, we can spin the query around:

SELECT id FROM addresses
  EXCEPT SELECT address_id FROM customers;

If this has whet your appetite, this tutorial by Lukas Eder goes into more detail with more examples and covers UNION as well.

This week's tip is sponsored by Datadog. Collect and monitor custom PostgreSQL metrics in real-time with Datadog.

🗓 Upcoming Events