Featured Post

Partitioning is an important database maintenance strategy for a growing application backed by PostgreSQL. As one of the main authors of pg_partman and an engineer here at Crunchy Data, I spend a lot of my time helping folks implement partitioning. One of the nuances of PostgreSQL’s partitioning implementation is the default partition

PostgreSQL is one of the most versatile data storage and processing tools available. We enhanced it even further by adding Iceberg tables to PostgreSQL in Crunchy Data Warehouse with a fast analytical query engine.

What is Iceberg? Iceberg tables are stored in a compressed columnar format for fast analytics in object storage (S3). This means storage is cheap and there are no storage limits. Yet the tables are still transactional and work with nearly all PostgreSQL features. Crunchy Data Warehouse can also query or load raw data from object storage into Iceberg tables via PostgreSQL commands.

A pattern we repeatedly see in data analytics scenarios is:

• Use temporary or external tables to collect raw data
• Use Iceberg as a central repository to organize data
• Use PostgreSQL tables or materialized views for querying insights

In late November, on the day after GIS Day, we hosted the annual PostGIS day online event. 22 speakers from around the world, in an agenda that ran from mid-afternoon in Europe to mid-afternoon on the Pacific coast.

We had an amazing collection of speakers, exploring all aspects of PostGIS, from highly technical specifics, to big picture culture and history. A full playlist

PostgreSQL is the bedrock on which many of today’s organizations are built. The versatility, reliability, performance, and extensibility of PostgreSQL make it the perfect tool for a large variety of operational workloads.

The one area in which PostgreSQL has historically been lacking is analytics, which involves queries that summarize, filter, or transform large amounts of data. Modern analytical databases are designed to query data in data lakes in formats like Parquet

The OpenStreetMap (OSM) database builds almost 750GB of location data from a single file download. OSM notoriously takes a full day to run. A fresh open street map load involves both a massive write process and large index builds. It is a great performance stress-test bulk load for any Postgres system. I use it to stress the latest PostgreSQL versions and state-of-the-art hardware. The stress test validates new tuning tricks and identifies performance regressions.

Two years ago, I presented (video

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, ROLLUP and CUBE. 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

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 through a near miss of Postgres release day.

Yesterday when Postgres 17.1 was released there appeared to be breaking changes in the Application Build Interface (ABI). The ABI is the contract that exists between PostgreSQL and its extensions. Initial reports showed that a number of extensions could be affected, triggering warning sirens

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

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

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 this post, I’ll walk through some of the key functions querying data by date.

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