DBaaS Optimisation: 5 Proven Levers to Cut Managed Database Cost

On this page

Evidence

The DBaaS optimisation evidence pack: catalog views and provider telemetry, read together

The assessment starts with seven days of engine telemetry placed next to seven days of bill lines. On PostgreSQL-family services the core source is pg_stat_statements, which RDS, Aurora, Cloud SQL and Azure all expose, joined to pg_stat_user_tables, pg_stat_user_indexes and, on PostgreSQL 16+, pg_stat_io. On MySQL-family services it is performance_schema.events_statements_summary_by_digest and the sys schema views. Provider layers add per-statement load over time: Performance Insights on RDS and Aurora, Query Insights on Cloud SQL and AlloyDB, Query Store on Azure Database for PostgreSQL.

-- DBaaS optimisation evidence, PostgreSQL 15+ (RDS, Aurora, Cloud SQL, AlloyDB, Azure): rank fingerprints by the meter they drive
SELECT queryid,
       calls,
       round(total_exec_time::numeric / 1000, 1)                       AS total_s,
       round(mean_exec_time::numeric, 2)                               AS mean_ms,
       shared_blks_read + shared_blks_written + temp_blks_written      AS io_blocks,
       round(100.0 * shared_blks_hit / greatest(shared_blks_hit + shared_blks_read, 1), 1) AS hit_pct,
       rows,
       left(query, 90)                                                 AS sample
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY io_blocks DESC          -- I/O meter first; re-run ORDER BY total_exec_time DESC for the compute meter
LIMIT 20;

-- Unused indexes: paid for on every write as WriteIOPS and WAL bytes, never read
SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- DBaaS optimisation evidence, MySQL 8.0 / 8.4 (RDS, Aurora MySQL, Cloud SQL, Azure Database for MySQL)
SELECT DIGEST_TEXT,
       COUNT_STAR                                   AS calls,
       ROUND(SUM_TIMER_WAIT / 1e12, 1)              AS total_s,
       SUM_ROWS_EXAMINED / GREATEST(SUM_ROWS_SENT, 1) AS examined_per_row_sent,
       SUM_NO_INDEX_USED,
       SUM_CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 20;

SELECT * FROM sys.schema_unused_indexes;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';   -- reads vs read_requests: the I/O meter in two counters

The output of this phase is one ranked list of statement fingerprints, each tagged with the meter it drives and its share of that meter. That list is the artefact the whole DBaaS optimisation engagement is organised around: it decides what is engineered first, it is what the verification phase re-measures, and it is what the quarterly review carries forward.

Lever 1

DBaaS optimisation at the statement: SQL rewrites and index engineering with plan evidence

Most of the compute and I/O meter is usually explained by a small number of fingerprints, and most of those are fixed by an index that matches the predicate and the sort, a rewrite that removes a correlated subquery or a SELECT *, or a partial or covering index that lets the executor answer from the index alone. The proof for every change is the plan with buffers, before and after, on a staging copy with production statistics.

-- Before: the Fig. 1 statement on a 400 GB orders table (illustrative), PostgreSQL 16 on Aurora
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT c.segment, count(*), sum(o.total)
FROM orders o JOIN customers c USING (customer_id)
WHERE o.created_at >= now() - INTERVAL '90 days' AND c.region = 'EMEA'
GROUP BY c.segment;
-- Parallel Seq Scan on orders ... Buffers: shared hit=212,410 read=3,948,117  (I/O meter: ~30 GB read per execution)

-- DBaaS optimisation change set (staged, then applied CONCURRENTLY in a window)
CREATE INDEX CONCURRENTLY idx_orders_created_at_recent
    ON orders (created_at) INCLUDE (customer_id, total)
    WHERE created_at >= DATE '2026-01-01';          -- partial: only the hot range is indexed and paid for
CREATE INDEX CONCURRENTLY idx_customers_region_segment
    ON customers (region, customer_id) INCLUDE (segment);

-- After: Index Only Scan using idx_orders_created_at_recent ... Buffers: shared hit=48,900 read=1,230
-- Validation: rerun EXPLAIN (ANALYZE, BUFFERS); compare shared read, temp written and execution time per call
-- Rollback: DROP INDEX CONCURRENTLY idx_orders_created_at_recent; (no data change, reversible in one statement)

