PostgreSQL Consultant

Description : About the Role :

We are seeking an experienced PostgreSQL Consultant Performance Tuning to optimize, troubleshoot, and enhance the performance, reliability, and scalability of enterprise-grade PostgreSQL databases.

The ideal candidate will have deep hands-on experience in PostgreSQL architecture, query optimization, indexing strategies, and database monitoring, along with a strong understanding of Linux environments and cloud platforms.

This role involves working closely with application developers, DevOps, and infrastructure teams to ensure high database performance and seamless integration across environments.

Key Responsibilities :

• Analyze and tune PostgreSQL databases to improve query execution time, resource utilization, and throughput.
• Identify and resolve performance bottlenecks in SQL queries, indexes, and schema design.
• Conduct in-depth analysis using tools like EXPLAIN / EXPLAIN ANALYZE, pg_stat_statements, and auto_explain.
• Optimize vacuum and autovacuum processes to ensure efficient database maintenance.
• Recommend and implement configuration adjustments (e.g., shared buffers, work_mem, checkpoint tuning).
• Review database architecture and provide recommendations for scalability and high performance.
• Advise development teams on database best practices, including normalization, partitioning, and indexing strategies.
• Support database refactoring, schema optimization, and storage design improvements.
• Participate in capacity planning, sizing, and infrastructure assessments.
• Implement proactive database monitoring using pg_stat_activity, pgBadger, Prometheus + Grafana, or other tools.
• Diagnose and resolve issues related to replication, connection pooling, locking, and resource contention.
• Develop scripts or automation tools for performance tracking, alerting, and diagnostics.
• Conduct root cause analysis of performance issues and recommend long-term fixes.
• Ensure robust backup and disaster recovery strategies using pg_dump, pg_basebackup, or Barman.
• Configure and maintain streaming replication, logical replication, or Patroni for high availability.
• Test and validate database recovery procedures.
• Recommend improvements in failover, replication lag management, and clustering strategies.
• Work closely with developers and DevOps to optimize database access patterns and application queries.
• Support migration, upgrades, and cross-platform database transitions (e.g., Oracle to PostgreSQL).
• Conduct performance assessments and provide consulting to internal and client teams.
• Document best practices, tuning guidelines, and operational runbooks.

Required Skills & Expertise :

(ref : hirist.tech)