Skip to content
PodSubs
ShowsPeopleGuestsJobsNewsEvents
More
BlogPodCred
Menu
ShowsPeopleGuestsJobsNewsEventsBlogPodCredNetworksSign in
Join nowSign in
  • Shows
  • People
  • Jobs
  • News
  • More
    • Guests
    • Events
    • Blog
    • PodCred
    • Networks
    Join nowSign in

Postgres FM

Postgres FM artwork

PostGIS

Mar 13, 202653 minAudio
ShareXLinkedInFacebookWhatsAppEmail

Nik and Michael are joined by Regina Obe and Paul Ramsey to discuss PostGIS. Here are some links to things they mentioned: Regina Obe https://postgres.fm/people/regina-obe Paul Ramsey https://postgres.fm/people/paul-ramsey PostGIS https://postgis.net MobilityDB https://github.com/MobilityDB/MobilityDB pgRouting https://github.com/pgRouting/pgrouting Google BigQuery GIS public alpha blog post https://cloud.google.com/blog/products/data-analytics/whats-happening-bigquery-integrated-machine-learning-maps-and-more PostGIS Day 2025 talk recordings https://www.youtube.com/watch?v=wuNO_cW2g-0&list=PLavJpcg8cl1EkQWoCbczsOjFTe-SHg_8m pg_lake https://github.com/Snowflake-Labs/pg_lake GeoParquet https://geoparquet.org ST_DWithin https://postgis.net/docs/ST_DWithin.html Postgres JSONB Columns and TOAST: A Performance Guide https://www.snowflake.com/en/engineering-blog/postgres-jsonb-columns-and-toast FOSS4G https://foss4g.org OpenStreetMap https://www.openstreetmap.org PgDay Boston https://2026.pgdayboston.org SKILL.md file https://github.com/postgis/postgis/blob/68dde711039986b47eb62feda45bb24b13b0ea37/doc/SKILL.md Production query plans without production data (blog post by Radim Marek) https://boringsql.com/posts/portable-stats PostgreSQL: Up and Running, 4th Edition (by Regina Obe, Leo Hsu) https://www.oreilly.com/library/view/postgresql-up-and/9798341660885 ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork

Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.

Transcript

From the show. Tap a time to play from there.

Michael: Hello and welcome to Postgres.FM, a weekly show about all things PostgreSQL. I am Michael, founder of pgMustard and I'm joined as usual by Nik, founder of PostgresAI. Hey Nik. Nikolay: Hi Michael. Michael: And we have not 1, but 2 heavy hitters in from the PostGIS world. Both also recognize Postgres core contributors. First we have Regina Obe, President of Paragon Corporation, a consulting firm, member of the PostGIS core development team and steering committee, and co-author of a whole host of books

on GIS and SQL, including the book PostGIS in Action. Welcome, Regina. Regina: Thanks, thanks for having me. Michael: It's our pleasure. And also we have Paul Ramsey, who is Staff Engineer at Snowflake via the Crunchy Data acquisition, the co-founder of PostGIS, member of the core development team and the chair of its steering committee. A pleasure to have you too, Paul. Paul: Hi, Michael and Nik. Nice to be here. Michael: It's wonderful. Right. So I was hoping actually that maybe one of you could give us a

kind of brief history of the project and maybe the other one could give us an update on where it's at today and what it looks like. Paul: Well I'll take the history side of this because yeah, because PostGIS comes out of my history as a geospatial consultant and it's a long time ago now. PostGIS first released 0.1 was at the end of May in 2001. So we're creeping up on 25 years of PostGIS. Pretty crazy. PostGIS was born because, out of a consulting company here in Victoria, British Columbia, and it was born because we ran out

of work. The fiscal year for the government, which is our main source of money, ends on the March 31st. And the first release of PostGIS is 2 months after that. We had been doing an analytical work for the government using standard GIS tools and a database to store interim results in the database we're using was Postgres. And having had that experience of using a database and using SQL, it's becoming familiar with this sort of powerful tool to slice through big sets of chunks of data in different ways.

And also being spatial people looked at this database tool and said, there's gotta be a way, like, there's gotta be a way to bring together the spatial questions we're asking and the non-spatial questions we're asking into one place so we can ask like more complicated questions and have it all fulfilled inside the database. And there was at that 0.1 relatively fresh international standard, the simple features for SQL standard from the Open Geospatial Consortium. There was one extant proprietary product that did that.

Oracle 8i at that point had been released and it had a pretty, 80, 20 complete implementation of Spatial SQL. And, and we did both. We both looked at Oracle Spatial and thought, can we use this? And at the time, if you wanted to use it, you had to have Oracle Enterprise. It really, the implication was about a quarter million dollar licensing bill to really use it in anger. And that obviously it scared us off as a small consulting company. It also successfully scared off anyone in the government that we like, we may all get some of the government who wants to do

this stuff and we'll piggyback on their licensing and learn it and do an implementation for them. And we'll be like spatial database experts. Now it's too expensive for anyone in the government. But we had this specification And we had this experience with Postgres and Postgres itself was built for type extension. So we also had the really like clear on-ramp to add a new type, to bind it to access methods, to add functions that worked on this type in a way that really didn't exist in what was at the time the sort of premier open source database MySQL.

It took quite a few years beyond 2001 before MySQL added Spatial. And when they did, We went and looked at their implementation, looked at the patch, and it was big. It was a big patch that crossed the whole code base. It really took someone who understood MySQL, soup to nuts to do it. They had to do the index implementation, like everything. They had to do a hell of a lot of work to do it. Whereas we got this nice, tight little code base sitting as an extension completely divorced from the core code. The core code provided access method prebuilt. We were good to go.

