Crunchy Data joins Snowflake. Read the announcement
Greg Sabino Mullane
Greg Sabino Mullane
Someone recently asked on the Postgres mailing lists about how to remove unwanted duplicate rows from their table. They are “unwanted” in that sense that the same value appears more than once in a column designated as a primary key. We’ve seen an uptick in this problem since glibc was kind enough to change the way they sorted things. This can lead to invalid indexes when one upgrades their OS and modifies the underlying glibc library. One of the main effects of a corrupted unique index is allowi...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
It is rare that a Postgres table keeps the exact same structure year after year. New columns get added. Old columns get dropped. Column data types need to change. Those are all done with the ALTER TABLE command. One big drawback to these changes is that they may force a complete table rewrite. A rewrite means a completely new copy of the table is created, and then the old one is dropped. This can take a very long time for large tables. Worse, everything else is blocked/locked from using the tabl...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
There is something new you may not have seen in the release notes for Postgres 17 . No, not a new feature - I mean inside the actual release notes themselves! The Postgres project uses the git program to track commits to the project, and now each item in the release notes has a link to the actual commit (or multiple commits) that enabled it. You may have missed it if you were scanning the release notes, but after the end of each specific item in the release note is a small “section” symbol whic...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
The Postgres hackers mailing list ( pgsql-hackers@postgresql.org ) is an invaluable resource for anyone wanting to contribute to the PostgreSQL code. The Postgres project does not use PRs (pull requests) or GitHub issues. So if you want to contribute an idea, or help with code reviews, the hackers mailing list is the canonical way to do so. More information on contributing is on the Postgres wiki at: https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer ? My colleague Elizabeth Christ...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
Postgres is an amazing database system, but it does come with a five-year life cycle. This means you need to perform a major upgrade of it at least every five years. Luckily, Postgres ships with the program, which enables a quick and easy migration from one major version of Postgres to another. Let's work through an example of how to upgrade - in this case, we will go from Postgres 12 to Postgres 16. You should always aim to go to the highest version possible. Check postgresql.org to see what...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
My colleague Bob Pacheco asked me to help with a strange problem he was witnessing for a client. A new Postgres cluster was getting created on a Kubernetes node, but it refused to start. More mysteriously, there was no message in the Postgres logs, nothing in the pg_ctl start up file, and no output anywhere. The pg_ctl program started up, then ended. Nothing on stderr, nothing on stdout! We were able to duplicate it on the command line using pg_ctl. Oddly enough, a syntax error in the post...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
You may have noticed a file called inside your data directory. This file gets created when Postgres first starts up, and gets removed on a clean shutdown. It seems to contain some random numbers and strings, but what do they all mean? The file will look like this: Here is a quick cheat sheet of the contents: The word "postmaster" is a relic from the early days of Postgres. This used to be the name of the main executable. While the main executable was, and still is, named "postgres", it also us...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
I’ve nearly finished solving the 2022 series in Advent of Code in PostgreSQL on our blog, many of these are available on our browser based Postgres playground as well. As many of you embark on your own Advent of Code adventures for 2023 this week, or maybe watch from afar, I wanted to pull together some themes, recommendations, tips, and tricks that I’ve seen work with the solutions. If there’s anything I’ve learned, it’s that you can solve almost anything with PostgreSQL! Before you do anyt...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 23's challenge "Unstable Diffusion" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. Tech used in this Day: • The file_fdw Foreign Data Wrapper • Materialized (and not materialized) CTEs aka Common Table Expressions • Custom data types • Various handy functions like string_to_table and array_agg and unnest • Tweaking the plan_...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 22's challenge "Monkey Map" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. Tech used: • The file_fdw extension to read the input • Unlogged tables • Sequences • Building and modifying arrays via regexp_split_to_array and array_remove • More ASCII animation! The file_fdw extension to read the input Unlogged tables Sequences Bui...
Read More