Bookmarks (1466)

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

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.

📊  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.

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.

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.”

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.

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.

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.

PGroonga: Extend Full Text Search Features to All Languages — Brings the Groonga fulltext search engine to Postgres which can suit certain use cases better than the Postgres default, such as with Japanese or Chinese.

Generating More Realistic Sample Time-Series Data with generate_series() — If you get deja-vu from the title, Timescale has covered using generate_series() before, but this takes a step-up in terms of realism. Goes into a lot more depth than you’d expect.

PostgreSQL 14.1, 13.5, 12.9, 11.14, 10.19, and 9.6.24 Released — Phew, try and say all of those version numbers in a row without getting tongue tied.. Releases come all round to resolve a variety of bugs and two man-in-the-middle security vulnerabilities. Note that this is also the last ever release of Postgres 9.6.

dynamodb_fdw 1.0: A Foreign Data Wrapper for DynamoDB — I haven’t tested it myself yet, but I’m surprised this only picked up one star so far. The idea here is to provide access from Postgres to Amazon’s DynamoDB key/value document database.

An Introduction to pg_auto_failover — pg_auto_failover is an extension that provides automated failover within a Postgres cluster. This post tells the entire story of the extension, how it works, and where its value lies.

Installing Postgres with WSL2 on Windows — The Windows Subsystem for Linux (WSL) provides a compatibility layer for running Linux binaries on Windows which means you can build Postgres in a Linux-oriented fashion and run it on Windows, if you like, rather than using a native Windows build.

Easily Running Babelfish for Postgres on Kubernetes — Babelfish is an open-source project from AWS that adds a SQL Server compatibility layer on top of Postgres, and StackGres is a platform for running Postgres on Kubernetes. You can now bring those two together.

Analyzing Scans in Postgres — Understand the different types of scans and why the query planner chooses them in different situations. If you’ve seen terms like ‘sequential scan’ or ‘bitmap heap scan’ in query plans and not quite understood what they mean, this provides some (quite basic) explanations.

ORDER BY is Mandatory in SQL to Get a Sorted Result — One of those things you might implicitly know without thinking about.

Cloudflare Workers Introduces Relational Database Connectors — Workers is Cloudflare’s globally distributed serverless function platform and they’ve added a feature for connecting functions to internally hosted relational databases (including Postgres) by way of a tunnel.

Lots of Lesser Known Postgres Features — Haki’s articles are often catnip to us as they cover a lot of ground, and so it goes with this roundup of practical examples of Postgres features you might not be familiar with. Worth a skim of the Table of Contents at the very least!

pspg 5.5.0: The Postgres Data Pager — Some UI tweaks, plus support for a streaming mode for files on BSD.

Finding Revenue Accrued Per Day with SQL — Some helpful queries and examples for revenue accruing per day over the course of a month.

Three Cases Against Using IF NOT EXISTS / IF EXISTS — Many DDL statements accept IF (NOT) EXISTS modifiers to let statements only run under certain conditions, but overuse “may lead to negative consequences” argues Nikolay.