Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Jesse 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 MoreCraig Kerstiens
Craig Kerstiens
We spend a lot of time at Crunchy Data helping people dig into the performance of their Postgres. If you're setting up a new Postgres database or already running on in production there are a number of very basic steps you can take that will save your tail in the future when it comes to investigating performance. Here is your guide that'll take less than 5 minutes to get in place. Future you will thank you for doing this today. Pg_stat_statements records and parameterizes queries, how long the...
Read MoreBob Pacheco
Bob Pacheco
Postgres clusters should be updated regularly and have routine maintenance. This regular maintenance is often referred to as “Day 2 operations” and can include a wide variety of tasks like restarting services, resetting passwords, or updating versions. Performing Day 2 operations can be complex and time consuming, especially if you are supporting a large number of Postgres clusters. With the adoption of GitOps and a little help from continuous delivery tools like Argo CD you can simplify your...
Read MorePaul Ramsey
Paul Ramsey
Over and over when I look at applications for performance, the lesson I learn and re-learn is, do more things right inside the database . Create, read, update, delete! All the things you do to a table or collection of tables to work with your ever-changing data. Most CRUD examples, and most CRUD thinking, tend to focus on one table at a time. That's easy to understand. It's also unrealistic. Even the simplest application will be working with several interlinked normalized tables. Here's our wor...
Read More