<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">Text as a column can be quite unhappy if you feed it with binary data (things like the printing code gets unhappy with the zero-byte parts, so you can end up with unexpected stops in printouts). <div class="">I have no idea why text would have any advantage over bytea, since if you check the source, they are both just typedefs over top of varlena.</div><div class="">It would be possible to do a gzip(text) returns bytea, but you’d still have a bytea in the end, and it wouldn’t be nice and bidirectional, since text::bytea works, but bytea::text doesn’t return the original ascii encoding.</div><div class=""><br class=""></div><div class="">There shouldn’t be any conversion cost in a cast, since the memory structure of text and bytea is identical, it’s just re-typing a pointer.</div><div class=""><br class=""></div><div class="">I can add a gzip(text) => bytea very easily though (heck, you can, just create a new gzip function that looks exactly like the bytea one, but takes text as the argument, it’ll work fine.</div><div class=""><br class=""></div><div class="">ATB</div><div class=""><br class=""></div><div class="">P<br class=""><div><br class=""><blockquote type="cite" class=""><div class="">On Nov 4, 2019, at 1:49 AM, Yuri Astrakhan <<a href="mailto:yuriastrakhan@gmail.com" class="">yuriastrakhan@gmail.com</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div dir="ltr" class="">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 class=""><br class=""></div><div class="">[1] <a href="http://engineering.pivotal.io/post/bytea_versus_text_in_postgresql/" class="">http://engineering.pivotal.io/post/bytea_versus_text_in_postgresql/</a></div></div><br class=""><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" class="">yuriastrakhan@gmail.com</a>> wrote:<br class=""></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" class="">Paul rulez!!!! Thank you!!! :)<div class=""><br class=""></div><div class="">Experimentation ensues...</div></div><br class=""><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" class="">pramsey@cleverelephant.ca</a>> wrote:<br class=""></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 class="">
<br class="">
<a href="https://github.com/pramsey/pgsql-gzip" rel="noreferrer" target="_blank" class="">https://github.com/pramsey/pgsql-gzip</a><br class="">
<br class="">
P<br class="">
<br class="">
On Sun, Nov 3, 2019 at 6:24 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank" class="">pramsey@cleverelephant.ca</a>> wrote:<br class="">
><br class="">
> I could not find a gzip extension on the web, even though I swear one<br class="">
> already exists.<br class="">
> So, I wrote up one, it's quite a small piece of work, but hopefully it serves.<br class="">
><br class="">
> <a href="https://github.com/pramsey/psql-gzip" rel="noreferrer" target="_blank" class="">https://github.com/pramsey/psql-gzip</a><br class="">
><br class="">
> ATB,<br class="">
><br class="">
> P<br class="">
><br class="">
> On Sun, Nov 3, 2019 at 11:57 AM Yuri Astrakhan <<a href="mailto:yuriastrakhan@gmail.com" target="_blank" class="">yuriastrakhan@gmail.com</a>> wrote:<br class="">
> ><br class="">
> > 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 class="">
> ><br class="">
> > [1] <a href="https://imposm.org/docs/imposm3/latest/tutorial.html#expire-tiles" rel="noreferrer" target="_blank" class="">https://imposm.org/docs/imposm3/latest/tutorial.html#expire-tiles</a><br class="">
> ><br class="">
> > On Sun, Nov 3, 2019 at 2:41 PM Martin Davis <<a href="mailto:mtnclimb@gmail.com" target="_blank" class="">mtnclimb@gmail.com</a>> wrote:<br class="">
> >><br class="">
> >> Good background to know. Quite a bit to grok there!<br class="">
> >><br class="">
> >> 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 class="">
> >><br class="">
> >> On Sun, Nov 3, 2019 at 11:00 AM Yuri Astrakhan <<a href="mailto:yuriastrakhan@gmail.com" target="_blank" class="">yuriastrakhan@gmail.com</a>> wrote:<br class="">
> >>><br class="">
> >>> 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 class="">
> >>><br class="">
> >>> 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 class="">
> >>><br class="">
> >>> [1] OpenMapTiles tools - <a href="https://github.com/openmaptiles/openmaptiles-tools" rel="noreferrer" target="_blank" class="">https://github.com/openmaptiles/openmaptiles-tools</a><br class="">
> >>> [2] tilelive-pgquery - <a href="https://www.npmjs.com/package/tilelive-pgquery" rel="noreferrer" target="_blank" class="">https://www.npmjs.com/package/tilelive-pgquery</a><br class="">
> >>><br class="">
> >>> On Sun, Nov 3, 2019 at 12:55 PM Martin Davis <<a href="mailto:mtnclimb@gmail.com" target="_blank" class="">mtnclimb@gmail.com</a>> wrote:<br class="">
> >>>><br class="">
> >>>> Great to hear that ST_AsMVT is useful.<br class="">
> >>>><br class="">
> >>>> 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 class="">
> >>>><br class="">
> >>>> 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 class="">
> >>>><br class="">
> >>>> Out of curiosity, what platform do you use for your external gzipping layer?<br class="">
> >>>><br class="">
> >>>> On Sun, Nov 3, 2019 at 8:29 AM nyurik <<a href="mailto:yuriastrakhan@gmail.com" target="_blank" class="">yuriastrakhan@gmail.com</a>> wrote:<br class="">
> >>>>><br class="">
> >>>>> The amazing ST_AsMVT() has two common usage patterns: copy resulting MVTs to<br class="">
> >>>>> a tile cache (e.g. .mbtiles file or a materialized view), or serve MVT to<br class="">
> >>>>> the users (direct SQL->browser approach). Both patterns still require one<br class="">
> >>>>> additional data processing step -- gziping.<br class="">
> >>>>><br class="">
> >>>>> Thus, rather than having a horizontally scalable db plus a simple IO-bound<br class="">
> >>>>> SQL->Web or a SQL->store process, one has to add a relatively CPU-intensive<br class="">
> >>>>> gzipping layer. This is especially relevant if I try to create a PG table<br class="">
> >>>>> with the pre-generated tiles - I must use an external data compression<br class="">
> >>>>> process to retrieve a tile, gzip it, and store it back, instead of running a<br class="">
> >>>>> single query for copying all tiles. My cursory look at the tile sizes<br class="">
> >>>>> indicate gzipping shrinks MVTs 50% to 300%.<br class="">
> >>>>><br class="">
> >>>>> Note that a similar CPU-intensive step - creating MD5 tile hashes for a more<br class="">
> >>>>> efficient storage - can be easily done with PG's `md5()` function, whereas<br class="">
> >>>>> `gzip()` doesn't appear to exist.<br class="">
> >>>>><br class="">
> >>>>> I would like to propose two possible solutions:<br class="">
> >>>>> * Implement ST_AsMVT(..., compress) parameter - NULL=no compression,<br class="">
> >>>>> 0-9=compression level.<br class="">
> >>>>> PROs: adds just the required functionality to where it is needed (YAGNI<br class="">
> >>>>> principle), does not require ungzip yet (ST_AsMVT is a one way function<br class="">
> >>>>> without the corresponding MVT->Table method)<br class="">
> >>>>> CONs: less generic (unusable for non-MVT usage)<br class="">
> >>>>> * Implement gzip() or ST_gzip()<br class="">
> >>>>> PROs: a more generic approach not tied to MVTs<br class="">
> >>>>> CONs: logically implies the need of ungzip(), requires PG community to<br class="">
> >>>>> agree this functionality is needed<br class="">
> >>>>><br class="">
> >>>>> Thanks!<br class="">
> >>>>><br class="">
> >>>>><br class="">
> >>>>><br class="">
> >>>>> --<br class="">
> >>>>> Sent from: <a href="http://postgis.17.x6.nabble.com/PostGIS-Dev-f3570762.html" rel="noreferrer" target="_blank" class="">http://postgis.17.x6.nabble.com/PostGIS-Dev-f3570762.html</a><br class="">
> >>>>> _______________________________________________<br class="">
> >>>>> postgis-devel mailing list<br class="">
> >>>>> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" class="">postgis-devel@lists.osgeo.org</a><br class="">
> >>>>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank" class="">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br class="">
> >>>><br class="">
> >>>> _______________________________________________<br class="">
> >>>> postgis-devel mailing list<br class="">
> >>>> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" class="">postgis-devel@lists.osgeo.org</a><br class="">
> >>>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank" class="">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br class="">
> >>><br class="">
> >>> _______________________________________________<br class="">
> >>> postgis-devel mailing list<br class="">
> >>> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" class="">postgis-devel@lists.osgeo.org</a><br class="">
> >>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank" class="">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br class="">
> >><br class="">
> >> _______________________________________________<br class="">
> >> postgis-devel mailing list<br class="">
> >> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" class="">postgis-devel@lists.osgeo.org</a><br class="">
> >> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank" class="">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br class="">
> ><br class="">
> > _______________________________________________<br class="">
> > postgis-devel mailing list<br class="">
> > <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" class="">postgis-devel@lists.osgeo.org</a><br class="">
> > <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank" class="">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br class="">
_______________________________________________<br class="">
postgis-devel mailing list<br class="">
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" class="">postgis-devel@lists.osgeo.org</a><br class="">
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank" class="">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></blockquote></div>
</blockquote></div>
_______________________________________________<br class="">postgis-devel mailing list<br class=""><a href="mailto:postgis-devel@lists.osgeo.org" class="">postgis-devel@lists.osgeo.org</a><br class="">https://lists.osgeo.org/mailman/listinfo/postgis-devel</div></blockquote></div><br class=""></div></body></html>