#359 — June 10, 2020

Read on the Web

Postgres Weekly

10 Common Postgres Errors — Some quick fire common errors and warnings to watch out for, with symptoms and solutions, around things like memory, disk space, and permissions.

Ibrar Ahmed (Percona)

A Tool to Make EXPLAIN ANALYZE Plans More Readable — A long standing (over 11 years!) Web-based tool that lets you paste in the result of EXPLAIN ANALYZE queries and see a more easily understandable version. Why are we linking it again? It’s had a bunch of updates.

Hubert Lubaczewski, Łukasz Lewandowski

Whitepaper: Business Case for Professional Support — Learn the importance of Professional Support for your mission-critical PostgreSQL systems & how it can benefit your company. It increases database performance, helps scale, distributes data, reduces costs, saves you from known pitfalls, and more.

2ndQuadrant Services sponsor

PostgreSQL Load Balancing with HAProxy — I’m a huge fan of the haproxy TCP and HTTP proxy/load balancer, but admittedly haven’t used it for Postgres before.. This post demonstrates using HAProxy with Severalnines’ ClusterControl for load balancing Postgres much in a similar way as you might an HTTP server.

Severalnines

Multi-Master Replication Solutions for Postgres — Horizontally scaling Postgres has been enough of a challenge over the years that entire companies (e.g. Citus Data) have been founded to make it easier. But there are various ways to do multi-master replication nonetheless, and this post links to several approaches, whether open or closed source, free or paid.

Ibrar Ahmed (Percona)

Deduplication in Postgres 13 B-Tree Indexes — PostgreSQL v13 introduces deduplication of entries for B-tree indexes. This article describes the new feature and demonstrates it with a simple test.

Laurenz Albe

A Step-by-Step Way to Backup a Heroku Postgres Database to an AWS S3 Bucket — Another variation on this that’s worth doing IMO is turning on S3 versioning so you can easily get rolling backups and keep a consistent filename.

Paweł Urbanek

Locating the Postgres Configuration FileSHOW config_file is the key here, but it might take a little more work if using Docker, say.

Luca Ferrari

What Are Failover Slots?

Craig Ringer

Visualize Postgres Performance In Real-Time With Datadog — Datadog’s Postgres OOTB dashboard visualizes data on latency, errors, read/write capacity, and throttled request in a single pane of glass.

Datadog sponsor

pgsql-http: An HTTP Client Extension for Postgres — If you need to make HTTP requests direct from Postgres, this is one way to go and links against libcurl.

Paul Ramsey

PgHero 2.5: A Performance Dashboard for Postgres — Built in Ruby. And, yes, we have a newsletter for that. 😁

Andrew Kane

SQLancer: A Tool for Detecting Logic Bugs in Database Systems — Considered a ‘research prototype’ for now, SQLancer’s job is to stress a database system into returning inconsistent or illogical results. Written in Java, it supports several databases (including Postgres).

Manuel Rigger and Zhendong Su

supported by Retool

💡 Tip of the Week

GROUPING SETS

Grouping sets let you perform grouping within a query that's more complex than simple GROUP BY column can do.

Given a table like this:

  name  | dept | location | salary
--------+------+----------+--------
 Abbey  | IT   | London   |  85000
 Paul   | IT   | Madrid   |  74000
 Clancy | HR   | London   |  71000
 Imani  | HR   | Madrid   |  74000

We could easily get the total salaries for each 'department' with a query like:

SELECT dept, SUM(salary) FROM staff GROUP BY dept;

But what about if we want the total salaries in various ways within the same query? For example, let's say we want the total salaries for everyone, the total salaries for each department, and the total salaries for each location, all within the same query. Can it be done?

Enter 'grouping sets' which lets you define multiple sets of grouping criteria each of which will be performed separately and appended to the result set.

For example, let's group the results by department, then by location, and finally by the empty set (i.e. the total):

SELECT dept, location, SUM(salary) FROM staff
GROUP BY GROUPING SETS ((dept), (location), ());

This gives us a set of results like so:

 dept | location |  sum   
------+----------+--------
      |          | 304000
 IT   |          | 159000
 HR   |          | 145000
      | London   | 156000
      | Madrid   | 148000

Now we can easily see from a single set of results that the staff in our example are paid more in London, paid more in the IT department, and the overall wage bill is 304000.

This week’s tip is sponsored by Retool. Build internal tools in minutes, not days. Retool provides powerful building blocks that connect to any DB so you can quickly build the tools your company needs.

🗓 Upcoming Online Events

  • Postgres Pulse - weekly at 11am ET each Monday. Weekly Zoom-based sessions with folks like Bruce Momjian, Vibhor Kumar, and other people at EnterpriseDB.
  • Postgres Vision 2020 on June 23-24. A full attempt at an online Postgres conference across multiple days with multiple tracks.