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.

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;

Persistent Cache

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 in customer-provided databases.

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.

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. 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 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 (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;

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 properties control how frequently the cache attempts to refresh, ensuring that Materialized Views reflect the latest data.

These properties apply to both in-memory and persistent caches with varying 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 in PostgreSQL synchronized with the source system. The Operational PAA then retrieves data from the persistent cache for improved performance.

Refer to the respective links for more information and examples.

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.