Posts about Production Postgres

The Postgres hackers mailing list (pgsql-hackers@postgresql.org) is an invaluable resource for anyone wanting to contribute to the PostgreSQL code. The Postgres project does not use PRs (pull requests) or GitHub issues. So if you want to contribute an idea, or help with code reviews, the hackers mailing list is the canonical way to do so. More information on contributing is on the Postgres wiki at: https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer

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

There’s a couple super common Postgres errors you’re likely to encounter while using this database, especially with an application or ORM. One is the PG::DiskFull: ERROR: could not resize shared memory segment. It will look something like this.

"PG::DiskFull: ERROR: could not resize shared memory segment "/PostgreSQL.938232807" to 55334241 bytes: No space left on device"

Many folks are surprised to hear that Postgres has parallel queries out of the box. This was released in small batches across a half dozen versions of Postgres, so the major fanfare for having parallelism got a little bit lost.

By default Postgres is configured for two parallel workers. The Postgres query planner will assemble a few plans for any given query and will estimate the additional overhead of performing parallel queries, and make a go or no-go decision. Depending on the settings and the calculations of the query planner, parallel queries are typically used by large and long running queries — like warehouse or analytical workloads.

Below is the output of a sample EXPLAIN

In the evolving world of data management, ensuring consistency and accuracy across multiple database systems is paramount. Whether you're migrating data, synchronizing systems, or performing routine audits, the ability to compare data across different database platforms is crucial. Enter pgCompare

When your company has decided it's time to invest in more open source, Postgres is the obvious choice. Managing databases is not new and you already have established practices and requirements for rolling out a new database. One of the big requirements we frequently help new customers with on their Postgres adoption is data encryption. While the question is simple, there's a few layers to it that determine which is the right approach for you. Here we'll walk through the pros and cons of approaches and help you identify the right path for your needs.

Overview of At-Rest Encryption Methods

I recently created a Postgres extension which utilizes the pg_cron extension to schedule recurring activities using the cron.schedule(). Everything worked great. The only problem was when I dropped my extension, it left the cron job scheduled, which resulted in regular errors:

2024-04-06 16:00:00.026 EST [1548187] LOG:  cron job 2 starting: SELECT bridge_stats.update_stats('55 minutes', false)
2024-04-06 16:00:00.047 EST [1580698] ERROR:  schema "bridge_stats" does not exist at character 8
2024-04-06 16:00:00.047 EST [1580698] STATEMENT:  SELECT bridge_stats.update_stats('55 minutes', false)

Postgres is an amazing database system, but it does come with a five-year life cycle. This means you need to perform a major upgrade of it at least every five years. Luckily, Postgres ships with the pg_upgrade program, which enables a quick and easy migration from one major version of Postgres to another.

Let's work through an example of how to upgrade - in this case, we will go from Postgres 12 to Postgres 16. You should always aim to go to the highest version possible. Check postgresql.org to see what the current version is. If you get stuck, the official documentation

There’s a pretty HOT performance trick in Postgres that doesn’t get a ton of attention. There’s a way for Postgres to only update the heap (the table), avoiding having to update all the indexes. That’s called a HOT update

PostgreSQL uses the concept of a timeline to identify a series of WAL records in space and time. Each timeline is identified by a number, a decimal in some places, hexadecimal in others. Each time a database is recovered using point in time recovery and sometimes during standby/replica promotion, a new timeline is generated.

A common mistake is to assume that a higher timeline number is synonymous with the most recent data. While the highest timeline points to the latest incarnation of the database, it doesn't guarantee that the database indeed holds the most useful data from an application standpoint. To discern the validity of this statement, a closer examination of the Write-Ahead Logging (WAL) history files is essential, unraveling the messages they convey.

In this discussion, we will explore a recovered database and trace the narrative embedded in the history files. By the conclusion, you will have gained a deeper insight into the functionality of these history files within Postgres, empowering you to address queries related to recovery processes and the database's historical journey (or may I call it the 'family tree').

Assessing current state