[[["容易理解","easyToUnderstand","thumb-up"],["確實解決了我的問題","solvedMyProblem","thumb-up"],["其他","otherUp","thumb-up"]],[["難以理解","hardToUnderstand","thumb-down"],["資訊或程式碼範例有誤","incorrectInformationOrSampleCode","thumb-down"],["缺少我需要的資訊/範例","missingTheInformationSamplesINeed","thumb-down"],["翻譯問題","translationIssue","thumb-down"],["其他","otherDown","thumb-down"]],["上次更新時間：2025-09-03 (世界標準時間)。"],[[["\u003cp\u003eThis document offers guidelines for sizing AlloyDB for PostgreSQL instances, focusing on both OLTP and OLAP workloads.\u003c/p\u003e\n"],["\u003cp\u003eAlloyDB for PostgreSQL features include automatic memory management, adaptive autovacuum, optimized performance settings, low replication lag, and non-disruptive maintenance.\u003c/p\u003e\n"],["\u003cp\u003eSizing considerations include workload type, performance needs, expected data size, and workload scalability, with the primary metric for OLTP workloads being transactions per second (TPS).\u003c/p\u003e\n"],["\u003cp\u003eFor OLTP workloads, deployment can be primary-only or with a read pool, which is beneficial for latency-sensitive reads and can support multiple databases within the same instance, allowing for independent sizing of primary and read pool instances.\u003c/p\u003e\n"],["\u003cp\u003eFor OLAP workloads, the main sizing metric is query performance, with the columnar engine being crucial for accelerating analytical queries, and deployment options include primary-only for HTAP or primary with a read pool for heavy writes and analytical reads.\u003c/p\u003e\n"]]],[],null,["# Sizing and deployment recommendations\n\nThis document provides workload and deployment recommendations for sizing\nAlloyDB for PostgreSQL instances for both online transaction processing (OLTP)\nand online analytical processing (OLAP) workloads.\n\nOverview\n--------\n\nTo help you achieve better database performance, AlloyDB for PostgreSQL provides the following built-in features:\n\n- Automatic memory management\n- Adaptive autovacuum\n- Optimized built-in performance settings\n- Low replication lag\n- Non-disruptive maintenance with sub-second downtime for the primary and zero downtime for the read pool nodes during scale operations\n\nTuning your AlloyDB for PostgreSQL instance for performanceincludes managing the following::\n\n- Sizing your primary and read pool instances correctly\n- Updating flags that impact performance\n\nSizing considerations\n---------------------\n\nBefore you size your AlloyDB for PostgreSQL instance, determine the following:\n\n- **Workload type:** OLTP, OLAP, or HTAP\n- **Performance requirements:** latency and throughput requirements\n- **Expected data size:** the size of the data you plan to store in AlloyDB for PostgreSQL, and the active dataset size\n- **Scale of your workload:** an increase or growth in data size over time\n\nOLTP workloads\n--------------\n\nYou can deploy your AlloyDB for PostgreSQL database as a zonal instance (single node) or as a highly available instance (two nodes in each zone). Optionally, you can also add read pool instances and a secondary cluster in another region for geographically distributed workloads or for disaster recovery (DR).\n\nAlloyDB for PostgreSQL is deployed using cloud-scale, distributed architecture with\ndisaggregated compute and storage. Writes are acknowledged as soon as the write\nahead logs (WAL) files are persisted on the regional storage, while the\nblock materialization is offloaded to storage.\n\nSimilarly, with multi-tier cache architecture, data is automatically placed\nbetween buffer cache, ultra-fast cache, and the intelligent storage engine. Due\nto this multi-tier cache architecture used in AlloyDB for PostgreSQL, input and\noutput operations per second (IOPs) aren't relevant in the context of\nAlloyDB for PostgreSQL to compare with other database systems.\n\nHowever, using transactions per second\n(TPS)/transactions per minute (TPM) can provide a meaningful comparison to\nunderstand the amount of data that can be handled by AlloyDB for PostgreSQL.\n\nThe primary sizing metric is TPS. To estimate the required AlloyDB for PostgreSQL size,\nfollow these steps:\n\n1. **Identify your existing workload.** If you are migrating either from your self-managed PostgreSQL or from other commercial databases, then you may already have the TPS value for your existing workload.\n2. **Analyze your queries.** Identify the most critical queries in your workload and determine their performance requirements.\n3. **Use a tool such as `HammerDB` or `pgbench`.** These tools help to benchmark AlloyDB for PostgreSQL and determine if the machine size satisfies your TPS requirements.\n4. [**Use the AlloyDB for PostgreSQL OLTP Benchmarking Guide.**](https://services.google.com/fh/files/misc/alloydb_oltp_benchmarking_guide.pdf) This guide provides performance data forvarious AlloyDB for PostgreSQL configurations, to find a configuration that meets your TPS requirements.\n5. **Choose an appropriate AlloyDB for PostgreSQL size.** Consider your current data size and future growth expectations.\n\n### Machine size guidelines\n\nThe following example table shows recommendations for data with TPC-C benchmarking that has a\nread-write ratio of approximately 65% reads and 35% writes. When sizing an\nAlloyDB for PostgreSQL instance, you must aim to have a steady state CPU utilization of about\n60-70% to avoid operating system scheduling overhead. This allows some margin\nfor spikes in resource utilization by client applications.\n| **Note:** The sizing recommendations provided in the following table can be considered as general guidelines.\n\n### Deployment types\n\nBased on your workload, you can deploy AlloyDB for PostgreSQL as a primary instance only or\nprimary with read pool instance.\n\n#### Primary only\n\nChoose primary only deployment for the following workloads:\n\n- Write-heavy with low-medium reads\n- Read-heavy queries with light writes\n- Typical OLTP read-write.(60-70% reads, 30-40% writes).\n\nFor more information about machine types, see [General machine size\nguideline](#general-machine-size-guidelines).\n\n#### Primary with read pool instance\n\nIf you choose to deploy a primary with read pool instance, consider the\nfollowing:\n\n- If you have latency sensitive reads, then consider offloading your read queries to read pool instances. You can configure up to 20 nodes across all read pool instances. For more information, see [Create a read pool instance](/alloydb/docs/instance-read-pool-create).\n- Configure multiple read pool instances, if you have more than one database---for example, CRM or Finance in the same instance. Using this strategy helps with effective caching and query performance.\n- You can size your primary and read pool instances differently based on your requirements. For more information about best practices for read pool instances, see [Best practices for improving AlloyDB performance and availability](/alloydb/docs/best-practices-improve-performance-availability#add-read-pool-instances).\n- Add more than one node per read pool instance for high availability.\n- Enable columnar engine selectively in specific read pool instances for read query performance. This doesn't require enabling columnar engine on the primary instance.\n\nConsider using built-in features such as index advisor to help you add\nindexes that can improve query performance.\n\nOLAP workloads\n--------------\n\nFor OLAP workloads, the primary sizing metric is query performance, especially\nqueries that demand full table scans or aggregations. AlloyDB for PostgreSQL includes a\nbuilt-in [columnar engine](/alloydb/docs/columnar-engine/about) that helps in accelerating analytical queries. Enabling\nthe columnar engine by default consumes 30% of memory and automatically uses\nultra-fast cache data.\n\nFor more information about measuring OLAP performance with AlloyDB for PostgreSQL using TPC-H\nworkload, seethe [AlloyDB for PostgreSQL for PostgreSQL OLAP Benchmarking Guide](https://services.google.com/fh/files/misc/alloydb_olap_benchmarking_guide.pdf).\n\n### Deployment types\n\nBased on your workload, you can deploy AlloyDB for PostgreSQL as a primary instance only or\nprimary with read pool instance.\n\n#### Primary only\n\nIf you deploy a primary-only instance, consider the following:\n\n- Use this deployment for both transactions with analytical queries (HTAP).\n- Enable columnar engine to help with OLAP queries.\n- Consider deploying with 16 vCPU or larger machine that provides more memory to store columnar data.\n\n#### Primary with read pool\n\nIf you deploy a primary with read pool instance, consider the\nfollowing:\n\n- If you have heavy writes and also latency sensitive analytical reads with low lag requirements, then deploy the primary instance with HA enabled, and with read pool instances.\n- Enable columnar engine in read pool instances where you run your analytical queries.\n- Configure multiple read pool instances, if you have more than one database---for example, CRM or Finance in the same instance. Using this strategy helps with effective caching and query performance.\n- You can size your primary and read pool instances differently based on your requirements. For more information about best practices for read pool instances, see [Best practices for improving AlloyDB performance and availability](/alloydb/docs/best-practices-improve-performance-availability#add-read-pool-instances).\n- Add more than one node per read pool instance for high availability.\n- Enable columnar engine selectively in specific read pool instances for read query performance. This doesn't require enabling columnar engine on the primary instance.\n\nWhat's next\n-----------\n\n- [Learn about best practices for improving performance and availability](/alloydb/docs/best-practices-improve-performance-availability).\n- [AlloyDB for PostgreSQL for PostgreSQL OLTP Benchmarking Guide](https://services.google.com/fh/files/misc/alloydb_oltp_benchmarking_guide.pdf).\n- [AlloyDB for PostgreSQL for PostgreSQL OLAP Benchmarking Guide](https://services.google.com/fh/files/misc/alloydb_olap_benchmarking_guide.pdf)."]]
