Were aggregation queries executed on the same database where active writes were happening? Were there any side effects? How does PostgreSQL react to data changes during a long aggregation?

In PostgreSQL, aggregation queries can be executed concurrently with write operations thanks to the MVCC (Multi-Version Concurrency Control) mechanism. This means that each query sees a consistent state of the data at the start of the transaction.

If the aggregation takes a long time, it does not block writes and does not see changes made after it started. Therefore, side effects such as inconsistent data or locks usually do not occur.

However, if the aggregation requires very fresh data, transaction isolation levels or special locks can be used, but this may impact performance.

Example:

BEGIN;
SELECT COUNT(*) FROM orders WHERE created_at >= now() - interval '1 day';
COMMIT;

This query will see the state of the orders table at the start of the transaction, even if other transactions add new records during its execution.