Senior PostgreSQL PL/pgSQL Developer

A Senior PostgreSQL PL/pgSQL Developer specializes in designing,
optimizing, and maintaining complex PostgreSQL databases and developing robust storedprocedures, triggers, and functions using PL/pgSQL.

Key Responsibilities

1. Database Design and Development
o Design, implement, and optimize complex database schemas for performance andscalability.o Develop advanced stored procedures, functions, and triggers using PL/pgSQL.o Write and maintain DDL (Data Definition Language) and DML (Data ManipulationLanguage) scripts.2. Performance Optimizationo Analyze and optimize query performance using EXPLAIN, ANALYZE, and executionplans.o Implement indexing strategies, partitioning, and materialized views to enhancedatabase performance.o Optimize storage and query performance for large datasets.3. Data Integration and ETLo Develop ETL pipelines for data extraction, transformation, and loading betweensystems.o Integrate data from multiple sources using custom SQL scripts and tools like Talend,or Python or Airflow4. Data Security and Governanceo Implement data security policies, including encryption, role-based access control, andauditing.

5. Automation and Maintenance
o Automate routine tasks like backups, indexing, and monitoring using cron jobs orscripting tools.o Perform regular database maintenance, including vacuuming, reindexing, andanalyzing tables.6. Monitoring and Troubleshootingo Monitor database performance and availability using tools like pgAdmin,pg_stat_activity, and third-party monitoring solutions (e.g., New Relic, Grafana, orNagios).o Troubleshoot database-related issues and resolve bottlenecks in real-time.7. Collaborationo Work closely with application developers to design efficient database queries andschemas.o Provide mentorship and guidance to junior database developers.

Skills and Expertise

Core Technical Skills
1. PostgreSQLo Advanced knowledge of PostgreSQL features, including triggers, partitions, andconstraints.2. PL/pgSQL Developmento Proficiency in writing and debugging complex PL/pgSQL functions, procedures, andtriggers.o Knowledge of exception handling and control structures in PL/pgSQL.3. SQL Optimizationo Strong skills in writing and optimizing complex SQL queries.o Proficiency in using EXPLAIN and ANALYZE to debug and optimize queries.4. Database Administrationo Experience with backup and recovery strategies (pg_dump, pg_restore).o Knowledge of database replication, high availability, and clustering solutions (e.g.,Patroni, PgBouncer).5. Data Integration Toolso Familiarity with ETL tools like Apache NiFi, Talend, or Python-based pipelines (e.g.,Pandas or SQLAlchemy).6. Scripting and Automationo Experience in scripting languages like Python, Bash, or Perl for database automation.o Familiarity with job scheduling tools like cron, Airflow, or similar.

Tools and Platforms

• Monitoring: pgAdmin, pg_stat_activity, Zabbix, New Relic, Grafana.
• Version Control: Git, Bitbucket.• DevOps Tools: Docker, Kubernetes (optional for cloud-based setups).

Experience Requirements

• 7+ years of experience in PostgreSQL development and database management.
• Proven track record of optimizing and scaling large databases.