Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Jonathan 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 MoreCraig Kerstiens
Craig Kerstiens
Backups are a key staple of running any database. Way back in the day, a good friend and colleague wrote one of the most used Postgres backup tools called wal-e. Wal-e was initially written in just a few days, and rolled out to the fleet of databases we managed in the early days at Heroku. We got pretty lucky with rolling that out, because shortly after we had there was the great AWS Apocalypse of 2011. This was a full day outage of AWS with lingering effects for nearly a week... Reddit was dow...
Read MoreKat Batuigas
Kat Batuigas
This post is the second in a two-part series on my experience with adding a user registration system to a simple demo app built in Django. In my first post , I talk about how Django's built-in authentication system can do some of the heavy lifting for your registration setup. In this post, I'll walk you through how we tied our data models and authentication together by extending Django's model. You may recall from the first post that there wasn't a pure out-of-the-box solution for user accoun...
Read MoreKat Batuigas
Kat Batuigas
If you haven't already read my colleague Steve Pousty's blog post on handling composite primary keys in a simple web application using Django and PostgreSQL, be sure to check it out. This post is going to be the first in a two-part series about adding a user registration system to the same app. In this first post, I'm going to talk specifically about setting up a "self-registration" form for our Dungeon and Dragons ( DnD ) players, using Django's built-in authentication (which includes built...
Read More