Postgres FDW: Pushdown is a negotiation
6 hours ago
- #Query Pushdown
- #Postgres Extensions
- #Foreign Data Wrapper
- Postgres extensions add functionality not included in Postgres itself, like PostGIS, pgvector, and TimescaleDB.
- Foreign Data Wrapper (FDW) extensions allow Postgres to read data from external sources, such as ClickHouse.
- pg_clickhouse is an FDW that fetches data from ClickHouse, enabling SQL queries across both systems.
- Pushdown is key in FDWs: determining how much query processing can be delegated to the remote system to reduce data transfer.
- Pushdown decisions are granular; each clause is evaluated for safe translation between Postgres and ClickHouse SQL dialects.
- Unsupported clauses block entire query pushdown, forcing rows to be streamed back for local processing, impacting performance.
- Example query analysis shows iterative improvements in pg_clickhouse: from basic WHERE pushdown to joins, aggregates, JSON, and window functions.
- Each step in pushdown development corresponds to adding specific translations, like percentile_cont to quantile and JSON field access.
- Some pushdowns are revoked if they lead to incorrect results, emphasizing correctness over speed.
- FDW work involves balancing translation accuracy, planner hooks, and remote system semantics to minimize data transfer while ensuring correct results.