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

  • 5 min read

    Easy Totals and Subtotals in Postgres with Rollup and Cube

    Elizabeth Christensen

    Postgres is being used more and more for analytical workloads. There’s a few hidden gems I recently ran across that are really handy for doing SQL for data analysis, and . Rollup and cube don’t get a lot of attention, but follow along with me in this post to see how they can save you a few steps and enhance your date binning and summary reporting. We also have a web based tutorial that covers Postgres Functions for Rolling Up Data by Date if you want to try it yourself with a sample dat...

    Read More
  • 8 min read

    A change to ResultRelInfo - A Near Miss with Postgres 17.1

    Craig Kerstiens

    Version 17.2 of PostgreSQL has now released which rolls back the changes to ResultRelInfo. See the release notes for more details. Since its inception Crunchy Data has released new builds and packages of Postgres on the day community packages are released. Yesterday's minor version release was the first time we made the decision to press pause on a release. Why did we not release it immediately? There appeared to be a very real risk of breaking existing installations. Let's back up and walk...

    Read More
  • 5 min read

    Accessing Large Language Models from PostgreSQL

    Paul Ramsey

    Large language models (LLM) provide some truly unique capacities that no other software does, but they are notoriously finicky to run, requiring large amounts of RAM and compute. That means that mere mortals are reduced to two possible paths for experimenting with LLMs: • Use a cloud-hosted service like OpenAI . You get the latest models and best servers, at the price of a few micro-pennies per token. • Use a small locally hosted small model. You get the joy of using your own hardware, and on...

    Read More
  • 8 min read

    8 Steps in Writing Analytical SQL Queries

    Christopher Winslett

    It is never immediately obvious how to go from a simple SQL query to a complex one -- especially if it involves intricate calculations. One of the “dangers” of SQL is that you can create an executable query but return the wrong data. For example, it is easy to inflate the value of a calculated field by joining to multiple rows. Use Crunchy Playground to follow allow with this blog post using a Postgres terminal: Postgres Playground w/ Sample Data Let’s take a look at a sample query. This appears...

    Read More
  • 8 min read

    4 Ways to Create Date Bins in Postgres: interval, date_trunc, extract, and to_char

    Christopher Winslett

    You followed all the best practices, your sales dates are stored in perfect timestamp format …. but now you need to get reports by day, week, quarters, and months. You need to bin, bucket, and roll up sales data in easy to view reports. Do you need a BI tool? Not yet actually. Your Postgres database has hundreds of functions that let you query data analytics by date. By using some good old fashioned SQL - you have powerful analysis and business intelligence with date details on any data set. In...

    Read More
  • 4 min read

    Crunchy Postgres for Kubernetes 5.7: Faster Backups, Automated Snapshots, Postgres 17 and More

    Greg Nokes

    We are excited to announce the release of Crunchy Postgres for Kubernetes 5.7! This latest version brings a wealth of new features and enhancements designed to make your Postgres deployments on Kubernetes more flexible, efficient, secure, and robust than ever before. We have highlighted a few of the features that we are excited about below. You can also check out the release notes for more details We took a close look at our backup and disaster recovery systems to identify areas where we could...

    Read More
  • 4 min read

    pg_parquet: An Extension to Connect Postgres and Parquet

    Craig Kerstiens

    Today, we’re excited to release pg_parquet - an open source Postgres extension for working with Parquet files. The extension reads and writes parquet files to local disk or to S3 natively from Postgres. With pg_parquet you're able to: • Export tables or queries from Postgres to Parquet files • Ingest data from Parquet files to Postgres • Inspect the schema and metadata of existing Parquet files Export tables or queries from Postgres to Parquet files Ingest data from Parquet files to Postgres I...

    Read More
  • 5 min read

    Convert JSON into Columns and Rows with JSON_TABLE

    Paul Ramsey

    If you missed some of the headlines and release notes, Postgres 17 added another huge JSON feature to its growing repository of strong JSON support with the JSON_TABLE feature. JSON_TABLE lets you query JSON and display and query data like it is native relational SQL. So you can easily take JSON data feeds and work with it like you would any other Postgres data in your database. A few days ago, I was awakened in the middle of the night when my house started to shake. Living in the  Cascadia su...

    Read More
  • 3 min read

    Enhanced Postgres Release Notes

    Greg Sabino Mullane

    There is something new you may not have seen in the release notes for Postgres 17 . No, not a new feature - I mean inside the actual release notes themselves! The Postgres project uses the git program to track commits to the project, and now each item in the release notes has a link to the actual commit (or multiple commits) that enabled it. You may have missed it if you were scanning the release notes, but after the end of each specific item in the release note is a small “section” symbol whic...

    Read More
  • Vehicle Routing with PostGIS and Overture Data

    Paul Ramsey

    The Overture Maps collection of data is enormous, encompassing over 300 million transportation segments , 2.3 billion building footprints , 53 million points of interest , and a rich collection of cartographic features as well. It is a consistent global data set, but it is intimidatingly large -- what can a person do with such a thing? Building cartographic products is the obvious thing, but what about the less obvious. With an analytical engine like PostgreSQL and Crunchy Bridge for Analy...

    Read More