Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Brian 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 MoreMarco Slot
Marco Slot
I've always found distributed systems to be the most fascinating branch of computer science. I think the reason is that distributed systems are subject to the rules of the physical world just like we are. Things are never perfect, you cannot get everything you want, you’re always limited by physics, and often by economics, or by who you can communicate with. Many problems in distributed systems simply do not have a clean solution, instead there are different trade-offs you can make. While at Cit...
Read More