Crunchy Data joins Snowflake. Read the announcement
Jonathan S. Katz
Jonathan S. Katz
Many applications today record data from sensors, devices, tracking information, and other things that share a common attribute: a timestamp that is always increasing. This timestamp is very valuable, as it serves as the basis for types of lookups, analytical queries, and more. PostgreSQL 9.5 introduced a feature called block range indexes (aka BRIN ) that is incredibly helpful in efficiently searching over large time series data and has the benefit of taking up significantly less space on di...
Read MorePaul Ramsey
Paul Ramsey
New features and better performance get a lot of attention, but one of the relatively unsung improvements in PostGIS over the past ten years has been inclusion in standard software repositories, making installation of this fairly complex extension a "one click" affair. Once you've got PostgreSQL/PostGIS installed though, how are upgrades handled? The key is having the right versions in place, at the right time, for the right scenario and knowing a little bit about how PostGIS works. To exercise...
Read MoreJonathan S. Katz
Jonathan S. Katz
Common table expressions , aka CTEs , aka WITH queries , are not only the gateway to writing recursive SQL queries , but also help developers write maintainable SQL. WITH query clauses can help developers who are more comfortable writing in imperative languages to feel more comfortable writing SQL, as well as help reduce writing redundant code by reusing a particular common table expressions multiple times in a query. A new patch , scheduled to be a part of PostgreSQL 12 major release late...
Read MoreJoe 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 More