MinervaDB FAQ: 40 Essential Answers, Trusted by Data Teams

SQL · PostgreSQL and MySQL: top statements by total database time

-- PostgreSQL 13+ (pg_stat_statements): where database time actually goes
SELECT queryid,
       calls,
       ROUND(total_exec_time::NUMERIC / 1000, 1)                       AS total_s,
       ROUND(mean_exec_time::NUMERIC, 2)                               AS mean_ms,
       ROUND(100 * total_exec_time / SUM(total_exec_time) OVER (), 1)  AS pct_db_time,
       shared_blks_read,
       temp_blks_written,
       LEFT(query, 80)                                                 AS query_head
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

-- MySQL 8.0+ (performance_schema): the same question, answered by statement digest
SELECT digest_text,
       count_star                                AS calls,
       ROUND(sum_timer_wait / 1e12, 1)           AS total_s,
       ROUND(avg_timer_wait / 1e9, 2)            AS mean_ms,
       sum_rows_examined,
       sum_no_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 15;

T-SQL and ClickHouse SQL · Query Store and system.query_log

-- SQL Server 2016+ (Query Store): top queries by total duration over the last 24 hours
SELECT TOP (15)
       q.query_id,
       SUM(rs.count_executions)                                   AS executions,
       SUM(rs.avg_duration * rs.count_executions) / 1000000.0     AS total_s,
       MAX(rs.max_dop)                                            AS max_dop,
       COUNT(DISTINCT p.plan_id)                                  AS plan_count
FROM sys.query_store_query           AS q
JOIN sys.query_store_plan            AS p  ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats   AS rs ON rs.plan_id = p.plan_id
WHERE rs.last_execution_time >= DATEADD(HOUR, -24, SYSUTCDATETIME())
GROUP BY q.query_id
ORDER BY total_s DESC;

-- ClickHouse 23.x+ (system.query_log): heaviest query shapes by bytes read
SELECT normalized_query_hash,
       count()                                   AS runs,
       quantile(0.99)(query_duration_ms)         AS p99_ms,
       formatReadableSize(sum(read_bytes))       AS read_total,
       any(substring(query, 1, 80))              AS sample
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date >= today() - 1
GROUP BY normalized_query_hash
ORDER BY sum(read_bytes) DESC
LIMIT 15;

Shell + SQL · Patroni, replication lag, Group Replication and backup verification

# Patroni 3.x: cluster state, roles, timeline and lag in one view
patronictl -c /etc/patroni/patroni.yml list

-- PostgreSQL 10+: replication lag per standby, measured on the primary
SELECT application_name,
       state,
       sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes,
       replay_lag
FROM pg_stat_replication
ORDER BY application_name;

-- MySQL 8.0+ Group Replication: every member ONLINE, exactly one PRIMARY
SELECT member_host, member_state, member_role, member_version
FROM performance_schema.replication_group_members;

# pgBackRest 2.x: backups are only trusted after a verify and a timed test restore
pgbackrest --stanza=${PG_STANZA} verify
pgbackrest --stanza=${PG_STANZA} info

SQL · PostgreSQL: least-privilege role and row-level security with verification

-- PostgreSQL 15+: least-privilege read role plus row-level security per tenant
CREATE ROLE app_reader NOLOGIN;
GRANT USAGE  ON SCHEMA sales TO app_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO app_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO app_reader;

-- Login role; the secret is injected by the secrets manager, never written in a file
CREATE ROLE svc_reporting LOGIN PASSWORD '${PG_REPORTING_PASSWORD}' IN ROLE app_reader;

ALTER TABLE sales.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY pol_orders_tenant_read
    ON sales.orders
    FOR SELECT
    TO app_reader
    USING (tenant_id = current_setting('app.tenant_id')::BIGINT);

-- Verify: RLS is on and the policy exists
SELECT c.relname, c.relrowsecurity, p.policyname, p.roles
FROM pg_class    AS c
JOIN pg_policies AS p ON p.tablename = c.relname
WHERE c.relname = 'orders';