Michael Golden, InterSystems Principal Software Developer, presented foreign tables as a method to integrate external data sources, such as Oracle, Postgres, and CSV files, with IRIS for SQL querying without data migration, utilizing foreign servers and data wrappers. He further detailed predicate pushdown and its enhancement to include aggregates and row limiting, aiming to offload more query execution to external systems and improve performance across different SQL dialects, demonstrated by a significant speed increase with aggregation in version 2025.2. The discussion covered accessing external data natively within Iris and the performance benefits of enhanced query pushdown capabilities.
Introduction to Foreign Tables: Golden introduced foreign tables as a way to connect to external systems and present them as native IRIS tables. The goal is to access data in external sources like Oracle, Postgres, or CSV files within IRIS using SQL without moving the data. This process involves foreign servers representing external sources and a foreign data wrapper for internal communication.
Predicate Pushdown: Golden explained the concept of predicate pushdown, where the system identifies predicates in a local query and pushes them down to the external data source for evaluation, especially for JDBC sources. This process reduces the amount of data transferred and the local processing required. However, this was initially limited to logical predicates in the WHERE clause.
Enhanced Pushdown: Beyond Predicates: Golden discussed going beyond predicate pushdown to include aggregates, row limiting (TOP, LIMIT), and other functions within the pushdown process. The aim is to push more of the query execution to the external system, leveraging its potential access to indexes and reducing data transfer, which addresses the fact that different databases have their own SQL dialects.
Pushdown Types and Demonstration: Golden mentioned “pushdown types” (Basic, SQL92, Extended, IRIS) to handle the variations in SQL dialects across databases. In a demo comparing older and newer IRIS versions, Golden demonstrated that with enhanced pushdown, an aggregation query was almost entirely executed on the foreign server, resulting in a significant performance improvement (approximately 10 times faster) compared to performing the aggregation locally. This enhancement will be available in version 2025.2.
