Keith 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
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 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 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 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
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 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