Why Postgres FDW Made My Queries Slow (and How I Fixed It)

Svix is a service designed to facilitate the creation of secure and scalable webhook platforms, but the text primarily focuses on the complexities of using Postgres Foreign Data Wrappers (FDWs) for executing queries across distributed databases. The core challenge described is the planner's difficulty in determining which operations can be executed on remote Postgres servers, a process known as "pushdown." When pushdown fails, operations are executed on the local Postgres server, leading to inefficiencies such as excessive data transfer and processing workloads. The text highlights common pitfalls like time-dependent expressions, ORDER BY and LIMIT clauses, and joins, which often do not push down due to issues like collation mismatches or the use of volatile functions. It emphasizes the importance of using EXPLAIN to verify where operations occur and suggests strategies like adjusting fetch_size and using remote estimates to improve query performance. The author encourages a vigilant approach to query planning, given the persistent risk of poor performance due to inaccurate planner estimates and network inefficiencies.