Bookmarks (163)

Index Merges vs Composite Indexes — Is it faster to use a composite index (a.k.a. a multicolumn index) than merge indexes? Yes and, at least in Postgres, by quite a lot, because Postgres doesn’t support index-only scans when queries involve index merges.

pgvector: Vector Similarity Search Extension — Supports L2 distance, inner product, and cosine distance. There’s a whole suite of client libraries for Node, Go, Ruby, PHP, Rust, and more, too.

Create D2 ERD Diagrams from Postgres Schemas — A simple Node script that can turn the schema of a Postgres database into an elegant diagram using the D2 declarative diagramming DSL. (Also of interest is the mega SQL query this script relies on for fetching a schema in one go.)

Using Postgres as a Task Queue for Rowdy Gophers — A developer walks through his experience of building a fast and efficient queuing system on top of Postgres for his Go routines to use for dividing up work.

AWS Database Migration Service Adds Managed Schema Migration — AWS Database Migration Service (DMS) is a long standing service for migrating databases (particularly those running on Oracle or SQL Server) to AWS. At this week’s re:Invent, AWS unveiled a new automatic database schema converter as an integral part of the service. It supports migrations from SQL Server 2008+ and Oracle 10.2+, 11g-12.2, 18c, and...

How Fly.io Runs Its New Postgres Platform — We’re big fans of Fly (a sort of broadly distributed, modern Heroku-a-like) so it’s been fun seeing them build out their platform. Running Postgres is pretty easy, but the trick is in managing replicas, failover, etc. and Fly uses entirely open-source pieces to make it all work.

Percentage Calculations Using Window Functions — Rather than fetching your data and then performing percentage calculations upon it, why not do it all in a single query? A perfect use case for window functions, says Paul, who gives a practical example, plus a link to an interactive sandbox where you can play for yourself.

Timescale's 'Bottomless' Object Storage Layer on Top of S3 — Timescale continues to innovate with its cloud service, this time with a (private beta only, for now) object storage mechanism built on Amazon S3. The idea is data can be tiered across disk and S3 while keeping the access transparent.

Squeal: A 'Deep Embedding' of SQL in Haskell — Essentially a client library that brings SQL concepts and types natively into Haskell so you can put together queries as functionally as possible.

The Dalibo Postgres Execution Plan Visualizer — Give this EXPLAIN ANALYZE's output and take a better view over what Postgres plans to do with your query. This has been around a while and continues to see frequent updates, including this very week. It's open source if you want to use it locally or within an app.

Do Different Equality Predicates Make a Difference? — Namely, IN vs =. Spoiler: No, for Postgres 13, at least. But it’s always worth checking due to behavior differences between databases.

A Query Boost by Using ANY Instead of IN? — Matt makes a case for using = ANY(array) (or SOME) in more cases where you might otherwise use IN(list).

A Serverless Architecture for Analyzing Postgres Logs with pgBadger — pgBadger is a log analysis tool (written in Perl) that produces reports showing statistics about database traffic, locks, slowest queries, and more. This post focuses on deploying it in a serverless architecture where AWS RDS sends logs to S3, through SQS and AWS Lambda, then to pgBadger which outputs its reports to S3 again… whew!

We’re All About Great Support — “I’m learning so much about @PostgreSQL from the @crunchydata support tickets I’ve filed. They know their Postgres, that is for sure!” - @frigidcode

The Power of Variadic Unnesting — There’s no obvious "big news" this week, so let’s go with “neat Postgres feature!” (that dates from Postgres 9.4, no less). This is a look at how string_to_array and the UNNEST table function can help to normalize messy data (from a spreadsheet in this case). As the author says: “I can’t do anything this sweet in other databases...

Postgres at Scale: Running Multiple PgBouncers — Connection pooling with pgBouncer is great, but at particularly high loads, there are benefits to be had with running multiple pgBouncers.

Reducing Replication Lag with I/O Concurrency in Postgres 15 — Postgres 15 comes with key replication improvements, but the work done to get there was deep. This post explains how IO concurrency affects replication lag but also tells the story of how Postgres’s IO story is improving over time and teases a future where data will be asynchronously transferred into Postgres’s buffers avoiding the...

Using Postgres with Deno Deploy — Accessing Postgres data at the edge presents new challenges with multi-region connectivity and latencies. Learn how to solve these in minutes with PolyScale’s plug-and-play database edge cache.

PostgreSQL 15.1, 14.6, 13.9, 12.13, 11.18, and 10.23 Released — A bevy of bug fix releases for maintained release lines. None of the bugs particularly stand out, but are listed in this post. We also get time zone data updates due to DST changes in various countries. It also marks the final v10 release.

A Tribute to 2017's Postgres 10 — Five years after each major Postgres release, the tide heads out and that version hits its ‘end of life’ (EOL) after a final farewell release (more info about this in the versioning policy document.) Jonathan indulges in some nostalgia for what was a ‘transformative’ version of Postgres that introduced logical replication, declarative partitioning, and SCRAM authentication.

EXPLAIN That Parameterized Statement — The output of EXPLAIN (ANALYZE, BUFFERS) is always useful, but can be hard to get for a parameterized statement. Unless you have some of Laurenz’s tips in your back pocket..

What is an 'Upsert' and When Should You Use One? — This comes from CockroachDB but covers the use of the same idea in Postgres by way of INSERT ON CONFLICT. (Postgres 15’s MERGE provides another approach.)

