Paul Ramsey, co-creator of PostGIS, writes about spatial databases, Postgres internals, open source sustainability and the GIS industry.
HN commenters recommend it as excellent GIS material, citing his critique of Esri and posts on Postgres indexing and parallel queries.
that's essentially the route that Mapbox went down and they've even invented an entire mbtiles file format that essentially is just a sqlite db for doing these kinds of queries on the server
it's the "status quo" approach today in the industry, but it has some downsides still especially for smaller builders (ie me):
1. i'd have to run a separate tile server to take in the tile requests and convert them to "sql" requests (or mbtile requests) under the hood, and I'm just not a fan of more moving parts
2. I'd have to take my 10+ TB (and growing) of geotiffs and process them all into mbtiles which is a huge compute and walltime cost
3. the resulting mbtiles end up being similar in size at best, but at worst far larger than the original geotiffs so it balloons up the hosting and egress costs in exchange for faster requests. this is a great compression optimization breakdown for geotiffs that dives into this if you're interested - https://blog.cleverelephant.ca/2015/02/geotiff-compression-for-dummies.html
anyways, I'm sure ted has his own thoughts as well but this is at least what I've taken away from this space after diving into it with fresh eyes in the past few months
A couple of excellent GIS related links I would recommend to anyone interested in this area.
Talks and writing by PostGIS co-creator Paul Ramsey (who is incredible): http://blog.cleverelephant.ca/writings
The mapscaping podcast - a lot of intros to the complex and overlapping worlds of GIS: https://mapscaping.com/blogs/the-mapscaping-podcast
do you have to do much tuning to trigger parallel queries? i'm also trying to do this with PostGIS, and it looks like you kind of have to tweak costs and fiddle with things a fair amount to get parallel queries to trigger: http://blog.cleverelephant.ca/2018/09/parallel-postgis-3.html
> A wildcard at the end can use a regular (btree) index.
depending on the locale your db is in, it might be a little less "regular". recently fell in that trap.
http://blog.cleverelephant.ca/2016/08/pgsql-text-pattern-ops.html
ESRI is a scourge on the GIS industry. One of the creators of postgis can say it much better than I can.
https://blog.cleverelephant.ca/2018/11/esri-dominates.html
OSM uses postgres: http://blog.cleverelephant.ca/2009/04/openstreetmap-moves-to-postgresql.html?m=1