#315 — July 24, 2019

Read on the Web

Postgres Weekly

Standby in Production: Scaling at the Second Largest Classified Site in the World — Avito is Russia’s largest classified ads site and stores 20TB of data across a hundred Postgres nodes. Here’s a very detailed, behind the scenes look at their Postgres high availability setup.

Konstantin Evteev

Automatic Index Recommendations using pg_qualstats and HypoPG — HypoPG is a Postgres extension for creating ‘hypothetical indexes’, essentially virtual indexes you can use to test if a certain index would improve your queries ahead of time. This article takes things to the next step by automatically trying to find indexes worth creating.

Avinash Vallarapu

Chef’s CTO Chooses StrongDM — Adam Jacob says “strongDM takes the friction out of getting end users access to the systems they need.” Zero trust access to any database, server or k8 cluster. Click here to try for free.

strongDM sponsor

Don’t Forget the Benefits of BRIN Indexes — BRIN indexes (which provide an efficient way to index values that can be grouped into ‘ranges’) were introduced in Postgres 9.5 and have some specific use cases. This post benchmarks a situation where they can really help.

Jobin Augustine

The Random Ordering of Results with SQL — An article that covers the use of ORDER BY to randomly sort rows in several database-specific ways, including Postgres.

Vlad Mihalcea

Checking the Version of Postgres in psql Scripts — Let’s say you want to run different code depending on the version of Postgres that’s running.. you can!

Luca Ferrari

(Even More) Postgres Tips for the Average and Power User — Hopefully you’ve been enjoying our various ‘tips of the week’ we’ve been including in Postgres Weekly this year, but here, Craig Kerstiens unearths a whole lot more :-)

Craig Kerstiens

Proactively Monitor PostgreSQL Performance with Datadog — Improve PostgreSQL performance with actionable alerts on latency spikes, errors, anomalies, and more. Smart monitoring with Datadog - try it free.

Datadog sponsor

How Are Functions and Stored Procedures Replicated? — A brief look behind the scenes at how Postgres actually stores functions and procedures and how that feeds into their replication.

Hans-Jürgen Schönig

A Certificate Authentication Recipe for Postgres Docker Containers — Learn how to set up PostgreSQL certificate-based authentication with a simple Docker container recipe.

Jonathan S. Katz

The 'Mysterious' backend_flush_after Configuration Setting — This is a setting very specifically for server operators (and only those using Linux too) looking to squeeze out some extra performance.

Kaarel Moppel

Configuring pgBackRest to use Amazon S3 — pgBackRest is a powerful backup and restore tool and can be configured to use AWS S3.

Stefan Fercot

supported by Gremlin

💡 Tip of the Week

Using ROLLUP to get an overall results row for aggregations

If you want to group and aggregate results together, using GROUP BY with a single column is a common approach, but ROLLUP is one of a handful of clauses that can help you take things to another dimension by letting you group by multiple sets in a single query.

This is a feature best demonstrated live, so let's create a table and load in some sample data.. the number of goals scored by different soccer teams in different years:

CREATE TABLE goals (
  team TEXT, year INT, goals INT);

INSERT INTO goals VALUES
  ('USA', 2010, 5), ('USA', 2011, 3),
  ('USA', 2012, 12), ('ENG', 2010, 17),
  ('ENG', 2011, 9), ('ENG', 2012, 11);

If we wanted to get the average number of goals scored per year, per team, we could do this:

SELECT team, ROUND(AVG(goals))
  FROM goals GROUP BY team;

But let's say we wanted to not only group by teams but also by the overall table (i.e. no column) to get the number of average goals overall. We could do this:

SELECT team, ROUND(AVG(goals))
  FROM goals GROUP BY ROLLUP(team);

This gets us a result like so:

  team | round
---------------
   ENG | 12
   USA | 7
 (null)| 10

There's a lot more to this than we can cover here, so enjoy this tutorial to learn more.

This week's tip is brought to you by Gremlin, a platform that helps you build more resilient software through running thoughtful Chaos Engineering experiments. Learn more at gremlin.com.

🗓 Upcoming Postgres Events