Add HA support for libsqld using Kubernetes StatefulSet and Leader Election with Lease API

[tamilhce]

Context:

We are currently exploring deploying libsqld in our environment. we are using libsqld with “bottomless/s3, it does not currently provide built-in High Availability (HA)
To ensure production readiness and resilience, we need to implement an HA pattern for libsqld pods running on Kubernetes.

##Problem:

• libsqld does not have built-in leader election or clustering for HA.
• Multiple instances/pods writing to the same backend (e.g., S3) simultaneously can cause consistency issues.
• Without a controlled failover, queries could be served by multiple instances leading to conflicts.

Proposal:

Implement leader election using Kubernetes Lease API

• deploy libsqld as a Kubernetes StatefulSet
• Use the coordination.k8s.io Lease API to elect a single leader pod responsible for write operations.
• Non-leader pods can either serve as hot standbys
• Use a sidecar or custom entrypoint to handle acquiring/releasing the lease.

Failover & Health Checks

• Use Kubernetes readiness probes to remove an unhealthy leader.
• When a leader pod fails, a new leader should be elected automatically.

[tamilhce]

Any updates or suggestions ?