PIP Caching
    • 05 Mar 2025
    • 3 Minutes to read
    • Dark
      Light
    • PDF

    PIP Caching

    • Dark
      Light
    • PDF

    Article summary

    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)
    );
    
    1. 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)
    );
    
    1. Configure Environment Variables:
    pipOperator:
      extraEnv:
        TRANSACTION_MANAGER_ENABLED: "true"
    
    1. 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

    Property NameDescriptionRequiredDefault
    MATERIALIZEDEnables materialized view functionality.trueNA
    MATERIALIZED_TABLEDefines the external materialization table. Omitting this invokes internal materialization.trueNA
    teiid_rel:ALLOW_MATVIEW_MANAGEMENTEnables automatic cache management.falsefalse
    teiid_rel:MATVIEW_TTLSets the cache time-to-live in milliseconds.false--
    teiid_rel:MATVIEW_POLLING_QUERYDefines a query returning a timestamp to trigger cache refresh.false--
    teiid_rel:MATVIEW_POLLING_INTERVALDefines the polling interval (ms) for the polling query.false--
    plainid:MAXIMUM_DATA_FRESHNESS_PERIOD_MINUTESThis parameter (related only to In-Memory Cache) sets the maximum time an internal materialized view can be used without a proper refresh. If this time limit is exceeded since the last successful load, a log message will be generated every minute indicating a potential unhealthy state.
    The log entry will be: Data in materialized view {Mat View Name} has not been refreshed for a long time. {the defined freshness period} minute expiration threshold exceeded.
    false--

    Multi-value Support

    For attributes with multiple values (e.g., an account associated with multiple countries), views should flatten the values into multiple records:

    Account IDBalanceCountry
    166:905000IL
    166:9010,000IL
    167:502000UK
    165:4010,000US
    167:5015,000US

    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.


    Was this article helpful?