Challenges of Transaction Data Archiving

One of the challenges in the implementation of a payment solution is the long-term storage of the data. The primary information to be stored by your payment system is the information about transactions processed. Systems that experience large processing volumes accumulate a lot of transaction data over time.

The transactional data being stored is needed for different purposes. On one hand, people need to run various types of historical reports (historical analysis of longer periods of data). On the other hand, sometimes people need to go back to refund some previously processed transactions, or to review a transaction that was processed, and, for some reason, resulted in a chargeback or an ACH return (in such cases 90 days’ worth of data is required).

While today’s database systems are fairly sophisticated, there are some challenges with transaction data archiving, which have to be faced by database administrators.

What are the challenges of transaction data archiving?

Not all database systems face the same issues, but, collectively, as the data grows, there are several groups of problems that become apparent.

  • Size. At the most basic level there is the elementary issue of the database size. As more data is accumulated, the physical size of this data on the hardware drive grows, so you can simply run out of space. Beside that, it takes much longer to backup large data volumes.
  • Indexing. Databases maintain additional indices to perform quick searches on various criteria. As the table grows, the indices also grow, and the searches become very memory-hungry, because the entire index has to be loaded into memory.
  • Performance. The general performance for insert operation tends to drop as the table grows (because of index updates). Consequently, on some database systems you can experience degradation in the performance of the insert statements in the database as it grows.
  • Changes. In some systems, the time it takes to alter a table structure depends on the size of the table. Consequently, the time, that it takes to update the system to a newer version, when a specific large table needs to be updated, also increases.

Effective transaction data archiving techniques

There are various general techniques that exist in database management systems, but we are going to address the ones, which are specific for payment industry due to the nature of the business.

Data partitioning

The classical approach, used for solving the abovementioned problems, which is already present in the database systems, is called data partitioning. Some criterion, usually, based on the time interval, is chosen, and the entire table is broken down into several partitions, where each partition only stores a certain portion of data, which represents a specific segment of time, such as a week or a month. When the search is executed, it only scans those partitions where the required data is stored, and not the whole table.

However, in high-volume systems with a very high transactional throughput, even data partitioning cannot guarantee sufficient level of performance. That is why in some payment systems another mechanism is implemented in addition to data partitioning.

Operational versus transaction data archiving tables

In the modern transaction processing, after transactions are settled, no changes are, generally, made to the majority of the transactions. Because of this, it is possible to segment transactional data into two tables. The tables have exactly the same structure. The first table (operational table or authorization table) only stores transactions that have not been settled yet, while the second one (transaction archiving table) stores all the data, i.e. both the transactions that have been settled, as well as pending ones, only waiting to be settled.

When a transaction comes in for authorization, it is immediately inserted into the authorization table. Once it is authorized and the request is consumed by the caller (transaction submitter’s POS system), it is replicated into the archiving table. At the end of the day, when settlement happens, the data is, once again, replicated, as needed, into the archiving table, and, subsequently, removed from the operational table.

Consequently, operational table contains only unsettled transactions at any point in time. It is structured to contain very few indexes, as most of the searches are done on the archiving table. All of the reporting, as well as the entire user interface, are built using the archiving table, while refund\void functionality is implemented by making the initial search in the operational table, and, subsequent search in the archiving table.

The additional advantage of the approach is that, if some maintenance work (restructuring etc) has to be done on the archiving table, the system can still be functioning, so that the authorizations will still come through. However, user interface or reports might remain unavailable for some time period, while maintenance works are done.


Depending on the transactional throughput you are dealing with, you have to make an informed choice of the most suitable effective transaction data archiving technique.

Integrating Terminal Application into a Payment Ecosystem

If you are implementing your own terminal application or planning to integrate terminal applications into your payments ecosystem, you have to choose potential ways of interaction with payment terminals.

In our previous article we discussed the pros and cons of several payment terminal solution implementation options. Now we are going to analyze several approaches to integration of payment terminal applications into your payment ecosystem.

