Database Insights

Use PostgreSQL tablespaces to distribute tables and indexes across NVMe, SSD, and archive storage. Covers creation, ALTER TABLE SET TABLESPACE, monitoring, and backup.

Run PostgreSQL schema changes without locking: CREATE INDEX CONCURRENTLY, NOT VALID constraints, expand-contract column type changes, and lock_timeout. Covers pg_repack for table rewrites.

Migrate Oracle to PostgreSQL using ora2pg. Covers NUMBER→NUMERIC, DATE gotchas, PL/SQL→PL/pgSQL conversion, sequences, DUAL, NULL handling, and validation steps.

Configure Patroni to use Consul instead of etcd as its DCS for PostgreSQL HA. Covers Consul setup, patroni.yml, service discovery, HAProxy routing, and failover testing.

Use pg_cron to schedule SQL jobs inside PostgreSQL. Covers installation, cron syntax, partition maintenance, materialized view refresh, job monitoring, and RDS setup.

Learn how to convert a standalone PostgreSQL instance to a Patroni high-availability cluster with etcd. Step-by-step guide covering installation, configuration, and failover testing.

Design multi-tenant databases — compare schema-per-tenant, row-level-security, and database-per-tenant approaches

Build a real-time analytics pipeline from PostgreSQL to ClickHouse using CDC — architecture, latency, and schema mapping

Use Aurora Serverless v2 for cost-effective scaling — ACU configuration, scaling behavior, and when it fits

Configure pgBackRest for full, differential, and WAL archiving backups with S3 storage and point-in-time recovery

Measure PostgreSQL table and index bloat, then use pg_repack to reclaim space with zero downtime

Fine-tune PostgreSQL autovacuum to eliminate dead tuples, prevent table bloat, and maintain query performance