Bookmarks (182)

How to Find and Stop Running Queries on Postgres — Postgres is seizing up under the pressure of some troublesome queries or resource constraints and you need to take control fast.. how can you stop those queries without bringing the whole deck of cards down? This post goes into more detail than you’d expect on poking about and taking charge of Postgres’s backend processes.

Timestamps & Timezones: What You Need to Know & What You Don’t — Some PostgreSQL programmers are daunted by the date and time data types, and by the effect of the session’s timezone setting on operations. YugabyteDB’s YSQL can result in the same experience. Read about a careful and exhaustive study recently conducted on this topic.

Postgres 14.4 Released — Last week we warned an imminent Postgres 14 update was coming to resolve a nasty bug around silent corruption of indexes when using CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY and now it’s here. You can use pg_amcheck to see if you were affected. Non Postgres 14 users remain unaffected.

Citus 11 for Postgres Goes Fully Open Source — Citus, a Postgres extension for horizontal scaling, was first open sourced six years ago but with the common business model of offering an extended ‘enterprise’ version for money. No longer. Now there’s only one Citus version and it’s all open source, so features like tenant isolation, non-blocking shard rebalancing, and others are now open to...

pgCluu: Postgres Performance Monitoring and Auditing Tool — A two part tool that collects statistics from a running Postgres cluster and then presents performance information for you to inspect. GitHub repo.

pg_squeeze: An Extension to Fix Table Bloat — While it’s not a replacement for vacuuming, it boasts being a handy extension to go a step further and gain even more space improvements on a table.

The Dalibo Postgres Execution Plan Visualizer — Give this the output from EXPLAIN ANALYZE and get a better view at what Postgres plans to do with your query. Along similar lines to the better known explain.depesz.com but presents the results in a rather different way, so it’s worth trying both. This has been around a while but has seen some recent updates.

Achieving High Uptime with Flexible Server PostgreSQL HA — The “HA” isn’t laughter but High Availability and Azure has introduced a couple of new HA capabilities to its slightly wordy Database for PostgreSQL Flexible Server service.

How TimescaleDB 2.7 Makes its Data Aggregation Better and Faster — The folks at Timescale are certainly putting in the work to make it a compelling option for time-series workloads. This post digs deep into some benchmarks showing off just how much faster the new 2.7 release is versus 2.6 with some queries up to 44,000x faster (while typically requiring 60% less storage).

Migrating Scheduled Jobs to pg_timetable From pg_cron — pg_timetable is an advanced job scheduling extension for Postgres and offers a bit more out of the box than the less frequently updated (but still very usable) pg_cron. If you want to migrate at all, here’s how.

Building IoT Apps on Postgres, Citus, and Azure — If you’re creating an app that handles time-series data coming from devices non-stop, scaling can quickly become an issue and leaning on managed services, such as those provided by Azure, can help keep scaling easy.

pg_graphql: Bring GraphQL Support to Postgres — A top feature for us several months ago but not a project to be slept on.

Handling Trailing Zeros with NUMERIC — Another quirk you might run in to when doing an Oracle to Postgres migration.

Handling Empty Strings When Migrating from Oracle to Postgres — I’ve never had to migrate a database from Oracle to Postgres, but AWS is really keen for you to do this and so is attempting to educate us on the problems that can arise.

Case-Insensitive Pattern Matching — You may sense some echoes with the fuzzy text searching item (featured above) but Laurenz covers slightly different ground particularly around the complexity of handling case sensitivity across collations and why German’s ß keeps things interesting.

Using Postgres Schemas — I always struggle to describe Postgres schemas/namespaces succinctly but “with minimal overhead, they enable segmentation and isolation of tables within a database” is a nice attempt. Aaron sells us on some of the benefits here.

What to Research When Upgrading Postgres Version — Some quick fire pointers on how to research the changes that might impact you when switching Postgres version. Why Upgrade PostgreSQL? is also a handy resource.

pguint: Unsigned Integer Types Extension — Brings signed 8 bit integer and unsigned 8, 16, 32, and 64 bit integer types to Postgres 9.1+.

Fuzzy Text Search: From Not So Fuzzy to Fuzziest — If you’ve got free form text data supplied by other humans, it’s not going to be perfect (typos, slang, odd formatting, and the like) so supporting a little ‘fuzziness’ when querying data can go a long way. This is a nice tour through some options Postgres offers to you from least ‘fuzzy’ to full-on...

⚠️  Imminent Postgres 14 Update to Avoid Silent Corruption of Indexes — Over on the Postgres mailing list, an important ‘out of cycle’ release has been preannounced for June 16 (tomorrow) that will fix a Postgres 14 issue that can cause silent corruption of indexes if you use CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY. There’s a full discussion on the mailing list about the problem too.

State of PostgreSQL 2022 Survey — The 2022 State of PostgreSQL survey is now open! Share your views on the PostgreSQL database, your favorite extensions, preferred frameworks, community experiences, and more.

ZomboDB: Use Elasticsearch to Power Postgres Indexes — We linked to this in beta a few years ago and it's time for a revisit! Zombo brings powerful text-search and analytics features to Postgres by using Elasticsearch as an index type. ZomboDB brings powerful text-search and analytics features to Postgres by using Elasticsearch as an index type, providing new and creative ways to query your...

The 2022 State of PostgreSQL Survey is Now Open — The folks at Timescale are running their third annual survey of the Postgres community and given the results of such surveys tend to make for interesting reading (you can read last year’s results here) it’d be neat if you could take part. (I just did it and it took 3-4 minutes.)

