Lakehouse Federation in AlloyDB overview

This page introduces Lakehouse Federation in AlloyDB for PostgreSQL, a unified data management solution that extends AlloyDB's query capabilities. This integration lets you seamlessly query a range of BigQuery resources, including the following:

With Lakehouse Federation, you can use AlloyDB's query engine to power your application's transactional and analytical workloads from within the same interface. You can also materialize or import this data on AlloyDB for faster access for use in your applications, which lets you use AlloyDB AI and the columnar engine.

Lakehouse Federation for AlloyDB lets you load and transform data from BigQuery or Iceberg into AlloyDB to support your operational applications or to join historical data with real-time transactional data. This use case supports unified analytics and a complete view of your business in your application context.

You can use AlloyDB as a transactional database and also have large amounts of data residing in BigQuery or BigLake. Your applications usually integrate independently with both of these systems to access data across these different Google Cloud services. Lakehouse Federation lets you use AlloyDB's federated query support implemented as a foreign data wrapper to access BigQuery and AlloyDB data using a SQL interface in AlloyDB.

Pushdown

You can use filter and aggregate pushdown techniques, which speed up queries and reduce costs by filtering or summarizing data in BigQuery before it's moved or processed by AlloyDB. This approach minimizes network traffic and memory usage, letting you analyze massive datasets quickly and efficiently without exceeding resource limits.

Filter pushdown

Filter pushdown, also known as predicate pushdown, is an optimization technique that moves the filtering of data as close to the storage layer as possible. Instead of reading a massive table into memory and then discarding the rows that you don't need, the database "pushes" the filter (using the WHERE clause) down into the initial data scan.

With filter pushdown, you can use SQL queries with a WHERE clause to access a subset of data from the remote table. This data can also be materialized on a local table or attached as a local partition to a PostgreSQL table.

Supported filters

The following comparison and arithmetic operations are pushed down to BigQuery using the BigQuery API if they appear in expressions in the filtering (WHERE) clause of the PostgreSQL query:

Comparison operators

=, >, <, >=, <=, <>, ~~, !~~

Arithmetic operators

+, -, *, /

Aggregate pushdown

Aggregate pushdown is an advanced database optimization that performs calculations—for example, SUM, COUNT, AVG, or GROUP BY as close to the storage layer as possible. While filter pushdown removes unneeded rows, aggregate pushdown summarizes needed rows before the database engine processes them further.

Supported aggregates

The following aggregation functions are pushed down to BigQuery using the BigQuery API, when applicable to the supported data types:

  • SUM
  • AVG
  • MIN
  • MAX
  • COUNT

BigQuery cost and billing

The BigQuery foreign data wrapper depends on the following:

  • BigQuery compute pricing
  • BigQuery Storage API pricing

For information, see BigQuery pricing.

Limitations

  • If you're using a non-default collation in BigQuery, make sure that it's also supported by AlloyDB. For more information, see Migration overview.
  • Queries that return a very large amount of data from BigQuery, after the pushdown, aren't optimized.

What's next