Faster Reads with Query Result Cache

Applications hit the database with the same SELECT queries constantly — dashboards reloading, reference data lookups, configuration checks. Every execution goes to the database, gets processed from scratch, and comes back with the same rows it returned ten seconds ago. Multiply that across hundreds of concurrent users and you are burning database resources on work that has already been done.

DataSunrise 12.0 adds Query Result Cache, a proxy-level feature that stores SELECT results and serves them directly on repeat queries without touching the database.

How It Works

When a SELECT query passes through the DataSunrise proxy for the first time, the result is stored in a local cache with a configurable time-to-live. When the same query comes through again — from the same user, on the same instance — the proxy returns the cached result immediately. The database never sees the second request.

The cache operates at the network protocol level, not the SQL parser level. DataSunrise reads and replays raw database protocol packets, which means it handles packet fragmentation and asynchronous pipelined queries correctly. If a batch of queries comes through and one of them is cached, DataSunrise waits for the preceding responses, injects the cached result at the right position in the pipeline, and the application sees no difference.

Cache entries are isolated per database user. Two users running the same query get separate cache entries — there is no cross-user data leakage. When the cache reaches its memory or entry limit, the least recently used entries are evicted automatically.

What Gets Cached

Only SELECT queries with deterministic results. Queries containing volatile functions like NOW(), RANDOM(), UUID(), NEXTVAL(), or LAST_INSERT_ID() are excluded automatically — their results change on every call, so caching them would return stale data. Queries against system catalogs, temporary tables, and locking queries (FOR UPDATE, FOR SHARE) are also skipped.

Non-cacheable queries pass through to the database normally and do not affect cache statistics.

Interaction with Masking

Query Result Cache is aware of Dynamic Masking. If a response is modified by a masking rule, the cache stores and replays the masked version. The original unmasked data is never served from cache.

Configuration

The cache is configured per database instance. Navigate to Configuration → Databases, select an instance, click Actions → Result Cache Settings, and set the parameters: maximum memory, maximum entries, TTL in seconds, maximum result size, and maximum query length. Enable the toggle and save.

There is nothing to configure on the database side. No triggers, no materialized views, no schema changes. The feature works entirely at the proxy level.

Monitoring

Live cache statistics are available at Monitoring → Performance → Result Cache Rate. The dashboard shows hit rate, total hits and misses, current entries, evictions, and invalidations. You can filter by server and worker process, and flush the cache manually if needed.

Supported Databases

Query Result Cache works with PostgreSQL, MySQL, Microsoft SQL Server, Oracle, and their compatible variants — including Greenplum, Amazon Redshift, Amazon Aurora PostgreSQL, CockroachDB, YugabyteDB, Azure SQL Database, and others. The feature requires Proxy mode and is not available in Sniffer or Agent modes.

There is no automatic cache invalidation when underlying data changes. Cached results remain until the TTL expires, the entry is evicted, or the cache is cleared manually. For workloads where data freshness is critical, set a shorter TTL or use the cache selectively on instances that serve read-heavy, infrequently changing data.

For configuration details and verification steps, see the Query Result Cache section of the User Guide.