PostgreSQL Developer Job Description Template

Schema and Migrations · 23%
This role owns database changes and must deliver them safely and independently. Evaluated in Round 1.

• Weak: Writes schema changes without clear migration safety steps or rollback planning.
• Meets the bar: Designs schema changes and migrations with forward/backward safety and validates approach in a realistic way.
• Strong: Implements zero/low-downtime migration strategies (e.g., phased changes, backfills), handles backward compatibility, and verifies correctness and performance impact.

Query Tuning · 23%
Performance optimization is central to the role and must be grounded in real plan analysis. Evaluated in Round 1.

• Weak: Tunes queries without using EXPLAIN/EXPLAIN ANALYZE or misreads plan bottlenecks.
• Meets the bar: Uses EXPLAIN/EXPLAIN ANALYZE to identify bottlenecks and applies targeted changes (indexes, rewrite, statistics).
• Strong: Systematically isolates root causes (join strategy, cardinality, misestimation), validates with repeated plans/benchmarks, and prevents regressions with regression checks.

Indexing and Plans · 17%
Correct indexing and plan reasoning prevents slowdowns and avoids costly mistakes. Evaluated in Round 2.

• Weak: Suggests indexes without clear understanding of access paths or introduces redundant/ineffective indexes.
• Meets the bar: Selects appropriate index types and understands when they will be used; explains tradeoffs and maintenance cost.
• Strong: Designs composite/covering indexes with selectivity reasoning, accounts for write overhead, and anticipates plan changes due to data distribution.

Transactions and Locking · 15%
Concurrency issues are a common cause of production incidents; the role must prevent and resolve them. Evaluated in Round 2.

• Weak: Cannot explain locking/isolation implications for real workloads.
• Meets the bar: Explains transaction isolation and locking behavior and chooses appropriate strategies to avoid contention.
• Strong: Identifies contention sources (hot rows, lock escalation patterns, long transactions) and proposes concrete mitigations (query changes, batching, isolation choices).

Backup and Recovery · 12%
Reliability depends on validated recovery procedures. Evaluated in Round 3.

• Weak: Describes backups at a high level without discussing restore steps, verification, or failure modes.
• Meets the bar: Builds backup/restore procedures and validates restores with clear success criteria.
• Strong: Plans for failure scenarios (point-in-time recovery where relevant), tests regularly, and documents evidence and runbooks for recovery.

PostgreSQL Security · 10%
Database safety requires least-privilege access and secure operational practices. Evaluated in Round 3.

• Weak: Uses broad privileges without justification or misunderstands role/privilege boundaries.
• Meets the bar: Applies least-privilege roles and privileges appropriately and avoids risky grant patterns.
• Strong: Implements secure access patterns (role separation, controlled schema ownership, object-level permissions) and anticipates common privilege escalation pitfalls.