AQR or TPS, who cares?

When a user says 'it's too slow, make it faster,' what do they actually mean? Database optimization, just like any optimization, needs to be informed by a well-understood measurable goal, because you can't improve what you can't measure.

When database experts ask their users what they want, the answer is most of the time: it's too slow, make it faster! When we start poking at that statement, it becomes clear that ‘faster’ is actually not well defined. Transactions per second (TPS) and average query response time (AQR) are widely used to quantify “faster,” but they express two very different concepts. Both relate to how quickly a database handles requests, but they cannot be used interchangeably, and each measures a specific aspect of database performance.

TPS measures how many database transactions are handled per second. Obviously, the result depends on the nature of the transaction. Some transactions consist of very simple statements that are well supported by indexes and that change only a few, or even no, records in the database. The standard pgBench transactions, often described as TPC-B-like, are good examples. Those are blazingly fast. Other transactions can be highly complex, like closing the books at the end of an accounting period. Those involve thousands, or even millions, of individual statements, and they can take hours to complete.

This simple example illustrates the difference between a database transaction (delimited by BEGIN and COMMIT) and individual statements (or queries).

BEGIN;

    INSERT INTO orders (id, customer, date)
        VALUES (4711, 666, NOW());

    INSERT INTO order_items (id, order_id, product, quantity)
        VALUES
            (1234567, 4711, 'Chocolat bar', 1),
            (1234568, 4711, 'Cookies', 12);

    UPDATE inventory SET on_hand = on_hand - 1
        WHERE product = 'Chocolat bar';

    UPDATE inventory SET on_hand = on_hand - 12
        WHERE product = 'Cookies';
COMMIT;

To be precise: single-statement transactions, often called implicit transactions, do not require a BEGIN/COMMIT delimiter. When PostgreSQL sees the semicolon, it assumes that it has reached the end of the transaction and executes a commit.

However, TPS also depends on the application that generates the load! If the application issues only a fixed number of transactions over a given period of time, then a faster database or a better-tuned query will not affect the TPS measurement, because the database is not the bottleneck — the application is!

Using TPS to measure database performance optimization only makes sense if the application can generate a persistent transaction load that keeps pace with the database's growing capacity as performance improves.

AQR measures average query response time. AQR is very different from TPS!

1. AQR measures the response time for individual queries, not transactions.
2. AQR does not directly depend on the volume of transactions generated by the application.

In the example above, TPS tracks how many times the block of four statements framed by BEGIN and COMMIT occurs. AQR tracks the database response time for each of the four queries.

AQR lets us dive into transactions and find what is slow. Still, more importantly, it doesn't rely on the application to generate an increasing workload to keep up with the optimized database's capabilities.

For example, one of our customers has an application that plans routes for repair technicians. Frequently, customers call with urgent requests, and technicians need to be rerouted ASAP with minimal impact on the SLA of other customers that have standard maintenance work scheduled for that day. In this use case, reducing AQR to regenerate the workplan by 80% contributes significantly to customer satisfaction and work productivity, but the TPS does not change.

When we put AQR and TPS into the car racing context, then AQR is measuring how fast we can take a specific turn, how fast we can be on the Mulsanne straight, and how fast we can complete one lap — AQR is all about execution speed.

TPS looks at the whole race: how many laps did we complete in 24 hours? While this may sound like the same thing, winning the whole race isn't just about speed on sections of the track; it's also about the pit crew, tire and brake management, optimally using the battery for overtakes, leveraging the competitor’s drag to make the engine last, managing the brakes, dealing with narrow lanes, like the Monaco circuit where ‘parallelism’ isn’t an option, and most importantly: don’t crash!

In summary: TPS is a good measurement for database performance improvement when the focus is on work volume; AQR is best used when the focus is on latency or execution speed.