Read Before You Upgrade: Best Practices for Choosing a Postgres Version — Reflections on when to consider upgrading your Postgres version and questions to ask before you start doing so. (As a Timescale article, Timescale’s own managed service does get recommended, but at the end.)

JSON in PostgreSQL: The Ultimate Guide — I encountered this when putting together some JSONB queries this week and found it useful.

How to Recreate ROW CHANGE TIMESTAMP from IBM Db2 on Amazon RDS or Aurora — In Db2, a column created with ROW CHANGE TIMESTAMP always contains a timestamp of when the row was last modified. Can you do this in Postgres too? Triggers to the rescue.

Improved Aarch64 (arm64) Support Now Available in the Postgres RPM Repository — You’ve been able to install aarch64/ARM64 architecture Postgres rpm packages for a while, but some focus has been put into full RHEL 9 support, improved support and fixes for RHEL 7 and 8, and more.

Postgres 15, A Billion Transactions Later — Last week B-tree and BRIN indexes battled it out, now it’s 2017’s Postgres 10 vs Postgres 15! Kaarel put them both under a billion transaction stress test and made some observations, including v15’s improved storage efficiency.

pg_builder 2.1.0: A Query Builder in PHP for Postgres — Curiously it contains a partial reimplementation of Postgres’s query parser and works by converting queries to and from an AST. v2.1 boasts improved Postgres 15 support.

Orafce 4.0: Oracle Compatibility Functions for Postgres — Orafce implements some of the functions from Oracle that might be useful for users migrating to Postgres. v4.0 drops Postgres 9.6 and 10 support and merges in orafce_sql, a Postgres subset of Oracle’s DBMS_SQL interface.

How LISTEN and NOTIFY Promote High Availability at the App Layer — Shaun looks at some alternatives to using triggers for sending notifications on a fictional social network such as leaning on Postgres’s built-in pub/sub functionality (via NOTIFY and LISTEN) to outsource work to an external daemon process.

Using JSON Functions to Navigate Restaurant Reviews — Practical examples of using Postgres’s JSON querying functionality are always welcomed by me. This starts from the basics but moves on to things like creating a histogram of restaurant ratings in a single query.

What is PostgreSQL Commitfest and How to Contribute — A time-tested method for contributing to the core Postgres code.

On Rewriting OR to UNION in Your Queries — A few years ago, Laurenz wrote about avoiding OR for performance reasons, instead leaning on a UNION based approach. But is it always a winner, and why doesn’t Postgres optimize for this anyway? Laurenz has some answers.

Postgres Insider Terminology — Want to seem like one of the cool Postgres kids? No? Well, tough, Paul is here to bring us up to speed with tuples, TOAST, relations, pages, and more. If you want to go a step further, did you know Postgres has an official glossary too?

Postgres Databases and Schemas — Craig has worked on Postgres databases of every scale, but they all have layers in common. This is aimed at beginners but quickly explains how clusters, databases, schemas and instances differ from each other.

Tips for Better Data Aggregation in PostgreSQL and TimescaleDB — Get familiar with TimescaleDB continuous aggregates, how they enable you to retrieve data aggregations with speed and convenience, and extra tips to make the most of them.

Safely Renaming a Table with No Downtime Using Updatable Views — Just use ALTER TABLE x RENAME TO y? In production, you might not want to do that as it can confuse clients currently using the database, but there is a way to pull it off cleanly..

B-tree vs. BRIN Indexes in Postgres Data Warehouses — Hans-Jürgen describes it as an ‘epic battle’ between the two popular index types and puts on a mini re-enactment that essentially demonstrates why doing your own benchmarking makes sense.

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.

pgdump-aws-lambda: Stream pg_dump Output to S3 via AWS Lambda — There are other ways to do this sort of thing on AWS, but this is a pretty direct and flexible way to take database backups.

PostgreSQL 15 64-Bit FDWs for Windows — Specifically, 64 bit binaries for file_textarray_fdw (process arbitrarily delimited text) and odbc_fdw (handy to connect to ODBC data sources).

pg_stat_kcache: Gather Statistics About Disk Access and CPU Consumption — Sits atop pg_stat_statements and creates a couple of views you can use to measure CPU time, page faults, swaps, bytes read and written, and more at the database and query level.

Data Loading in Postgres for Newbies — Quickly covers various routes to ingest data from CSV, JSON, backup files, via foreign data wrappers, and even generating data with queries. Using jq along with \copy is a neat approach I hadn’t considered before.

Azure Cosmos DB Brings in Distributed Postgres — Azure Cosmos DB is a long-standing ‘multi-model’ managed database from Azure that has typically focused on providing NoSQL services with multi-region, global availability, so it’s curious to see Postgres support being introduced. In short, Azure Database for PostgreSQL's Hyperscale (Citus) offering is now.. this.

▶  Postgres FM: A Weekly Postgres Podcast — We’ve mentioned this show a few times, but it deserves a little extra support, especially as it has kept going every week without dropping off (quite an achievement for a new podcast!) Recent shows cover topics including stored procedures, Postgres 15 and query optimization.

'PostgresML Is 8-40x Faster Than Python HTTP Microservices' — Postgres gets compared to MySQL, SQL Server or Oracle all the time.. but Python? PostgresML is a Postgres extension for training and deploying machine learning models. It’s a task more commonly associated with Python, but Postgres offers a compelling, if quite different, interface and Lev has the numbers to prove it.