Elizabeth Christensen
Elizabeth Christensen
Many folks are surprised to hear that Postgres has parallel queries out of the box. This was released in small batches across a half dozen versions of Postgres, so the major fanfare for having parallelism got a little bit lost. By default Postgres is configured for two parallel workers. The Postgres query planner will assemble a few plans for any given query and will estimate the additional overhead of performing parallel queries, and make a go or no-go decision. Depending on the settings and th...
Read MoreBrian Pace
Brian Pace
In the evolving world of data management, ensuring consistency and accuracy across multiple database systems is paramount. Whether you're migrating data, synchronizing systems, or performing routine audits, the ability to compare data across different database platforms is crucial. Enter pgCompare , an open-source tool designed to simplify and enhance the process of data comparison across PostgreSQL, Oracle, MySQL, and MSSQL databases. The key features of pgCompare: • Multi-Database support : p...
Read MoreGreg Nokes
Greg Nokes
When your company has decided it's time to invest in more open source, Postgres is the obvious choice. Managing databases is not new and you already have established practices and requirements for rolling out a new database. One of the big requirements we frequently help new customers with on their Postgres adoption is data encryption. While the question is simple, there's a few layers to it that determine which is the right approach for you. Here we'll walk through the pros and cons of approach...
Read MoreDavid Christensen
David Christensen
I recently created a Postgres extension which utilizes the extension to schedule recurring activities using the . Everything worked great. The only problem was when I dropped my extension, it left the cron job scheduled, which resulted in regular errors: If you look in the table, you can see the SQL for the cron job is still present, even though the extension/schema isn’t: This got me thinking: how can you create a Postgres extension that can clean up after itself for cases like this? If...
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 MoreElizabeth Christensen
Elizabeth Christensen
There’s a pretty HOT performance trick in Postgres that doesn’t get a ton of attention. There’s a way for Postgres to only update the heap (the table), avoiding having to update all the indexes. That’s called a HOT update , HOT stands for heap only tuple. Understanding HOT updates and their interaction with page fill factor can be a really nice tool in the box for getting performance with existing infrastructure. I’m going to review HOT updates and how to encourage them in your Postgres updates...
Read MoreBrian Pace
Brian Pace
PostgreSQL uses the concept of a timeline to identify a series of WAL records in space and time. Each timeline is identified by a number, a decimal in some places, hexadecimal in others. Each time a database is recovered using point in time recovery and sometimes during standby/replica promotion, a new timeline is generated. A common mistake is to assume that a higher timeline number is synonymous with the most recent data. While the highest timeline points to the latest incarnation of the datab...
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 MoreJesse Soyland
Jesse Soyland
On the Customer Success Engineering team at Crunchy Bridge , we run across customers with lock issues on their Postgres database from time to time. Locks can have a cascading effect on queries. If one process is locking a table, then a query can be waiting on the process before it, and the process before that one. Major lock issues can quickly take down an entire production Postgres instance or application. In this post let’s look at why locks happen, and more importantly how to get to the bott...
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 More