Greg Sabino Mullane
Greg Sabino Mullane
Data checksums are a great feature in PostgreSQL. They are used to detect any corruption of the data that Postgres stores on disk. Every system we develop at Crunchy Data has this feature enabled by default. It's not only Postgres itself that can make use of these checksums. Some third party tools such as the awesome pgBackRest program can also use them for enhanced data integrity. Sadly, enabling data checksums is still not the default behavior when creating a new Postgres cluster. When you...
Read MoreGreg Smith
Greg Smith
By default Linux uses a controversial (for databases) memory extension feature called overcommit . How that interacts with PostgreSQL is covered in the Managing Kernel Resources section of the PG manual. Overcommit allows clients to pre-allocate virtual memory beyond even server RAM. They are only nailed down to a real allocation, committed to use its terminology, when it's actually used. This lets applications have a flatter memory model without having to grapple with virtual memory coding...
Read MoreJonathan S. Katz
Jonathan S. Katz
I can talk about the benefits of PostgreSQL for application development and operations all day. But there two enduring topics that are close to my heart: SCRAM (you need to update your passwords to use SCRAM ) and range types . I've been stoked about range types since they were released in PostgreSQL 9.2 . Before I joined Crunchy Data , I deployed them heavily in production to help manage a mature scheduling and space booking application. Performance wise, range types provided a fa...
Read MoreDavid Christensen
David Christensen
We spend time day in, day out, answering the questions that matter and coming up with solutions that make the most sense. However, sometimes a question comes up that is just so darn…interesting that even if there are sensible solutions or workarounds, it still seems like a challenge just to take the request literally. Thus was born this blog series, Devious SQL. Devious: "longer and less direct than the most straightforward way." The basic question/request that inspired this article (and in fact...
Read MoreCraig Kerstiens
Craig Kerstiens
Postgres has had " JSON " support for nearly 10 years now. I put in quotes because well, 10 years ago when we announced JSON support we kinda cheated. We validated JSON was valid and then put it into a standard text field. Two years later in 2014 with Postgres 9.4 we got more proper JSON support with the datatype. My colleague @will likes to state that the B stands for better. In Postgres 14, the JSONB support is indeed getting way better. I'll get into this small but pretty incredible cha...
Read MoreDavid Christensen
David Christensen
There have certainly been times when using PostgreSQL, that I’ve yearned for an or statement with a feature. While the SQL standard itself has no say in the matter as of SQL:2016, there are definite cases of existing SQL database dialects that support this. Sadly, if you try to do something like this in PostgreSQL, this is the result: Before we dig into the nitty-gritty, let's look at some use cases for such a feature. The primary desire for this behavior is to break large transactions up...
Read MoreJonathan S. Katz
Jonathan S. Katz
We're excited to announce the new version of PGO , the open source Postgres Operator from Crunchy Data version 4.7! There's a lot of really cool features that make it easy to deploy production Postgres clusters on Kubernetes. In this release, we focused on adding enhancements around "Day 2" operations (e.g. PVC resizing), allowing for backups to be stored in Google Cloud Storage ( GCS ), improvements to monitoring, and included more Postgres extensions ! Let's take a quick tour of all of...
Read MorePaul Ramsey
Paul Ramsey
A common situation in the spatial data world is having discrete measurements of a continuous variable. Every place in the world has a temperature, but there are only a finite number of thermometers: how should we reason about places without thermometers and how should we model temperature? For many use cases, the right way to model a continuous spatial variable is a raster: a regularly spaced grid where each square in the grid contains a value of the variable. This works for temperature and prec...
Read MoreJonathan S. Katz
Jonathan S. Katz
Not too long ago I wrote a blog post about how to deploy TLS for Postgres on Kubernetes in attempt to provide a helpful guide from bringing your own TLS/PKI setup to Postgres clusters on Kubernetes. In part, I also wanted a personal reference for how to do it! However, some things have changed since I first wrote that post. OpenSSL released a fix for CVE-2021-3450 (courtesy to my colleague Tom Swartz for reminding me of this) that prevents users from bypassing some of the x509 certificate...
Read MoreJonathan S. Katz
Jonathan S. Katz
A recent (well depending on when you read this) Twitter discussion mentioned the topic of creating the quintessential "read-only Postgres user" that can, well, only read info from a database, not write to it. A simple way to handle this case is to create a read-only Postgres replica, but that may not make sense based on your application. So, how can you simply create a read-only Postgres user (and note that I will use "user" and "role" interchangeably)? Let's explore! If you have managed users i...
Read More