So it was doable to have a working extension that we could demonstrate to ourselves and to customers within 2 months and then release. So yeah, we were clever people, but Postgres is a hell of a clever product and the PostGIS wouldn't exist if Postgres hadn't been so clever to start with. Nikolay: And it was just right at that time. Paul: At that point it was just, it still is just actually the access method we use for spatial indexing. Yeah. Nikolay: I'm smiling the whole time you're talking because it connects 20 years ago, 2005, 2006, 2007. I remember first of all, this is when I left Oracle 8i.

Exactly. Like last time I touched Oracle was exactly 8i. And then when I was building my second startup social network, we put a lot of people and objects like restaurants and so on, on map 2007, it was too early. And I remember I hired 2 guys, Oleg Bartunov and Teodor Sigaev, to help us. It was so bad because it was amazing to have GiST and reading

all the articles and basics and like 7 functions, abstraction, all great. But when we went to practical questions and we had 10 million people to be placed on the map and a lot of objects and then you try to solve simple problem, show me nearest 10 objects on the map which have good reviews. And it's terrible because there was no support for order by and KNN. I didn't know KNN.

And since we moved so fast, I eventually decided to pivot my startup away from maps and so on. But Oleg and Teodor, a couple of years later, brought KNN to GiST. It was 9.1. And then Alexander Korotkov, also Nikita Glukhov helped to brought the same like to SP-GiST. And this connects us a lot. But I just, I showed them this problem and that's it for me. It was great. Paul: Yeah, back in the early days, starting off incubating an

open source project, within a couple of years, we had quite a few users and some institutional users. And at the time the GiST infrastructure was, did not include recovery. It wasn't a completely ACID implementation. And, at that point I had like a stable of people who had businesses depending on the PostGIS, and I put out a call for development money and got scraped together from all the different companies who were using or depended on Postgres about 10 or $15,000 and

then ship that money to Oleg and Teodor who did the work necessary to make GiST recoverable. And it was a weird thing to sell to the community. It's we're going to get this work done. You're using it now as a result of the release cycle of Postgres and time takes to do the work and all that stuff. You'll be able to use it and show it to your clients in 12 to 18 months. But you're going to give me the money right up front. So it was interesting introduction, both to the, to the Postgres development cycle, and also to like trying to drum up crowdsourced

funds for open source development. And it really taught me that crowdsourcing funds is really hard because it's people are willing to pay to get the bug, which is bothering them right now, fixed right away. Much more difficult to get them to pay for something, which will maybe make them a little bit happier in the far future. It's a harder, it's a harder sale. Nikolay: Especially for startups. So they move too fast to Paul: understand that Nikolay: the thing will come only in a couple of years. Regina: Yeah, they might not be around that long.

Nikolay: But I'm glad K&N was brought into Postgres and PostGIS as well. Yeah, great. Mason. Michael: Is that a good excuse for us to get Regina to give us a little bit of like a catching up from, it's been 25 years, right? You've done a lot of work since those early days. What's changed? What does it look like now and how is that different to the first versions? Regina: I mean, we definitely have a bigger audience and we now have people seem to be running things in the cloud more than

they're running things on premises or maybe not. I think it's yeah slowly getting to the point where most people we see are running PostGIS in Google Cloud or Amazon Cloud or Microsoft Azure. I think that's the biggest thing that's changed since we started. Michael: I was thinking as well, it looked like it started as a single extension and it's now kind of a collection, it's like

a family of extensions that serve quite a few more use cases and lots Regina: of functions. So when we started off, we just had the vector support and then we added raster support. And originally we thought, Oh, we'll just throw raster in the PostGIS extension. And then it got very fat and people started complaining. Why do I have to carry these thousands of functions that I don't even use raster? So then we broke it out into 2 extensions, PostGIS and PostGIS

raster. And we also have PostGIS topology And then PostGIS spun off other extensions. So you have the H3 extension, which is now it's, we brought it into our organization, but it's still a separate extension. It's not even part of the PostGIS extension because we forked it from the original owner into our system. So that was very recent. And then there's another extension called MobilityDB, which is a separate extension, separate group of people, but they build

on top of PostGIS. And they deal with things like, you know, vehicle movement, the speed at which things move and all that and flying birds, trains. I think those are the key ones. Yeah. And then there's also a pgRouting, which I'm a member of too, which deals with network routing, like driving, biking, walking, getting directions on that. So yes, it's spun up a lot of other extensions. Paul: Yeah. So that's the technical side. Deployment is a big deal for sure.

When we started, it was, you want to use PostGIS? Here's the source code. I'm tired in the contrib directory of an existing Postgres installation. And then you build and then you install and now you have PostGIS. And around the time Regina started joining us, she helped with Windows builds. So Windows people at least could have a pre-build binary. Regina: And I still do Windows builds. Paul: At some point, PDDG included us in their builds, and that was a huge change. So now it meant that you didn't have to build it yourself. You could just find the right packages and install those. That opened up a whole pile of new users who previously wouldn't

have tried it. But yeah, as Regina said, the real tipping point lately has been being part of the default installs on all the clouds. So there's no longer a software install step from the point of view of the end user. It's just type create extension PostGIS. And that ubiquity really goes beyond the ubiquity for users. I feel like we've achieved a certain level of ubiquity in the industry. Like, first of all, like just geospatial as database functionality is table stakes now. It's pretty rare to find a SQL implemented database that doesn't

have Geospatial. Those who don't are all tacking it on at a great rate. Regina: Ours is still the most popular, I think. I think it's surprising that Postgres, even though it started out as a meager database, PostGIS itself is probably more popular than Oracle Spatial and SQL Server Spatial and MySQL. I don't even hear other people doing serious work on those. Paul: Yeah, And that's probably not fair because I bet you there's a whole bunch of institutional users of those databases who they never leave the ranch.

