Database Specialist

Exp: 5+ years

Database Design & Development: Design, develop, and maintain PostgreSQL schemas that support high-volume, multi-tenant financial workloads — including normalization, data modelling, constraints, declarative partitioning, and appropriate use of native types (JSONB, arrays, ranges, UUID, enums).

SQL & PL/pgSQL Development: Write and review complex SQL, PL/pgSQL functions and procedures, triggers, views, and materialized views to support data extraction, reporting, and system integration needs.

Performance Optimization: Analyze and tune slow queries using EXPLAIN (ANALYZE, BUFFERS), pg stat statements, and auto explain. Design effective indexing strategies (B-tree, GIN, GiST, BRIN, partial, expression, and covering indexes), manage table/index bloat, tune autovacuum, and optimize server parameters (shared buffers, work mem, effective cache size, checkpoint and WAL settings).

Cloud Database Administration (Azure & AWS): Provision, configure, and operate managed PostgreSQL instances in the cloud, including:

◦ Azure: Azure Database for PostgreSQL – Flexible Server; zone-redundant and same-zone HA, read replicas, server parameter groups, built-in PgBouncer, storage/IOPS scaling, private endpoints and VNet integration, Azure Monitor and Log Analytics, Azure Backup and point-in-time restore.

◦ AWS: Amazon RDS for PostgreSQL and Aurora PostgreSQL; Multi-AZ deployments and failover, read replicas and Aurora reader endpoints, parameter/option groups, RDS Proxy, snapshots and PITR, Performance Insights, CloudWatch metrics and alarms, IAM database authentication, Secrets Manager integration.

Standalone / Self-Managed PostgreSQL Administration: Install, configure, harden, and operate PostgreSQL directly on Linux servers (on-premise, VMs, or cloud IaaS) without a managed service layer, including:

◦ End-to-end installation and upgrade of PostgreSQL from packages or source; management of postgresql.conf, pg hba.conf, and pg ident.conf.

◦ OS-level tuning for database workloads — kernel parameters, hugepages, filesystem and storage layout, tablespaces, I/O scheduler, memory and swap configuration, ulimits, and systemd service management.

◦ Building and operating HA clusters using tools such as Patroni, repmgr, or pg auto failover, with etcd/Consul, and HAProxy / Keepalived / virtual IPs for failover and connection routing.

◦ Backup and recovery using pgBackRest, Barman, WAL-G, or pg basebackup, with WAL archiving and validated point-in-time recovery.

◦ Independent monitoring, alerting, and log management (Prometheus + postgres exporter, Grafana, pgBadger, Zabbix, Nagios, or similar), since no cloud-native metrics are provided.

◦ Capacity planning, patching, security hardening, and OS/database lifecycle management.

High Availability, Backup & Recovery: Design and regularly test backup, restore, and disaster recovery procedures. Manage streaming and logical replication, WAL archiving, PITR, failover/switchover runbooks, and defined RPO/RTO targets.

Upgrades & Migrations: Plan and execute PostgreSQL major and minor version upgrades with minimal downtime. Support migrations to PostgreSQL from other engines (e.g., SQL Server, MySQL, Oracle) and between clouds, using tools such as AWS DMS, Azure Database Migration Service, pglogical, or logical replication.

Connection & Scale Management: Implement and tune connection pooling (PgBouncer, pgpool-II, RDS Proxy), read/write splitting, and workload isolation for high-concurrency application traffic.

Data Security & Compliance: Implement and maintain role-based access control, row-level security (RLS), TLS enforcement, encryption at rest via Azure Key Vault / AWS KMS, pgcrypto, and auditing via pgaudit. Ensure database practices meet financial-industry security and compliance requirements.

Extensions & Tooling: Evaluate and manage PostgreSQL extensions relevant to our workloads (pg stat statements, pg partman, pgaudit, pgcrypto, postgres fdw, pg repack, TimescaleDB, PostGIS, Citus as applicable).

Collaboration: Work closely with software developers, DevOps, data analysts, and IT teams to review schema changes and queries before release, and to translate business requirements into sound database solutions.

Troubleshooting: Diagnose and resolve database issues including lock contention and deadlocks, long-running transactions, transaction ID wraparound risk, replication lag, bloat, connection exhaustion, and network/connectivity problems.

Documentation: Maintain comprehensive documentation of schemas, ER diagrams, query patterns, tuning decisions, runbooks, and administrative procedures.

Work Location: In person