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
Crunchy Data is pleased to announce a new open source pgMonitor Extension . Crunchy Data has worked on a pgMonitor tool for several years as part of our Kubernetes and self-managed Postgres deployments and recently we’ve added an extension to the tool set. Two primary scenarios motivated the creation of the pgMonitor extension : 1. Quicker Metrics : Monitoring metrics often need quick response times to allow for frequent updates. We've noticed that certain metrics become slower as the datab...
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 Bridge for Analytics . 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 d...
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 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 MoreKeith Fiske
Keith Fiske
One of the most requested features by Crunchy Data customers using modern enterprise database environments is some form of data encryption. However, nailing down exactly what someone means when they say "We need our data encrypted" is often a challenge due to the actual requirements not being fully clarified or even understood. So, before anyone tries to implement database encryption it is critically important to understand what needs to be encrypted and what benefit is actually gained by the...
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 MoreKeith Fiske
Keith Fiske
For connection pooling in PostgreSQL, one of the best and most popular tools out there is PgBouncer . However, monitoring PgBouncer can be challenging due to its use of SHOW commands, which are only available via a special database connection as opposed to making its statistics available via a standard table or view. In order to more easily monitor PgBouncer, the team at Crunchy Data developed an open source PgBouncer Foreign Data Wrapper (pgbouncer_fdw). This blog post describes why monitoring...
Read MoreKeith Fiske
Keith Fiske
One of the most critical topics to understand when administering a PostgreSQL database is the concept of transaction IDs ( TXID ) and that they can be exhausted if not monitored properly. However, this blog post isn't going to go into the details of what it TXID exhaustion actually is. The Routine Vacuuming section of the documentation is probably one of the most important to read and understand so I will refer you there. What this blog post is going to cover is an easy way to monitor for it...
Read MoreKeith Fiske
Keith Fiske
I’ve been noticing that the query used in v1.x of my pg_bloat_check.py script (obtained from the check_postgres.pl module) was not always accurate and was often not reporting on bloat that I knew for a fact was there (Ex: I just deleted over 300 million rows, vacuumed & analyzed the table and still no bloat? Sure it could happen, but highly unlikely). So I continued looking around and discovered the pgstattuple contrib module that comes with PostgreSQL. After discussing it with several of...
Read More