Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Jonathan 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 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 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 MoreJonathan S. Katz
Jonathan S. Katz
Having replicas in a database environment can fulfill many goals: create a high-availability environment, load balance read-only queries across several nodes, have a read-only database to run analytical queries on, and more. PostgreSQL introduced streaming replication in PostgreSQL 9.0 almost 8 years ago to help facilitate satisfying the above requirements, and through the years, the community has added many features to continue to enhance the replication user experience. There are many diff...
Read MoreJonathan S. Katz
Jonathan S. Katz
Crunchy Data recently released version 2.6 of the PostgreSQL Kubernetes Operator , a powerful controller that follows the Kubernetes Operator pattern that enables users to provision and manage thousands of PostgreSQL databases at scale. We thought that we would demonstrate some of the features in this latest version over the next few weeks, which includes support for manual database failover, storage selection, node (or server) selection preference, and many goodies that make it easier...
Read MoreJonathan S. Katz
Jonathan S. Katz
If you have been asked to provide a CSV that someone can open up in their favorite spreadsheet editor, chances are you have used the PostgreSQL COPY command. COPY has been around since the early open source releases of PostgreSQL back in the late 1990s and was designed to quickly get data in and out of PostgreSQL. COPY is also incredibly helpful for ingesting data into a table, especially if you have a lot of it to ingest, and will generally outperform INSERT. Let’s explore a few ways to use C...
Read MoreJonathan S. Katz
Jonathan S. Katz
On March 1, 2018, the PostgreSQL community released version 10.3 and other supported versions of PostgreSQL. The release centered around a disclosed security vulnerability designated CVE-2018-1058, which is related to how a user can accidentally or maliciously "create like-named objects in different schemas that can change the behavior of other users' queries." The PostgreSQL community released a guide around what exactly CVE-2018-1058 is and how to protect your databases. However, we thoug...
Read More