We are looking for a highly skilled Database Architect with deep expertise in PostgreSQL to design, optimize, and scale our mission-critical data systems. The ideal candidate will work closely with development and operations teams to define data models, tune SQL queries, and architect high-performance, scalable database solutions. This role focuses on database design, optimization, and performance engineering, while actively collaborating with the operations team to recommend tuning strategies and resolve database bottlenecks across environments. Conversant with NoSQL databases is plus.

Key Responsibilities :

1. Database Architecture & Design :

- Architect scalable, secure, and high-performance PostgreSQL database solutions for transactional and analytical systems.

- Design and maintain logical and physical schemas, ensuring proper normalization, entity relationships, and data integrity without compromising performance.

- Define database standards for naming conventions, indexing, constraints, partitioning, and query optimization.

- Architect and maintain database-specific materialized views, stored procedures, functions, and triggers.

- Oversee schema migrations, rollbacks, and version-controlled database evolution strategies.

- Implement distributed transaction management and ensure ACID compliance across services.

2. Performance Optimization & Query Engineering :

- Analyze and tune complex SQL queries, joins, and stored procedures for high-throughput applications.

- Define and monitor indexing strategies, query execution plans, and partitioning for large-scale datasets.

- Lead query optimization, deadlock detection, and resolution strategies with development teams.

- Collaborate with developers to optimize ORM configurations and reduce query overhead.

- Design database caching and sharding strategies that align with application access patterns.

- Review and recommend improvements for connection pooling configurations on the application side to ensure alignment with database capacity planning.

3. Data Modelling, Integrity & CDC Integration :

- Design conceptual, logical, and physical data models aligned with system requirements.

- Ensure data consistency and integrity using constraints, foreign keys, and triggers.

- Define and implement CDC (Change Data Capture) strategies using tools like Debezium for downstream synchronization and event-driven architectures.

- Collaborate with data engineering teams to define ETL and CDC-based data flows between microservices and analytics pipelines for data warehousing use cases.

4. Monitoring, Dashboards & Alerting :

- Define and oversee database monitoring dashboards using tools like Prometheus, Grafana, or pgBadger.

- Set up alerting rules for query latency, replication lag, deadlocks, and transaction bottlenecks.

- Collaborate with operations teams to continuously improve observability, performance SLAs, and response metrics.

- Perform Root Cause Analysis (RCA) for P1 production issues caused by database performance or query inefficiencies.

- Conduct PostgreSQL log analysis to identify slow queries, locking patterns, and resource contention.

- Recommend design-level and query-level corrective actions based on production RCA findings.

- Regularly review metrics to anticipate and prevent scalability or resource utilization issues before they impact live systems.

5. Automation, Jobs & Crons :

- Architect database-level jobs, crons, and scheduled tasks for housekeeping, data validation, and performance checks.

- Define best practices for automating materialized view refreshes, statistics updates, and data retention workflows.

- Collaborate with DevOps teams to ensure cron scheduling aligns with system load and performance windows.

- Introduce lightweight automation frameworks for periodic query performance audits and index efficiency checks.

6. Security, Transactions & Compliance :

- Define transaction isolation levels, locking strategies, and distributed transaction coordination for high-concurrency environments.

- Collaborate with security and compliance teams to implement data encryption, access control, and auditing mechanisms.

- Ensure database design and data storage align with compliance frameworks like DPDP, ISO 27001, or GDPR.

- Validate schema and transaction logic to prevent data anomalies or concurrency violations.

7. Collaboration & Technical Leadership :

- Work closely with backend developers to architect high-performance queries, schema changes, and stored procedures.

- Collaborate with DevOps and SRE teams to define HA/DR strategies, replication topologies, and capacity scaling (advisory role).

- Mentor developers and junior database engineers in query optimization, data modeling, and performance diagnostics.

- Participate in architecture reviews, technical design sessions, and sprint planning to guide database evolution across services.

8. Documentation & Knowledge Sharing :

- Maintain comprehensive documentation for schemas, views, triggers, crons, and CDC pipelines.

- Record rationale for schema design choices, indexing decisions, and tuning recommendations.

- Contribute to internal playbooks for query optimization, connection pooling best practices, and RCA workflows.

- Drive knowledge sharing through internal workshops, design walkthroughs, and code review sessions.

Required Skills & Experience :

- 8+ years of experience in database architecture, design, and performance engineering with PostgreSQL.

- Strong proficiency in SQL and PL/pgSQL, with proven ability to design performant, scalable database solutions.

- Expertise in query optimization, stored procedure design, indexing strategies, and execution plan analysis.

- Demonstrated experience in data modeling, normalization, and schema design for both transactional and analytical workloads.

- Hands-on experience in defining CDC architectures (e.g., Debezium) and data flow integration across microservices or analytics pipelines.

- Solid understanding of replication topologies, sharding, and partitioning from a design and scalability standpoint (advisory role, not operational).

- Experience working with connection pooling (PgBouncer, HikariCP, etc.) and ensuring capacity alignment between applications and database tiers.

- Proficiency in designing and monitoring database dashboards and alerts using tools such as Prometheus, Grafana, pgBadger, or PMM.

- Strong skills in diagnosing and resolving performance bottlenecks, deadlocks, and transactional anomalies.

- Experience with cloud database architectures (AWS RDS, Aurora PostgreSQL, Azure Database for PostgreSQL, GCP Cloud SQL).

- Proven ability to collaborate with developers, DevOps, and data engineering teams to build scalable and resilient data systems.

- Excellent communication and documentation skills, with the ability to articulate complex database concepts to technical and non-technical teams.

Good to Have :

- Experience with NoSQL databases such as MongoDB administration.

- Exposure to Kafka, Airflow, or other ETL orchestration tools.

- Knowledge of containerized deployments (Kubernetes, Helm) and IaC tools (Terraform, Ansible).