#​470 — September 7, 2022

Web Version

Postgres Weekly

📊  How to Plot an ASCII Bar Chart with SQL — Want to see an epic SQL query (which you could spend a good twenty minutes studying on its own) that results in something arguably quite useful? It could be interesting to turn this into a function you could use from psql..

Lukas Eder

Full SQL/JSON Support Deferred from Postgres 15 — While Postgres has a lot of ways to work with JSON data already, SQL/JSON is more of a standard for working with JSON from SQL generally. Postgres has supported its path language for a while, but patches to bring more complete support to v15 have been reverted, with a hope it can become a headline feature in v16 next year instead. Read the full discussion here.

Hubert depesz Lubaczewski

The What, Why, and How of Time-Series Databases — Time-series databases are the fastest growing database category – but what are they? Get a primer on what time-series data is - complete with real-world examples - when and why “normal” databases aren’t enough and ways to get started.

Timescale sponsor

Working with Postgres Dump Manifests — Postgres dumps store not just what’s in the database but metadata about those databases too (in the form of manifests). Why is this interesting? You can edit the manifests to affect how such dumps are restored later!

Robert Bernier

Parallel Vacuuming in Amazon RDS and Amazon Aurora — No, this isn’t about vacuuming in RDS and Aurora at the same time(!) but about using Postgres 13+’s parallel-operating vacuuming process on both services. (Most of the article applies to Postgres generally, however.)

Bhattacharyya and Asadulla Baig (AWS)

The Diversity of Postgres's Configuration Parameters — PostgreSQL has a lot of parameters to be configured. Guess how many. We won’t spoil it (this time) but it’s enough that Stefanie has managed to break them down to 15 groups. She also provides some pointers for working with them.

Stefanie Janine Stölting

"ERROR: nextval: reached maximum value of sequence" — Basic advice when using the serial pseudo-type (a handy notation for creating an integer-based auto-incrementing column). A better option is to use serial8 or bigserial to get 64 bits instead which should last you.. quite a while :-)

Hans-Jürgen Schönig

How to Create an Audit Trail for an Aurora PostgreSQL Table — You could use a trigger on a table and track everything that occurs in another table, but that introduces too much of a performance overhead, says Rana. AWS Aurora offers a different approach using AWS Database Migration Service (DMS) to create a continuous audit log table on a separate database instance.

Rana Dutt (AWS)

Your Database, At The Edge

PolyScale.ai sponsor

Digging into Postgres and OS Memory Allocation — Few folks need to go down to this level, but if spying on the interaction between Postgres and Linux’s memory allocation systems interests you..

Frits Hoogland (YugabyteDB)

▶  Discussing Postgres and Ruby with Andrew Atkinson — Ruby-flavored Postgres database discussion covering areas like constraints, catching unsafe migrations, and analyzing query plans.

Code with Jason Podcast podcast

🛠 Code and Tools

Kanel: Generate TypeScript Interfaces from Postgres — It works by inspecting a live database, sort of like a reverse object/relation mapper. GitHub repo.

Kristian Dupont

Mikro ORM 5.4 – TypeScript ORM for Node.js based on Data Mapper.
TimescaleDB 2.8 – Popular time-series data extension.
scany 1.2 – Go library to scan data from a database into structs.
Slonik 30.4 – Node.js Postgres client with type safety.
postgres-meta 0.45 – A RESTful API for managing Postgres.