Caching Views

The PIP Service provides multiple caching capabilities, including Materialized Views, to significantly improve performance in various use cases.

In-Memory Cache

To create an in-memory cache, add the relevant options within the view definition.

Example

CREATE VIEW v_Person (
    id varchar,
    name varchar,
    dob date,
    PRIMARY KEY (id)
) OPTIONS (
    UPDATABLE 'false', MATERIALIZED true,
    "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
    "teiid_rel:MATVIEW_POLLING_QUERY" '(SELECT NOW() FROM SYSADMIN.MatViews WHERE Name = ''v_aad_users'' AND LoadState = ''FAILED_LOAD'')
        UNION
        (SELECT Updated FROM SYSADMIN.MatViews WHERE Name = ''v_aad_users'' AND LoadState = ''LOADED'')',
    "teiid_rel:MATVIEW_POLLING_INTERVAL" 30000
) AS SELECT * FROM <USER>.Users;

Persistent Cache

To use the PIP Cache effectively, configure two PAAs within the same PAA Group, along with a PostgreSQL database.

• Cache Management PAA – Manages the external cache, including data synchronization and Materialized Views.
• Operational PAA – Handles runtime operations and serves requests efficiently.
• External PostgreSQL Database – Stores cached data and serves as a data source for operational PIPs in customer-provided databases.

Environment Setup Overview for PAAs

Cache Management PAA

The PIP in the Cache Management PAA caches data from defined data sources and manages Materialized Views. It retrieves, processes, and writes data to the external PostgreSQL database.
Only one PIP should manage Materialized Views to prevent data duplication or overwriting, ensuring that the most recent data is retained.

Operational PAA

The Operational PAA uses the external PostgreSQL database as its data source, improving performance and scalability by offloading query processing from the original data source.

The Persistent Cache stores view data in an external PostgreSQL database. Queries then retrieve cached results instead of re-computing them, reducing processing time and CPU usage.

To Create a Persistent Cache:

1. Create a Data Source for caching in the PlainID Platform.

2. Define the required PostgreSQL database schema.

3. Create a Materialized Table:

   CREATE TABLE public.userscached (
       id varchar NOT NULL,
       name varchar NULL,
       loadnumber BIGINT NOT NULL,
       CONSTRAINT userscached_pkey PRIMARY KEY (id)
   );
   

4. Create a Status Table:

   CREATE TABLE public.status (
       vdbname varchar(50) NOT NULL,
       vdbversion varchar(50) NOT NULL,
       schemaname varchar(50) NOT NULL,
       name varchar(256) NOT NULL,
       targetschemaname varchar(50) NULL,
       targetname varchar(256) NOT NULL,
       valid bool NOT NULL,
       loadstate varchar(25) NOT NULL,
       cardinality int8 NULL,
       updated timestamp NOT NULL,
       loadnumber int8 NOT NULL,
       nodename varchar(25) NOT NULL,
       stalecount int8 NULL,
       CONSTRAINT status_pkey PRIMARY KEY (vdbname, vdbversion, schemaname, name)
   );
   

5. Configure Environment Variables and Enable Transactional Support:

   pipOperator:
     extraEnv:
       TRANSACTION_MANAGER_ENABLED: "true"
   

6. Create a Cached View:

   CREATE VIEW v_Person (
       id varchar,
       name varchar,
       dob date,
       PRIMARY KEY (id)
   ) OPTIONS (
       MATERIALIZED 'TRUE',
       UPDATABLE 'FALSE',
       MATERIALIZED_TABLE 'materialized.PersonCached',
       "teiid_rel:MATVIEW_TTL" 20000000,
       "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
       "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
       "teiid_rel:MATVIEW_STATUS_TABLE" 'materialized.STATUS',
       "teiid_rel:ON_VDB_START_SCRIPT" 'UPDATE PersonModel.PersonCached SET LoadNumber = 0',
       "teiid_rel:MATVIEW_POLLING_INTERVAL" 30000,
       "teiid_rel:MATVIEW_POLLING_QUERY" '(SELECT NOW() FROM (SELECT updated, loadstate FROM ExtPgsqlModel.status WHERE name = ''v_users_external'' ORDER BY updated DESC LIMIT 1) x WHERE x.loadstate = ''FAILED_LOAD'')
           UNION
           (SELECT updated FROM (SELECT updated, loadstate FROM ExtPgsqlModel.status WHERE name = ''v_users_external'' ORDER BY updated DESC LIMIT 1) x WHERE x.loadstate = ''LOADED'')'
   ) AS SELECT * FROM <USER>.Users;
   

Redis-Based Request Distributed Caching

Redis-Based Distributed Caching allows multiple PIP instances to share retrieved data through a common Redis store, preventing duplicate calls to external sources like Azure. By ensuring only one instance fetches the data while others read it from Redis, the feature significantly improves performance and reduces unnecessary load on data services. For more information and examples, see the REST Services Source and Microsoft Entra ID (Azure AD).

Polling Query and Interval Properties

The Polling Query (teiid_rel:MATVIEW_POLLING_QUERY) and Polling Interval (teiid_rel:MATVIEW_POLLING_INTERVAL) properties continuously attempt to refresh the cache until successful.
These control how frequently the cache refreshes, ensuring that Materialized Views always reflect the latest data.

These properties apply to both in-memory and persistent caches, with differing query syntax:

• In-Memory Cache: Used within the view definition to refresh Materialized Views.
• Persistent Cache: Managed by the Cache Management PAA to keep cached data synchronized with PostgreSQL. The Operational PAA retrieves this data for improved performance.

Refer to the linked sections for detailed examples.

View Options

Common View Properties

Multi-Value Support

For attributes with multiple values (for example, an account associated with multiple countries), views should flatten the values into multiple records:

Note: Multi-value Attributes in Asset Templates are not supported in LDAP Data Sources.

Views List

The Views List displays all available views on the main page. Expanding a view shows its available columns.