So that's all I understand is Oracle spatial or SQL server spatial because that's just the way their institutions are shaped. But there's no doubt that in the wider world, PostGIS is the thing. The thing that made me really understand that PostGIS was the lingua franca was seeing the Google BigQuery announcement blog post where they said one of the features of Google BigQuery was that it was PostGIS compatible. It was like, Oh, wow. Okay. So that's nice to see. Michael: That's them saying you're the standard, right?

That's pretty cool. Paul: Yeah. And it's a standard that I could have said, we support the simple features for SQL standard, but they decided that PostGIS compatible was more understandable to the larger marketplace. It's like, okay, so we have the mindshare. Nikolay: Yeah, that's great. Actually, my, my daughter, she is a fourth year bachelor degree student at UCSD and they have projects related to wildfires in California and GIS as well. And, but I was super annoyed to hear it's not PostGIS, but it's actually Google BigQuery, so that's good to hear.

Yeah. It's at least compatible. Great. Paul: Oh, reusable. Reusable knowledge. Nikolay: Cool. What's the latest things you can share? What's the latest development? This is one thing I'm also curious about. I know, for example, PostGIS works quite well with TimescaleDB. It should work with pgvector. I never tried. I'm curious. And with sharding systems as well. There are super extensions. In the case of Postgres, it's not super extension, it's a super set of extensions.

Not super set, a collection of extensions. But sometimes it's hard to make it work together, these big building blocks what's there especially PostgreSQL vector because it's also important building block is there's right. What's the recent development. Regina: No if there's any recent development I think they just work together and it was because of the way Postgres is set up right. You can all You can have 3 or 4 different extensions in the same query, and it's just because they standardize on all data sets,

get the same feature set, get the same core feature set. And so you don't have that annoying problem that MySQL has with their different storage engines and their different query syntax for things. So things just work seamlessly well. So I don't think we did anything special to try to make ourselves work with pgvector. Paul: Yeah, pgvector is more similar to us than, say, one of, like you said, the super extensions like Timescale or Citus, like these sort of overarching big swings of the architecture of the

system. pgvector, like, they provide a type, they provide access methods, they provide some functions that make sense for that type. They're very PostGIS-esque in that respect. But from the point of view of what they're for, they're kind of orthogonal. I've only seen one example recently of PostGIS and pgvector being used together. And they're not being used together because they're getting any great leverage out of PostGIS. And they're getting leverage out of the fact that PostGIS itself has a huge amount of third-party interconnectivity. So you use something like really high dimensional search and pgvector to get a bunch of results.

And if those results happen to include spatial information, then you throw that spatial information on a map because everything connects to PostGIS, all the mapping software connects to PostGIS, and that's how it works. We really saw a cool example of this in our last PostGIS Day. We have a PostGIS Day mid-November every year, an online conference, and Shawn Burke from GeoBase showed us geospatial AI using pgvector as the, as a search engine, but then getting the results back through PostGIS to his mapping tools. So it could show us the results. Neat example of what the geospatial are calling people are calling

foundational models, not large language models, but a completely different look at how to use the AI kind of technology for doing geospatial work. Nikolay: And if we talk back to TimescaleDB, I saw great examples of Postgres and TimescaleDB working together, because if you collect a lot of geographic signals from drivers or from anything like on the map, you want to partition all this, right? Because it's a lot of data, right? One way or another partitioning should work, because it's also time-series, not only geographical data, right?

So this is natural pair of extensions working together. Okay. What about lakes and since like your company got acquired last year? Yeah. Yeah. What, what, recent development there? Paul: And this ties actually back to your other question, which is at the core is an interoperability question. And the nature of the type model in Postgres is such that when you add a new type using the Postgres type model, a lot of the time things just work because the overarching machinery of a

big, a big piece of machinery, say Citus doesn't think necessarily about particular types. It thinks just about type mappings and the type mappings are entered in when you add the, add the new type to the, like the pg_type table and, and your pg_attributes just refer back to the type table. So everything is runtime configured and that's the underlying expectation of these extensions. So stuff just works. It's only when you have to push out to some external format that has its own understanding of the world. You have to start thinking about these mappings again. pg_lake, which was open sourced a few months ago, is a set of

tooling to allow you to push data from Postgres database out to parquet or iceberg data on object stores. Michael: I had a question around that. Only in researching for this, I came across GeoParquet for the first time. I didn't know that was a thing. Paul: Yeah. There's a long history of the geospatial community taking extant open source standards, as they say, which are like good and clearly going somewhere insane. Regina: GeoJSON is another 1. Paul: Exactly. Back when JSON was turning into the lingua franca wire transfer

protocol, The geospatial community said, but we need a way to encode geometry too. So they invented one and eventually got it ISO, not ISO, IETF standardized. Same thing with Parquet. So you have Parquet, which in its initial versionings understands like the basic types of relational databases and doesn't understand anything about geo. So like people, geo people want to push data through this, through this format out to their own clients and say, we've got this byte type and we've got this metadata slot. So we'll slot some information about columns that are holding

geometry and we'll stick the raw geometry in there into the byte type. And that will be just enough affordance for geo-aware tools just to look in the metadata and say, Oh wait, there's geo here. And we can pull it out. Fortunately that for the Parquet and Iceberg formats has been a very short interregnum. Sometimes these things last for like years, 5 years. And we're still playing with these hacks. I think GeoParquet and GeoIceberg lasted for about a year and a half before the mainline projects came along and said, you know what we need, we need a geometry type and a geography type. We should just formalize that.

