Kat Batuigas
Kat Batuigas
The idea of writing a database query that can then go out to an external source may not occur to someone who is not a DBA early on. That is: instead of figuring out how to grab then load multiple data sets into the same store, or configuring your application backend to connect to a bunch of disparate sources, why not use query JOINs like you usually would across tables within one database? In case you're not familiar, the dblink module in PostgreSQL, along with the concept of database links o...
Read MoreBrian Pace
Brian Pace
There is no cure-all when it comes to security. Making things open to the public internet can leave you vulnerable to various security risks, including zero-day vulnerabilities or various password attacks . You can put your services in a VPN or a private network, but you can still be susceptible to an internal compromise. While security for most people isn't a "fun" thing to spend time on, it's a necessity. You shouldn't have to trade off between security and ease-of-use. This was one big rea...
Read MoreKat Batuigas
Kat Batuigas
Early in on my SQL journey, I thought that searching for a piece of text in the database mostly involved querying like this: Then I would throw in some wildcard operators or regular expressions if I wanted to get more specific. Later on, I worked with a client who wanted search functionality in an app, so and regex weren't going to cut it. What I had known all along was just pattern matching . It works perfectly fine for certain purposes, but what happens when it's not just a matter of checki...
Read MorePaul 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 More