Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Keith Fiske
Keith Fiske
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 , which I’ll dig into in this post and discuss how to use it effectively. The default partition is pretty much what it sounds like; you can make a special parti...
Read MoreKeith Fiske
Keith Fiske
Whether you are managing a large table or setting up automatic archiving , time based partitioning in Postgres is incredibly powerful. pg_partman ’s newest versions support a huge variety of custom time internals. Marco just published a post on using pg_partman with our new database product for doing analytics with Postgres , Crunchy Data Warehouse . So I thought this would be a great time to review the basic and complex options for the time based partitioning. When I first started designi...
Read MoreMarco Slot
Marco Slot
One of the unique characteristics of the recently launched Crunchy Bridge for Analytics is that it is effectively a hybrid between a transactional and an analytical database system. That is a powerful tool when dealing with data-intensive applications which may for example require a combination of low latency, high throughput insertion, efficient lookup of recent data, and fast interactive analytics over historical data. A common source of large data volumes is append-mostly time series data o...
Read MoreKeith Fiske
Keith Fiske
You could be saving money every month on databases costs with a smarter data retention policy. One of the primary reasons, and a huge benefit of partitioning is using it to automatically archive your data. For example, you might have a huge log table. For business purposes, you need to keep this data for 30 days. This table grows continually over time and keeping all the data makes database maintenance challenging. With time-based partitioning, you can simply archive off data older than 30 days....
Read MoreCraig Kerstiens
Craig Kerstiens
Postgres is a robust data platform . Yes, it's more than a boring old relational database. It has rich indexing, data types (including JSON ), and so much more. It also has support for a variety of extensions that can further broaden it's already great functionality. Two of those extensions when coupled together make Postgres a very compelling approach for IoT architectures. Today we're going to start from the ground up on how you would design your architecture with Postgres along with the Ci...
Read MoreKeith Fiske
Keith Fiske
After much testing and work the PostgreSQL Partition Manager, pg_partman , version 5 is now available for public release. Thanks to everyone involved for helping me get here! My recent post discusses many of the big changes, so please see that post or the CHANGELOG for a full summary of version 5. What I'd like to do today is take a step back and review five notable features that make pg_partman an important tool for managing large tables in PostgreSQL: • Retention • Background Worker • Add...
Read MoreElizabeth Christensen
Elizabeth Christensen
Vanilla Postgres has native partitioning? Yes! And it's really good! We frequently get questions like: Can Postgres handle JSON? Can Postgres handle time series data? How scalable is Postgres? Turns out the answer is most usually yes! Postgres, vanilla Postgres, can handle whatever your need is without having to go to a locked in proprietary database. Unless you're really close to the Postgres internals and code releases you might have missed that Postgres natively has partitioning. Our head of...
Read MoreKeith Fiske
Keith Fiske
PostgreSQL 10 introduced native partitioning and more recent versions have continued to improve upon this feature. However, many people set up partition sets before native partitioning was available and would greatly benefit from migrating to it. This article will cover how to migrate a partition set using the old method of triggers/inheritance/constraints to a partition set using the native features found in PostgreSQL 11+. Note these instructions do not cover migrating to PG10 since some key f...
Read More