John 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 MoreJonathan S. Katz
Jonathan S. Katz
I've made it no secret that I am a huge fan of PostgreSQL 's SCRAM support: it provides a secure, standardized way of performing authentication with passwords, and is a method that can be universally applied. The beauty of SCRAM is that both authenticating parties (in this case, your client/application and PostgreSQL) can both verify that each party knows a secret without ever exchanging the secret . In this case, the secret is a PostgreSQL password! That is incredible , and is also a hug...
Read MoreJames Chanco Jr.
James Chanco Jr.
The first step of working with any software is getting it installed. Based on your environment, this could be as easy as a one-click installer, or require a manual that's hundreds of pages long with thousands of config bits to tune. Deploying complex applications on Kubernetes and OpenShift can often fall somewhere in the middle. For example, deploying an Operator on OpenShift can be viewed as an intimidating thing, but it doesn't have to be. Here we're going to walk through the quickest and e...
Read MoreKat Batuigas
Kat Batuigas
Even if you're relatively new to SQL , chances are that you know the INSERT command is used to add new data to a table. For those of you newer to Postgres such as myself, you may not be familiar with a couple of neat tricks you could do with inserts. This post is a refresher on INSERT and also introduces the RETURNING and ON CONFLICT clauses if you haven't used them yet, commonly known as upsert. Let's start with the basics, we usually see INSERT in two forms. The first explicitly provides the...
Read MoreSteve Pousty
Steve Pousty
Today’s blog post is going to be a nice little adventure of learning how to use composite primary keys in a PostgreSQL many-to-many relationship table while building a Django application. Along the way we will talk about some basics of Django and some workarounds you need to use. Let’s dig in and get started. Here on the developer relations team at Crunchy Data , we have started building a demo application that manages Dungeon and Dragons (D&D) players, characters, dungeon masters, and c...
Read MoreYorvi Arias
Yorvi Arias
An important part of performing a migration from Oracle to PostgreSQL is selecting the right tool for helping with the conversion between systems. When people ask me how they can get the migration process started, I like to recommend a powerful open source utility called " ora2pg ". As the name suggests, ora2pg is a tool that migrates Oracle (or MySQL) databases to PostgreSQL by generating compatible SQL files As the documentation states, ora2pg “connects your Oracle database, scans it automatic...
Read More