Support Geospatial data access use case

[haubourg]

Hi all,
the classical PostgreSQL + PostGIS couple allows either to build transactionnal apps (OLTP) , but is more often close to OLAP use cases. Mapping huge regions, building dashboard on top of it is a fairly common use case.
I run a 6 TB database here, that serves both GIS and datascience use cases.
The row storage somewhat starts to be at its limits.
I gave a try at Hydra and I am a big fan already.
I added postGIS to the docker container, and was surprised to have it work smoothly with columnar tables. Well done!
However the rendering speed when accessing data by geographic extent was a lot slower than classical tables with GIST index.
EXPLAIN shows a similar plan.
So my guess is that geometry columns - which are already compressed in WKB format- are compressed again by HYDRA and decompressed on the fly.
I have the intuition that not compressing geometry and geography fields would give the best of both worlds.
In the meantime, I am going to play with external storage with GeoParquet and FDW to shrink down my storage and my backups.

Does that ring any bell on your roadmap?

[haubourg]

reference to the source discussion on discord : #141
Is GIS use case interesting to you ?

[wuputah]

Currently we believe the main issue is the index usage (or lack thereof), which applies to many use cases. We are looking into adding a custom index-backed scan, and hope to release this in 1.1.

In 1.0, you may need to use pg_hint_plan to force the planner to use the GiST index. Check with EXPLAIN and see if the index is being used. See our docs for details. Enabling and using the column cache may also help once the index is in use; the column cache helps eliminate repeated decompression.

In the short term, we're not looking to optimize compression / storage based on column data type, but we are thinking about this for longer term features.