pg_clickhouse 0.1.10

Hi, it’s me, back again with another update to pg_clickhouse, the query interface for ClickHouse from Postgres. This release, v0.1.10, maintains binary compatibility with earlier versions but ships a number of significant improvements that increase compatibility of Postgres features with ClickHouse. Highlights include:

• Mappings for the JSON and JSONB -> TEXT and ->> TEXT operators, as well as jsonb_extract_path_text() and jsonb_extract_path(), to be pushed down to ClickHouse using its sub-column syntax.
• Mappings to push down the Postgres statement_timestamp(), transaction_timestamp(), and clock_timestamp() functions, as well as the Postgres “SQL Value Functions”, including CURRENT_TIMESTAMP, CURRENT_USER, and CURRENT_DATABASE.
• And the big one: mappings to push down compatible window functions, including ROW_NUMBER, RANK, DENSE_RANK, LEAD,LAG, FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE, CUME_DIST, PERCENT_RANK, and MIN/MAX OVER.
• Oh yeah, the other big one: added result set streaming to the HTTP driver. Rather that load all the results A testing loading a 1GB table reduced memory consumption from over 1GB to 73MB peak.

We’ll work up a longer post to show off some of these features in the next week. But in the meantime, git it while it’s hot!

• PGXN
• GitHub
• Docker

Thanks to my colleagues, Kaushik Iska and Philip Dubé for the slew of pull requests I waded through this past week!