Joe Conway
Joe Conway
I want to take a few minutes for a deep dive into the effect your data model has on storage density when using PostgreSQL . When this topic came up with a customer, I explained my thoughts on the matter, but I realized at the time that I had never done a reasonably careful apples-to-apples test to see just exactly what the effect is, at least for a model sample size of one. So here it is. Let's start out with a presumed use-case. I have two tables: one is a parent table and the other a detail t...
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 MoreDave Cramer
Dave Cramer
One of the toughest challenges facing database engineers today is ensuring their data is always accessible so they can meet the high-availability requirements for their applications. While this problem may seem trivial in the world of applications where one can have many instances behind geographically distributed load balancers, in the database world where there is only one version of the data globally, the problem faces many obstacles. PostgreSQL replication has advanced considerably in rece...
Read MoreJeff McCormick
Jeff McCormick
Crunchy Data is happy to announce the release of the open source PostgreSQL Operator 3.5 for Kubernetes project, which you can find here: https://github.com/CrunchyData/postgres-operator/ This latest release provides further feature enhancements designed to support users intending to deploy large-scale PostgreSQL clusters on Kubernetes , with enterprise high-availability and disaster recovery requirements. When combined with the Crunchy PostgreSQL Container Suite , the PostgreSQL Operato...
Read MoreJonathan S. Katz
Jonathan S. Katz
An important part of running a production PostgreSQL database system (and for that matter, any database software) is to ensure you are prepared for disaster . There are many ways to go about preparing your system for disaster, but one of the simplest and most effective ways to do this is by taking periodic backups of your database clusters. How does one typically go about setting up taking a periodic backup? If you’re running PostgreSQL on a Linux based system, the solution is to often use c...
Read MoreJonathan S. Katz
Jonathan S. Katz
Interested in running PostgreSQL natively on Kubernetes ? Let's look at a few quick steps to get up and running with the open source Crunchy PostgreSQL Operator for Kubernetes on your choice of Kubernetes deployment. The Crunchy PostgreSQL Operator (aka " pgo ") provides a quickstart script to automate the deployment of the Crunchy PostgreSQL Operator to a number of popular Kubernetes environments, including Google Kubernetes Engine ( GKE ), OpenShift Container Platform ( OCP ) and...
Read MoreDavid Thomas
David Thomas
For most major upgrades using a utility such as pg_upgrade or a replication tool such as pglogical will be the best solution. However if these options are not available, pg_dumpall can be used to perform a major upgrade. What follows is a guide on how you can safely upgrade your database to a newer version of PostgreSQL with pg_dumpall. You will first need to install the latest PostgreSQL binaries on the new system (hostname new in this example). Once the binaries are installed you will ne...
Read MoreJonathan S. Katz
Jonathan S. Katz
Many applications these days want us to know how close we are to things: • What are the three closest coffee shops to my current location? • Which is the nearest airport to the office? • What are the two closest subway stops to the restaurant? What are the three closest coffee shops to my current location? Which is the nearest airport to the office? What are the two closest subway stops to the restaurant? and countless more examples. Another way of asking these questions is to say “who are my ne...
Read MoreJonathan S. Katz
Jonathan S. Katz
The PostgreSQL 11 release is nearly here (maybe in the next couple of weeks?!), and while a lot of the focus will be on the improvements to the overall performance of the system (and rightly so!), it's important to notice some features that when used appropriately, will provide noticeable performance improvements to your applications. One example of such feature is the introduction of "covering indexes" for B-tree indexes. A covering index allows a user to perform an index-only scan if the...
Read MoreJonathan S. Katz
Jonathan S. Katz
Crunchy Data recently announced the publication of the CIS PostgreSQL Benchmark by the Center for Internet Security , a nonprofit organization that provides publications around standards and best practices for securing technologies systems. This CIS PostgreSQL Benchmark builds on earlier work that Crunchy started when it helped to publish the PostgreSQL Security Technical Implementation Guide ( PostgreSQL STIG ) and provides guidance and steps to help secure your PostgreSQL databases. A CIS...
Read More