Craig Kerstiens
Craig Kerstiens
Is your database ready for production? You've been building your application for months, you've tested with beta users, you've gotten feedback and iterated. You've gone through your launch checklist, email beta users, publish the blog post, post to hacker news and hope the comments are friendly. But is your database ready for whatever may come on launch day or even 2 months in? Here's a handy checklist to make sure you're not caught flat footed. • Backups❓ • High availability❓ • Logs properly co...
Read MorePaul Ramsey
Paul Ramsey
It's the easiest thing in the world to put a timestamp on a column and track when events like new records or recent changes happen, but what about reporting? Binning data for large data sets like time series is a great way to let you group data sets by obvious groups and then use SQL to pull out a query that easily works in a graph. Here's some PostgreSQL secrets that you can use to build up complete reports of time-based data. Earthquakes are a natural source of time-stamped data, and Crunchy B...
Read MorePhilip Hurst
Philip Hurst
Backups in the database world are essential. They are the safety net protecting you from even the smallest bit of data loss. There’s a variety of ways to back up your data and this post aims to explain the basic tools involved in backups and what options you have, from just getting started to more sophisticated production systems. and are tools designed to generate a file and then allow a database to be restored. These are classified as logical backups and they can be much smaller in size than...
Read MoreJesse Soyland
Jesse Soyland
Integer overflow occurs when a computer program tries to store an integer but the value being stored exceeds the maximum value that can be represented by the data type being used to store it. We have helped a few Crunchy Data clients navigate this recently and wanted to write up some notes. In Postgres, there are three integer types: • - A 2-byte integer, -32768 to 32767 • - A 4-byte integer, -2147483648 to 2147483647 • - An 8-byte integer, -9223372036854775808 to +9223372036854775807 - A 2-by...
Read MoreJacob Coblentz
Jacob Coblentz
Geocoding is the process of taking addresses or location information and getting the coordinates for that location. Anytime you route a new location or look up a zip code, the back end is geocoding the location and then using the geocode inside other PostGIS functions to give you the routes, locations, and other data you asked for. PostGIS comes equipped with an easy way to use the US Census data with the Tiger geocoder . Using the Tiger geocoder requires downloading large amounts of census d...
Read MorePaul Ramsey
Paul Ramsey
In geospatial terminology, a "raster" is a cover of an area divided into a uniform gridding, with one or more values assigned to each grid cell. A "raster" in which the values are associated with red, green and blue bands might be a visual image. The rasters that come off the Landsat 7 earth observation satellite have eight bands: red, green, blue, near infrared, shortwave infrared, thermal, mid-infrared and panchromatic. Working with raster data via SQL is a little counter-intuitive: rasters...
Read MoreChristopher Winslett
Christopher Winslett
Note: We have additional articles in this Postgres AI series . In the past month at Crunchy Data , we have talked to a steady stream of customers & community folks wanting to know how to augment their data platforms for AI. Fortunately, Postgres is equipped, nearly out of the box, and ready for the task of storing and querying this data. Through the magic of OpenAI’s API we can easily send data for classification and return the values. Alongside this post, I created a sample code-base and dat...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
While supporting customers at Crunchy Data , I sometimes see users add unlogged tables. In this post, I'm going to review some of the specifics about this. Unlogged tables in Postgresql are a way of preventing specific tables from generating WAL (Write Ahead Log) information by adding the keyword to a command: You can also change existing tables to unlogged, and switch them back again: While there are strong advantages to using unlogged tables, you must use them carefully as their disadvant...
Read MoreBrian Pace
Brian Pace
The Postgres Write Ahead Log ( WAL ) is a functional component to the database. WAL makes a lot of key functionality possible, like Point-in-Time-Recovery backups , recovering from an event , streaming replication , and more. From time to time, those deep inside the database will need to work directly with WAL files to diagnose or recover . Recently in working with one of Crunchy Data's customers, I came across a situation where understanding the names and sequence numbers was important....
Read MoreMartin Davis
Martin Davis
In a previous post we announced the CQL filtering capability in . It provides powerful functionality for attribute and spatial querying of data in PostgreSQL and PostGIS. Another important datatype which is often present in datasets is temporal . Temporal datasets contain attributes which are dates or timestamps. The CQL standard defines some special-purpose syntax to support temporal filtering. This allows to take advantage of the extensive capabilities of PostgreSQL for specifying qu...
Read More