[Home](https://hmbastion.com/) [Blog](https://hmbastion.com/blog/) Infrastructure Infrastructure 5 min read # Size a SQL database around its working set and expensive queries Database sizing is more than comparing the size of a data directory with a server’s RAM. A small database can perform badly through repeated scans or lock contention, while a larger one can serve a focused workload efficiently. Investigate the work the application asks the database to do before choosing the next resource allocation. HM Bastion Editorial Published 2026-10-26 ## In this guide - [Describe the workload before choosing a size](https://hmbastion.com/blog/sql-database-sizing-query-bottlenecks/#describe-the-work-and-data) - [Find the query that consumes useful time](https://hmbastion.com/blog/sql-database-sizing-query-bottlenecks/#find-the-query-that-matters) - [Change one cause at a time](https://hmbastion.com/blog/sql-database-sizing-query-bottlenecks/#choose-a-reversible-change) - [Validate under concurrency and keep recovery in the plan](https://hmbastion.com/blog/sql-database-sizing-query-bottlenecks/#test-growth-and-recovery) [All hosting guides](https://hmbastion.com/blog/) ## The practical takeaway Identify expensive and repeated queries, inspect their plans safely, measure memory and concurrency together, and change the query or resource that actually limits useful work. ## Describe the workload before choosing a size List the database engine, application, main tables and important user journeys. A reporting job that scans historical data behaves differently from a small lookup behind every web request. Record normal request volume, concurrent jobs, data growth and the time-sensitive operations. Include background tasks, imports and exports: they may create the busiest periods even when public traffic is quiet. Keep credentials and personal data out of the sizing notes you share. Estimate storage beyond current table data. Indexes, transaction logs, temporary work, growth and maintenance can all need space. Backups also require a deliberate destination and recovery procedure. A storage figure on a hosting card is the capacity allocated to the service, not a statement that every byte should be occupied by the live database. Check free space trends and leave room for the operations your engine and application actually perform. The working set is the data and indexes a workload repeatedly touches. It can be much smaller than the full historical database, and it can change when a report or promotion becomes popular. Memory helps when useful pages can be reused, but a generic rule that all data must fit in RAM is too crude. Observe reads, cache behaviour and request duration during representative work rather than applying a universal memory percentage. ## Find the query that consumes useful time Start from a slow application journey and relate it to database activity. One exceptionally slow query is visible, but a modest query repeated hundreds of times can consume more total work. Group similar queries and compare frequency, duration, rows examined or returned, and the periods when users notice the delay. Use the logging and diagnostic facilities available for your engine, with appropriate access and retention for potentially sensitive query data. An execution plan describes how the engine expects to retrieve and combine data. Read the access path, estimated rows, joins and sorting work together. A scan is not automatically wrong: for a small table or a broad query, it can be sensible. The question is whether the work matches the result the application needs. Compare estimated and observed behaviour when suitable tooling is available, and note the exact schema and engine version. | Symptom | Possible cause | Evidence to gather | | --- | --- | --- | | Many rows processed for a small result | Query shape or missing useful index | Plan, predicates and examined rows | | Delay rises with simultaneous writers | Lock or transaction contention | Waiting transactions and transaction duration | | Repeated identical lookups | Application query repetition | Grouped query frequency and request trace | | Slow large sorts or reports | Working data exceeds available memory | Temporary work, memory pressure and report size | Be careful with tools that execute a statement while measuring it. PostgreSQL EXPLAIN ANALYZE runs the query, and data-changing statements can change data unless the investigation deliberately prevents that outcome. MySQL’s analysis features also require understanding the chosen statement and version. Use a representative test environment where possible. Never paste an unfamiliar diagnostic command into production merely because it contains the word explain. ## Change one cause at a time An index can reduce the rows needed for a useful lookup, but it also occupies storage and adds maintenance work to writes. Design it around the actual predicates and ordering, then measure the relevant reads and writes. Creating indexes on every column creates cost without proving benefit. Large schema operations need their own plan because locking, temporary space and completion time depend on the engine, operation and workload. Application changes can remove work before the database receives it. Fetch only needed columns, avoid retrieving the same relationship repeatedly, and use a deliberate pagination strategy for large lists. Keep transactions focused so they do not hold resources while waiting for unrelated network calls. These choices often improve both the average request and the worst periods of contention. Validate the result returned to users as well as the reduced query duration. Increase resources when the evidence supports it. More RAM may help a useful working set, while additional CPU can help work that can actually run in parallel. Neither change automatically resolves a long-held lock or an unnecessary full scan. When choosing hosting, compare the exact memory, storage and processor allocation, then include the operating system and other services that share the machine. Document why that allocation fits the measured workload. ## Validate under concurrency and keep recovery in the plan - Preserve the schema, engine version and baseline measurements. - Use representative data distributions rather than only tiny sample tables. - Compare the same query and workload after one controlled change. - Measure successful operations, latency and errors during concurrent work. - Verify backups and a restore procedure before risky maintenance. A test with one connection does not describe every production peak. Repeat the relevant workload with realistic concurrent requests and scheduled tasks, and stop when the agreed error or resource limits are reached. Use synthetic or properly protected test data. Do not involve an external payment service or another party’s systems in a generated workload without arranging that participation. Keep the load generator separate enough that its own limits are visible. Finally, turn the diagnosis into a short maintenance record. Keep the query fingerprint, plan, observed constraint, chosen change and rollback path. Add storage growth and recovery requirements to the next review date. This gives future maintainers a reasoned starting point when the workload changes, instead of a mysterious memory setting or index that everyone is reluctant to touch. Product examples use the published HM Bastion catalogue. Availability, prices and selectable options are shown when you configure an order. ## Choose your next foundation. [Compare VPS resources](https://hmbastion.com/hosting/?category=cloud) [Read the server-resource guide](https://hmbastion.com/blog/understand-server-resources/) [Discuss a database workload](https://hmbastion.com/support/) ## Sources and further reading - [MySQL documentation: optimising queries with EXPLAIN](https://dev.mysql.com/doc/refman/8.4/en/using-explain.html) - [PostgreSQL documentation: using EXPLAIN safely](https://www.postgresql.org/docs/current/using-explain.html) - [MySQL documentation: the InnoDB buffer pool](https://dev.mysql.com/doc/refman/8.4/en/innodb-buffer-pool.html) ## Keep exploring [ Security 5 min read ## Keep HTTPS working through the whole certificate lifecycle Plan domain validation, certificate renewal, deployment and external checks so HTTPS remains correct after the first successful installation. ](https://hmbastion.com/blog/https-tls-certificate-lifecycle/) [ Infrastructure 5 min read ## IPv4, IPv6 and firewalls: review the paths into your server Inventory listening services, public addresses and firewall policies across both IP families before publishing an application or changing network access. ](https://hmbastion.com/blog/ipv4-ipv6-firewall-exposure/)