Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more

Posts about Production Postgres

  • Postgres Locking: When is it Concerning?

    David Christensen

    When using monitoring tools like PgMonitor or pganalyze , Crunchy clients will often ask me about high numbers of locks and when to worry. Like most engineering-related questions, the answer is: "it depends". In this post, I will provide a little more information about locks, how they are used in PostgreSQL, and what things to look for to spot problems vs high usage. PostgreSQL uses locks in all parts of its operation to serialize or share access to key data. This can come in the form of two...

    Read More
  • Phases of Database Growth and Cost

    Christopher Winslett

    TL;DR : keep your data-layer simple, you'll need the agility it offers when budgets get stretched. Over the past 12 years of helping people run databases for their businesses, I have discovered that there are only 2 financial phases in the lifecycle of a database: • Phase 1 : Absolute costs are low, thus marginal costs do not matter. • Phase 2 : Absolute costs are high, thus marginal costs are all that matter. Phase 1 : Absolute costs are low, thus marginal costs do not matter. Phase 2 : Absolut...

    Read More
  • Exporter2Perfdata - Connecting Postgres Monitoring Tools

    Yogesh Sharma

    pgMonitor is a popular open source monitoring tool based on Prometheus, Exporters, and Grafana. We have many customers leveraging pgMonitor when using the Crunchy Postgres for Kubernetes and Crunchy Postgres . pgMonitor ships out-of-the-box with its own configurations for easy setup of exporters and has a special set of SQL queries for Postgres monitoring using the . We often hear questions from customers like "Can I use Prometheus Exporters with Icinga2 or Nagios?". We are excited to offer...

    Read More
  • Postgres Query Optimization: LEFT JOIN vs UNION ALL

    David Christensen

    The PostgreSQL optimizer is an amazing thing, getting only more amazing with each release. It is able to take information about your data definitions, your data distribution, constraints, and the specific queries and come up with the generally most efficient way to return the results of that query. Since SQL is a declarative language, we're explicitly giving up defining how the database determines the results and trusting it to get the correct results in whatever method it deems most efficient...

    Read More
  • Postgres Traceability: Use SQL Comments Like a Stacktrace

    Christopher Winslett

    Have you traced a sql statement back to the source application? Of course you have! Was it time consuming? Probably! SQL queries can hide in plain sight. If you've read through any of Crunchy's advice on query performance or healthy databases you know there's a goldmine of optimization waiting for you in your database. When using an ORM, the queries are obfuscated, and tracing a specific query to the application code can be challenging. Using Ruby as an example below (BTW, I <3 Ruby; I will...

    Read More
  • Demystifying Database Performance for Developers

    Christopher Winslett

    For many developers, databases are basically magic. Like Penn & Teller, this blog post is set to break the illusion. Databases are just like any other code, they have algorithms and processes. These algorithms and processes are meant to improve performance, but can cause limitations if they are not expected. Disclaimer : it is okay to break the rules. Sometimes, you may choose to have slow database interactions, because you know they are rare events. Assuming a well-designed database infrastru...

    Read More
  • Five Tips For a Healthier Postgres Database in the New Year

    Craig Kerstiens

    It's been a busy year building Crunchy Bridge and we've shipped a lot of new awesome things. Instead of doing a wrap-up of all the growth and exciting features, instead I wanted to take the time to try to teach a few more things to those that follow us. While onboarding customer after customer this year I've noted a few key things everyone should put in place right away - to either improve the health of your database or to save yourself from a bad day. Long running (usually unintentionally so)...

    Read More
  • Patroni & etcd in High Availability Environments

    Caitlin Strong

    Crunchy Data products often include High Availability. Patroni and etcd are two of our go-to tools for managing those environments. Today I wanted to explore how these work together. Patroni relies on proper operation of the etcd cluster to decide what to do with PostgreSQL. When communication between these two pieces breaks down, it creates instability in the environment resulting in failover, cluster restart, and even the the loss of a primary database. To fully understand the importance of...

    Read More
  • Postgres 14: It's The Little Things

    Craig Kerstiens

    A lot of years Postgres will have some big pillar or theme to the release. Often this is thought of after the fact. Everything that is committed is looked at and someone thinks, "This is the key thing to talk about." In Postgres 9.2 it was JSON , in 9.4 it was JSONB, in 10 it was logical replication , 12 was a broader performance theme. While I look forward to each of these big highlights, in each release I'm equally excited to browse through and pull out the small things that simply make my l...

    Read More
  • Insert-Only Tables and Autovacuum Issues Prior to PostgreSQL 13

    David Christensen

    Generally, one appreciates new features of PostgreSQL on the release date after anxious inspection of the release notes or having skimmed through the git logs. Every once in a while, particularly when dealing with an older version of PostgreSQL, you will discover a feature that didn't get the necessary fanfare in order to come to your attention, but when you discover it, you're all the happier for it. I recently ran into an issue with some strange performance issues on a client's PostgreSQL...

    Read More