#306 — May 22, 2019

Read on the Web

Postgres Weekly

Don't Do These Things in Postgres — An interesting page on the official Postgres wiki that attempts to round up a variety of ‘common mistakes’ in using Postgres, such as “Don’t use char(n)” and “Don’t use serial“. Some of these are opinionated, but are well backed up with reasons.

Postgres Wiki

PostgresLondon 2019 Schedule Out Now 🇬🇧 🐘  — Join us in London for the evolution of PGConf UK to learn more about recent achievements & exciting future developments in PostgreSQL. You can also participate in hands-on training with experts covering: Performance Tuning, PostgreSQL Security, & Multi-master Replication.

2ndQuadrant PostgreSQL Events sponsor

Using pg_cron to Automatically Schedule Database Tasks — pg_cron is a Postgres extension that runs a cron-like job scheduler within a running Postgres instance allowing you to run commands of your choice at defined intervals or times.

Luca Ferrari

A Look at RUM Indexes — A few weeks ago we linked to an amazing introduction to GIN indexes, now the same author is back with a similar deep dive into the world of RUM, essentially an extended type of GIN index for specific use cases. It’s not a built-in index type, however.

Egor Rogov

Abusing SECURITY DEFINER Functions — Functions defined with SECURITY DEFINER have the privileges of the user that created the function which, naturally, has security consequences. Here’s a look at how even a seemingly ‘harmless’ function can go awry.

Laurenz Albe

A Health Check Playbook for Your Postgres Database — As data comes in and queries are executing, the composition of your database will change - here, Craig suggests some things you can do frequently to ensure the long term health of your database.

Craig Kerstiens

Getting The Most Out of Your Postgres Indexes — Quick examples and tips around covering indexes, partial indexes, multi-value indexes, and more.

RapidLoop

Driving Time-Series Database Performance with PostgreSQL & TimescaleDB — Learn how ClusterControl can monitor, manage, and ensure the performance of your time-series data.

Severalnines sponsor

Considering Multi-Column Indexes — A look at some of the downsides to multi-column indexes and how to consider if they fit your use case.

David Conlin

How to Deploy Postgres to a Docker Container with ClusterControl

Sebastian Insausti (Severalnines)

pgBackRest: A Year of Growth for a 'Great Backup Solution' — “pgBackRest addresses many of the must-have features that you’ll want to look for in a PostgreSQL backup solution.”

Jobin Augustine

supported by GitPrime

💡 Tip of the Week

Domains: A way to bundle up check constraints and defaults into simple types

A domain is a lightweight data type that has its own optional default value and constraints and is an SQL:2003 feature that has been supported in Postgres for several years now.

For example, let's say you wanted to store an adult age (>18) on a table, but rather than use an integer and a check constraint, you could create your own 'domain' to the same ends:

CREATE DOMAIN adult_age AS INTEGER CHECK (VALUE >= 18);

This new adult_age type/domain can now be used in a table in the usual way:

CREATE TABLE adults (name text, age adult_age);
            
INSERT INTO adults VALUES ('Ahmed', 17);
→ ERROR: value for domain adult_age
         violates check constraint
         "adult_age_check"

You can add constraints or default to a domain (using ALTER DOMAIN). For example, to set a default age to our type:

ALTER DOMAIN adult_age SET DEFAULT 18;

There's more information on the domain feature in Postgres' docs.

This week's tip is sponsored by GitPrime. Get your copy of their new field guide '20 Patterns to Watch for in Engineering Teams', filled with actionable insights to help debug your development process with data.

🗓 Upcoming Postgres Events