Data Intensity - PostgreSQL Database Administrator

: Experience : - Require a minimum of 10+ years of dedicated, relevant experience in PostgreSQL Database Administration. - Must possess a strong, authoritative command over both enterprise Linux (RHEL/OEL) and Windows Server environments, actively managing database hosting layers, file system structures, and platform-specific performance variables across both operating systems. Backup & Recovery Engineering : - Design, implement, and audit automated logical (pg_dump, pg_dumpall) and physical backup strategies (pg_basebackup, pgBackRest, Barman), enforcing strict Point-In-Time Recovery (PITR) workflows via Write-Ahead Log (WAL) archiving to satisfy strict corporate RPO and RTO objectives. Capacity & Cloud Cost Planning : - Handle advanced capacity planning, transaction log growth forecasting, data density mapping, and public cloud financial optimization (right-sizing compute nodes, adjusting Provisioned IOPS/throughput limits, and choosing between serverless and provisioned models). Deep Health Telemetry : - Monitor, baseline, and troubleshoot cluster health using native analytical extensions (pg_stat_statements, pgstattuple), catalogs (pg_stat_activity, pg_stat_bgwriter), log parsers (pgBadger), and cloud telemetry engines (AWS CloudWatch, Azure Monitor) alongside third-party APM observability suites. Incident & Lock Management : - Diagnose and mitigate active production incidents, including replication lag starvation, connection pool exhaustion, spinlock contentions, query regressions, and complex relational deadlocks or transactional blocking queues via recursive pg_locks analysis. Security & Compliance Hardening : - Implement and enforce rigorous database infrastructure security protocols, utilizing connection-level encryption (SSL/TLS), Row-Level Security (RLS), Column-Level encryption, strict Role-Based Access Controls (RBAC), advanced extension auditing (pgaudit), vulnerability remediation, and corporate compliance architectures (e.g., SOC2, ISO 27001, HIPAA, GDPR). Enterprise User Management & Security Hardening : - Design and maintain robust security architectures by managing user roles, fine-grained privileges, and group inheritances. - Implement strict role-based access control (RBAC), secure authentication methods (SCRAM-SHA-256, LDAP, Active Directory integration), connection-level encryption (SSL/TLS), and advanced runtime extension auditing (pgaudit). - Enforce complex Row-Level Security (RLS) and Column-Level encryption policies to safeguard sensitive data while meeting regulatory compliances (e.g., SOC2, HIPAA, GDPR). Advanced Table Partitioning & Lifecycle Management : - Architect and manage horizontal scale-out strategies utilizing Native Declarative Partitioning (Range, List, and Hash methods) for very large tables (VLDB) to maximize query performance and data localization. - Design custom partition maintenance strategiesincluding automated partition creation, data archiving, and historical retention pruningsleveraging tools like pg_partman. - Optimize queries to fully exploit partition pruning mechanisms and handle complex global index considerations across deeply nested table hierarchies. Advanced Performance & MVCC Tuning : - Execute advanced performance tuning leveraging a granular understanding of Multi-Version Concurrency Control (MVCC) internal mechanics, table heap page structures (8KB blocks), tuple visibility rules, and index-only scan patterns. - Responsibilities include custom configuration of the Autovacuum daemon (autovacuum_vacuum_scale_factor, autovacuum_vacuum_cost_limit) to proactively eliminate page bloat, transaction ID (XID) wraparound hazards, and unfreezing table locks. Query & Execution Plan Optimization : - Drive complex query refactoring and index optimization strategies, interpreting production execution paths through comprehensive logging parameters and deep command parsing (EXPLAIN (ANALYZE, BUFFERS)). - Mitigate query performance degradation by identifying sequential table scans, resolving external disk merge spills by adjusting transactional allocations (work_mem, maintenance_work_mem), replacing subquery anti-patterns, and choosing appropriate advanced indexing structures (B-Tree, GIN, GiST, BRIN, Partial, or Covering Indexes). High Availability & Connection Orchestration : - Design, implement, and operate zero-downtime clustering fabrics and automatic failover systems using Streaming Replication (Synchronous/Asynchronous), EDB Enterprise Failover Manager (EFM), Patroni, or repmgr. - Architect low-latency client connection pooling frameworks using PgBouncer (Transaction-mode mapping) or Pgpool-II to support massive enterprise concurrent scaling. Migrations & Scale-Out Architecture : - Plan and execute multi-terabyte data migrations, minor engine security rollouts, major version upgrades (via pg_upgrade with - link mode optimization or logical replication), and horizontal partition scale-out designs utilizing Native Declarative Range/Hash Partitioning, foreign data pipelines via Foreign Data Wrappers (FDW), and automated partition handlers (pg_partman). Multi-Cloud Administration : - Manage, optimize, and secure PostgreSQL instances deployed across physical/virtual infrastructures and managed public cloud databases (AWS RDS for PostgreSQL, Amazon Aurora PostgreSQL, and Azure Database for PostgreSQL Flexible Server). Production Operations & RCA : - Provide Tier-3 mission-critical operational production support, leading high-priority Root Cause Analysis (RCA) investigations following production outages, documenting detailed engineering post-mortems, and building script automation via UNIX/Linux Shell scripting (Bash) or Python to deploy self-healing infrastructure patches. Sybase Administration (Add-on) : - Provide secondary operational overview, maintenance support, or migration mapping assistance for legacy enterprise environments running Sybase (SAP ASE/IQ).