A Balanced Approach to Automatic Postgres Indexing: The New Version of the pganalyze Index Advisor — The pganalyze Index Advisor is a tool that can help you figure out what indexes your database could benefit from and now there’s a new version of the tool ready to use promising less ‘noise’ and more helpful (and ranked) suggestions.

Reducing Read I/O Costs on Amazon Aurora with Range Partitioning — Aurora’s ‘pay as you use’ model incurs some financial implications around just how heavy you want to lean on I/O. This post shows how using a time-based partitioning policy can reduce costs on a database using 33TB of data and ingesting 44GB per day.

How pganalyze Built its Indexing Engine — We mentioned pganalyze’s index suggestion tool above, but if you’re curious as to how it actually works under the covers, this is the article for you.

How to Handle Logical Replication Conflicts — Fujitsu’s Takamichi Osumi explains how new mechanisms coming to Postgres (and already usable in the Postgres 15 beta) make it easier to address replication conflicts.

Effective Postgres Cluster Configuration and Management Using PGO v5.1 — PGO is a Postgres operator for Kubernetes that handles cluster management, customization, and more.

Postgres Docker Container Migration Cheat Sheet — It’s very basic, but.. “I just finished migrating a postgres database to a new host. To remember how to do it next time, I’m writing down the commands I used here.”

pgsql-http: An HTTP Client for Postgres — Would you like to be able to call web services from a trigger, say? Here’s your chance.

pgtt 2.8: Extension to Manage and Use Oracle-Style Global Temporary Tables — Postgres 15 support has now been added.

Neon: An Open Source Serverless Alternative to AWS Aurora Postgres? — This is the codebase behind an eponymously named company (founded by Nikita Shamgunov who co-founded MemSQL) building a multi-cloud serverless Postgres platform and giving us strong PlanetScale vibes right now, but with extra source.

Exporter2Perfdata: Connecting Postgres Monitoring Tools — exporter2perfdata is a new open source tool (written in Go) that collects metrics from Prometheus exporters and converts it to perfdata format for Icinga 2 / Nagios.

Querying SQLite and Postgres Data from DuckDB — PostgresScanner provides an FDW-style integration between DuckDB, a popular in-process SQL OLAP DBMS (imagine SQLite but for OLAP), and Postgres.

How to Backup and Restore Data with pg_rman — pg_rman is a Postgres backup and restore tool we’ve not mentioned before, but it’s been around many years and supports right up to Postgres 15 beta. Its goal is to be as easy to use as pg_dump but work for online backups.

Compose Transporter 1.1: Sync Data Between Persistence Engines — I love how it bills itself “like ETL only not stodgy”. This Go-powered tool lets you connect together various databases (MongoDB, Postgres, MySQL, Elasticsearch, and more), files, and other resources, run some basic transformations (optionally) and then send the data off elsewhere. So, yes, ETL basically.

The Architecture of pgAdmin 4 — pgAdmin is a popular browser-based Postgres dashboard and management tool and if you’ve wondered what moving parts it has, these diagrams are for you. Did you realize pgAdmin has a database that isn’t Postgres at the heart of it…?

Near Real-Time Notifications from Aurora PostgreSQL with Triggers, Lambda, and SNS — This is the sort of plumbing work that AWS is very well suited for. This tutorial covers the process of enabling notifications of data changes on a Postgres-compatible Aurora database to be sent through a serverless function and then on to other places via SNS. Just be careful not to send yourself...

Looking Under the Hood at AlloyDB's Columnar Engine — Two weeks ago, we mentioned Google’s release of AlloyDB, its Aurora-a-like Postgres-compatible scalable managed database. This post digs a little more into its unique features and how a columnar engine enables (significantly) faster performance on certain types of query.

On View Permissions and Row-Level Security — Views provide a handy way to give access to a predefined SQL query and they can also be used as an extra layer of security by restricting access to a view’s underlying table while still allowing use of the view. This post covers the basics while also looking at Postgres 15’s forthcoming security_invoker option.

Postgres 15 Beta 1 Released — Last week we linked to the draft release notes but now the actual beta is here. Don’t expect to roll it out into production just yet, but you can play with it quite quickly using the just released Docker image 15beta1. I've written a quick tutorial on spinning up Postgres 15 with Docker if you're new to the...

Moving Postgres Data 26X Faster with Azure Data Factory — If you’re using Postgres on Azure and you need to get data either into or out of a database en masse, Azure Data Factory has to be on your radar, especially now it’s even faster than before.

Let's Build a Distributed Postgres Proof of Concept in Go(lang) — Could you put together a mini Postgres in 600 lines of code? A neat example of some of the complicated things you can get done with just a little bit of code in Go (even if third-party packages do much of the lifting here.)

Instant Heatmaps with pg_featureserv — Despite the name, pg_featureserv isn’t a Postgres extension but is instead a Go-based microservice that provides RESTful access to PostGIS – this quick tutorial shows how to bring together the various parts to use it to make a dynamic heatmap.

Unfinished Business with Postgres — Craig (who now works at Crunchy Data) was a key player in Heroku’s managed Postgres service and in this post he tells some of the story of how it came together, some of the technical considerations around its operation, and, well, the sheer amount of care that went into making it run well.

Speeding Up Sort Performance in Postgres 15 — A fantastic look at the results of the work that’s been put in to making sorting – about as fundamental an operation as it gets in a database – faster in Postgres. Will your queries run faster on Postgres 15? Most likely.

PostgreSQL Anonymizer 1.0: Privacy By Design for Postgres — An extension that hides or replaces personally identifiable information (PII) or commercially sensitive data in your database using one of three different strategies. With this 1.0 release, it’s now considered ‘ready for production’. Project homepage.