So yeah, so that was formalized, I think 9 to 12 months ago, both Parquet and Iceberg got around to saying, yeah, we have an actual type. And I meant that the metadata could like live in proper slots. So where you'd expect it to be, which in turn made engine developers be able to do things like much faster scans and pruning of these files. So it's been good all around and you can forget about GeoParquet and forget about GeoIceberg because those there's a pieces of history now there, the geo stuff is now actually embedded in the standard proper. That's Michael: great news.

Paul: Yeah. Yes. So, yeah. So pg_lake is a way of pushing data out of Postgres into the public internet in places where these Parquet and Iceberg engines can scan them. And the promise of that is that if you pump your data out in hopefully an ordering, which is good for the bulk queries you expect to apply to the data, you can then hit those object store data lakes, if you will, God help us, with an engine which understands

those formats and can slam through them in massive parallel. And, you know, one of those engines is DuckDB, a single node engine, which is very good at that. 1 of those engines is Snowflake, a multi-node, highly parallel engine, which is also really good at that. Another one is Databricks. All the same gig. It's like this separation of compute and storage. The compute comes to the object store, rips this file off. It is ridiculously inefficient. If you're a Postgres developer, you think, this is really inefficient, you should have an index, so you can find exactly the thing you're

looking for. And these compute engines are, no, I will look at every record. I will lightly prune the file as I zip through it. But they're willing to scan a terabyte of data to get a few hundred thousand rows of results, which is, which is crazy, unless you happen to have the compute and storage and cloud infrastructure to afford to do that, which these days we do. It's one of the problems of being old. You, your understanding of what is efficient and correct is receding in the distance. Nikolay: But I still have some doubts and questions how stable performance for such queries is.

Like it's maybe not fully resolved problem. Paul: I agree. I find it really weird that you have to reform or resort your data to match your use case. And it's nice. It's great when things work out great. So like you're sloughing your data off the, off the transactional store, off to the object store in, usually it's like time series order, right? Time of most recent change. And it so happens that your queries are either grouping on time or filtering on time, and then the underlying format metadata

allows the engines to really throw away 90% of the data before it looks at it. That's great. I'm used to the power of SQL inside a real OLTP engine where you can address any combination of tables and joins, and you're not going to be hit too hard with efficiency as long as your indexes are in place. Nikolay: And it sounds reasonable for columnstore and for purely analytical queries, but when you, for example, go back to this KNN, K-Nearest Neighbors problem, we just need like 10 Paul: rows, but we Nikolay: have millions, right, or billions.

Regina: Yeah. How does that work? Does it even work? Can you even do a KNN in those systems? Paul: Ah, you end up making some kind of compromise with reality. If you sort your data, like on a key, like a Hilbert code or something that has like spatial co-location to it, then you can do that kind of scan a little better because you know where to hop in the Hilbert code. Duck for its internal implementation, I think it's just sucked

it up and put a lightweight index next to the table. So they're not actually doing the full column store scan for those particular kinds of queries. Spatial is as database people who have put a geospatial type into a standard relational database, we like to say spatial isn't special because you just type SQL and you get your answer back, but at an implementation level, there's no getting around it. Spatial is special in a whole bunch of ways, because just being higher dimensional, it's the same thing people run into with pgvector. Once you get beyond that one dimensional, 1 dimension of sort order,

a lot of your preconceptions about the most efficient way to deal with the data fall away and you have to rethink problems. Michael: one difference seems to be people aren't as forgiving of accuracy issues when it comes to spatial data. You know, you don't have approximate nearest neighbor. Paul: Neil That is 100% true. Yeah. Things on maps, People are very unforgiving with maps. It's like one mistake, you're wrong. Whereas you roll up a BI dashboard and give some people a pie chart, whatever. It's actually one of my great shaking fist at cloud things about

the big column stores is that the people insist on summing up every record of 5 billion records and make a pie chart. You could have sampled and got the exact same pie chart. Michael: I feel like we've dived into the most complex type of performance issues, but I think a lot of people, like I've come across some performance issues when it comes to PostGIS work, a few customers that have the odd specific case. And I think even it's like the basics that they have questions around like geometry versus geography or GiST versus SP-GiST. And any advice either of you have for kind of people that are

new to this stuff on the basics of on the simpler queries, how should they be thinking about these things? Nikolay: IMB The absolute basic is how R-tree is implemented and what R-tree is compared to B-tree. This is like the gem of Postgres actually, right? Maybe let's talk a little bit about this, like some basics in the middle of it. Regina: I think that bores most users, the discussion. They just want to say that I can write this and I get this. And they don't care about the underlying implementation.

Paul: You think Nikolay: so? There are different users, I don't know. Maybe. But okay, let's mention that Postgres has this GiST, a generalized search tree, and R-tree is implemented using it, not separately. Correct. And yeah, there is a great work by Hellerstein from Berkeley and from the late 90s. And it's like basically generalization of B-tree idea to multidimensional space, two-dimensional, three-dimensional, n-dimensional.

Paul: Well, n-dimensional is defined more or less by the API, right? Not so much by the space and whatever space you're in, as long as you can meet the conditions of the API. Nikolay: 7 functions, right? Paul: Yeah. Given a key, can you say whether things are consistent or not with that key, that's one piece of the API. Another is given a collection of keys, can you split that collection? And that depending on your dimensional space, those splits will be calculated differently, but it's just like, given a set, can you do a split for me? Yes.

Okay. Can you do key consistency? I'm trying to think what the other, what the other API pieces, I feel like there's a third, but regardless you can, with the rules of the GiST API, you can implement an R-tree, like The kinds of questions that an R-tree answers, the kinds of guarantees an R-tree gives are ones that fit inside the generalized API of GiST. So it means that you don't need a separate access method for R-tree. You don't actually need a separate access method for B-tree either. In fact, there's a B-tree GiST implementations in contrib that

