TASK
Implementation
A naive ALTER TABLE ADD COLUMN NOT NULL DEFAULT 'x' rewrites the entire table and holds an exclusive lock, blocking all reads and writes. Zero-downtime migrations use PostgreSQL features to make schema changes without taking the table offline.
Implement a node that performs lock-safe schema changes:
// Create index without locking the table
{ "type": "create_index", "msg_id": 1,
"table": "users", "column": "email", "concurrently": true }
-> { "type": "index_created", "in_reply_to": 1,
"index": "idx_email", "duration_seconds": 120,
"table_locked": false }
// Add NOT NULL column with default — no table rewrite needed
{ "type": "add_column", "msg_id": 2,
"table": "users", "column": "status",
"default": "active", "nullable": false }
-> { "type": "column_added", "in_reply_to": 2,
"duration_seconds": 0.1, "table_rewritten": false }
// Migrate data in batches to avoid long locks
{ "type": "migrate_data", "msg_id": 3,
"table": "users", "batch_size": 1000, "total_rows": 10000 }
-> { "type": "data_migrated", "in_reply_to": 3,
"total_rows": 10000, "batches": 10, "table_locked": false }Lock-aware migration: if a lock cannot be acquired within max_lock_duration_ms, abort and roll back rather than blocking the application.
Sample Test Cases
Create index concurrentlyTimeout: 5000ms
Input
{
"src": "admin",
"dest": "migrations",
"body": {
"type": "create_index",
"msg_id": 1,
"table": "users",
"column": "email",
"concurrently": true
}
}Expected Output
{"type": "index_created", "in_reply_to": 1, "index": "idx_email", "duration_seconds": 120, "table_locked": false}Add column without table rewriteTimeout: 5000ms
Input
{
"src": "admin",
"dest": "migrations",
"body": {
"type": "add_column",
"msg_id": 1,
"table": "users",
"column": "status",
"default": "active",
"nullable": false
}
}Expected Output
{"type": "column_added", "in_reply_to": 1, "duration_seconds": 0.1, "table_rewritten": false}Hints
Hint 1▾
CREATE INDEX CONCURRENTLY builds the index without holding an exclusive table lock
Hint 2▾
Adding a column with a server-side default does not rewrite the table in PostgreSQL 11+
Hint 3▾
Batch data migration: process rows in chunks (e.g. WHERE id BETWEEN x AND x+1000); sleep between batches
Hint 4▾
Lock-aware migration: set a short lock timeout; abort if a lock cannot be acquired quickly
Hint 5▾
table_locked: false confirms the operation did not block read/write access
OVERVIEW
Theoretical Hub
Concept overview coming soon
Key Concepts
concurrent indexlock-free migrationsbatch data migrationlock-aware migration
main.py
python
1
2
3
4
5
6
7
8
9
10
11
12
13
#!/usr/bin/env python3
import sys
import json
def main():
# Your implementation here
for line in sys.stdin:
msg = json.loads(line)
print(json.dumps(msg), flush=True)
if __name__ == "__main__":
main()