<div dir="ltr">Paul, one (possibly newbie) question - I noticed you used `gzip(bytea)`, and I read in [1] that text performs about 17% better (for read queries, not sure about in-memory operations). Does it make sense to add gzip(text) variant? I concatenate ST_AsMVT() data with STRING_AGG, thus end up with a TEXT, and wonder if there is a conversion cost. Thanks!!<div><br></div><div>[1] <a href="http://engineering.pivotal.io/post/bytea_versus_text_in_postgresql/">http://engineering.pivotal.io/post/bytea_versus_text_in_postgresql/</a></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Nov 4, 2019 at 4:24 AM Yuri Astrakhan <<a href="mailto:yuriastrakhan@gmail.com">yuriastrakhan@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Paul rulez!!!! Thank you!!! :)<div><br></div><div>Experimentation ensues...</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Nov 3, 2019 at 9:29 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Excuse me,<br>
<br>
<a href="https://github.com/pramsey/pgsql-gzip" rel="noreferrer" target="_blank">https://github.com/pramsey/pgsql-gzip</a><br>
<br>
P<br>
<br>
On Sun, Nov 3, 2019 at 6:24 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>> wrote:<br>
><br>
> I could not find a gzip extension on the web, even though I swear one<br>
> already exists.<br>
> So, I wrote up one, it's quite a small piece of work, but hopefully it serves.<br>
><br>
> <a href="https://github.com/pramsey/psql-gzip" rel="noreferrer" target="_blank">https://github.com/pramsey/psql-gzip</a><br>
><br>
> ATB,<br>
><br>
> P<br>
><br>
> On Sun, Nov 3, 2019 at 11:57 AM Yuri Astrakhan <<a href="mailto:yuriastrakhan@gmail.com" target="_blank">yuriastrakhan@gmail.com</a>> wrote:<br>
> ><br>
> > OSM -> PostgreSQL is done by Imposm 3, which updates database tables on the daily/hourly/minute basis. Every time it runs, it generates a list of changed tiles. I do not know what process they use for it -- from my perspective, I simply get the list of updated tiles on zoom 14 as a file, and I could use it to regenerate cached tiles or purge them from Varnish. Perhaps Imposm maintainers could find a good use for that functionality?<br>
> ><br>
> > [1] <a href="https://imposm.org/docs/imposm3/latest/tutorial.html#expire-tiles" rel="noreferrer" target="_blank">https://imposm.org/docs/imposm3/latest/tutorial.html#expire-tiles</a><br>
> ><br>
> > On Sun, Nov 3, 2019 at 2:41 PM Martin Davis <<a href="mailto:mtnclimb@gmail.com" target="_blank">mtnclimb@gmail.com</a>> wrote:<br>
> >><br>
> >> Good background to know. Quite a bit to grok there!<br>
> >><br>
> >> One question: does any part of that toolchain regenerate tile cache subsets depending on detection of feature change? And if so, would it be useful to have a DB function which can determine the set of tile ids that need to be refreshed (i.e. by mapping a (set of or single) geometry (or envelopes) into a set of tile ids?<br>
> >><br>
> >> On Sun, Nov 3, 2019 at 11:00 AM Yuri Astrakhan <<a href="mailto:yuriastrakhan@gmail.com" target="_blank">yuriastrakhan@gmail.com</a>> wrote:<br>
> >>><br>
> >>> Martin, I am working on improving OpenMapTiles tooling [1] - the ultimate goal is to have tiles generated in real time from the up-to-date OSM data, and serve them directly to user's browser via some caching layer (i.e. Varnish). The tools already contain postserve - a simple python server that queries for MVT tiles (no compression yet, but can be easily added)<br>
> >>><br>
> >>> The other task is tile pre-generation using tilelive-copy (nodejs) - I wrote a tilelive-pgquery plugin [2] that queries PG for the tile, compresses it, and passes it on to tilelive-copy for storage.<br>
> >>><br>
> >>> [1] OpenMapTiles tools - <a href="https://github.com/openmaptiles/openmaptiles-tools" rel="noreferrer" target="_blank">https://github.com/openmaptiles/openmaptiles-tools</a><br>
> >>> [2] tilelive-pgquery - <a href="https://www.npmjs.com/package/tilelive-pgquery" rel="noreferrer" target="_blank">https://www.npmjs.com/package/tilelive-pgquery</a><br>
> >>><br>
> >>> On Sun, Nov 3, 2019 at 12:55 PM Martin Davis <<a href="mailto:mtnclimb@gmail.com" target="_blank">mtnclimb@gmail.com</a>> wrote:<br>
> >>>><br>
> >>>> Great to hear that ST_AsMVT is useful.<br>
> >>>><br>
> >>>> The other PostGIS capability that is useful for web spatial applications is the (recently enhanced) ST_AsGeoJSON. This should also be gzipped over the wire. So this suggests a modular gzip capability would be more useful.<br>
> >>>><br>
> >>>> If this isn't provided in Postgres in some way (now or in near term) perhaps we should just add a ST_Gzip function to PostGIS.<br>
> >>>><br>
> >>>> Out of curiosity, what platform do you use for your external gzipping layer?<br>
> >>>><br>
> >>>> On Sun, Nov 3, 2019 at 8:29 AM nyurik <<a href="mailto:yuriastrakhan@gmail.com" target="_blank">yuriastrakhan@gmail.com</a>> wrote:<br>
> >>>>><br>
> >>>>> The amazing ST_AsMVT() has two common usage patterns: copy resulting MVTs to<br>
> >>>>> a tile cache (e.g. .mbtiles file or a materialized view), or serve MVT to<br>
> >>>>> the users (direct SQL->browser approach). Both patterns still require one<br>
> >>>>> additional data processing step -- gziping.<br>
> >>>>><br>
> >>>>> Thus, rather than having a horizontally scalable db plus a simple IO-bound<br>
> >>>>> SQL->Web or a SQL->store process, one has to add a relatively CPU-intensive<br>
> >>>>> gzipping layer. This is especially relevant if I try to create a PG table<br>
> >>>>> with the pre-generated tiles - I must use an external data compression<br>
> >>>>> process to retrieve a tile, gzip it, and store it back, instead of running a<br>
> >>>>> single query for copying all tiles. My cursory look at the tile sizes<br>
> >>>>> indicate gzipping shrinks MVTs 50% to 300%.<br>
> >>>>><br>
> >>>>> Note that a similar CPU-intensive step - creating MD5 tile hashes for a more<br>
> >>>>> efficient storage - can be easily done with PG's `md5()` function, whereas<br>
> >>>>> `gzip()` doesn't appear to exist.<br>
> >>>>><br>
> >>>>> I would like to propose two possible solutions:<br>
> >>>>> * Implement ST_AsMVT(..., compress) parameter - NULL=no compression,<br>
> >>>>> 0-9=compression level.<br>
> >>>>> PROs: adds just the required functionality to where it is needed (YAGNI<br>
> >>>>> principle), does not require ungzip yet (ST_AsMVT is a one way function<br>
> >>>>> without the corresponding MVT->Table method)<br>
> >>>>> CONs: less generic (unusable for non-MVT usage)<br>
> >>>>> * Implement gzip() or ST_gzip()<br>
> >>>>> PROs: a more generic approach not tied to MVTs<br>
> >>>>> CONs: logically implies the need of ungzip(), requires PG community to<br>
> >>>>> agree this functionality is needed<br>
> >>>>><br>
> >>>>> Thanks!<br>
> >>>>><br>
> >>>>><br>
> >>>>><br>
> >>>>> --<br>
> >>>>> Sent from: <a href="http://postgis.17.x6.nabble.com/PostGIS-Dev-f3570762.html" rel="noreferrer" target="_blank">http://postgis.17.x6.nabble.com/PostGIS-Dev-f3570762.html</a><br>
> >>>>> _______________________________________________<br>
> >>>>> postgis-devel mailing list<br>
> >>>>> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
> >>>>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
> >>>><br>
> >>>> _______________________________________________<br>
> >>>> postgis-devel mailing list<br>
> >>>> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
> >>>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
> >>><br>
> >>> _______________________________________________<br>
> >>> postgis-devel mailing list<br>
> >>> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
> >>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
> >><br>
> >> _______________________________________________<br>
> >> postgis-devel mailing list<br>
> >> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
> >> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
> ><br>
> > _______________________________________________<br>
> > postgis-devel mailing list<br>
> > <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
> > <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></blockquote></div>
</blockquote></div>