Latest posts from 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

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

Whether you are managing a large table or setting up automatic archiving, time based partitioning in Postgres is incredibly powerful. pg_partman

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.

The nature of most relational databases means that deleting large volumes of data can be very inefficient and that space is not immediately, if ever, returned to the file system. PostgreSQL does not return the space it reserves to the file system when normal deletion operations are run except under very specific conditions:

1. the page(s) at the end of the relation are completely emptied
2. a VACUUM FULL/CLUSTER is run against the relation (exclusively locking it until complete)

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

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 methods that are employed. This blog post is not going to discuss any deep technical implementations of encryption. Instead, let's discuss what vectors of attack any given encryption method will mitigate since that will greatly influence which method is effective before you even reach any sort of development or deployment phases.

The application of encryption to a database environment can be broken down into three different methods:

1. Data-At-Rest
2. Data-In-Transit
3. Data-In-Use

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 features that make this migration easier were not yet implemented. It is highly recommended to move to PG11 or higher if you want to migrate existing partition sets.

Also note that while this migration article is specific to pg_partman

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 PgBouncer is important, and how you can do this easily using pgMonitor

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

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