#312 — July 3, 2019

Read on the Web

Postgres Weekly

▶  Being a Better Developer With EXPLAIN — An interesting 40 minute talk that digs into the world of query parsing and planning and how to use the EXPLAIN command to create query plans for analyzing the efficiency of your queries and your database’s design.

Louise Grandjonc

A Dive into PostgreSQL Defaults and Their Impact on Security — A two part exploration (part 2 is here) of Postgres’s default security settings and how the crunchy_check_access extension can help you dig deeper.

Joe Conway

Monitoring Your PostgreSQL Database with Telegraf and InfluxDB — This tutorial will specifically cover the process of setting up Telegraf and InfluxDB to monitor PostgreSQL.

InfluxData sponsor

Another Introduction to GIN Indexes — We’ve been seeing many great articles about Postgres indexes lately and Louise Grandjonc has one here focused on GIN (Generalized Inverted Index) indexes, as well as how to use them.

Louise Grandjonc

The Slow Climb of Postgres and the Value of Persistence — Attendees at the recent PostgresVision conference heard from database pioneer Dr. Michael Stonebraker. He gave an account of how he helped bring Postgres into the world — a 15 year journey of amazing highs and lows.

Joab Jackson (The New Stack)

The Challenge of Setting max_connections and Why to Use a Connection Pooler — A look at why max_connections can be a tricky Postgres setting to get right and how you can use some benchmark testing to approach a reasonable figure for your own setup.

Richard Yen

How the CIS Benchmark for Postgres 11 Works — The CIS PostgreSQL Benchmark is a set of guidelines and best practices issued by the Center for Internet Security on how to test Postgres. Here’s a look at how it can be used to improve your own Postgres setup and as part of automating the security verification process.

Douglas Hunley

Postgres Ibiza: A Different Type of Conference — The Postgres conference scene took a bold step last month with Postgres Ibiza, a resort-based conference on Spain’s (in)famous party island. But.. Bruce was won over.

Bruce Momjian

How to Regain Access to Postgres If Your Admin User Can't Log In — You can lean on Postgres’s single user mode to regain access.

Luca Ferrari

PostgreSQL Logging Best Practices — Here are a few best practices to keep everyone on the same page.

strongDM sponsor

How to Setup PgBouncer as a Connection Pooling Proxy on Azure DB for PostgreSQL

Parikshit Savjani

Bulding Postgres and OpenSSL using MSYS2 and MinGW under Windows — I’m a little naive to Windows, alas, but this walks through the steps of building Postgres for yourself on Windows using MSYS2, a Cygwin-derived POSIX compatibility layer.

Pavlo Golub

PgBouncer 1.10.0 Released — PgBouncer is a lightweight connection pooler and this latest release improves TLS 1.3 support amongst other things.

Peter Eisentraut

db-to-sqlite 1.0: A CLI Tool for Exporting Tables or Queries From Any SQL Database to SQLite

Simon Willison

supported by GitPrime

💡 Tip of the Week

Trading durability for extra performance

Durability in Postgres is the concept of ensuring that any data-related operations you perform get committed successfully to disk, so that if the server Postgres is running on suddenly restarts or loses power, it can return to the right state later on.

The need for durability can vary depending on your use case or what operations you want to perform. For example, when loading a large data set into a new database, you might trade short term durability for raw performance, whereas if you're running an account ledger in real time, durability is more important than performance!

If you're happy to trade durability for raw performance, temporarily or not, there are several settings you can tweak (in the file shown by SHOW config_file;) to make it happen:

Want to return to this tip in future? You'll want Postgres's Non-Durable Settings documentation.

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