Paul Ramsey
Paul Ramsey
In this series , we talk about the many different ways you can speed up PostGIS. Today let’s talk about looking across the queries with pg_stat_statements and some basic tuning. A reasonable question to ask, if you are managing a system with variable performance is: “what queries on my system are running slowly?” Fortunately, PostgreSQL includes an extension called “pg_stat_statements” that tracks query performance over time and maintains a list of high cost queries. Now you will have to leave...
Read MorePaul Ramsey
Paul Ramsey
I am kicking off a short blog series on PostGIS performance fundamentals. For this first example, we will cover fundamental indexing. We will explore performance using the Natural Earth “admin0” (countries) data (258 polygons) and their “populated places” (7342 points). A classic spatial query is the “spatial join”, finding the relationships between objects using a spatial contain. “How many populated places are there within each country?” This returns an answer, but it takes 2200 milliseco...
Read MorePaul Ramsey
Paul Ramsey
I am excited to announce PostGIS 3.6 and GEOS 3.14. The PostGIS spatial extension to PostgreSQL and the GEOS computational geometry library taken together provide much of the functionality of PostGIS, and are the open source focus of the ( Crunchy Data ) Snowflake PostGIS team. Each year we work hard to ensure a release before the autumn PostgreSQL release, to ensure that the latest and greatest PostGIS and GEOS ready to be packaged with the latest PostgreSQL . All the critical issues are...
Read MorePaul Ramsey
Paul Ramsey
What's your favourite infinite sequence of non-repeating digits? There are some people who make a case for e , but to my mind nothing beats the transcendental and curvy utility of π, the ratio of a circle's circumference to its diameter. Drawing circles is a simple thing to do in PostGIS -- take a point, and buffer it. The result is circular, and we can calculate an estimate of pi just by measuring the perimeter of the unit circle. Except, look a little more closely -- this "circle" seems to...
Read MorePaul Ramsey
Paul Ramsey
With the extension, it is possible to access gigabytes of raster data from the cloud, without ever downloading the data . How? The venerable extension (released 13 years ago ) already has the critical core support built-in! Rasters can be stored inside the database, or outside the database, on a local file system or anywhere it can be accessed by the underlying GDAL raster support library. The storage options include S3, Azure, Google, Alibaba, and any HTTP server that supports RANG...
Read MorePaul Ramsey
Paul Ramsey
The number of cool things you can do with the http extension is large, but putting those things into production raises an important problem. The amount of time an HTTP request takes, 100s of milliseconds, is 10- to 20-times longer that the amount of time a normal database query takes. This means that potentially an HTTP call could jam up a query for a long time. I recently ran an HTTP function in an update against a relatively small 1000 record table. The query took 5 minutes to run, and durin...
Read MorePaul Ramsey
Paul Ramsey
"Retrieval Augmented Generation" (RAG) is a useful technique in working with large language models (LLM) to improve accuracy when dealing with facts in a restricted domain of interest. Asking an LLM about Shakespeare: works pretty good. The model was probably fed a lot of Shakespeare in training. Asking it about holiday time off rules from the company employee manual: works pretty bad. The model may have ingested a few manuals in training, but not yours ! Is there a way around this LLM limi...
Read MorePaul Ramsey
Paul Ramsey
In late November, on the day after GIS Day, we hosted the annual PostGIS day online event. 22 speakers from around the world, in an agenda that ran from mid-afternoon in Europe to mid-afternoon on the Pacific coast. We had an amazing collection of speakers, exploring all aspects of PostGIS, from highly technical specifics, to big picture culture and history. A full playlist of PostGIS Day 2024 is available on the Crunchy Data YouTube channel . Here’s a highlight reel of the talks and themes t...
Read MorePaul Ramsey
Paul Ramsey
Large language models (LLM) provide some truly unique capacities that no other software does, but they are notoriously finicky to run, requiring large amounts of RAM and compute. That means that mere mortals are reduced to two possible paths for experimenting with LLMs: • Use a cloud-hosted service like OpenAI . You get the latest models and best servers, at the price of a few micro-pennies per token. • Use a small locally hosted small model. You get the joy of using your own hardware, and on...
Read MorePaul Ramsey
Paul Ramsey
If you missed some of the headlines and release notes, Postgres 17 added another huge JSON feature to its growing repository of strong JSON support with the JSON_TABLE feature. JSON_TABLE lets you query JSON and display and query data like it is native relational SQL. So you can easily take JSON data feeds and work with it like you would any other Postgres data in your database. A few days ago, I was awakened in the middle of the night when my house started to shake. Living in the Cascadia su...
Read More