PIP Caching

PIP Cache Overview

The PIP Service provides several capabilities for caching data, including materialized views. This is used to significantly improve performance in many situations.

To effectively utilize the PIP Cache, 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.

Environment Setup Overview for PAAs

Cache Management PAA

The PIP in the Cache Management PAA caches data from specified data sources and manages Materialized Views. It retrieves, processes, and writes data to the external PostgreSQL database. A single PIP should manage Materialized Views to prevent data duplication or overwriting in the external database, ensuring the most recent data is maintained.

Operational PAA

Operational PAAs use the external PostgreSQL database as their data source. This improves performance and scalability by offloading query processing from the original data source.

In-Memory Cache

To create an in-memory cache, add the relevant options as part of 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;

Polling Query and Interval Properties

The Polling Query and Interval properties continuously attempt to refresh the cache until successful. This ensures that Materialized Views reflect the most current and accurate data, avoiding data availability issues.

Persistent Cache

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

Creating a Persistent Cache:

1. Create a Data Source for caching in the PlainID Platform.
2. Enable Transactional support.
3. Define the required PostgreSQL database schema.
4. 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)
);

5. 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)
);

6. Configure Environment Variables:

pipOperator:
  extraEnv:
    TRANSACTION_MANAGER_ENABLED: "true"

7. Create a cached View with the following definition:

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:MATVIEW_POLLING_INTERVAL" 30000,
    "teiid_rel:MATVIEW_POLLING_QUERY" '(SELECT NOW() from SYSADMIN.MatViews where Name = ''v_USER'' AND LoadState = ''FAILED_LOAD'')
    UNION
    (SELECT Updated FROM SYSADMIN.MatViews where Name = ''v_USER'' AND LoadState = ''LOADED'')'
     ) as select * from <USER>.Users;

View Options

Common View Properties

Multi-value Support

For attributes with multiple values (e.g., 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

A list of all available views is presented on the main page. Expanding a view displays its available columns.