you can look at. Because it's more generic, There's less opportunities for performance optimizations, but the beauty of being able to, for almost any type, cause the just conditions are really, really loose and generic, almost any type you can get an access method without having to think about all the problems of pages and consistency and so on. That's one of the things that has made, say, a tool like pgvector a complex thing to approach and that some of the only someone with like serious computer science chops would want to attack is that they had to write their own access method right down

to disk. They did not get to just layer their access method on top of an existing implementation like GiST. They had to write it all the way down to all the way down to the disk and page level, which is a lot harder to do if you're going to retain guarantees of consistency. Nikolay: Right. So you mentioned, I guess you mentioned B-tree GiST, it's called, or GiST B-tree, I always Paul: confuse it. Yeah, B2 GiST. Nikolay: Yeah, and it lost comparison to native implementation of B-tree, while R-tree didn't lose, like it won. And just... Paul: Yeah. And I'm not sure if that's just, probably that's more a matter

of people not wanting to spend the time to work on the R-tree implementation. Like it was considered, it was considered not a good implementation at the time that we started. It still existed when we started with PostGIS, but it was more tightly bound to the spatial types, the native spatial types in Postgres. And so we didn't use it. No one else used it. Eventually it was ripped out. It had, I think all the way up to the time it got taken out, an object size limitation.

So it couldn't handle objects larger than a page size. That was, I think, the death knell, really. Because practical work with spatial data inevitably involves dealing with objects that exceed the page size. Nikolay: So I want, what I wanted to just like some counter argument why it matters to understand things like slightly more than just let's use it. And that's it. Because for example, in this particular case, B-tree is like less, less than greater than equals. R-tree is like is contained, contains, right?

Overlaps. You can, if you use this Btree GiST extension, you can combine and have multi-column index of very different data types, right? And Regina: I use that a lot too. Nikolay: Yeah. And this is great performance trick. And if you know like why it's useful, it can save you because it's just a single index scan. That's it. So you use it a lot. Regina: Well, if you have things where there's a lot of Attribute things that you have to filter against, but you also need to do a spatial at the same time, especially with the way the sensitivity

that Postgres has, it sometimes picks the wrong plan, whereas when you have it together, it can't screw up. So there's some cases where I've had where if I had just a GiST index and a B-tree separate, the planner would do something really stupid and try to use the spatial and not use it the B-tree at all and so the performance you'd go from minutes to milliseconds in those cases. Nikolay: Yeah yeah so I had some experience not once yeah.

Michael: Same And I also thought like Postgres would be able to do a bitmap scan. I thought it would be able to use the GiST index and the B-tree separately. I know it wouldn't be as efficient still, but it seemed to be refusing to do so for me. So I also wonder if there's something missing on the Postgres side to consider them for bitmap scans as well, multiple indexes with an and condition. I thought it would consider it but I really struggled to get it to. Nikolay: I also can complain You take pgvector and you cannot combine it with GiST basically.

Paul: Yeah, that's super hard. Given just the use case for pgvector, like the find me the nearest thing is the core use case, and that is like really does not fit nicely into the executor. Nikolay: one case works really fast and the other doesn't. Michael: What about for beginners? Regina, you've written some books on this. How do you describe performance stuff for beginners, like in terms of practical tips, or what do you tend to see mistakes people make? Regina: I think the classic one is people try to do distance by doing using ST_Distance.

So they say ST_Distance this less than 50 instead of using ST_DWithin. So like ST_Distance can't use an index. So you know their queries might take minutes and then when they switch it to ST_DWithin and finishes in milliseconds, that kind of thing because it would have to scan the whole all the records if you do ST_Distance. That's a classical mistake that people make. Nikolay: I'm curious, does AI make this mistake or not?

Regina: And what's annoying is like in SQL Server, it's different. You can actually use ST_Distance and it uses an index. Paul: So what are they doing in SQL Server? They must be rewriting the query. Regina: Yeah, they must be rewriting the query. So I was surprised that, oh yeah, you can do distance less than 50 and it will do the right thing. It will do it pretty much what RS_ST_DWithin does. Paul: That's interesting. That, yeah, well query rewriting is kind of deep. But the fact that they do that is interesting just in and of

itself. It speaks to them, well either like presupposing a problem their users are going to have or something their users really did have constantly so it's worth it for them to go to the extra work of doing a query rewrite for that case. Regina: I noticed that Postgres does do query rewrites too, right? Paul: It does. There's an affordance there. We could muck with the plan if we wanted to. Regina: Yeah, if you do in, they change it to any, right? Paul: Yeah. Yeah. Regina: So in theory, we could do a rewrite somewhere. It's just, where would that go?

Paul: Yeah. I mean, that's like adding a certain amount of brittleness to the system where you don't know that the users actually need it. Anyways, your example presupposes that they have a spatial index in the first place, which is what I would have raised as like the number 1, the number one new user mistake. Regina: Oh, that's true. Yeah. That's the other thing. Paul: It's quite an easy mistake to make because, by and large, yeah, by and large, people don't think about index as indexes as having a type, like if they come from a SQL server world or whatever, like the idea that this, they need this extra keyword,

the using just keyword to get a spatial index, not going to be super obvious as they're just coming from some other database. And if you type create index blah on geometry column, it'll do something like it won't error out because we've had to do lightweight B-tree bindings in order to get aspects of sortability from our type. So it's not like it'll just say, oh, I don't have a B-2B binding. It'll go and build a really terrible index that's no good for you and quietly return. So I think that's one of the biggest gotchas. It's just like having a proper spatial index.

