Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more

  • 8 min read

    Contributing to Postgres 101: A Beginner's Experience

    Elizabeth Christensen

    I recently got my very first patch into PostgreSQL! To be clear I'm not a C developer and didn't contribute some fancy new feature. However, I do love Postgres and wanted to contribute. Here's my journey and what I learned along the way. I had an idea for a docs patch while I was talking to Stephen Frost about some research and writing I was doing about HOT updates and fill factor . A recent update to HOT updates meant HOT could be compatible with BRIN. And while the HOT readme was up to date,...

    Read More
  • 5 min read

    Inside PostGIS: Calculating Distance

    Paul Ramsey

    Calculating distance is a core feature of a spatial database, and the central function in many analytical queries. • "How many houses are within the evacuation radius?" • "Which responder is closest to the call?" • "How many more miles until the school bus needs routine maintenance?" "How many houses are within the evacuation radius?" "Which responder is closest to the call?" "How many more miles until the school bus needs routine maintenance?" PostGIS and any other spatial database let you answ...

    Read More
  • Examining Postgres Upgrades with pg_upgrade

    Greg Sabino Mullane

    Postgres is an amazing database system, but it does come with a five-year life cycle. This means you need to perform a major upgrade of it at least every five years. Luckily, Postgres ships with the program, which enables a quick and easy migration from one major version of Postgres to another. Let's work through an example of how to upgrade - in this case, we will go from Postgres 12 to Postgres 16. You should always aim to go to the highest version possible. Check postgresql.org to see what...

    Read More
  • Postgres Performance Boost: HOT Updates and Fill Factor

    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 More
  • 6 min read

    Connecting QGIS to Postgres and PostGIS

    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 More
  • 4 min read

    Migrate from Heroku Postgres to Crunchy Bridge

    Craig Kerstiens

    While database migrations are not an everyday thing for you, they are for us. Migrating to a new database provider isn't something you ever take lightly. Once you've come to the decision that you may want to migrate then you look at the time and effort cost of switching, only to wonder if it's really worth it. You decide it is, and still you're left with uncertainty of what-if: • What about Postgres versions? • What about Postgres extensions? • What about collations? • How do you minimize cutove...

    Read More
  • The Rest is History: Investigations of WAL History Files

    Brian Pace

    PostgreSQL uses the concept of a timeline to identify a series of WAL records in space and time. Each timeline is identified by a number, a decimal in some places, hexadecimal in others. Each time a database is recovered using point in time recovery and sometimes during standby/replica promotion, a new timeline is generated. A common mistake is to assume that a higher timeline number is synonymous with the most recent data. While the highest timeline points to the latest incarnation of the datab...

    Read More
  • 3 min read

    PostGIS Clustering with K-Means

    Paul Ramsey

    Clustering points is a common task for geospatial data analysis, and PostGIS provides several functions for clustering. • ST_ClusterDBSCAN • ST_ClusterKMeans • ST_ClusterIntersectingWin • ST_ClusterWithinWin ST_ClusterDBSCAN ST_ClusterKMeans ST_ClusterIntersectingWin ST_ClusterWithinWin We previously looked at the popular DBSCAN spatial clustering algorithm, that builds clusters off of spatial density. This post explores the features of the PostGIS ST_ClusterKMeans function. K-means cluste...

    Read More
  • Postgres Not Starting: The Case of the Missing Output

    Greg Sabino Mullane

    My colleague Bob Pacheco asked me to help with a strange problem he was witnessing for a client. A new Postgres cluster was getting created on a Kubernetes node, but it refused to start. More mysteriously, there was no message in the Postgres logs, nothing in the pg_ctl start up file, and no output anywhere. The pg_ctl program started up, then ended. Nothing on stderr, nothing on stdout! We were able to duplicate it on the command line using pg_ctl. Oddly enough, a syntax error in the post...

    Read More
  • 5 min read

    Crunchy Bridge: Announcing Postgres Insights in Your CLI

    Craig Kerstiens

    Today we're excited to release a big update to our Crunchy Bridge CLI: a new interactive menu for psql! Now when connecting to your Crunchy Bridge database with you'll have a option. The menu is an easy to navigate collection of insights about your database. All of these insights are powered by data already contained in Postgres system catalogs. We have these same database insights in the dashboard, so this feature extends that to those working directly from the command line. Before we add...

    Read More