~postgresweekly | Bookmarks (128)

Plugging the Postgres Upgrade Hole? — The author claims that community discussions often raise the point that Postgres is difficult to upgrade – but is that a fair point? Shaun looks at how Postgres approaches upgrades, limitations in the pg_upgrade approach, and considers how things could be made better.

ProxySQL Expands Database Support to Postgres — ProxySQL is an open source MySQL proxy that has, now, introduced Postgres support in anticipation of its v3.0 release. v3.0.0-alpha is now available for testing.

pgCompare: A Java App to Check Post-Replication Data Consistency — If you’re moving data from one database to another, say, you can use this to perform a comparison to check the integrity of the migration.

OneSparse: Accelerated Sparse Linear Algebra for Postgres — Like JSON/JSONB types did for unstructured data, OneSparse wants to make dense and sparse matrices and vectors fully native Postgres types along with optimized parallel matrix multiplication and other operations like reduction, selection, assignment, extraction, element-wise union, and intersection.

Migrating In-Place from Postgres to MySQL – (!) — Not the typical migration we’d be talking about here! But Yelp’s status as a heavy user of MySQL played a big role when needing to integrate a Postgres-powered site they acquired. They had to make some compromises due to Postgres-specific functionality, but with few Postgres skills in-house, they deemed it a necessary sacrifice.

Speaking in Tongues: Postgres and Character Encodings — Character encodings may be the definitive ‘important but I don’t want to think about it’ topic when it comes to databases. Luckily, using UTF-8 gets you most of the way to a happy place.

How to Compare Postgres EXPLAIN Plans & Tune Slow Queries — Are slow Postgres queries impacting your app performance? Join this webinar to learn how to diagnose and tune them effectively. We'll use pganalyze to walk through how to compare plans & debug common scenarios like inefficient nested loops & missing indexes.

Case Study of Optimizing a 'Weirdly Picked' Bad Plan — “We recently hit an interesting case where planner picked wrong plan. Kinda. And figured it could be used to show how to deal with such cases.”

Using CTID Based Pagination for Data Cleanups — The CTID field is unique for every row in a table but is no mere simple row ID. It’s a tuple that combines both physical page and row position information, and can help you process a table in predictable chunks based on physical storage.

How to Build an Image Search App with OpenAI CLIP, Postgres, and JavaScript — A tutorial that brings together a lot of ideas in one place. CLIP is used to turn images into text descriptions. Postgres is used as a vector database. JavaScript provides the glue for both the frontend (with React) and backend (Node.js).

Request-Reply in Postgres — “I wondered the other day if you could build a request-reply mechanism using plain Postgres components. Turns out, you can! Whether or not you should is left as a decision for the reader.”

Postgres Version Report — An online tool where you paste your Postgres server’s full version information and you get to where it stands in relation to Postgres’ release schedule, outstanding CVEs, bugs, and what improvements have been made in later versions.

pg_duckdb: DuckDB-Powered Postgres for High Performance Analytics — An official Postgres extension (built in collaboration with Hydra and MotherDuck) that embeds DuckDB’s columnar-vectorized analytics engine and features into Postgres.

pg_parquet: An Extension to Connect Postgres and Parquet — A new open-source extension for working with Parquet files. It reads and writes parquet files to local disk (or S3) right from Postgres. GitHub repo.

pgdoc.link: A New Way to Search Postgres Documentation — Hubert has been looking for a way to work around inconsistencies in looking up Postgres docs – this is the result. If there’s a single, canonical document for what you search for, you’re taken straight to it, otherwise you get a page helping you resolve the ambiguity (e.g. *_connections). This is a neat idea.

4 Ways to Create Date Bins in Postgres — When you need to generate a report or otherwise bundle up data within certain ‘bins’ of dates (e.g. a full month, quarter, or year) Postgres offers several ways to do it. Christopher looks at INTERVAL, date_trunc, extract and to_char, each of which has its pros and cons.

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. Now supports Postgres 17 as of v3.2.4.

Greenmask: Postgres Data Masking and Obfuscation Tool — A Go-powered utility for dumping a Postgres database and anonymizing the data in a deterministic way. GitHub repo.

Postgres Type Explorer: See Data Types for Various Use Cases — As part of the launch of Mastering Postgres, Aaron Francis has unveiled an online tool that offers recommendations of which data types to use for different situations (e.g. storing hashes, monetary amounts, UUIDs, binary data). It also gives indications of how much storage space each could use.

Is pg_dump a 'Backup' Tool? — I’ve used pg_dump to make backups, and probably so have you, but the docs seem keen to stress it’s really an export tool. Robert ponders the narrative.

Trigger Recursion and How to Deal with It — If you’ve set up a trigger whose behavior results in the same trigger being triggered, you’re in for a lot of potentially tricky to debug problems. Luckily, it’s trivial to tweak triggers to only run when intended.

Get Proactive with Postgres Performance with This Monthly Check List — Staying on top of PostgreSQL maintenance can be challenging. Our monthly check list guides you through critical tasks to maintain performance, reliability, and uptime—quickly and effectively. Download your free check list today.

Convert JSON into Columns and Rows with JSON_TABLE — New Postgres 17 feature alert! JSON_TABLE is a function that lets you query JSON data and get results in a typical, relational view. Paul shows us a practical use case.

SQL/JSON is Here (Somewhat!) — JSON_TABLE (above) is great, but makes up just one part of the SQL/JSON experience available. SQL/JSON is a broad term for a path language, query functions, and operators for working with JSON data from SQL, and here we see more of what Postgres 17 brings to the JSON table.