PostgreSQL & SQL Excellence Professional

PostgreSQL & SQL Excellence :

- Minimum 6+ years of relevant experience in write production-grade SQL using CTEs, window functions, and set-based processing.

- Ensure SQL code is readable, testable, reusable, and version-controlled (peer-reviewed via PRs).

- Optimise PostgreSQL performance through :

1. Query tuning and execution plan analysis

2. Indexing strategies

3. Partitioning design and maintenance

4. VACUUM/ANALYZE and table statistics management

5. Locking and concurrency handling

- Use EXPLAIN (ANALYZE, BUFFERS) to diagnose slow queries and interpret :

1. Join strategies (nested loop, hash, merge)

2. Sort methods

3. Buffer hits vs reads (I/O hotspots)

- Design effective indexes (btree, hash, GiST, GIN), including composite indexes aligned to filters, joins, and sort order.

- Apply index-friendly query patterns :

1. Sargable predicates

2. Avoiding functions on indexed columns where possible

3. Minimising implicit casts and datatype mismatches

- Implement resilient pipelines with proper error handling, retry logic, idempotency, and reprocessing/backfill capability