~postgresweekly | Bookmarks (128)

pglite-fusion: Embed SQLite Databases in Postgres Tables — An extension that gives you the ability to embed SQLite databases into Postgres tables by way of a special new SQLITE column type (so, yes, every row in a table could itself have an embedded SQLite database..)

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

Loading the World: An OpenStreetMap Import in Under 4 Hours — Did you know that OpenStreetMap’s database is a whopping 750GB in size, and you can grab it and set it up for yourself as a way to stress-test Postgres? Greg looks at how long it takes to ingest, how to make it faster, and performance improvements over the years. (Minor spoiler: Postgres 17...

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.

Postgres 17.1, 16.5, 15.9, 14.14, 13.17, and 12.21 Released — A roundup of releases for every maintained line of Postgres including bug fixes and fixes for four security issues. 12.21 also marks the final release of Postgres 12. Be careful with upgrading to Postgres 17.1, though, as there are some potential compatibility issues.. see the next item!

A Change to ResultRelInfo: A Near Miss with Postgres 17.1 — A seemingly minor change in the Postgres 17.1 release led to compatibility issues with some extensions (such as TimescaleDB and Apache AGE – see what TimeScale’s CTO had to say here). Craig explains what the issue is and why it arose here, reminding us that developing extensions robust to such changes is a...

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.

Boosting INSERT Performance by 50% with UNNEST — You probably know that using COPY is usually the fastest way to ingest data into Postgres at scale, but INSERT retains some advantages due to its extra features. Can INSERT be sped up? UNNEST arrives to shake things up..

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.

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.

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.

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.

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.

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.

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.

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.

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

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

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.