Job Title: Senior PostgreSQL Database Designer & Administrator (IoT Solutions)
Role Overview:
We are looking for a highly skilled PostgreSQL Database Designer and Administrator to lead the architecture and management of our IoT data platform. You will be responsible for designing schemas that handle high-velocity ingestion from millions of devices while ensuring sub-second query performance for real-time dashboards. You will bridge the gap between Database Design (structuring complex sensor hierarchies) and Administration (maintaining 99.99% uptime and high-frequency writes).
Key Responsibilities:
1. Database Architecture & Design:
Time-Series Modeling: Design and implement optimized schemas for time-series data using partitioning and extensions like Timescale DB.
IoT Metadata Management: Develop relational models for "Digital Twins," mapping physical assets, sensors, and their hierarchical relationships.
Schema Evolution: Manage zero-downtime schema migrations for high-availability production environments.
Data Lifecycle Strategy: Design automated data retention and archiving policies (cold storage) to manage multi-terabyte growth without performance degradation.
2. Performance & Ingestion Tuning:
High-Frequency Ingestion: Optimize the database for massive "insert-heavy" workloads, utilizing techniques like COPY commands, unlogged tables, and asynchronous commits where appropriate.
Query Optimization: Identify and fix slow-running queries using EXPLAIN ANALYZE and pg_stat_statements.
Indexing Strategy: Implement advanced indexing (BRIN for time-series, GIN for JSONB telemetry, and partial indexes) to balance read speed with write overhead.
3. Database Administration & Security:
High Availability (HA): Configure and maintain robust HA clusters using Patroni, repmgr, or streaming replication.
Disaster Recovery: Manage point-in-time recovery (PITR) and regular backup validation using tools like pgBackRest.
Resource Management: Fine-tune memory settings (shared_buffers, work_mem) and monitor system health (CPU, I/O wait, disk bloat).
Security & Compliance: Enforce Row Level Security (RLS) for multi-tenant IoT environments and ensure compliance with GDPR/HIPAA data encryption standards.
Required Skills & Qualifications:
- Experience: 5+ years of experience in PostgreSQL administration and design, specifically with high-scale or IoT-related datasets.
- IoT Ecosystems: Deep understanding of IoT protocols (MQTT, Kafka) and how they interface with relational databases.
- Extension Expertise: TimescaleDB (for time-series analytics).
- Programming: Proficiency in PL/pgSQL for stored procedures and triggers, as well as .Net programming language or Shell for automation.
- Cloud Platforms: Experience managing PostgreSQL on Linux servers and self-managed on Kubernetes.
Preferred Qualifications:
- Experience with PgBouncer for connection pooling in high-concurrency device environments.
- Knowledge of CDC (Change Data Capture) for syncing data with downstream analytics or data lakes.
Pay: ₹20,000.00 - ₹25,000.00 per month
Benefits:
- Health insurance
- Leave encashment
- Provident Fund
Work Location: In person
