Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Paul Ramsey
Paul Ramsey
A community member on the postgis-users mailing list had a question recently: I have a table of elevation points, and I would like to figure out an elevation profile for a flightline running through those points. How? This question is a nice showcase of some of my favorite spatial tools with indexing, point to point distance queries on a sphere, and nearest neighbor queries. I thought it would make a great post. The original question author was nice enough to share his elevation data, so I c...
Read MoreElizabeth Christensen
Elizabeth Christensen
If you’ve read Crunchy blogs recently you probably noticed by now that we’re all big fans of indexing. Indexing is key to optimizing your database workloads and reducing query times. Postgres now supports quite a few types of indexes and knowing the basics is a key part of working with Postgres. The role of database indexes is similar to the index section at the back of a book. A database index stores information on where a data row is located in a table so the database doesn't have to scan...
Read MorePaul Ramsey
Paul Ramsey
Last month, just under the wire for a 2021 release, the 3.2 version of PostGIS hit the streets! This new PostGIS also supports the latest 3.10 release of GEOS, which underpins a few of the new features. The extension uses the GDAL raster library for things like vectorizing rasters and rasterizing vectors (yes!). This release exposed a few more cool GDAL algorithms. • The new ST_InterpolateRaster function allows collections of measurement points to be interpolated into a continuous raster s...
Read MoreStephen Andert
Stephen Andert
"Who is in charge of this database?" Everyone on the DBA team shook their head and someone asked, "Is it Oracle or SQL server?" "I think it is called My SQL," the development manager said. During my 20 years as a database administrator, that is often how I ended up learning new RDBMS systems. As a result, I know first-hand how challenging it can be to learn another system when you get thrown into the deep end. New databases are added to a production environment in different ways. A new software...
Read MoreJoe Conway
Joe Conway
Co-authored by Brian Pace I was excited to hear that Kubernetes 1.22 was recently released with better support for cgroup-v2 and has support for Linux swap . These changes potentially resolve two of my chief complaints about running Postgres under Kubernetes. Obviously it will take some time before we see uptake in the wild on these features, but I wanted to become familiar with them. For what it's worth, I also want to eventually play with the new alpha seccomp support in Kubernetes v1....
Read MoreBrian Pace
Brian Pace
UPDATE TO THIS CONTENT: Since releasing this article, newer versions of Crunchy Postgres for Kubernetes have additional features for streaming replication across clusters. See our post that accompanied the release: Multi-Cloud Strategies with Crunchy Postgres for Kubernetes . Read the disclaimer above, as this content is now out of date. A common need when running PostgreSQL in Kubernetes is to establish a standby database in a different Kubernetes cluster. In a typical configuration there...
Read MoreCraig Kerstiens
Craig Kerstiens
It's been a busy year building Crunchy Bridge and we've shipped a lot of new awesome things. Instead of doing a wrap-up of all the growth and exciting features, instead I wanted to take the time to try to teach a few more things to those that follow us. While onboarding customer after customer this year I've noted a few key things everyone should put in place right away - to either improve the health of your database or to save yourself from a bad day. Long running (usually unintentionally so)...
Read MorePaul Ramsey
Paul Ramsey
While supporting Crunchy Spatial and Crunchy Bridge clients, I’ve been thinking about how I usually clean messy data. I wanted to talk about regular expressions ( regex ) and Postgres. Regular expressions get a bad rap. They're impossible to read, they're inconsistently implemented in different platforms, they can be slow to execute. All of these things may be true, and yet: if you don't know regular expressions yet, you are missing a key skill for data manipulation that you will use throu...
Read MoreBrian Pace
Brian Pace
As a Solutions Architect at Crunchy Data, I work directly with customers testing and deploying Crunchy Postgres for Kubernetes . I often see our customers fully removing a PGO cluster during testing or migrations and still needing to keep their storage and backups intact. In this post, I will dig into the steps to removing your PGO cluster while keeping your storage and backups. With the move to a declarative approach in 5.0 PGO, storage and backup retention can be accomplished by following a...
Read MorePaul Ramsey
Paul Ramsey
One of the curious aspects of spatial indexes is that the nodes of the tree can overlap, because the objects being indexed themselves also overlap. That means that if you're searching an area in which two nodes overlap, you'll have to scan the contents of both nodes. For a trivial example above, that's not a big deal, but if an index has a lot of overlap, the extra work can add up to a measurable query time difference. The PostGIS spatial index is based on a R-tree structure, which naturally t...
Read More