Faster VARCHAR inserts with PFSC_BOOST

Abstract

Before a variable length row can be inserted into an Informix table, a page with sufficient space must first be located. That can be slow, particularly with large volatile tables with configuration parameter MAX_FILL_DATA_PAGES enabled to reduce its size. This article describes how the problem can be eliminated on 14.10.xC2 or later versions.

Content

Examples in this article were produced using the provided “stores_demo” database in a Docker container from informix-dockerhub with Informix Dynamic Server (IDS) updated to 14.10.FC11.

The new feature is documented here: Boosted Partition Free Space Caches (PFSC)

There are two relevant configuration parameters which are both enabled by default on later IDS versions:

informix@docker:~$ onstat -g cfg full PFSC

IBM Informix Dynamic Server Version 14.10.FC11DE -- On-Line -- Up 01:33:29 -- 327812 Kbytes

Configuration Parameter Info

id   name                      type    maxlen   units   rsvd  tunable
101  PFSC                      BOOL    2                      *

default : 1
     onconfig:
     current : 1

This parameter is undocumented.

Description:
     PFSC stands for "Partition Free Space Cache", which is a feature designed
     to improve insert performance of tables with variable-length rows when
     MAX_FILL_DATA_PAGES is enabled. Setting the PFSC parameter to 1 enables
     this feature, 0 disables it.

informix@docker:~$ onstat -g cfg full PFSC_BOOST

IBM Informix Dynamic Server Version 14.10.FC11DE -- On-Line -- Up 01:33:40 -- 327812 Kbytes

Configuration Parameter Info

id   name                      type    maxlen   units   rsvd  tunable
102  PFSC_BOOST                BOOL    2                      *

default : 1
     onconfig:
     current : 1

Description:
     PFSC stands for "Partition Free Space Cache", which is a feature that
     comes standard for tables with variable-length rows when
     MAX_FILL_DATA_PAGES is set. Setting PFSC_BOOST enables boosted caches,
     which track all free space in the table, regardless of schema and
     regardless of the MAX_FILL_DATA_PAGES setting. A table must be marked
     as having a boosted cache or this parameter has no effect on it.

What is not obvious is that you must specify tables that should have a boosted cache using SQL statements as in this example:

Copy to Clipboard

To list which tables have a boosted cache:

An actual case history follows which illustrates why doing this can be a good idea.

Whenever a production Informix server was restarted for patching, the first transaction afterwards involving such tables was taking longer than 20 seconds, causing a public Web application to fail with a timeout. We devised a procedure to be run after a restart to perform a sequential scan and dummy insert before any real ones, forcing the table to be loaded into the buffer pool and the default light PFSC to be populated (table names have been changed to examples in the “stores_demo” database):

sp_load_caches

Copy to Clipboard

CREATE FUNCTION "informix".sp_load_caches()
    RETURNING
        DATETIME YEAR    TO SECOND AS date_time,
        CHAR(18)                   AS tabname,
        INT                        AS nrows,
        CHAR(24)                   AS action,
        INTERVAL HOUR(1) TO SECOND AS run_time;
/*
    Preload critical tables after restart
    Doug Lawry, October 2024

1) BUFFERPOOL: whole table scan
    2) PFSC: perform a dummy insert

Tables must have a *SERIAL* column and no other UNIQUE index.
    *SERIAL* value -1 avoids incrementing the next real insert.
    TEMP tables do not allow ALTER, so RAW is in fact used.

Usage:
    EXECUTE FUNCTION sp_load_caches();
*/
    DEFINE l_temptab, l_tabname, l_colname VARCHAR(128);
    DEFINE l_time_0, l_time_1, l_time_2 DATETIME YEAR TO SECOND;
    DEFINE l_nrows INT;

SET DEBUG FILE TO '/tmp/sp_load_caches.debug';
    TRACE ON;

SET ISOLATION TO DIRTY READ;
    SET LOCK MODE TO WAIT 60;

LET l_temptab = 'dummy_data';

EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS ' || l_temptab;

SELECT DBINFO('utc_to_datetime',sh_curtime)
    INTO l_time_1
    FROM sysmaster:sysshmvals;

LET l_time_0 = l_time_1;

