Supabase Wrappers v0.2: Query Pushdown & Remote Subqueries

Supabase Wrappers v0.2 is out and now available on the Supabase platform. Wrappers is a Postgres extension that provides integrations with external sources so you can interact with third-party data using SQL.


_12-- Connect Postgres to Stripe
_12create foreign table products (
_12  id text,
_12  name text,
_12  description text,
_12  default_price text
_12)
_12server my_stripe_server
_12options ( object 'products' );
_12
_12-- Fetch all your Stripe products in Postgres
_12select * from products limit 10;

Key Features and Improvements in Wrappers v0.2:

New Wrappers
Query Pushdown
Remote Subqueries
Usage Statistics

To start using Wrappers on the Supabase platform, check out the Wrappers docs.

New Wrappers and Improvements

Airtable (Read-only): query your Airtable bases with support for jsonb
AWS S3 (Read-only): with support for CSV, JSON, and Parquet
Clerk (from the team at Tembo)
And a WIP Wrapper for Auth0

More improvements and updates can be found on Wrappers release page, including support for Query Pushdown, Remote Subqueries, and Usage Statistics which we’ll explore below.

Support for Query Pushdown

The Wrappers v0.2 framework now supports Query Pushdown.

What is Query Pushdown?

Query pushdown is a technique that enhances query performance by executing parts of the query directly on the data source. It reduces data transfer between the database and the application, enabling faster execution and improved performance.

How to Use Query Pushdown in Wrappers

In Wrappers, the pushdown logic is integrated into each extension. You don’t need to modify your queries to benefit from this feature. For example, the Stripe FDW automatically applies query pushdown for id within the customer object:


_10select *
_10from stripe.customers
_10where id = 'cus_N5WMk7pvQPkY3B';

This approach contrasts with fetching and filtering all customers locally, which is less efficient. Query pushdown translates this into a single API call, significantly speeding up the process:


_10https://api.stripe.com/v1/customers/cus_N5WMk7pvQPkY3B

We can use push down criteria and other query parameters too. For example, ClickHouse FDW supports order by and limit pushdown:


_10select *
_10from clickhouse.people
_10order by name
_10limit 20;

This query executes order by name limit 20 on ClickHouse before transferring the result to Postgres.

For details on where pushdown is supported, consult each FDW's documentation in the Wrappers Documentation.

Remote Subqueries

Remote subqueries enable the use of prepared data on a remote server, which is beneficial for complex queries or sensitive data protection.

Static Subqueries

In its most basic form, you can map a query on the remote server into a foreign table in Postgres. For instance:


_10create foreign table clickhouse.people (
_10  id bigint,
_10  name text,
_10  age bigint
_10)
_10server clickhouse_server
_10options (
_10  table '(select * from people where age < 25)'
_10);

In this example, the foreign table clickhouse.people data is read from the result of the subquery select * from people where age < 25 which runs on ClickHouse server.

Dynamic Subqueries

What if the query is not fixed and needs to be dynamic? For example, ClickHouse provides Parameterized Views which can accept parameters for a view. Wrappers v0.2 supports this by defining a column for each parameter. Let's take a look at an example:


_11create foreign table clickhouse.my_table (
_11  id bigint,
_11  col1 text,
_11  col2 bigint,
_11  _param1 text,
_11  _param2 bigint
_11)
_11server clickhouse_server
_11options (
_11  table '(select * from my_view(column1=${_param1}, column2=${_param2}))'
_11);

You can then pass values to these parameters in your query:


_10select id, col1, col2
_10from clickhouse.my_table
_10where _param1 = 'abc' and _param2 = 42;

Currently, this feature is supported by ClickHouse FDW and BigQuery FDW, with plans to expand support in the future.

FDW Usage Statistics

Quantitative metrics play a pivotal role when working with Postgres FDWs because of their impact on performance optimisation, monitoring, and query planning across distributed databases. We introduced a FDW usage statistics table wrappers_fdw_stats in Wrappers v0.2, storing:

create_times - number of times the FDW instance has been created
rows_in - number of rows transferred from source
rows_out - number of rows transferred to source
bytes_in - number of bytes transferred from source
bytes_out - number of bytes transferred to source
metadata - additional usage statistics specific to a FDW

We can use these to identify bottlenecks, latency issues, and inefficiencies in data retrieval. Access this table on the Supabase platform using the following:


_10select *
_10from extensions.wrappers_fdw_stats;

Thanks to Our Community Contributors

We couldn't build Wrappers v0.2 without our community and we'd like to thank all the following people for their contributions:

Dom Jocubeit, 0xflotus, Glitch, Tobias Florek, Jubilee, Germán Larraín, Kavanaugh Latiolais, tedverse.

A separate shout-out belongs to the pgrx team, which allows us to write Wrappers with Rust.

Want to join the Supabase Wrappers community contributors? Check out our contribution docs. We'd love to add you to the list next time.