Regina: Yes, knowing that you need an index and knowing that you need it to be a GiST index. Paul: Yeah. Regina: As far as between GiST and SP-GiST, I'd still tend to go toward GiST because I know we put in more effort in GiST than we did in SP-GiST. Same. I think our GiST support is more robust than SP-GiST. Paul: Yeah. I have tried different benchmarks and come up with different answers from different data, different queries. There's not, I've not been able to like fully characterize the kinds of places where SP-GiST outperforms. I don't know, Nik, you, you seemed like really excited about

SP-GiST or like there are places you've found where like SP-GiST is the clear winner. Nikolay: No, somehow I just recently don't see this kind of workloads at all, like maybe a couple of years, so I don't have fresh data at all. I don't know like why, obviously, like it's just a matter of luck. Michael: I came across one recently where they were using a GiST index and still struggling with performance and I suggested trying SP-GiST because I thought it might make sense and it didn't help. So it wasn't much worse, it was very similar.

But the one thing that they changed that did help a lot was they were using geography before, and it was only to calculate quite small distances, like within 25 meters type thing and switching to geometry made a big difference. Yeah. That was surprised by, Yeah, you 2 are not surprised. You 2 are nodding along. Paul: I'm not surprised at all. Regina: Because geography is a more complex, it's more, it's three-dimensional, whereas the geometry is two-dimensional, really. Paul: I have a slide in my sort of standard Postgres talk where

I say, you know, geometry or geography, what should I do? And the thing that I use to demonstrate why geometry is going to be better for performance purposes is just the distance calculation. Everyone has learned Pythagoras. So I know what the distance calculations looks like there, right? 2 squares and a square root. Any distance calculation in geography space is going to have to do a bunch of Haversine calculations, which is the distance on a sphere. And Haversine has 5 transcendentals in it, plus the square root. So it's just way more, like orders of magnitude more computationally intensive.

So if you're doing a bunch of those calculations, you're just going to pay a big price for sure. Nikolay: The earth is flat. The earth is flat. I knew it. Paul: Peter Ideal, in an ideal world, the office is flat. Yes. So that means you need to commit to a planar understanding of your work area if you can. So if you are doing an app which works in London and say, you know, there's a planar projection which is good for London. If you're doing something in Idaho, there's a planar projection which is good for Idaho. If You're doing something which is like the continent of the U.S. There is a planar projection, which is good for most people's

purposes in the continent of the U.S. It's very rare that they have something which requires latitude and longitude. You only get the breakdowns when you're starting to work with truly global data. Places where you go over the poles, places where you have data in the Northwest territories and also in South America, where the scale distortions of something like Mercator cannot be ignored, that's when you say, okay, fine, I'll use geography. And then you'll get some nice advantages out of geography. If you have truly global data, the fact that the shortest path might go over the North Pole, that's a useful thing to have.

Or it would go across the dateline. That's a useful thing for the system to just transparently handle, which it does for both the calculations of things like distance and area, And for the performance-based stuff like indexing, like it does not care. It goes over the poles. It goes around the dateline. It doesn't notice because the model just, it takes away those singularity points when you're working on a sphere. Michael: Nice. The, I think the reason I was surprised was more because I'm so used to queries being IO bound and it's, it's quite fast to get one that's actually CPU. You know, actually the CPU do matter here.

Yeah. Paul: That is one of the places where spatial is special and people don't notice it. And it's a weird 1. Like we for, trying to integrate with a Postgres planner, we've been trying to help the planner get smarter by costing our functions appropriately. But the planner only looks at function costs in filters and joins. A lot of the times People are doing the spatial calculations in the SELECT line, right? The return values. So if you have a complex function in your result set, Postgres

is not going to add that in and say, oh, I should do a different thing, or that there's a lot of cost here. So that's an issue. And then actually it's a real problem on IO as well. I actually did a blog post for Snowflake about 2 weeks ago that went public, talking about that with respect to JSONB, but I learned about it in the geography, geometry world, which is, you know, you get a big object and Canada, Canada has like 10,000 vertices, like as well, as well above the, the maximum object size that you, that you'll find in a, in a page.

So it's going to be TOASTed. And that means any retrieval of that object is going to be a 2 stepper where it has to go and find the TOAST pointer and then go into the TOAST tables and gather all the parts and put them all together in order and then decompress all that, like just to get it out, just in the IO. And that's like a combination of the IO, pull that across, but also the computational overhead of just reassembling the thing, it's about 10 times, 10X. I found in joins, You can get things to go 10X faster when I don't have that TOAST overhead. If I pre-cut everything into shapes which are smaller than the

object to the max. Regina: Yeah, Speaking of TOAST, there's also the one gigabyte. Is that the limit on TOAST? If people have already started complaining about that, about that. Paul: It's or Nikolay: what are the types of Arduino types? So it's one gigabyte, I think. Yeah. Yeah. But it's better not to go beyond 300 megabytes. I think there are benchmarks showing that for JSON is terrible. Paul: Oh, I can't imagine. Nikolay: Yeah. There is a drop in performance after 300 megabytes or so.

Michael: That's a lot of JSON though. Regina: Yeah, I think I forget what the reason was. It had more to do with outputting stuff. Nikolay: I also forgot. Regina: Like if you're trying to output a big NVT file. Paul: The whole world in one file? The level 0 file? Michael: Does it compress well? I was just thinking, as JSON generally, there's a lot of repetition, a lot of like 300 megabytes post-compression JSON file would be like Paul: pretty massive. This is one of the places where there are potential implementation

paths which would have given us a much better I.O. Profile, but at the cost of something which the users considered pretty inviolate, and that's perfect fidelity of input and output. So users are feeding the system with geometries which are described in terms of coordinates which are built on double precision. And if you want to return what they got, you have to store the double precision. And double precision does not compress so great. So in answer to your compression question, yeah, not so great.