FOREACH WITH HOLD

SELECT tabname, colname
        INTO l_tabname, l_colname
        FROM systables AS t
        JOIN syscolumns AS c
        ON c.tabid = t.tabid
        WHERE tabname IN
        (
            'catalog', -- Replace with your table names
            'warehouses'
        )
        AND MOD(coltype,256) IN
        (
            6,  -- SERIAL
            18, -- SERIAL8
            53  -- BIGSERIAL
        )
        ORDER BY 1

PREPARE e_count FROM 'SELECT COUNT(*) FROM ' || l_tabname || ' WHERE 1=1';
        DECLARE c_count CURSOR FOR e_count;
        OPEN    c_count;
        FETCH   c_count INTO l_nrows;
        CLOSE   c_count;
        FREE    c_count;
        FREE    e_count;

SELECT DBINFO('utc_to_datetime',sh_curtime)
        INTO l_time_2
        FROM sysmaster:sysshmvals;

RETURN l_time_1, l_tabname, l_nrows, 'Started sequential scan', l_time_2 - l_time_1 WITH RESUME;

EXECUTE IMMEDIATE 'SELECT FIRST 1 * FROM ' || l_tabname || ' INTO RAW '      || l_temptab;
        EXECUTE IMMEDIATE 'ALTER TABLE '           || l_temptab || ' MODIFY '        || l_colname || ' INTEGER';
        EXECUTE IMMEDIATE 'UPDATE '                || l_temptab || ' SET '           || l_colname || ' = -1';
        EXECUTE IMMEDIATE 'BEGIN WORK';
        EXECUTE IMMEDIATE 'INSERT INTO '           || l_tabname || ' SELECT * FROM ' || l_temptab;
        EXECUTE IMMEDIATE 'ROLLBACK WORK';
        EXECUTE IMMEDIATE 'DROP TABLE '            || l_temptab;

SELECT DBINFO('utc_to_datetime',sh_curtime)
        INTO l_time_1
        FROM sysmaster:sysshmvals;

RETURN l_time_2, l_tabname, l_nrows, 'Started dummy insert', l_time_1 - l_time_2 WITH RESUME;

END FOREACH

RETURN l_time_1, NULL, NULL, 'All tasks completed', l_time_1 - l_time_0;

END FUNCTION;

It was then set to run via a “cron” job shortly after the patching window with:

In our example, that produces the following output (“warehouses” is not listed as it does not have a *SERIAL* column):

date_time	tabname	nrows	action	run_time
24/07/2025 14:25	catalog	74	Started sequential scan	0:00:00
24/07/2025 14:25	catalog	74	Started dummy insert	0:00:01
24/07/2025 14:25			All tasks completed	0:00:01

In the real case, the sequential scan of the most problematic table took 1 second whereas the dummy insert took over 20 seconds, proving the main problem was loading PFSC.

We have since declared each table to have a boosted PFSC cache (see SQL already described). We still execute the “sp_load_caches” function shortly after a restart just in case it also helps, but no step now takes more than 2 seconds according to actual output. The Informix message log contains output like this for each of those tables starting around 6 seconds after the instance comes on-line:

Here is a function to list tables containing any kind of VARCHAR column that have boosted PFSC enabled or disabled, or to generate scripts to change that:

sp_pfsc_boost

Copy to Clipboard

DATABASE oninit;

EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('t');

DROP FUNCTION IF EXISTS sp_pfsc_boost;

CREATE FUNCTION sp_pfsc_boost
(
    p_mode  VARCHAR(9),
    p_debug BOOLEAN DEFAULT 'f'
)
    RETURNING VARCHAR(255) AS result;

-- List or change whether tables with *VARCHAR
    -- have a boosted partition free space cache

-- Doug Lawry, Oninit Consulting, August 2025

DEFINE l_dbname  VARCHAR(128);
    DEFINE l_tabname VARCHAR(128);
    DEFINE l_exp1    VARCHAR(128);
    DEFINE l_exp2    VARCHAR(128);
    DEFINE l_state   VARCHAR(10);
    DEFINE l_boosted BOOLEAN;

IF p_mode NOT IN ('enabled','disabled','enable','disable') THEN
        RAISE EXCEPTION -746, 0,
            'Allowed parameters: enabled disabled enable disable';
    END IF

