Database APM, Query Insights & Observability

1. Cloud-Native Observability & Telemetry Architecture

The GERCIA Platform monitors its managed database tier through Google Cloud SQL Query Insights and Google Cloud Monitoring, delivering real-time visibility into query execution latency percentiles (p50, p95, p99), lock contention, table bloat, and transactional throughput with zero overhead on production containers.

flowchart TD
    subgraph APPLICATION ["Application Tier (Google Cloud Run)"]
        SERVER["Gercia.Server (.NET 10)<br>Minimal APIs with ApplicationName=gercia-server"]
        MIGRATOR["gercia-migrator (.NET 10)<br>Cloud Run Jobs with ApplicationName=gercia-migrator"]
    end

    subgraph CLOUD_SQL ["Google Cloud SQL for PostgreSQL"]
        ENGINE[("PostgreSQL 18+ Engine<br>Native pg_stat_statements & pg_locks")]
        INSIGHTS["Cloud SQL Query Insights<br>(Query Load Metrics & Plan Sampling)"]
    end

    subgraph GCP_OPS ["Google Cloud Operations Suite"]
        MONITORING["Google Cloud Monitoring<br>(CPU, Memory, Connections, Storage)"]
        LOGGING["Google Cloud Logging<br>(PGAudit, Slow Queries > 250ms)"]
        ALERTS["Automated SRE Alerts<br>(alerts@gercia.com.br & devsecops@)"]
    end

    SERVER -- "Npgsql Connection Pool (TLS 1.3)" --> ENGINE
    MIGRATOR -- "Npgsql Connection Pool (TLS 1.3)" --> ENGINE

    ENGINE --> INSIGHTS
    ENGINE --> LOGGING
    INSIGHTS --> MONITORING
    MONITORING --> ALERTS
    LOGGING --> ALERTS

---

2. Deep Query Profiling: Cloud SQL Query Insights

Cloud SQL Query Insights continuously instruments SQL execution directly inside the PostgreSQL kernel without requiring external scraper daemons or third-party agent containers:

Key Metrics Monitored

1. Database Load by Query: Deconstructs CPU wait time, I/O wait time, and lock wait time per normalized SQL statement fingerprint.
2. Normalized Query Latency: Tracks p50, p95, and p99 latency profiles, identifying slow queries in high-volume compounding order and clinical consultation tables.
3. Execution Plan Sampling: Automatically captures execution plans for queries exceeding latency thresholds, providing visual EXPLAIN visualizations without running manual queries in production.
4. End-to-End Application Tagging: Connections initiated by Npgsql are tagged with ApplicationName (gercia-server, gercia-migrator), allowing engineers to isolate query load by service.

Database Flags Configuration for Query Insights

To optimize Query Insights sampling for enterprise clinical workloads:

gcloud sql instances patch gercia-db-prd \
    --project=gercia-gcp \
    --database-flags=cloudsql.enable_pgaudit=on,cloudsql.pg_stat_statements=on,log_min_duration_statement=250

---

3. PGAudit Regulatory Compliance Logging

In compliance with CFM Res. 1.821 (Digital Medical Records) and ANVISA RDC 67/2007 (Compounding Quality Assurance), all administrative and sensitive operations are logged via the pgaudit extension:

• Session Auditing: Tracks ROLE, DDL, MISC, and FUNCTION statements.
• Immutable Storage: Audit logs are streamed directly to Google Cloud Logging and retained in protected, append-only log buckets with retention rules conforming to Brazilian medical record retention mandates (20 years for longitudinal health data).
• Zero Performance Penalty: Execution overhead of pgaudit in Cloud SQL is less than 1.5% under peak compounding load.

---

4. Proactive SRE Alerting & Thresholds

Google Cloud Monitoring evaluates metric thresholds and routes critical incidents to alerts@gercia.com.br:

Alert Policy	Condition / Threshold	Severity	Immediate Action
High CPU Utilization	CPU $\ge 85%$ for 5 consecutive minutes	CRITICAL	SRE investigates Query Insights for runaway query or scale instance tier
Storage Capacity Exhaustion	Free storage $\le 15%$	WARNING	Cloud SQL automatic storage expansion triggers; verify auto-grow
High Connection Count	Active connections $\ge 80%$ of budget	HIGH	Verify connection pool leaks in Gercia.Server
Replication Lag	Regional Standby replica lag $> 10$ seconds	CRITICAL	Investigate network partition in southamerica-east1
Slow Query Spike	Queries $> 250$ms rate exceeds 5% of total throughput	MEDIUM	Query Insights triage and index optimization