#388 — January 13, 2021

Web Version

Postgres Weekly

Re-Introducing Hash Indexes in Postgres — Billed as the ‘ugly duckling of index types’, this is a look at an uncommonly used type of index whose use was discouraged in the past but whose performance can sometimes beat the trusty B-tree as we see here.

Haki Benita

int4 vs. float4 vs. numeric — Postgres offers many different data types and this post focuses on three important ones for storing numbers and their differences.

Hans-Jürgen Schönig

Free eBook: Efficient Search in Rails with Postgres — Speed up a search query from seconds to milliseconds and learn about exact matches, trigrams, ILIKE, and full-text search.

pganalyze sponsor

Amazon RDS for PostgreSQL Supports 12.5, 11.10, 10.15, 9.6.20, and 9.5.24 — Support has also been added for pg_cron (for scheduling maintenance commands) and pg_partman (for managing partitions).

Amazon Web Services

Waiting for Postgres 14: Add idle_session_timeout — Have sessions terminated when they’re been waiting around for too long.

Hubert depesz Lubaczewski

Using R in Postgres for Logistic Regression Modeling — Continuation of a ‘Postgres for Data Science’ series of posts that moves on to doing data modeling and fire occurrence predictions with R right from the database.

Steve Pousty

How to Limit Rows to At Most N Per Category (Take Two) — Several issues ago we linked to a handy tutorial demonstrating how to limit the rows returned by a query to a certain number per some second condition. It had a problem related to concurrency, however, so Hubert has a second go at it here.

Hubert depesz Lubaczewski

⏰ 12 Things You Need to Know About Time-Series DataGet our top advice and time-series resources, from PostgreSQL pro tips and example projects to database benchmarks & more.

Timescale sponsor

Sysbench: In-Memory Postgres, Postgres Is Boring — Sometimes no news is good news. “I am happy to claim that Postgres is boring as there are no CPU regressions from version 11 to 13.”

Mark Callaghan

Hosting Postgres on an AWS EC2 t4g Graviton2 ARM Instance — Sure, AWS has its managed RDS service for running Postgres, but if you fancy trying it out on EC2’s newer ARM-based instances, here are some pointers.

Pedro Alonso

The Mapping of oid and relfilenode — Technical ‘under the covers’ stuff about how the location of tables are tracked internally. Few people need to understand this, but perhaps many more would like to :-)

Movead Li

🔧 Tools and Code

pg_back 1.10: A Bash Script for Dumping Databases to Files — Essentially a wrapper around pg_dumpall and pg_dump to add a bit more structure to a simple database backup process. GitHub repo.

Nicolas Thauvin

PostgresNIO: Non-Blocking, Event-Driven Client for Swift


PGMoon 1.12.0: A Pure Lua Postgres Driver for Use in OpenResty and More