Indexes are not free on a metered service: each one is written on every INSERT and UPDATE, which shows up as WriteIOPS and WAL or binlog bytes, and it adds to GB-month and to snapshot size. The unused-index query above is run before any index is added, duplicate and overlapping indexes are consolidated, and write amplification is measured on the staging copy using pg_stat_user_tables.n_tup_upd and WAL bytes per hour. Predicates that defeat indexes (functions on columns, implicit casts, leading wildcards) are rewritten in the application with the app team, with the fingerprint’s meter share as the priority.

Lever 3

Storage class, instance class and commitments: the DBaaS optimisation decisions made last, from evidence

Only after the top fingerprints are fixed and the meters have moved for seven days does the assessment turn to the instance. The storage decision comes first because it changes the I/O meter directly: gp3 with baseline IOPS versus provisioned IOPS, io2 for sustained write-heavy workloads, Aurora Standard versus I/O-Optimized (the break-even is a function of the I/O share of the bill, which the evidence pack already contains), Cloud SQL SSD versus Hyperdisk tiers, Azure premium SSD versus v2.

The instance decision follows, from CPU p95 and freeable memory after the change set, never from average CPU. Commitments (reserved instances, committed-use discounts, reserved capacity) are bought last, against the right-sized footprint, so that a one-year commitment is not made for a class the workload no longer needs.

# DBaaS optimisation right-size evidence, captured per instance for 7 days after the change set (illustrative)
instance:                 prod-orders-01 (Aurora PostgreSQL 16, db.r6g.4xlarge)
cpu_p95_before / after:   71% / 34%          # CloudWatch CPUUtilization, 1-minute
read_iops_p95:            18,400 / 2,900     # ReadIOPS; the Fig. 1 statement set is fixed
write_iops_p95:           4,100 / 3,700      # WriteIOPS; one unused index dropped
freeable_memory_min:      9.8 GB / 41 GB     # buffer cache no longer churned by scans
io_share_of_bill:         38% / 11%          # from Cost Explorer, Aurora Standard I/O line
top20_p95_ms:             640 / 95           # pg_stat_statements, same fingerprints
decision:                 db.r6g.2xlarge; stay on Aurora Standard (I/O-Optimized no longer breaks even)
rollback:                 modify-db-instance back to r6g.4xlarge; Multi-AZ failover, ~60 s

Backup and log retention are part of this lever: automated backup retention beyond the free window, manual snapshots that were never pruned, and long PITR windows are GB-month lines that grow with the database. They are set from the recovery objectives in your DR plan, which ChistaDATA reviews in the same assessment, and never silently reduced to save money.

Lever 5

Autoscaling and serverless governance: DBaaS optimisation for Aurora Serverless v2, read-replica autoscaling and storage autoscaling

Autoscaling converts inefficiency into spend without an alert. Aurora Serverless v2 scales ACUs on CPU and memory pressure, so an unindexed report ramps the instance and the bill together; read-replica autoscaling adds readers that execute the same scans; storage autoscaling grows volumes that bloat filled. Governance means bounding each of these from measured need: a minimum ACU that covers the OLTP steady state so cold starts do not hurt p95, a maximum ACU set from the highest legitimate peak in the evidence pack rather than the default, replica scaling policies keyed to DatabaseConnections and CPU on the readers, and storage autoscaling paired with bloat monitoring so growth is a signal, not a silent line.

Every scaling event in the evidence window is matched to the fingerprints active at the time. A scale-out that coincides with a release is a regression to send back to the app team with the plan attached; one that coincides with a genuine traffic peak is a sizing input. This distinction is what keeps DBaaS optimisation from becoming a monthly argument about the bill.

Engagement

How ChistaDATA delivers DBaaS optimisation: assessment, engineering, and 24×7 support or managed services

Engagements cover Amazon RDS and Aurora (PostgreSQL and MySQL), Google Cloud SQL and AlloyDB, and Azure Database for PostgreSQL and MySQL; MariaDB-family services follow the MySQL method. Every engagement starts with the pre-engagement questionnaire so the first conversation is about your fingerprints and your meters rather than about generic best practice. Where the assessment finds that the workload is correctly sized and the bill is what the business genuinely needs, the report says so.