Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Kat Batuigas
Kat Batuigas
Several weeks ago, we tried our hand at building a basic Dungeons and Dragons player application powered by Django . Last time, we hooked up the app to a containerized Postgres instance. In this post I'll walk through the steps to connect a Django app to PostgreSQL in the cloud via Crunchy Bridge . Note that in this scenario we're not actually taking the original database and moving it to Crunchy Bridge. We may have a follow-up blog post on migrating this existing database but for now we'll...
Read MoreJonathan S. Katz
Jonathan S. Katz
I am an accidental DBA, with a huge emphasis on "accidental." I came to PostgreSQL as an application developer who really liked to program with SQL and use the database to help solve my problems. Nonetheless, these systems would enter into production, and as such I had to learn to support them. PostgreSQL monitoring and performance optimization is a vast topic . In fact, I'll read content like what my colleague Greg Smith wrote on benchmarking PostgreSQL 13 on Ubuntu and be reminded that I h...
Read MoreJonathan S. Katz
Jonathan S. Katz
My professional background has been in application development with a strong affinity for developing with PostgreSQL (which I hope comes through in previous articles ). However, in many of my roles, I found myself as the "accidental" systems administrator, where I would troubleshoot issues in production and do my best to keep things running and safe. When it came to monitoring my Postgres databases, I initially took what I knew about monitoring a web application itself, i.e. looking at CPU, m...
Read MoreKat Batuigas
Kat Batuigas
I've been digging a little bit into PL/Python recently-inspired by our recent launch of Crunchy Bridge: a multi-cloud Postgres service with PL/Python and PL/R built right in. One thing that has tripped me up a few times is how PostgreSQL data types work with Python - especially when using external packages like NumPy or SciPy . Data type mapping is something you'll deal with when working with different languages or trying to get separate systems to talk to one another. Mapping types isn'...
Read MoreKat Batuigas
Kat Batuigas
PL/Python can be a convenient and powerful way to take advantage of your PostgreSQL database. In an earlier post , I talked a little bit about how you can use modules included in the Python standard library with PL/Python functions. In this post, we'll try running NumPy in a simple user-defined function which also takes advantage of PL/Python database access functions. The function will show a working example of how to easily convert a data table in Postgres to a NumPy array. Perhaps you've...
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 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 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 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 More