Bookmarks (128)

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!

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.

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

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.

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

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.

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

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.

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

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

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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

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.

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.

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.

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.

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

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

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

🤖 dbx: A Forthcoming AI-Powered SQL Editor and Database Client — We rarely link to tools that aren’t openly available, but as one of our readers asked so nicely, their landing page is certainly worth a look and you can register for early access if you like what you see.

Building AI Apps on Postgres? Start with pgai — pgai is a PostgreSQL extension that brings more AI workflows to PostgreSQL, like embedding creation and model completion. pgai empowers developers with AI superpowers, making it easier to build search and retrieval-augmented generation (RAG) applications.