If you just take the smallest swing at saying, this stuff is quantized or like, this has an effective minimum precision, like your road segment is not more precise than a centimeter. And if I take advantage of that fact, if I know that there is a quantization available, I can squash stuff down into a delta encoding, which is incredibly cheap for spatial data, because spatial data tends to be highly auto-correlated in space. So you can store the differences between each coordinate with

a very small number, which is great. It's a really good compression and it's a very fast decompression. But you have to give up precision to take advantage of it. Nikolay: Or if it's a lot of moving objects on the map, but this is should be column compression, like TimescaleDB does. So we just change a little bit position. We cannot jump to different continents immediately. Paul: Yeah, but there will have to be precision associated with that. Like they will be losing a little bit of precision when they

compress. Like you can't just slam floats or slam doubles together cleanly all the time and get a nice clean delta encoding. Sometimes you can, but not always. Nikolay: Similar with timestamps, actually. If you want to give up milliseconds, you can have fewer bytes, definitely. Speaking of numbers, I'm curious what are the biggest PostGIS setups you saw? Both single node or maybe sharded systems like with Citus or

something. Paul: Regina, you're the implementer. What have you seen? Regina: I have no idea. I don't work with that big of data sets. Paul: Yeah. So Postgres is, or PostGIS is weird, a weird number of the Postgres community because we have, I think almost certainly the largest number of deployed clusters. Like the number of PostGIS users is huge. I remember Stephen Frost came to his first Phosphor G and his jaw sort of hit the floor because he was used to going to a Postgres

conference where there'd be like 200 attendees and he came to 1 of the free and open source for geospatial conferences. And there are 800 attendees. And every one of them he talked to said, yeah, yeah, we use PostGIS. It was like he'd never seen so many distinct Postgres users in 1 place. That's one difference, the population of PostGIS users is huge relative to the population of Postgres users, but they all have very small deployments. So yeah, if you're, if you're a county, you've got several dozen tables, none of which has more than 100,000 records, like from

the point of view of scale, it barely registers, But there are a few big ones. It's just that you don't necessarily know they exist because no one talks about them. Like pretty sure Apple Maps production system is all on Postgres. That's only because someone whispered to me in a dark alley, like no one's ever made it public. I know that here, which is a really large geospatial data aggregator. If you're going to get raw geospatial data and want to have a map, which like competes with what Apple or Google provides,

you probably go to here and license their data. I know that their production pipeline and a lot of their APIs are built on PostGIS. So it's a really big user, global data, high uptime requirements. And then one of repeats over and over again. So there's a lot of these installations and they're big and they're high performance is people who are doing OSM map builds. Because the open means OpenStreetMap. OpenStreetMap is a map of the world. It's a map of the world based on a very strict topological model, edge node collections, which means that anything which covers

an area has to be built from those edge node relationships, which implies loading the whole edge node system and then building up the polygons from the parts and then testing the polygons against each other. And so people who end up using PostGIS as the intermediate stage between I've got a lot of raw open stream app data and I have a rendered tile or a vector tile, there's a big PostGIS Postgres process in the middle of that where it's shuffling through all the data and building up the higher level structures just don't exist in the raw OSM dump.

And there's, there's gotta be hundreds and hundreds of those, but they all look about the same size. There's a size of OSM. Regina: There's more than hundreds. Paul: Okay. Thousands of them. Nikolay: Yeah. Also, I also saw in your blog posts, Redfin, State Farm, like we should have a lot, this setup should have a lot of data, right? Paul: They do, and real production loads. They're actually, they're asking real questions to the spatial database and getting spatial answers back. Nikolay: And also state level, government level, like in France, Norway?

Paul: Yeah, that was our biggest installation for a long time. It was the biggest one I could like quote chapter and verse on. It was the, and they came on very early 2005. 1 of our first big institutional users was IGN, the French National Mapping Agency. And they ran Postgres and PostGIS through a competitive process with Db2 Spatial and Oracle Spatial at that time, 2005, and determined that Postgres and PostGIS was, in other words, just as good as Db2 Spatial and Oracle Spatial, and most importantly, like way

cheaper. So, and way easier to deploy. They didn't have to go through like a full commercial, like commercial requisition. They could just say, okay, we're going to use it. And they did. So IGN has been on Postgres PostGIS for 20 years. And that initial database was a 150 million object database. Michael: At the Paul: time, very substantial. Nowadays, it's routing error. But at the time, it was one of the biggest databases. Nikolay: You needed to be really brave those days because of lack of replication and proper backup. Yeah. Today, it's much, much better. Much better. Paul: They wanted to have, one of the requirements was multi-site

replication. Because they wanted to have... I think it was Slony to start with. Nikolay: Yeah. Good. I'm glad now it's already solved. Relatively. We have problems with large setups, but it's already a different story. Great. Michael: Is there anything we haven't talked about that either of you wanted to make sure we mentioned? Regina: I guess I should mention conferences. So there's going to be a PgDay Boston conference, June 9th. And then for the conference that Paul was mentioning, the free

and open source geospatial, that's going to be in Japan this year. And let's see. Paul: That's Hiroshima, August 30th to September 5th. Regina: Yeah. Oh, and then there's also FOSS4G NA. So that's coming up. But I don't think we've said we haven't set this to the day yet, but it's gonna be probably in November. Paul: November. Michael: I really appreciate that you wrote in PostGIS day, the day after GIS day. And I've actually watched a few of them. We run them all online, which is great.

