4 ms·

Just curious, have you tried Alloy with the PostGIS extension? I've been trying to get my work let us use BigQuery for our geospatial vector data queries, beca

Just curious, have you tried Alloy with the PostGIS extension? I've been trying to get my work let us use BigQuery for our geospatial vector data queries, because we've managed to break all the equivalent AWS products, Snowflake, and Databricks on our dataset. Regular Postgres + PostGIS takes about 30 minutes to run our query and BigQuery does the same in 4 seconds. Unfortunately, BigQuery is a bit of a pipe dream right now for us, so it would be interesting to understand if PostGIS benefits from some of the changes in Alloy.

GabeWeiss_ 4y ago
The improvements we've made on the read side of things likely won't affect the geo data directly, BUT, depending on what other aggregate data you're combining with the geo data you might see some improvement from the columnar engine. Hard to know without digging in deeper on the queries themselves and schemas you're working with. I don't want to steer you down a path of moving a ton of data and infra just to look. Best guess is that it'll be a bit better, but likely closer to your PG experience vs. the BQ experience.
- ZeroCool2u 4y ago
  Really appreciate the reply! Everything you said makes sense. It does feel like it would be really hard to beat BigQuery at this kind of task. The main query we run is just comparing two tables geometry where one is a polygon/multipolygon column and the other is a column of points. We ask, are any of these points in any of these polygons? So it's N x M comparisons, pretty much worst case scenario and there's no aggregation. I've had a hell of time trying to optimize PG for just running this one query. On the bright side, I've gotten to learn about and try a bunch of different databases now though :)
GabeWeiss_ 4y ago
My PM also reminded me, depending on the data set size too, if it's bigger than the buffer cache, we might see some improvements over PG as well from that (we've made memory improvements around that which might help potentially).

GabeWeiss_ 4y ago

The improvements we've made on the read side of things likely won't affect the geo data directly, BUT, depending on what other aggregate data you're combining with the geo data you might see some improvement from the columnar engine. Hard to know without digging in deeper on the queries themselves and schemas you're working with. I don't want to steer you down a path of moving a ton of data and infra just to look. Best guess is that it'll be a bit better, but likely closer to your PG experience vs. the BQ experience.

ZeroCool2u 4y ago
Really appreciate the reply! Everything you said makes sense. It does feel like it would be really hard to beat BigQuery at this kind of task. The main query we run is just comparing two tables geometry where one is a polygon/multipolygon column and the other is a column of points. We ask, are any of these points in any of these polygons? So it's N x M comparisons, pretty much worst case scenario and there's no aggregation. I've had a hell of time trying to optimize PG for just running this one query. On the bright side, I've gotten to learn about and try a bunch of different databases now though :)

GabeWeiss_ 4y ago

My PM also reminded me, depending on the data set size too, if it's bigger than the buffer cache, we might see some improvements over PG as well from that (we've made memory improvements around that which might help potentially).