Background​

The PostgreSQL columns of type TEXT and BYTEA allow the storage of large binary data (e.g. PDF documents or images) up to 1GB. If such column types are used in a table their data is accessed like any other data of the record when using the Xbase++ ISAM emulation. With each navigational operation, the entire record data is transferred from the database server to the client's main memory.

Since this dramatically affects the performance of the ISAM navigation, the PostgreSQL ISAM emulation implements multiple adaptive pre-fetch algorithms that try to avoid round trips to the server. As always with optimizations, there is a price. In this context, it is memory consumption versus latency to store record data on the client.

In short, pre-fetching large - hundreds of kilobyte-sized - objects such as TEXT/BYTEA increases your client-side memory consumption. In the following, we outline strategies to deal with these types of situations.

Strategies​

Adapt your data model​

Limit the number of columns in a table to a minimum. Especially with applications that have grown over time, tables have grown with them, resulting in more and more columns being added to a table. Sooner or later, it is worth revising the data model and splitting the monster-tables into meaningful entities again.

As a rule of thumb and this is true for NoSQL and SQL Database Systems, the smaller the record size the better it is. In general, try to be under 4K with NoSQL file storage base DBEs and smaller than 8K with the PostgreSQL DBMS. In addition, try to move BLOB data out to different tables or in the case of SQL statements add only those columns to the SELECT part which are really required. Avoid SELECT * FROM tables.

Limit fetch amount​

In situations where rebuilding or adapting the data model is not a way to go in the short term, the amount of data for a single operation such as SKIP (DbSkip()) or GO (DbGoto()) can be reduced by limiting the maximum amount of rows to be pre-fetched as well as how many row-cache-partitions can exist.

The PostgreSQL database engine provides a configuration to exactly control that. The define constant PGDBE_ISAM_PAGECACHE_SIZE can be used to define the maximum read-ahead in case of pre-fetching. By default this is -1, allowing the engine to adapt dynamically depending on the navigational pattern. Using a fixed amount of 10 or lesser will be a good choice for tables with large objects stored. In addition, the ISAM emulation does hold multiple pre-fetched cache pages which are organized as an LRU list. The amount of pages can be controlled using the PGDBE_ISAM_PAGECACHE_PAGES define.

index.php


The following code will set a fixed read ahead of 10, and reduce the amount of pages to 1 (default 10).

Xbase++: 
DbeSetDefault("pgdbe")
DbeInfo( COMPONENT_DATA, PGDBE_ISAM_PAGECACHE_SIZE, 10)
DbeInfo( COMPONENT_DATA, PGDBE_ISAM_PAGECACHE_PAGES, 1 )
USE <yourtable>

The setting varies from case to case and must be optimized depending on your concrete scenarios.

Summary​

The overall workload for data access can be optimized by reducing the amount of data involved to access a record. It is always a good idea to add new columns to your tables sparely and rethink twice if it may be better to add a new table instead. The number of records transferred from the server to the client with the reading of a record can be limited by adjusting the default page cache setup with the function DbeInfo().