And I, yes, I've seen quite a few, tend to at least watch the talks on performance. My special interest and anything by Brian Timoney as well. I wanted to give him a shout out. I've enjoyed. Paul: Yeah. PostGIS day. If you just type it in, you'll get all the video archives. So if you're jonesing for PostGIS conference content, that's the place to get it for sure. Michael: Yeah. It's awesome. I highly recommend anything. Any last things for me, Paul? Paul: We haven't talked about AI, which feels like a huge mess. Yeah.

And, and I don't know what your experience is with is with this is Regina: The next company has AI in its name. Paul: Oh yeah. So he's really into it. That's good. And I wanted to point out like a couple of things that I've seen like in the AI space. 1 is the geospatial AI space. There's this whole other field called foundation models, which is basically like RAG, retrieval log, retrieval log meta generation, like RAG for geospatial, where they build up a characterization of an area or the whole globe where every pixel in that characterization has a multi-dimensional and very high dimensionality vector associated

with it. So it really is a reg process. But people have been using pgvector as like the storage engine for their foundation models and to do like quick gatherings of nearest neighbor. And when you do nearest neighbor with a foundation model, what you get is like land, which is similar to this other land. So you can draw a circle around, say, a bunch of solar panels and say, show me the other solar panels, and it will just go off and find every solar panel in the world for you. Very cool stuff. There's a, and there's a cool talk on it in the 2025 Postgres day.

Really worth looking at. The other thing that came up in that Postgres day around AI was the idea of natural language to SQL and swing a cat, throw a stone, hit someone who's doing natural language to SQL out there. But a really cool observation from Brendan Ashworth from Bunting Labs during his talk that a lot of the code models, like how you get a coding model is first train a generic model and then do reinforcement learning against it. And the way you get a code model which can speak SQL, is you do a lot of reinforcement learning against standard SQL patterns.

And his observation was that the standard SQL patterns tend to be like Postgres SQL patterns. So you tend to get really good Postgres SQL and maybe not so good Oracle SQL. And the same thing holds true with the spatial side because they're using Postgres because PostGIS is the lingua franca for geospatial SQL. You tend to get good spatial SQL generated by these models. And the real limiting step tends to be providing the correct amount of like semantic metadata around your data model so that the LLM can make sense of the natural language question you're

making and spit out the right SQL. But you're already ahead of the game if you're using Postgres and Postgres as your engine, because these models will tend to do SQL that matches them. Nikolay: It's writing in Python is easier with like using LLMs because it's they know much better than new languages. Paul: Yeah, give me Perl 6. Nikolay: Yeah, and that's why Postgres is very natural because so many, so much material it was trained on it and Postgres the same thing. This is a great point. So it should help to grow popularity even more because a lot

of applications are created by AI these days. But I'm curious, if you deal with a lot of data, you need to verify. If you don't have a way to verify all the ideas AI is generating, the quality will be not good. So this is one thing I always do, how to give AI tools to verify its own ideas very fast without humans. Before humans are involved, it's already much better. I'm curious what you think about this.

How to write proper queries and don't use this distance function you mentioned. Function you mentioned. Regina: The good thing is, as Paul said, it does write the right distance function. It does do ST_DWithin, if you give it a text, say, give me something within 50 miles, it writes the right query. I have tested that. Nikolay: Good, But it still would be better if it would have some tooling to test and see actual results, not just guess. Paul: Yeah, and we've started to see that discipline arrive in

the PostGIS codebase. We have our first agents file. Regina: Oh yeah, we have that SKILL.md. Paul: Or SKILL, sorry. So yeah, we have our first skills file, which does touch on that kind of stuff you're talking about, Nik. Like these are best practices for generating spatial SQL. Nikolay: Yeah, in my opinion, copy on write and database branching should grow because we need to be able to test on large data sets for very low money and very fast. It can be as in S3, like some company implemented, right?

Neon or Timescale, they implemented it. It can be with ZFS or anything, but it should be fast and cheap. So you give like millions of points on the map and tell, just check, EXPLAIN, ANALYZE BUFFERS. Michael: Or, Nik, did you see friend of the show, Radim posted a new blog post recently about just import the stats. So you don't even need to load all the data. Nikolay: Yeah, I saw it. Just test planning and behavior without data actually, just with stats. I think it's a valid approach, but at the same time I think it's

limited because you don't see actual execution. Michael: Of course, but you can at least check like index use and things, which is cool. Nikolay: Yeah, definitely. It's a super lightweight approach. I like that. And I think more and more should be developed because of AI. Otherwise, so many mistakes. But if you have this testing pipeline and you're even not involved. AI has some ideas how to improve query and tooling to verify this is the way and put it to test to CI and so on. And it's great. Yeah.

So that's why we created branching for many years ago with DBLab. We like We were sitting and waiting for these very days when it's needed so much. Great. Michael: Well, it was a pleasure to meet you both. Thank you so much for coming. Thank you for joining us. Nikolay: Thank you for coming. Regina: It was a nice talk. Paul: Thanks Nik. Thanks Michael. Nikolay: Yeah. Have a great week.

Open the transcript file

Hosts and Guests

Hosts

  • Michael Christofides Host Feed-stated
  • Nikolay Samokhvalov Host Feed-stated

Guests

  • Paul Ramsey Guest Feed-stated
  • Regina Obe Guest Feed-stated

Credit the people on this episode

Be the first to rate this episode

More episodes

All episodes
  1. Postgres 19

    Nov 19, 202653 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...

    Credit the people on this episode

  2. The hidden costs of CREATE TEMP TABLE

    Nov 13, 202639 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...

    Credit the people on this episode

  3. Extensions on demand

    Nov 6, 20261 hr 9 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...

    Credit the people on this episode

  4. UUIDv4 vs UUIDv7

    Oct 30, 202648 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...

    Credit the people on this episode

  5. Dump/restore done. Now what?

    Oct 23, 202651 minAudio

    Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...

    Credit the people on this episode