Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Joe Conway
Joe Conway
tl;dr -- If you are not actively monitoring your PostgreSQL clusters, and alerting on anomalous conditions, you are "flying blind" . Don't do that! At Crunchy Data , we distribute and support PostgreSQL as a product, in multiple forms, to many enterprise customers. We also make our products available via GitHub , our developer portal , and other places. In particular, our team is well known for our Container Suite and Kubernetes Postgres Operator . Also pertinent to this blog, if less we...
Read MoreKat Batuigas
Kat Batuigas
If you're an application developer, analyst, data scientist, or anyone who's had to figure out how to work with relational databases, chances are you're familiar with indexes . At least to the extent that you know they somehow help speed up your queries. (That's where I'd left my understanding of indexes for a good amount of time). You may have also seen that PostgreSQL automatically creates an index for a column with a primary key or unique constraint . But start to dig just a little bit deep...
Read MoreYogesh Sharma
Yogesh Sharma
Prometheus is a popular open source monitoring tool and we have many customers that leverage it when using the Crunchy PostgreSQL Operator or Crunchy PostgreSQL High Availability . Prometheus ships out-of-the-box with its own time series data store but we’re big fans of Postgres, and we know Postgres can do time series just fine. Furthermore, if you’re already running PostgreSQL and using Prometheus to monitor it, why not just store that data in a Postgres database? Just because you can do...
Read MoreJonathan S. Katz
Jonathan S. Katz
I'll admit it: I used to have triskaidekaphobia , otherwise known as a "fear or avoidance of the number 13." After all, I grew up in an area with many tall buildings where the elevators have no 13th floor , so it seems like I could rationalize this irrationality. I've been able to push aside this phobia, but I can completely empathize with those who don't like dealing with 13. Which comes to our topic today: the upcoming PostgreSQL 13 release . The fear of the number 13 was enough to prompt d...
Read MoreCaitlin Strong
Caitlin Strong
More and more frequently, customers are being given access to company databases for purposes of account management, receiving customer support, or placing and tracking an order. Although this provides great convenience for the end user, it also opens the database up to certain vulnerabilities . Any feature that allows a user to search or edit content within a database runs the risk of an attacker exploiting this feature to obtain additional access to company information. When a user subverts th...
Read MoreKat Batuigas
Kat Batuigas
Python is one of many procedural languages ( PLs ) included in standard PostgreSQL distributions. In this post, we'll take a quick look at how to get started with using PL/Python to write Postgres functions . If you're new to user-defined functions in Postgres, the idea is the same as in general programming: they're blocks of code you've written that are basically saved for use later. Each function carries out a specific task whenever it's called. A powerful feature of Postgres, especially...
Read MoreCraig Kerstiens
Craig Kerstiens
I'm a big fan of data in general. Data can tell you a lot about what users are doing and can help you gain all sorts of insights. One such aspect is in making recommendations based on past history or others that have made similar choices. In fact, years ago I wrote a small app to see if I could recommend wines based on how other ones were rated. It was a small app that I shared among just a handful of friends, some with similar taste, some with different taste. At first it was largely an academi...
Read MoreSteve Pousty
Steve Pousty
There you are writing some SQL, having a great time. Uh oh, you need to iterate over each item in a result set and apply a function. You think, "Now I am going to have to write a stored procedure." Well today's post will give you an alternative by using lateral joins in Postgres . Lateral... See what I did there? You are probably familiar with normal database joins , which are usually used to match up a column in one table with a column in another table to bring the data from both tables toget...
Read MoreJohn Porvaznik
John Porvaznik
Postgres has a number of different index types. You’ve got B-Tree, GIN, GiST, Sp-GiST, and BRIN. BRIN is a lightweight index that often goes misunderstood. When implemented correctly, it offers significant benefits such as space savings and speed. However, when implemented incorrectly, it loses some of its benefit, so it's important to look at some of the reasons a BRIN index might not be right for you. A BRIN is a Block Range Index . A block is Postgres’ base unit of storage and is by defaul...
Read MorePaul Ramsey
Paul Ramsey
PostGIS is a "geospatial database" and the "geo" in "geospatial" is an important qualifier: it means that all the coordinates in PostGIS point, lines, and polygons can be located somewhere on the earth. As we all know (except for a few of us ) the earth is not flat, it's round . It's almost a sphere, an "oblate spheroid", slightly wider than it is tall. Because it is (mostly) spherical, we don't use cartesian coordinates (x, y) to describe locations on the earth, we use spherical coordinates...
Read More