Reduce Transaction Handling Overhead with Caching and Batching #379
Description
Describe the pattern you'd like to propose
As a software developer or architect, I want to introduce caching mechanisms or batch processing, so that the application does not need to open and close a transaction for each small database operation.
A use case repeatedly fetches a small amount of indexed records, for example by their primary key.
This results in excessive transaction handling overhead, as each fetch operation opens and closes a transaction, while the actual fetch operation is very fast because of the optimized indexing.
A second use case stores or updates thousands of records one by one, resulting in similar overhead.
Describe specific emission impact from this pattern
Reducing transaction handling as well as the amount of database requests reduces blocking I/O operations, which improves the performance of the application and eventually reduces its energy consumption.
SCI = (E * I) + M per R
The energy consumption E is affected by reducing the amount of network calls introduced through unnecessary transaction handling.
References to this pattern
There is another pattern on the Green Software Patterns page that refers to caching static data.
Additional context
For read operations, the enterprise application uses a cache like Redis to store frequently accessed data as key-value pairs.
When a record is requested from the database, the application first checks the cache, returns the cached data if available, or fetches it from the database and stores it in the cache afterward.
For write operations, the application groups multiple records into batches, for example 1000 records per batch, and processes each batch in a single transaction.