In essence, you can choose from among the two conceptually different approaches: standalone terminal use and integrated terminal use. The first approach allows the terminal to function on its own. The second approach envisions control of the terminal by point-of-sale (POS) application.

Standalone terminal use

You can often witness a classical example of standalone terminal use in many convenience stores. In such a store, after all the items you’ve picked are rung up at the POS, the cashier keys in the amount into the terminal and hands it over to you to complete the payment. In this case, the POS application has no interaction with the terminal.

When the terminal functions independently, in order to process a payment, you only have to input the payment amount. After that, the payment is made, while POS application, through which the sale was made, is not even “aware” of that.

As more and more commercial and open-source POS systems become available for various segments of businesses, the demand for standalone terminal offerings is gradually declining, because more and more businesses desire to have an integrated approach.

Integrated solution for terminal application

To integrate a POS system with a terminal, you have to connect the terminal to the workstation (on which the POS system is installed). Traditionally, POS was connected to the terminal through a serial port (presently – through a USB port). There are several ways of structuring interaction between POS and the terminal.

Fully integrated versus semi-integrated approaches

When it comes to integrated terminal solutions, there are two terms, that are commonly used to define the communication paradigm between POS and a terminal: semi-integrated and fully integrated.

Traditionally, all of the applications were fully integrated. In a fully integrated approach a terminal is used as a reader, obtaining card information, which is then consumed by the POS system, formatted into appropriate message, and sent to the gateway for processing. In a semi-integrated approach, the terminal is used as a standalone processing unit, which collects payment information, formats the appropriate message, and sends it to the gateway or processor for authorization.

Regardless of the communication approach used, there are several ways, in which the actual integration can be done.

Integration through an SDK

Traditionally, the terminal was managed through low-level libraries, allowing communication with serial/USB ports. In case of Windows these were the DLL libraries, provided by the manufacturer of the terminal (as we’ve mentioned in the previous article).

The advantage of the approach is that it is the most popular and widely spread one, as respective SDKs are available, and generic drivers such as uPOS, JavaPOS, etc, are available and supported by the existing POS systems.

Some payment solution providers see the advantage in the fact that most of the terminal behavior-controlling logic is located outside of the terminal, i.e. on the POS end, and, thus, in order to change the application’s “behavior”, no updates within the terminal are required. Consequently, you can reduce dependence on terminal management system (which is primarily used for remote terminal application updates).

The disadvantage of the approach is that it is not always suitable for web applications, which are not allowed to access to the low-level services of an operating system. While such applications can fall back on usage of ActiveX controls or Java Applets, these approaches present various security and deployment challenges.

Integration through a REST API

An alternative approach is to connect to the terminal through a REST API.

The advantage of the approach is that you do not have to deal with native code. Any application, capable of making web-service calls, can communicate with the terminal and control it.

The disadvantage of the approach is that all the logic driving the terminal’s behavior is, usually, deployed within the terminal as the terminal application. In this case, any enhancements of the application require updates within the terminal. Consequently, it is difficult to maintain such a configuration without involving a terminal management system of some sort.

Non-integrated approach

In some cases, the POS system does not need to control the terminal’s behavior at every step of the process. In such cases, POS developers prefer not to do integration with the terminal of any kind. However, you might still need the terminal to receive the information on the amount to be collected, while your POS system has to get the information about payments going through terminal.

In such cases you can utilize the so-called non-integrated approach.

When the POS system needs to process a transaction, it sends it to the gateway (without payment information included). Once a key on a terminal is pressed, the terminal connects to the gateway and checks if there are any pending transactions to be processed. It “sees” the transaction, that the POS sent previously, collects the payment information from the cardholder, and sends it to the gateway for processing. Once the gateway authorizes the transaction, it issues a call to the POS system, informing it that the original sale request has been fulfilled and the payment has been successfully made.

Naturally, the solution is available only for systems with a centralized processing server, such as web-based systems.

The advantage of this approach is that web-based systems can use the terminal without actually integrating with it.


Now that you have better understanding of payment terminal integration issues, you can decide, what terminal strategy is best for your business.