Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Elizabeth Christensen
Elizabeth Christensen
There’s a pretty HOT performance trick in Postgres that doesn’t get a ton of attention. There’s a way for Postgres to only update the heap (the table), avoiding having to update all the indexes. That’s called a HOT update , HOT stands for heap only tuple. Understanding HOT updates and their interaction with page fill factor can be a really nice tool in the box for getting performance with existing infrastructure. I’m going to review HOT updates and how to encourage them in your Postgres updates...
Read MoreElizabeth Christensen
Elizabeth Christensen
QGIS, the Quantum Geographic Information System, is an open-source graphical user interface for map making. QGIS works with a wide variety of file types and has robust support for integrating with Postgres and PostGIS. Today I just wanted to step through getting QGIS connected to a Postgres database and the basic operations that let you connect the two systems. Connecting QGIS to Postgres is very similar to any other GUI or application, you’ll need the database host, login, and password details....
Read MoreElizabeth Christensen
Elizabeth Christensen
At PostGIS Day 2023 , one of our speakers showed off a really cool demo for getting JSON and SVGs in and out of Postgres / PostGIS and into Google Sheets. Brian Timoney put together several open source projects in such a cool way that I just had to try it myself. If you want to see his demo video, it is on YouTube . With Brian’s blessing, I’m writing up some additional details with a few of the sample code bits for those of you that want to try this or something similar for your own projects....
Read MoreElizabeth Christensen
Elizabeth Christensen
If you’ve ever dug under the hood of Postgres a bit, you’ve probably heard about the page. This is the on-disk storage mechanism and it's limited to an 8kb size. But what happens when you have data bigger than that 8kb? TOAST is made. Postgres TOASTs data by splitting it up into smaller chunks. TOAST stands for The Oversized Attribute Storage Technique. TOAST happens automatically, you don’t set up anything, it just comes with Postgres out of the box. So why should you care? Well TOAST can impac...
Read MoreElizabeth Christensen
Elizabeth Christensen
We hosted our annual PostGIS day a couple weeks ago with some great talks on a big variety of topics within open-source GIS. Here is a summary of the themes I saw take shape across the day’s events that will point you towards the recordings, depending on your interests. A full playlist of PostGIS Day 2023 is available on our YouTube channel. If you’ve spent time with developers this year you know that folks love to tell you the details and reasoning behind their tech stack and the GIS communit...
Read MoreElizabeth Christensen
Elizabeth Christensen
Wouldn’t it be awesome if money worked just like time in Postgres? You could store one canonical version of it, it worked across the globe? Well sadly, money is a whole different ball of wax. Though like time, money is part of most database implementations and I wanted to lay out some of the best practices I’ve gathered for working with money in Postgres. I also have a tutorial up if you want to try this with Postgres running in a web browser. Postgres actually does have a data type. This...
Read MoreElizabeth Christensen
Elizabeth Christensen
Postgres databases are very compliant, they do what you tell them until you tell them to stop. It is really common for a runaway process, query, or even something a co-worker runs to accidentally start a never ending transaction in your database. This potentially uses up memory, i/o, or other resources. Postgres has no preset default for this. To find out your current setting: A good rule of thumb can be a minute or a couple minutes. This is a connection-specific setting, so you’ll need to rec...
Read MoreElizabeth Christensen
Elizabeth Christensen
Beyond a basic query with a join or two, many queries require extracting subsets of data for comparison, conditionals, or aggregation. Postgres’ use of the SQL language is standards compliant and SQL has a world of tools for subqueries. This post will look at many of the different subquery tools. We’ll talk about the advantages and use cases of each, and provide further reading and tutorials to dig in more. I’ll take a broad definition of “subquery”. Why am I calling all of these subqueries? The...
Read MoreElizabeth Christensen
Elizabeth Christensen
We recently gave a talk at SCaLE (Southern California Linux Expo) about common problems and solutions for managing large Postgres databases. One of the topics we covered was data skewing and partial indexing. This piqued some conference discussion afterwards so we wanted to do a deeper dive. Skewed data is when your data is kind of bunched up - essentially it is not evenly distributed. You might have one really large customer with a customer id that takes up more than half the rows in your eve...
Read MoreElizabeth Christensen
Elizabeth Christensen
Since humans first started recording data, they’ve been keeping track of time. Time management is one of those absolutely crucial database tasks and Postgres does a great job of it. Postgres has a lot of options for storing and querying time so I wanted to provide an overview of some of the most common needs for storing and retrieving time data. This blog is also available as a hands on tutorial running in your local browser via our Postgres playground. If you ask Postgres what time it is, You...
Read More