Apache Ignite as transparent cache for Postgresql tables

Question

Is it possible to use Apache Ignite as transparent cache for several tables in PostgreSQL RDBMS and to query that cache using Ignite SQL?

For example like this:

Application (via SQL) ---> Apache Ignite (if data is not loaded) ---> Postgresql

I'm new to Ignite and cannot figure out how to do that or is it even possible.

Ladoga

Answer 1

Ignite's SQL works over in-memory data only so you need to load data into caches beforehand. In other words, read-through doesn't work for SQL queries.
Starting with version 2.1 Ignite provides its custom persistent store that allows running SQL queries against the data both in memory and on disk.

Dhyana

Answer 2

It will work, if you preload data to cache before querying.

You can do it by configuring a CacheStore and calling IgniteCache#loadCache(). Here is documentation: https://apacheignite.readme.io/v2.3/docs/3rd-party-store#cachestore

Another option is to enable readThrough parameter and use cache API. Unfortunately, this option has no effect on Ignite SQL and works for cache API only.

Magpie