Posts about Postgres Tutorials

My colleague Kat Batuigas recently wrote about using the powerful open-source QGIS desktop GIS to import data into PostGIS from an ArcGIS Feature Service. This is a great first step toward moving your geospatial stack onto the performant, open source platform provided by PostGIS. And there's no need to stop there! Crunchy Data

In the last several months, we've featured simple yet powerful tools for optimizing PostgreSQL queries. We've walked through how the pg_stat_statements extension can show which queries are taking up the most time to run system-wide. We've also looked at how to use the EXPLAIN command

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!

Background: GRANT, REVOKE, ALTER DEFAULT PRIVILEGES

Connection pooling and management is one of those things most people ignore far too long when it comes to their database. When starting out, you can easily get by without it. With 1 or 2 application servers spawning 5-10 connections, even the tiniest of Postgres servers can handle such. Even with our $35 a month hobby plan

In a previous post, I talked about pg_stat_statements as a tool for helping direct your query optimization efforts. Now let's say you've identified some queries you want to look into. The EXPLAIN

"I want to work on optimizing all my queries all day long because it will definitely be worth the time and effort," is a statement that has hopefully never been said. So when it comes to query optimizing, how should you pick your battles? Luckily, in PostgreSQL we have a way to take a system-wide look at database queries:

• Which ones have taken up the most amount of time cumulatively to execute
• Which ones are run the most frequently
• And how long on average they take to execute

There are a lot of ways to load data into a PostgreSQL/PostGIS database and it's no different with spatial data. If you're new to PostGIS, you've come to the right place. In this blog post, I'll outline a few free, open source tools you can use for your spatial data import needs.

You can use a desktop GUI application like QGIS, and/or command-line utilities. If you want more flexibility, it's great to have both types in your toolkit.

I'll talk about importing to PostGIS within the context of vector data, since it's a much more common use case. It's possible to import raster data

In this series so far we've talked about how to get our Django application to save uploaded images as bytea in Postgres. We've also walked through an example of a PL/Python function that processes the binary data to apply a blur filter to the uploaded image. Now, we'll show how to retrieve the blurred image from Django.

Use the function in a SQL view mapped to a Django model

Now that we have a PL/Python function, what do we do with it? The function lives in PostgreSQL, so we'll have to figure out how to call the function from Django.

You do have the option in Django to perform raw SQL queries

I recently wrote about building a Django app that stores uploaded image files in bytea format in PostgreSQL. For the second post in this series, we're now going to take a look at applying a blur filter to the uploaded image using PL/Python

I'm not someone who spends a lot of time or energy on digital images and photography. I'm usually set with my phone's camera app and maybe applying a filter as I upload to Instagram. But when you work at a database company like Crunchy Data, anything in your life can inspire a demo application. How about a simple image upload app built with Django 3.1