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

  • 14 min read

    Snap Clones with the Postgres Operator on Kubernetes

    Brian Pace

    Getting frequent copies of data for development and testing purposes is a very common use case with our enterprise customer base. We have customers getting data copes for each sprint or development cycle. This increasing data copy problem can put a strain on IT budgets with the storage consumed and the hours spent performing database refreshes. A common process is to build or refresh environments using database restores. There can be some challenges with this approach. First, the data must be mo...

    Read More
  • 6 min read

    Postgres' Clever Query Planning System

    Paul Ramsey

    The sheer cleverness of relational databases is often discounted because we so frequently use them for very simple data management tasks. Serialize an object into a row, store with unique key. yawwwn Search for unique key, deserialize row into an object. yawwwwwwn The real power of relational databases is juggling "relations" (aka tables) in large numbers and figuring out on-the-fly the most effective way to filter out rows and find an answer. PostgreSQL has an undeniably clever query plannin...

    Read More
  • 9 min read

    Quick and Easy Postgres Data Compare

    Brian Pace

    If you're checking archives or working with Postgres replication, data reconciliation can be a necessary task. Row counts can be one of the go to comparison methods but that does not show data mismatches. You could pull table data across the network and then compare each row and each field, but that can be a demand on resources. Today we'll walk through a simple solution for your Postgres toolbox - using Foreign Data Wrappers to connect and compare the two source datasets. With the foreign data...

    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
  • 5 min read

    Effective PostgreSQL Cluster Configuration & Management Using PGO v5.1

    Andrew L'Ecuyer

    Modern day production ready Postgres solutions require quite a bit of sophistication and automation. Changes need to be applied in a uniform and safe way. DevOps and SRE teams need to be in control system updates while limiting disruption to their users. With the release of PGO v5.1 , we are excited to announce enhancements in each of these areas. Not only does PGO v5.1 now automatically rollout all PostgreSQL configuration changes, but it allows you to protect your running databases against ou...

    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
  • 5 min read

    Instant Heatmap with pg_featureserv

    Paul Ramsey

    The pg_featureserv micro-service is a thin middleware that binds tables and functions in a PostgreSQL database to a JSON collections API, accessible over HTTP. Using the Crunchy Bridge container apps , I'm going to give a quick overview of how to set up a web based spatial heatmap from Postgres. The application uses PostgreSQL to store and search 2.2M geographic names in the USA. Type in the search box and the auto-fill form will find candidate words. Select a word, and the database will pe...

    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
  • 7 min read

    The Vectors of Database Encryption

    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 More