---
title: "Don't modify Postgres's data directory"
publication: "Postgres Weekly"
issue: 355
published_at: "2020-05-13"
canonical_url: "https://postgresweekly.com/issues/355"
---

This week's Postgres news, views, and tools.       

#355 — May 13, 2020 

[Read on the Web](https://postgresweekly.com/issues/355)   

Postgres Weekly 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/v1589300029/pryzuzd84hdzubadddkj.jpg)](http://rhaas.blogspot.com/2020/05/dont-manually-modify-postgresql-data.html)  

[Why You Shouldn't Manually Modify the Postgres Data Directory](http://rhaas.blogspot.com/2020/05/dont-manually-modify-postgresql-data.html) — 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()`](https://www.cybertec-postgresql.com/en/postgresql-now-vs-nowtimestamp-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   [![\](https://copm.s3.amazonaws.com/72aacb41.jpg)](https://tsdb.co/guide-to-grafana-alerting) 

[💻 Live Coding: Guide to Grafana 101 - Getting Started with Alerts](https://tsdb.co/guide-to-grafana-alerting) — [Join us on May 20th](https://tsdb.co/guide-to-grafana-alerting) 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?](http://rhaas.blogspot.com/2020/05/who-contributed-to-postgresql.html) — 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](http://amitkapila16.blogspot.com/2020/05/improved-autovacuum-in-postgresql-13.html) — 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](http://www.interdb.jp/pg/) — We’ve linked it a couple of times over the years, but if Bruce Monjian [hasn’t seen something](https://momjian.us/main/blogs/pgblog/2020.html#May_8_2020), 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](https://www.cybertec-postgresql.com/en/postgresql-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](https://pgexperts.com/) — 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](https://dev.to/kaleman15/fuzzy-searching-with-postgresql-97o) 

Kevin Alemán   

[Using `DISTINCT ON` (from Ruby) to Avoid an N+1 Query](https://johnnunemaker.com/postgres-distinct-on/) — _“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](https://www.2ndquadrant.com/en/blog/local-persistent-volumes-and-postgresql-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\](https://res.cloudinary.com/cpress/image/upload/v1586255470/csxhe00zlog0bec0ptmf.png)](https://retool.com/?utm_source=postgresweekly&utm_medium=newsletter&utm_campaign=may_13&utm_content=days)  

💡**Tip of the Week** 

`BETWEEN` and `BETWEEN SYMMETRIC` 

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

![](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/v1589197107/par7aprhjllesvagqzjw.png)   

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. 

Enter `BETWEEN SYMMETRIC`:  

SELECT * FROM people WHERE age BETWEEN SYMMETRIC 50 and 25;   

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](https://retool.com/?utm_source=postgresweekly&utm_medium=newsletter&utm_campaign=may_13&utm_content=days). Build internal tools in days, not weeks. [Retool provides UI building blocks](https://retool.com/?utm_source=postgresweekly&utm_medium=newsletter&utm_campaign=may_13&utm_content=days) 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'](http://www.postgresqlchina.com/newsDetails/23) - on May 15/16 (depending upon timezone). The first in a series of sessions being run by the China PostgreSQL Association.
- [Postgres Pulse](https://www.enterprisedb.com/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](https://www.postgresvision.com/) on June 23-24. A full attempt at an online Postgres conference across multiple days with multiple tracks.          

Curated by [Cooperpress](https://cooperpress.com/), [Peter Cooper](https://twitter.com/peterc), and [Craig Kerstiens](https://twitter.com/craigkerstiens). 

[Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) 

© Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom