Performance of PostGIS vs pmtile

[J4gQBqqR]

Hi, I am serving some US Census Block Group polygons from martin. I downloaded the zip file from Census and transformed them into pmtiles, I also populate a PostGIS table with the same data:

I can serve them in martin and achieve the same result. However, I am really curious about the pros and cons of each method. I knew that pmtiles are nothing but a simple SQLITE file, which is passive and notorious for low performance when handling large data query. PostgreSQL, on the other hand, is an active backend and scales well with large amount of data. When we talk about performance of SQLITE vs PostgreSQL, we normally think that PostgreSQL is better.

But right now, Martin is also a player here and I am told pmtile's MVT is optimized for web tiles, PostGIS is more complex as it provides other querying capabilities. I am not so sure which should I use for best performance. Is there any professional suggestion here? Could you help me understand why one should be chosen vs another?

[nyurik]

• pmtiles is NOT an SQLite, but rather a custom binary format optimized for the HTTP GET range requests (i.e. hosting .pmtiles on a regular web server as a simple file without martin or other custom software). You were thinking about .mbtiles - which are sqlites.
• mbtiles and pmtiles allow you to serve "pre-packaged" tiles - they are simply container formats storing tiles as blobs. Usually they are stored pre-compressed as well. In theory, Postgres also allows this usage pattern, but usually it is used to store raw geometries in one or more tables, and combine that data on the fly into tiles. This is slower, but allows far greater flexibility. In our demo, Martin queries PG with use-supplied parameters, constructing different tiles for each user. This is obviously not possible for the pre-packaged formats.
  Hope this helps