Greg 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 MoreElizabeth Christensen
Elizabeth Christensen
If you’ve ever dug under the hood of Postgres a bit, you’ve probably heard about the page. This is the on-disk storage mechanism and it's limited to an 8kb size. But what happens when you have data bigger than that 8kb? TOAST is made. Postgres TOASTs data by splitting it up into smaller chunks. TOAST stands for The Oversized Attribute Storage Technique. TOAST happens automatically, you don’t set up anything, it just comes with Postgres out of the box. So why should you care? Well TOAST can impac...
Read MoreCraig Kerstiens
Craig Kerstiens
Postgres is a robust data platform . Yes, it's more than a boring old relational database. It has rich indexing, data types (including JSON ), and so much more. It also has support for a variety of extensions that can further broaden it's already great functionality. Two of those extensions when coupled together make Postgres a very compelling approach for IoT architectures. Today we're going to start from the ground up on how you would design your architecture with Postgres along with the Ci...
Read MoreCraig Kerstiens
Craig Kerstiens
If you're building a B2B app chances are it's multi-tenant, meaning one customer data is separated and doesn't intermingle with other customer data. When building the app itself you'll typically have some URL route defining the specific tenant. When it comes to your database there are a number of design patterns that you can use that offer different advantages, trade-offs, and scaling opportunities. When you begin building your app, time to market is essential, but you don't want to make decisio...
Read More