Supabase Wrappers v0.2: Query Pushdown & Remote Subqueries

2023-12-14

•

6 minute read

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
_12
create foreign table products (
_12
id text,
_12
name text,
_12
description text,
_12
default_price text
_12
)
_12
server my_stripe_server
_12
options ( object 'products' );
_12
_12
-- Fetch all your Stripe products in Postgres
_12
select * from products limit 10;

Key Features and Improvements in Wrappers v0.2:

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). Not yet available on our platform since it is a community wrapper, see below for details.
  • And a WIP Wrapper for Auth0

We cannot support community Wrappers inside the Supabase Dashboard until the Wrappers API is stabilized. You can vote your favorite Wrapper if you'd like it to be added to Supabase in the future. If you have developed a Wrapper that you want inside the Supabase Dashboard, please contribute it as a PR in the wrappers repo. Once we release Wrappers 1.0, we will support community Wrappers within the Supabase Dashboard.

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:


_10
select *
_10
from stripe.customers
_10
where 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:


_10
https://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:


_10
select *
_10
from clickhouse.people
_10
order by name
_10
limit 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:


_10
create foreign table clickhouse.people (
_10
id bigint,
_10
name text,
_10
age bigint
_10
)
_10
server clickhouse_server
_10
options (
_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:


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

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


_10
select id, col1, col2
_10
from clickhouse.my_table
_10
where _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:


_10
select *
_10
from 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.

More About Wrappers

Launch Week Supabase Launch Week X icon
11-15 Dec
Main Stage
Build Stage
Share this article

Build in a weekend, scale to millions