Databases as locks, heap tables and more — results of the latest hackathon

Once again, developers across our company came together for another iteration of our monthly hackathon — a day dedicated to exploration, learning, and discussion. This time, the spotlight was on SQL databases. We followed a familiar structure, much like our previous Git and Shell events:

1. We compiled a long list of interesting topics and assigned them to different team members.
2. Everyone had 60–90 minutes to prepare a short session on their topic.
3. We reconvened to present, discuss, and share insights.

This format allows us to dive into new topics, refine our teaching skills, and learn in a collaborative, low-pressure environment. At the end of the day, each participant walks away with their own key takeaways. Today, I want to share two insights that stood out to me from this deep dive into databases.

Databases for Lock Management

While discussing transactions, database servers, and management systems, we explored the idea of using a database as a mutex or lock to prevent parallel execution of a given task. Initially, this was just a thought experiment — since locks are often managed in memory — but we discovered a real-world scenario where this approach is valuable: serverless computing.

On platforms like Vercel, developers have little control over the horizontal scaling of their applications. The platform may spin up hundreds of instances during peak traffic while shutting everything down when idle. In such cases, a central database (assuming it's not distributed) can act as a resilient locking mechanism:

const hasLock = await db.transaction(async (tx) => {
  // Retrieve locks newer than 2 minutes
  const currentLockCount = await tx
    .select()
    .from(lock)
    .where(gt(lock.lastTouched, sql.raw(`(NOW() - INTERVAL 2 MINUTE)`)))
    .for("update")
    .execute();

  if (currentLockCount.length > 0) {
    return false;
  }

  await tx
    .insert(lock)
    .values({ lastTouched: sql`NOW()` })
    .execute();

  return true;
});

if (!hasLock) {
  return { kind: "LOCK_NOT_FREE" };
}

try {
  // Ensuring that only one instance runs this code
  console.log("I'm alone!");
} finally {
  await db.delete(lock).execute();
}

One drawback of this approach is the possibility of orphaned locks: If a process crashes before releasing its lock, the entry remains in the database. To mitigate this, it's crucial to implement a timeout period that determines how long a lock remains valid.

Heap Tables and Wasted Disk Space

Naturally, our discussions covered indexes, keys, and constraints. One lesser-known fact that stood out: tables without a primary key are stored as heaps.

A heap table lacks natural order, meaning there’s no direct way to reference rows (except by row number). More importantly, deleting rows from a heap table does not immediately free up disk space. Instead, deleted rows are merely marked as unavailable, but they continue to occupy storage.

For frequently updated tables, this can lead to serious inefficiencies. In fact, during our hackathon, we identified two instances where tables in our databases did not have an obvious primary key and were thus stored as heaps, that had grown massively despite only containing a few hundred rows — leading to performance issues and excessive disk usage.

The solution? Introducing a unique key, even if it is just a hash of the content, which forces the table to use a clustered index, preventing unnecessary disk bloat. The more you know...

Never Stop Learning

This hackathon reinforced an important lesson: no matter how long you’ve been working with a technology, there are always new things to learn.

If you ever have the opportunity to participate in a similar event, don’t hesitate just because you think you already know the topic well. Discussions, experiments, and research will always uncover fresh insights — sometimes in the most unexpected places.