IF p_debug THEN
        SET DEBUG FILE TO '/tmp/sp_pfsc_boost.debug';
        TRACE ON;
    END IF

SELECT TRIM(odb_dbname) -- main session database
    INTO l_dbname
    FROM sysmaster:sysopendb
    WHERE odb_sessionid = DBINFO('sessionid')
    AND odb_odbno = 0;

-- Boolean group aggregate expression showing whether any partition
    -- for that table has the boosted PFSC flag 0x10000000 set:
    LET l_exp1 = 'MAX(BITAND(p.flags,POW(16,7))) > 0';

-- Substitute the single table partnum if zero for each partition
    -- when the table is fragmented:
    LET l_exp2 = 'DECODE(t.partnum,0,f.partn,t.partnum)';

PREPARE e_boosted FROM
        ' SELECT ' || 'tabname, '    || l_exp1 ||
        ' FROM '   || l_dbname                 || ':systables '    || 'AS t' ||
        ' JOIN '   || l_dbname                 || ':syscolumns '   || 'AS c' ||
        ' ON '     || 'c.tabid = t.tabid'      || ' LEFT OUTER'    ||
        ' JOIN '   || l_dbname                 || ':sysfragments ' || 'AS f' ||
        ' ON '     || 'f.tabid = t.tabid'      ||
        ' AND '    || 'f.indexname IS NULL'    ||
        ' JOIN '   || 'sysmaster'              || ':sysptnhdr '    || 'AS P' ||
        ' ON '     || 'p.partnum = ' || l_exp2 ||
        ' WHERE '  || 'tabtype = ''T'''        || -- exclude views and external tables
        ' AND '    || 't.tabid > 99'           || -- exclude system tables
        ' AND '    || 'MOD(coltype,256)'       || -- discard NOT NULL bit
        ' IN '     || '(13,16,40)'             || -- VARCHAR,NVARCHAR,LVARCHAR
        ' GROUP '  || 'BY 1' ||
        ' ORDER '  || 'BY 1';

DECLARE c_boosted CURSOR FOR e_boosted;
    OPEN    c_boosted;

WHILE 1 = 1

FETCH c_boosted INTO l_tabname, l_boosted;

IF SQLCODE != 0 THEN
            EXIT WHILE;
        END IF

LET l_state = DECODE(l_boosted,'t','enabled','disabled');

IF p_mode MATCHES '*abled' THEN
            IF l_state = p_mode THEN
                RETURN l_tabname WITH RESUME;
            END IF
        ELSE
            IF l_state NOT MATCHES p_mode || '*' THEN
                RETURN
                    'EXECUTE FUNCTION sysadmin:task(' ||
                    '''' || 'table pfsc_boost ' || p_mode || ''',' ||
                    '''' || l_tabname                     || ''',' ||
                    'DBINFO(''dbname''))'
                WITH RESUME;
            END IF
        END IF

END WHILE

CLOSE c_boosted;
    FREE  c_boosted;
    FREE  e_boosted;

END FUNCTION

DOCUMENT "

-- Connect to the required database first
    -- Prefix function name with database and colon if different

-- List tables in a given state:
    EXECUTE FUNCTION sp_pfsc_boost('enabled');
    EXECUTE FUNCTION sp_pfsc_boost('disabled');

-- Save SQL script to enable:
    UNLOAD TO 'enable.sql' DELIMITER ';'
    SELECT * FROM TABLE(FUNCTION sp_pfsc_boost('enable'));

-- Save SQL script to disable:
    UNLOAD TO 'disable.sql' DELIMITER ';'
    SELECT * FROM TABLE(FUNCTION sp_pfsc_boost('disable'));
";

Caveats

PFSC_BOOST is only available from IDS 14.10.xC2 and there were initially related defects, but you can use it with confidence in more recent versions.

Conclusion

Setting MAX_FILL_DATA_PAGES can substantially reduce the size of tables with large variable length columns. However, to improve insert performance, particularly after a restart, make sure you have PFSC (undocumented) and PFSC_BOOST enabled, and declared those tables with table pfsc_boost enable.

Disclaimer

Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.