~postgresweekly | Bookmarks (119)

Postgres Reimagined for Data Warehousing — Crunchy Data Warehouse combines managed storage, a powerful analytics engine, and unified data pipeline. Seamlessly query data using familiar Postgres tools with the added benefit of columnar storage and performance.

DBngin: Simple Postgres, MySQL and Redis on Mac and Windows — You could use Docker, you could use Postgres.app.. but if you’d like to spin up different versions of several common databases using a simple UI, this could fit the bill.

The Doom That Came To Postgres: When Collations Change — What can happen when the library that provides locales changes out from under an existing Postgres database? It’s not pretty..

Running Postgres on Spot VMs? 🤯 — Building on a talk from PGConf Europe, Kaarel looks at how viable it is to run Postgres on instances that could, well, disappear at almost any time. pg-spot-operator is a utility he’s built to make it work.

Easy Totals and Subtotals in Postgres with Rollup and Cube — Handy additions for performing more analytical queries. ROLLUP can create hierarchical subtotals along with a grand total, while CUBE generates subtotals for every possible combination of grouping columns.

Learn PostgreSQL with Redgate — New to PostgreSQL? Check out Redgate’s PostgreSQL learning resources hub for easy-to-follow webinars, how-to articles, and a supportive community forum. Boost your skills and save time by learning tips, tricks and best practices from experts.

pgvector 0.8.0 Released — There’s a post about this release of the popular vector indexing and querying extension on the Postgres site, but I like this writeup as it digs into a change in index scans. With 0.8, we get improved performance, support for iterative index scans, and a way to cast arrays to sparsevec.

▶  The PostgreSQL Conference Europe 2024 Talks — Europe’s annual Postgres conference went down well in Athens last month and there are now a whopping sixty videos to enjoy. We haven’t had time to go through them yet, but will be featuring some in forthcoming issues.

8 Steps in Writing Analytical SQL Queries — If you’ve got a complex analytical query to write, start small and build from the ground up to avoid issues creeping in, says Christopher, who has an eight step rubric to doing just that.

Understanding Volatility in PL/pgSQL Functions — Deepak shares a real-world example of the importance of understanding the different levels of function volatility you can specify for PL/pgSQL functions.

Text Identifiers in Postgres Database Design — A variety of things to consider if, rather than numbers or UUIDs, you’re working with textual identifiers for your records.

From Backup to Integrity: Leveraging WAL-G — Making backups is one thing, ensuring their integrity in the face of hardware or network issues is another, but it’s possible to use checksums to detect integrity problems.

PostgreSQL Workload Analyzer (PoWA) 5.0 Released — A performance tool for collecting and aggregating metrics from multiple Postgres instances which can then be viewed through real-time charts and used to help optimize your setup. v5 has a much improved Web UI and offers more data sources than before, including WAL stat views, an IO stats view, and info on background writers and replication slots.

Schemalint: A Linter for Postgres Schemas — A linter that lets you raise errors on a variety of common matters like name casing, enforcing RLS, or the presence of mandatory columns (so think more like ESLint than a basic formatter). You can also write your own custom rules.

pg_flo: Stream, Transform, and Route Postgres Data in Real-time — Offers a set of filters and transformations that can make moving data between, say, production and staging databases simple using one of two modes: copy-and-stream or stream-only. Uses NATS and Postgres’s replication.

Benchmarking Postgres vs MariaDB and MySQL — Using a custom-built benchmarking tool, Reserva, the author found Postgres leads the way, performance-wise. Usual disclaimers about the troubles of benchmarking and getting accurate results, etc. etc..

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.

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.

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.

pg-dump-parser: Parses Postgres Dump Files into Arrays of Schema Objects — Takes a Postgres database dump, splits it up, and turns the table and view structures into a format you can more easily work on from code (or just use as a reference or check into version control).

Vector Databases are the Wrong Abstraction — When it comes to semantic search, Timescale thinks that externally producing embedding vectors and then putting them into a database is the wrong approach, and that embeddings should be treated more like database indexes and handled by the database. Pgai Vectorizer is their attempt at making this a reality.

Amazon Aurora PostgreSQL Limitless Database Generally Available — A new way to horizontally scale/shard Amazon Aurora to extend a database beyond Aurora’s typical storage and write throughput limits while still looking and feeling like a single database. It’s now GA with Postgres 16.4 compatibility.

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.

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.