The risk of analytical queries impacting the OLTP workload depends on where they are executed. For example, consider a cluster in which the primary server processes the OLTP workload and synchronous and asynchronous standby servers process the OLAP workload:
If analytical queries are executed on the primary server, the risk is high because OLTP and OLAP workloads compete for the same computational resources within a single cluster. In this case, an incorrect analytical query may interfere with OLTP workload processing.
If analytical queries are executed on the synchronous standby server, the risk is decreased but not absent because the synchronous standby server may still fail due to an incorrect analytical query and, depending on the server configuration, may affect the primary server.
If analytical queries are executed on the asynchronous standby server, the risk is low. Even if the asynchronous standby server fails due to an incorrect analytical query, the primary server continues to process the OLTP workload.
Recommendations for processing OLAP workloads:
Use the primary server and synchronous standby servers to process stable and verified OLAP workloads, based on available computational resources. Avoid granting direct access rights to analyst groups, as well as executing ad hoc analytical queries, unverified analytical queries, and analytical queries with unpredictable resource usage.
Use asynchronous standby servers to process most of the OLAP workload and to test analytical queries before executing them on the primary server or synchronous standby servers. Cascading standby servers that do not impact the primary server can be used for interactive analysis and ad hoc analytical queries.
Use standalone Postgres Pro servers working with shared network or S3 storages to create, test, and execute ad hoc analytical queries with unpredictable execution times.