#355 — May 13, 2020

Read on the Web

Postgres Weekly

Why You Shouldn't Manually Modify the Postgres Data Directory — If you’re tempted to ask “Why?” when told to not ever fiddle around with the contents of Postgres’s data directory, this is for you. It’s complicated.

Robert Haas

now() vs. 'NOW'::timestamp vs. clock_timestamp() — Ah, dates and times, the bane of any app developer’s life.. and database users too :-) This post looks at the different flavors of working with the current time in Postgres.

Hans-Jürgen Schönig

💻 Live Coding: Guide to Grafana 101 - Getting Started with AlertsJoin us on May 20th to see how to use Grafana’s alerting functionality to get notified about anomalies in your data, dig into root causes, and respond to critical issues. Step-by-step demos + tips = cheaper, more flexible monitoring ✅.

Timescale sponsor

Who Contributed to Postgres Development in 2019? — Robert’s annual look at who contributed the most to Postgres development, if you go by lines of code added, committers of other people’s code, and participants on the pgsql-hackers list.

Robert Haas

On the Improved (Auto)Vacuum in Postgres 13 — An overview of six different improvements being made to Postgres’s auto-vacuuming capabilities in the next major version.

Amit Kapila

The Internals of Postgres — We’ve linked it a couple of times over the years, but if Bruce Monjian hasn’t seen something, I’m going to assume many of you haven’t either. There’s a lot of gnarly stuff in here, but if you can follow even a bit of it, you’ll learn a lot about how Postgres does its business.

Hironobu SUZUKI

Network Latency Does Make a BIG Difference — Getting your queries as fast as possible is great, but network latency is important too. Hans-Jürgen demonstrates how to measure its impact in a situation with simulated latency.

Hans-Jürgen Schönig

Your Data Is Your Business — PGX is a full-service database consultancy focused on PostgreSQL data systems, on any platform or hosting environment.

PostgreSQL Experts, Inc. sponsor

Fuzzy Searching with PostgreSQL

Kevin Alemán

Using DISTINCT ON (from Ruby) to Avoid an N+1 Query“Recently I fixed a tricky N+1 query and thought I should write it up..”

John Nunemaker

Local Persistent Volumes and Postgres Usage in Kubernetes — 2ndQuadrant ran some Postgres benchmarks on Kubernetes 1.17 to test the performance of local persistent volumes using OpenEBS Local PV.

Gabriele Bartolini

supported by Retool

💡 Tip of the Week


Let's say you have a basic people table like this:

If you wanted to return rows matching people with an age between (and inclusive of) 25 and 50, you could write a query like this:

SELECT * FROM people WHERE age >= 25 AND age <= 50;

So far, so good, but you can also use the BETWEEN operator to tighten things up a bit:

SELECT * FROM people WHERE age BETWEEN 25 and 50;

(Extra tip: You can use NOT BETWEEN to get the opposite effect!)

This is great and makes things very readable, but what if the values were switched around, such as BETWEEN 50 and 25? It won't work as the values have to be in order.



This query will put the range limits in the correct order and return the relevant two rows. Do note that this is a Postgres specific extension to BETWEEN, though (mostly! - HSQLDB supports it as well) so it depends how universal you want your SQL to be :-)

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

🗓 Upcoming Online Events

  • Bruce Momjian on 'The Future of Postgres Sharding' - on May 15/16 (depending upon timezone). The first in a series of sessions being run by the China PostgreSQL Association.
  • 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.