Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Greg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 16's challenge "Probscidea Volcanium" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. This article is delayed from the actual puzzle's release. My solutions may not be the "best" solution, as the goal is to provide a quick solution. We've also loaded a tutorial for Day 16's challenge if you want to try it with a pre-loaded data set....
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
In an earlier post, I went into a lot of detail about unlogged tables . But tables are not the only thing to get the unlogged treatment - as of version 15 of Postgres, sequences can be unlogged as well! If you want to create your own, it's simply a matter of adding the keyword to your statement: The use case for unlogged sequences in Postgres is primarily to keep the sequence data for an unlogged table out of the WAL stream. Although unlogged tables provide a significant performance boost,...
Read MoreChristopher Winslett
Christopher Winslett
MongoDB got an early start as a developer friendly database. Mongo was trivial to get started with and has some good tooling for scaling. Since you didn't have to create a schema, you were off and running in no time. Don't get us wrong, we do firmly believe a well designed schema is important. If you're not managing your schema it's managing you. Because of these capabilities, MongoDB has been used by teams for: • the typical, primary application database • supporting database for large, sharded...
Read MoreCraig 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 More