Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
David 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 MorePaul Ramsey
Paul Ramsey
Spatial indexes are used in PostGIS to quickly search for objects in space. Practically, this means very quickly answering questions of the form: • "all the things inside this this" or • "all the things near this other thing" "all the things inside this this" or "all the things near this other thing" Because spatial objects are often quite large and complex (for example, coastlines commonly are defined with thousands of points), spatial indexes use "bounding boxes" as index and search keys: •...
Read MorePaul Laurence
Paul Laurence
Whether you are starting a new development project, launching an application modernization effort, or engaging in digital transformation, chances are you are evaluating Kubernetes. If you selected Kubernetes, chances are you will ultimately need a database . Kubernetes provides many benefits for running applications including efficiency, automation, or infrastructure abstraction. These features allow you to deploy highly availability databases and scale, making it easier to manage hardware f...
Read MoreStephen Frost
Stephen Frost
Contributing author David Youatt An underappreciated element of PostgreSQL performance can be the data types chosen and their organization in tables. For sites that are always looking for that incremental performance improvement, managing the exact layout and utilization of every byte of a row (also known as a tuple) can be worthwhile. This is an important consideration for databases that are migrating from other databases to PostgreSQL as the data types available in PostgreSQL and how th...
Read More