2.2. Deployment Scenarios #
Postgres Pro AXE includes, but is not limited to, the following main deployment scenarios:
A single server that processes OLAP and OLTP workloads simultaneously without high availability.
Figure 2.1.
A cluster in which the primary server processes the OLTP workload and one of the standby servers processes the OLAP workload.
Figure 2.2.
A cluster in which the primary server processes the OLTP workload and standby servers process the OLAP workload balanced by proxima.
Figure 2.3.
You can store Parquet files with the OLAP data in a local, network, or S3 storage.
In all deployment scenarios the pgpro_axe and pgpro_metastore extensions are installed on each server processing the OLAP workload.
2.2.1. Impact on OLTP Workload from Analytical Queries #
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.
2.2.2. Verifying Available Network, Storage, and Computational Resources #
When deploying Postgres Pro AXE, distribute the OLAP workload across servers so that the resulting topology meets performance requirements.
Recommendations for building the topology:
Identify the main OLAP processes and applications, and define time limits for performing ETL operations.
Create a diagram that illustrates data flows between storages.
Ensure that the network and storages provide sufficient throughput to perform operations, with a recommended 30% margin.
Select servers to use for processing most of the OLAP workload and ensure that they have sufficient computational resources to perform regular ETL operations within the required time limits, with a recommended 30% margin.
Ensure that there are no bottlenecks. If there are, consider adding computational resources or modifying the topology.