possebon

-- Find what's blocking autovacuum across a PostgreSQL cluster.
--
-- Autovacuum can only reclaim dead tuples older than the oldest running
-- transaction *anywhere in the cluster*. One old idle-in-transaction session,
-- or one inactive replication slot holding back xmin, can silently stall
-- reclaim across dozens of tables.
--
-- This query returns both culprit classes in one result set:
--   kind = 'backend'           → a client-backend transaction older than 10 minutes
--   kind = 'replication_slot'  → a slot pinning xmin or catalog_xmin

possebon

-- pg_wait_sampling quickstart
-- The Postgres equivalent of Oracle ASH or SQL Server wait stats.
-- Samples wait events from shared memory at sub-millisecond intervals.
--
-- Docs:  https://github.com/postgrespro/pg_wait_sampling
-- Works on PostgreSQL 11+.
-- Blog post: https://www.linkedin.com/in/fernando-possebon/

-- 1. Add to shared_preload_libraries (requires a Postgres restart).
--    Edit postgresql.conf, or use ALTER SYSTEM:

possebon

// Correlation ID echo for async WebSocket responses.
//
// Context: a single WebSocket per instance carries two kinds of commands
// (user queries + background health polls). Without a request-level ID,
// whichever waiter grabbed the response first "won" and the other timed out.
//
// The fix was one field: correlationId, generated by the hub, echoed verbatim
// in every response. The hub routes responses to the correct waiter by that
// field, never by sessionId.
//

possebon

-- SQL Server Query Store week-over-week regression diff
-- Compares avg_duration between the last 7 days and the 7 days before.
-- Returns queries that slowed by >= 2x and ran >= 100 times in each window.
--
-- Works on SQL Server 2016+ with Query Store enabled.
-- Blog post: https://www.linkedin.com/in/fernando-possebon/

DECLARE @now              DATETIME2 = SYSUTCDATETIME();
DECLARE @this_week_start  DATETIME2 = DATEADD(day, -7,  @now);
DECLARE @last_week_start  DATETIME2 = DATEADD(day, -14, @now);

possebon

// split-go-batches.go — line-by-line GO batch splitter for SQL Server clients.
//
// GO is not a SQL Server keyword. It's a client convention. If you write your
// own SQL Server tooling (like I did with a Go-based DBA diagnostic agent),
// you need to split on GO before sending each batch over TDS. The naive regex
// on substring "GO" breaks on strings like 'GOPHER'. This version matches GO
// only when it's on its own line, case-insensitive, with whitespace trimmed.
//
// Written up in: https://www.linkedin.com/in/fernando-possebon
//

possebon

Security Checklist

• Infisical Machine Identity created
• All secrets migrated to Infisical
• No hardcoded credentials in repository
• SSL certificates auto-renewing
• Firewall rules configured
• Keycloak admin password changed
• Database passwords rotated
• Backup encryption enabled

possebon

Essential Commands Quick Reference

# Initialize infrastructure
tofu init && tofu apply

# Deploy all services
ansible-playbook playbooks/site.yml

# Deploy single environment

possebon

Infrastructure Repository File Structure

infrastructure/
├── opentofu/
│   ├── main.tf           # Server provisioning
│   ├── secrets.tf        # Infisical integration
│   ├── versions.tf       # Provider configuration
│   └── variables.tf      # Input variables
├── ansible/

possebon

GitHub Actions GitOps Workflow

# .github/workflows/deploy.yml
name: Deploy Infrastructure

on:
  push:
    branches: [main]

possebon

Complete Deployment Pipeline

# Step 1: Bootstrap Infisical credentials
export INFISICAL_CLIENT_ID="your-client-id"
export INFISICAL_CLIENT_SECRET="your-client-secret"

# Step 2: Provision infrastructure
cd infrastructure/opentofu
tofu init