Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Paul Ramsey
Paul Ramsey
One theme of the 3.2 release is new analytical functionality in the raster module, and access to cloud-based rasters via the "out-db" option for rasters. Let's explore two new functions and exercise cloud raster support at the same time. Can you believe that there is a complete raster data set of all SRTM elevation data online, in cloud optimized GeoTIFF format? It's true, there is (and much more), at OpenTopography ! The SRTM data set is a collection of 14380 files, with a pixel size o...
Read MoreMartin Davis
Martin Davis
My colleague Kat Batuigas recently wrote about using the powerful open-source QGIS desktop GIS to import data into PostGIS from an ArcGIS Feature Service. This is a great first step toward moving your geospatial stack onto the performant, open source platform provided by PostGIS. And there's no need to stop there! Crunchy Data has developed a suite of spatial web services that work natively with PostGIS to expose your data to the web, using industry-standard protocols. These include: • p...
Read MorePaul Ramsey
Paul Ramsey
One of the less visible improvements coming in PostGIS 3.2 (via the GEOS 3.10 release) is a new algorithm for repairing invalid polygons and multipolygons. Algorithms like polygon intersection, union and difference rely on guarantees that the structure of inputs follows certain rules. We call geometries that follow those rules "valid" and those that do not "invalid". The rules are things like: • Polygon rings should not cross themselves • Polygon rings should not cross other rings • Multipol...
Read MorePaul Ramsey
Paul Ramsey
Too often, web tiers are full of boilerplate that does nothing except convert a result set into JSON. A middle tier could be as simple as a function call that returns JSON . All we need is an easy way to convert result sets into JSON in the database. PostgreSQL has built-in JSON generators that can be used to create structured JSON output right in the database, upping performance and radically simplifying web tiers. Fortunately, PostgreSQL has such functions , that run right next to the data...
Read MoreJonathan S. Katz
Jonathan S. Katz
We recently announced the release of version 5.0 of PGO , the open source Postgres Operator from Crunchy Data . In this previous post , I discussed the design decisions and architecture around building the next generation of Kubernetes Native Postgres Operator . Now let's further dive into the feature set and demonstrate how easy it is to get started . With many years of active development, PGO put forward a strong feature set for managing open source Postgres databases on Kubernetes...
Read MoreJonathan S. Katz
Jonathan S. Katz
We're excited to announce the release of PGO 5.0, the open source Postgres Operator from Crunchy Data . While I'm very excited for you to try out PGO 5.0 and provide feedback, I also want to provide some background on this release. When I joined Crunchy Data back in 2018, I had heard of Kubernetes through my various open source activities, but I did not know much about it. I learned that we had been running Postgres on Kubernetes and OpenShift in production environments for years. T...
Read MoreKat Batuigas
Kat Batuigas
In the last several months, we've featured simple yet powerful tools for optimizing PostgreSQL queries. We've walked through how the pg_stat_statements extension can show which queries are taking up the most time to run system-wide. We've also looked at how to use the EXPLAIN command to uncover query plans for individual queries. You can get a lot out of those two, but you may have also wondered, "What about logs? Surely I can use Postgres' logs to help me find and track slow queries too?" T...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
Data checksums are a great feature in PostgreSQL. They are used to detect any corruption of the data that Postgres stores on disk. Every system we develop at Crunchy Data has this feature enabled by default. It's not only Postgres itself that can make use of these checksums. Some third party tools such as the awesome pgBackRest program can also use them for enhanced data integrity. Sadly, enabling data checksums is still not the default behavior when creating a new Postgres cluster. When you...
Read MoreGreg Smith
Greg Smith
By default Linux uses a controversial (for databases) memory extension feature called overcommit . How that interacts with PostgreSQL is covered in the Managing Kernel Resources section of the PG manual. Overcommit allows clients to pre-allocate virtual memory beyond even server RAM. They are only nailed down to a real allocation, committed to use its terminology, when it's actually used. This lets applications have a flatter memory model without having to grapple with virtual memory coding...
Read MoreJonathan S. Katz
Jonathan S. Katz
I can talk about the benefits of PostgreSQL for application development and operations all day. But there two enduring topics that are close to my heart: SCRAM (you need to update your passwords to use SCRAM ) and range types . I've been stoked about range types since they were released in PostgreSQL 9.2 . Before I joined Crunchy Data , I deployed them heavily in production to help manage a mature scheduling and space booking application. Performance wise, range types provided a fa...
Read More