Craig Kerstiens
Craig Kerstiens
Postgres comes with built-in partitioning and you can also layer in for for additional help with maintenance of your partitioning. It works quite well for partitioning your data to make it easy to retain a limited set of data and improve performance if your primary workload is querying a small time series focused subset of data. Oftentimes, when implementing partitioning you only keep a portion of your data then drop older data as it ages out for cost management. But what if we could move ol...
Read MoreKeith Fiske
Keith Fiske
UPDATE : Using a default partition as a safety net—and draining it—still applies on Postgres 19. Native partition DDL got richer around it ( / , smoother detach/attach), but the default-partition operational guidance here remains current. See Postgres 19: How Our Advice Has Changed Since We Wrote It . 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...
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
UPDATE : Retention by detaching or dropping children still beats giant s, and pg_partman remains the calendar for create/retain schedules. Postgres 19 adds native / for coarsening or splitting ranges; use those in quieter windows alongside concurrent detach for zero-downtime rotation. See Postgres 19: How Our Advice Has Changed Since We Wrote It . 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...
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
UPDATE : Partition for lifecycle and cheap drops first—that framing still holds. Later releases added online , identity columns on partitioned parents, and in Postgres 19 native / plus direct on the parent. See Postgres 19: How Our Advice Has Changed Since We Wrote It . 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...
Read MoreKeith Fiske
Keith Fiske
UPDATE : Preferring native declarative partitioning over trigger/inheritance is still correct. Postgres has continued to smooth day-to-day partition operations through 19 ( , richer parent DDL, / ). See Postgres 19: How Our Advice Has Changed Since We Wrote It . 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 migra...
Read More