#​432 — November 24, 2021

Web Version

Postgres Weekly

Entity-Attribute-Value (EAV) Design in Postgres and Why Not to Do It — EAV provides a way to get key value-esque flexibility of adding properties to objects on the fly but in a structured, relational way that works with any standard relational database. Is it a worthwhile idea now that we have JSONB? I don’t think so, and neither does the author.

Laurenz Albe

Should I Create an Index on Foreign Keys? — Spoiler: “The takeaway here is that we should not indiscriminately create indexes on all FKs because many of them will just not be used or so rarely used that they aren’t worth the cost.”

Charly Batista (Percona)

Meet the Only Bug You’ll Ever Love: CockroachDB Serverless — Elastic scale doesn’t have to mean sacrificing consistency or familiarity. Get effortless scaling, automated ops, and guaranteed transactional consistency with CockroachDB Serverless, the world’s most evolved SQL database.

CockroachDB Serverless sponsor

Does VARCHAR(n) Use Less Disk Space Than VARCHAR() or TEXT? — Once upon a time on certain databases, limiting your VARCHAR columns to a specific length could have space advantages, but with Postgres and its TOAST system, it’s just not the case.

Hubert depesz Lubaczewski

What's New with JSON in Postgres 14 — There are no huge leaps forward but Postgres 14 provides some niceties for working with JSON documents in the form of subscripting.

Sarah Chima Atuonwu

Using Postgres Views in Django — Python and Django user? You’ll want to check this out. It shows how views differ from materialized views as well as how using views makes querying aggregated data easier and (with materialized views) faster.

Josh Alletto

⚡ 13 Tips to Improve PostgreSQL Insert Performance

Timescale sponsor

Using TimescaleDB with the PGO Postgres Operator — Interested in trying out TimescaleDB with the Crunchy Postgres Operator for Kubernetes? Here’s a quick guide to set it up.

Jonathan S. Katz

📊  Enterprise Postgres Growth in Japan — EDB’s Bruce Momjian gave a talk for the Japan PostgreSQL User Group on how Japanese users were early adopters of Postgres and how Japanese developers keep Postgres moving forward. No video yet.

Bruce Momjian slidedeck

Isolating Postgres with repmgr — Advanced repmgr use cases to up your Postgres High Availability game.

Shaun Thomas

pg_dirtyread: Read Dead But Unvacuumed Tuples from a Relation — So if a row has been deleted, say, you could potentially still read it.

Christoph Berg

Sqitch 1.2: Database Change Management Tool — A database and framework independent (Postgres 8.4+ is supported) system for managing database and schema changes via SQL scripts. Here’s how it works with Postgres.