What Happens When You Build a Database for One Job Only?

In the modern world, anything that moves generates this exact type of data: where something is(space) and when it was there(time).
It could be a runner running around the city, a drone trying to deliver a product, or you wandering through a maze not knowing which direction you are running.

When you need to store this data, the conventional choice is to reach for a general-purpose relational database like PostgreSQL or an embedded store like SQLite. And for many applications, that is a perfectly reasonable choice. General-purpose databases can certainly handle high ingestion rates when tuned properly.

However, general-purpose databases are designed to support a broad range of workloads: mutable tables, random-access updates, secondary indexes, and relational joins.

potDB isn't built on the premise that general-purpose databases are inherently unsuitable. Instead, it explores an alternative: what if your workload is extraordinarily predictable, and you design a lightweight storage engine optimised specifically for that narrower use case?

So, let me introduce you to potDB.

Well, before that, the initial idea for potDB wasn't this. I had an old project lying around, which was a clone of SQLite written in C. Initially, I was thinking of adding some features like crash recovery and transactions to it to make it something more than just a clone. Then I thought, since I'm planning to work on this anyway, why not build my own custom database? So, after snooping around the internet, I decided to build a simple database called potDB. And no, "pot" isn't the pot you might be thinking of, it stands for Position Over Time.

Chapter 1: The Requirements

Let's start with the what we want from this database i.e, requirements.

Functional Requirements:

1. Strict Mapping: Strictly map timestamps to spatial coordinates: timestamp -> (x, y) or (x, y, z).
2. Chronological Ingestion: Data always arrives in real-time order. No out-of-order writes or editing past points.
3. Smart Flushing: Flush data when the buffer is full OR when telemetry time progresses past a configured window , supported by cooperative polling so slow streams don't get trapped in RAM.
4. Range Queries: Quickly filter points within a specific time window and spatial bounding box without reading the entire file.
5. Data Integrity: Checksum every chunk to catch corrupted data or torn writes immediately.
6. Configurable Durability: Let the user choose between extreme speed or maximum safety.
7. Crash Recovery: If power cuts unexpectedly, already-saved data must never be corrupted, and the database should recover cleanly on restart.

Non-Functional Requirements:

1. Small Memory Footprint: Must use a lean, predictable memory footprint so it can comfortably fit onto resource-constrained edge devices.
2. Fast Writes: Ingestion must be fast without being bogged down by complex tree rebalancing.
3. Zero Third-Party Dependencies: Written in clean C relying solely on standard POSIX system interfaces.
4. Portable: The database files must follow explicit byte-level serialisation so a file written on an ARM board is readable on an x86 laptop.
5. Truly Embedded: Lives directly inside the client application as a library with no background database server to manage.

These requirements come from the primary use cases I had in mind when envisioning potDB. For example, the database should easily fit onto a smartwatch to map the route the user is running, or run on low-power, resource-constrained edge devices.

Choosing this niche means recognising that while a general-purpose database can serve most use cases, potDB is specifically designed around having a small memory footprint and working smoothly on edge hardware. Again, this is not built around the idea that it must solve every problem for every use case.

To put its scale into perspective:
We are talking about an engine with a modest, predictable memory footprint that compiles to a compact library, yet can comfortably ingest high volumes of coordinates on platforms ranging from embedded devices to laptops.

Chapter 2: Designing the Database

When designing a database, the first question that comes to mind is: which data structure should we use to store the data?

There are many data structures to consider when it comes to time-series databases, such as Log-Structured Append-Only files, R-Trees, and LSM-Trees. Choosing the right data structure comes down to handling the time axis and the space axis efficiently.

For the time axis, the primary architectural choices are either a Chunked Append-Only Log (grouping sequential samples into immutable contiguous blocks) or a Log-Structured Merge-tree / LSM variant (such as InfluxDB's TSM or Cassandra's SSTables).

LSM-trees and complex merge architectures are very useful when handling out-of-order data and frequent overwrites. But since our timestamps are monotonically increasing in chronological order, out-of-order writes are practically non-existent. Therefore, we will go with a Chunked Append-Only Array.

The Chunked Append-Only Log

The log is the foundation of this database.
potDB organises all data as a strictly sequential, binary append-only stream.
It is append-only because this database has no updates. It doesn't make sense to modify a position that has already happened in the past!

Each data point is represented as a key-value pair:

• Key: Timestamp
• Value: Spatial coordinates $(x, y)$ or $(x, y, z)$

potDB supports both 2D and 3D coordinates, configured during database initialisation.

This takes care of inserting data. But the next question is: should we just dump everything into one giant flat file and call it a day?
The reading side is still a challenge. If we dump everything into a single massive file, how will we read or query it efficiently?

At the very start of the database file sits a 16-byte File Header, written during database creation. It records the database magic number (0x42445450, which spells 'PTDB' in little-endian), the version, the spatial dimension (2D or 3D), and the time unit.

Following the file header, coordinates are grouped into neat, bite-sized packages called chunks (for example, 500 points at a time).

Every chunk consists of two parts: a Chunk Header (44 bytes for 2D, 52 bytes for 3D) and the Payload (the actual coordinates).

Why do we need each of these fields?

The Chunk Header tells us everything we need to know about what's inside:

• Magic Number (2 Bytes): At the very start of every chunk, we store the constant 0x4348(translates for HC in little Endian). If these bytes do not match, the chunk is treated as corrupted.
• Count (2 Bytes): Tells us exactly how many coordinates are inside this chunk.
• Time Window (16 Bytes): Records the exact starting and ending time of all points in this chunk (start_time and end_time) for fast range pruning.
• Bounding Box (MBR) (16 Bytes for 2D / 24 Bytes for 3D): Tracks the lowest and highest coordinates ($X, Y$ for 2D; $X, Y, Z$ for 3D) to draw a tight bounding box around all points in the chunk.
• Payload Size (4 Bytes): Tells us the exact number of bytes the coordinate data takes up on disk. If the time window or bounding box don't match what you're looking for, the database can instantly skip over the entire chunk on disk in one leap, without wasting time reading it into RAM.
• Checksum (CRC-32) (4 Bytes): Before saving the chunk, we compute an IEEE 802.3 CRC-32 checksum covering both the chunk header metadata (excluding the checksum field itself) and the coordinate payload. If the power cuts while writing or a sector on your storage goes bad, the seal won't match. potDB catches this instantly and safely ignores the bad chunk instead of feeding you garbage data.
• The Payload: The contiguous array of binary coordinate points.

Chapter 3: Storage and Persistence

Based on everything we've discussed up to this point, you probably have a clear idea of what and how we are storing data.
But where are we storing it?

Initially, it goes into RAM. But RAM is volatile, anything in RAM vanishes the moment the system shuts down or loses power.
What non-volatile memory comes to our rescue? The disk (or ROM/Flash).

So, problem solved, right? Not quite.
Even though persisting everything immediately seems like the safest approach, writing every single coordinate straight to disk is terribly inefficient due to disk I/O overhead.

Yet, we still need to persist the data safely.
Like every database predecessor before us, we balance both RAM and disk. Remember the chunks we discussed earlier? Leveraging the concept of chunks, we keep an in-memory buffer matching the chunk size. We accumulate incoming coordinates in user-space RAM until the buffer is full, and only then do we flush the entire chunk sequentially. Using write(), we send the chunk to the operating system, which handles writing it to permanent storage.

Chapter 4: Crash Recovery and Durability

What happens if, mid-flight, a drone runs out of battery and crashes? If the chunk buffer is large and the system shuts down before flushing, you lose the trajectory of the drone. I know if a drone crashes, the trajectory might be the least of your worries, but as developers, we should definitely care about protecting that data.

This brings us to crash recovery and data durability.

Traditionally, relational databases like PostgreSQL handle durability using a Write-Ahead Log (WAL). But here, there are no complex transactions or in-place table overwrites requiring a WAL. In fact, the entire database is an append-only log, so why add another log on top of it? We could take a primitive approach and achieve ultra-durability by forcing every single coordinate directly to disk with fsync(), like I did in my early SQLite clone. But it's called primitive for a reason: it kills performance.

potDB simplifies crash recovery through its append-only chunk architecture. Since existing chunks are never modified, recovery primarily involves identifying and validating the last complete chunk.

In addition to capacity-based flushing, potDB checks timestamp progression during point insertion to flush chunks when timestamp - start_time >= max_time_window_ms. Since potDB runs as an embedded library without background threads, slow or idle streams that stop sending points can be explicitly flushed by calling ptdb_poll with the current wall-clock timestamp.

A crash can happen at three distinct stages:

The Three Crash Scenarios

1. Scenario 1: Crash in Memory (Before Flush)

   • Un-flushed points sitting in user-space RAM are lost on a sudden power cut or crash, but already-persisted chunks remain completely intact. Because potDB never overwrites existing data, there are no corrupted B-tree pointers or broken node links.
   • You can bound this loss window (e.g., set max_time_window_ms = 1000 for at most 1 second of data in RAM). However, two nuances are important:
     • Event Time vs. Wall-Clock: During active streaming, potDB checks the time difference between the incoming point and the first point in the chunk to decide when to flush. This is based on incoming data, not a background timer.
     • Cooperative Polling: If incoming data stops, a partially filled chunk will not be flushed automatically. Since potDB has no background timer, the host application must periodically call ptdb_poll to check whether the buffer needs to be flushed.

2. Scenario 2: Crash Mid-Write (Torn Chunks)

   • If power cuts halfway through writing a chunk, potDB detects it immediately on reboot:
     1. Header check: Checks header size and magic number.
     2. Payload check: Ensures bytes_read == payload_size.
     3. CRC-32 checksum: Verifies that both the chunk header metadata and payload bytes haven't suffered bit-rot or torn sectors.
   • Reader behavior differs by access path: sequential chunk traversal returns explicit error codes, whereas range queries halt cleanly at the last verified chunk.

3. Scenario 3: Crash in Cache (The Durability Dial)

Once write() sends data to the OS page cache, it may not yet be stored permanently. potDB provides three durability modes:

• PTDB_SYNC_ALWAYS: Calls fsync() after every chunk flush. A process crash or power failure should not lose successfully synced chunks. The current chunk may be lost if the crash occurs before syncing. (~96.7K pts/sec)
• PTDB_SYNC_PERIODIC: Calls fsync() every N flushes. A process crash preserves data already written to the OS, but a power failure can lose all chunks written since the last successful fsync(). (~813K pts/sec at N=10)
• PTDB_SYNC_NONE: Relies on OS background writeback. A process crash generally preserves data already written to the page cache, but a power failure can lose any data not yet written to permanent storage. (~4.37M pts/sec)

Benchmarks use 500-point chunks. Actual power-loss protection depends on the OS, filesystem, and storage device.

Crash Recovery: Truncation over Traditional WAL

Traditional relational databases maintain a Write-Ahead Log (WAL) to undo partial writes or replay committed transactions across in-place table pages. Because potDB never overwrites historical chunks in place, it does not need a traditional WAL.

When a crash or system shutdown occurs mid-write, partial trailing bytes may remain on disk. potDB recovers from incomplete writes through file truncation:

• If a write error occurs during ptdb_flush(), the engine immediately rolls back the partial chunk using ftruncate().
• On database restart, potDB scans sequential chunks from the beginning, verifies header integrity and CRC-32 checksums, and invokes ftruncate() to roll back any torn trailing bytes to the end of the last complete, valid chunk.

Chapter 5: Querying for You

How do you query points inside a spatial bounding box across a specific window of time without scanning gigabytes of data?

Because every chunk in potDB is sealed with a header containing:

• The Time Window: [start_time .. end_time]
• The Spatial Bounding Box (MBR): [min_x .. max_x, min_y .. max_y] (plus [min_z .. max_z] in 3D)

potDB performs Two-Level Spatiotemporal Chunk Skipping:

1. Level 1 (Header Pruning): potDB sequentially scans chunk headers (reading 44 bytes for 2D or 52 bytes for 3D). If the chunk's time window or spatial MBR does not intersect your query bounds, it calls lseek() to jump straight over the entire payload without reading coordinates from disk.
2. Level 2 (Point Filtering): Only if both the time window and bounding box overlap does potDB read the payload into memory, verify its CRC-32 checksum, and stream matching points to your callback.

Chapter 6: Reliability and Concurrency Guarantees

Even though potDB is a specialized, lightweight time-series storage engine without general SQL transactions (BEGIN/COMMIT), it provides specific guarantees around persistence:

• Atomicity (Chunk-Level Ingestion): potDB does not implement multi-statement ACID transactions. Instead, chunks serve as the atomic unit of persistence. Partial or torn chunk writes caused by mid-write crashes are rejected by CRC-32 integrity validation on recovery and rolled back via ftruncate(), ensuring incomplete chunks are never visible to readers.
• Consistency: Coordinate dimension and time units are locked in the file header at initialisation. Timestamp monotonicity is enforced at runtime during point insertion.
• Isolation: Designed for single-writer access without built-in concurrency control or multi-process locking. Range queries read completed chunks up to the last verified boundary, though concurrent uncoordinated writes without external synchronisation can cause transient read errors.
• Durability: Configurable per application requirements via different durability modes.

Chapter 7: Benchmarks and Performance

All benchmarks were conducted on a specific hardware configuration. The results are empirical measurements, not universal performance guarantees.

Benchmark Environment

Synchronization Scope: The ingestion benchmark measures the wall-clock time to insert points, serialize chunk headers, issue write() calls to the OS page cache, and execute fsync() barriers according to the chosen durability mode until ptdb_close() flushes remaining buffers.

1. Architectural Properties

These properties are determined by potDB's binary format and implementation.

Note: A 3D point contains 20 bytes of actual data: an 8-byte timestamp and three 4-byte floats. However, the 64-bit system adds 4 bytes of padding, making the structure 24 bytes. potDB writes all 24 bytes to disk, allowing it to copy the structure directly without extra serialisation.

• Storage density: Binary storage uses approximately 50–75% less space than the example JSON and CSV representations.
• Memory: The insertion path performs no dynamic allocations after initialisation.
• Syscall batching: A 1,000-point chunk requires two write() calls (header and payload), instead of 1,000 individual writes.

2. Ingestion Throughput

Measured ingestion throughput across different chunk capacities and durability modes, using 200,000 2D points:

All values are points per second.

Larger chunks amortise synchronisation overhead across more points.

• SYNC_ALWAYS: Synchronises after every chunk, providing the strongest synchronisation frequency.
• SYNC_PERIODIC: Synchronises every 10 flushes, balancing throughput and durability.
• SYNC_NONE: Delegates synchronisation to the operating system's background writeback.

3. Sequential Scan Performance

Tested using 500,000 points stored in 1,000-point chunks.

The scan was performed against the operating system cache, with CRC-32 verification enabled.

4. In-Memory Accessor Performance

Measured byte-level deserialisation from an already-loaded memory buffer:

These measurements represent in-memory decoding, not disk access latency.

Benchmark Takeaway

The results demonstrate how chunk capacity and durability configuration influence potDB's performance. Larger chunks significantly improve ingestion throughput, particularly when synchronisation is enabled, while sequential scanning and in-memory decoding remain efficient.

Chapter 8: Limitations and Trade-offs

A specialised engine is defined as much by what it refuses to do as by what it implements. potDB makes deliberate engineering trade-offs:

1. No Out-of-Order Ingestion: All points must arrive with non-decreasing timestamps. Ingestion of late-arriving or historical out-of-order data is rejected. Applications requiring historical backfilling should consider LSM-based architectures.
2. No In-Place Point Updates or Deletions: Historical coordinates are immutable. You cannot modify or delete an individual point recorded ten minutes ago.
3. No Generalised Relational Schema: potDB stores only timestamps and spatial coordinates. It does not support arbitrary columns, dynamic metadata attributes, strings, or relational joins.
4. Single-Writer Concurrency: The engine does not provide multi-process writer coordination or file locking. Ingestion must be handled by a single dedicated writer process or thread.
5. Sequential Header Scanning: Without the planned sparse index, range queries must read every chunk header sequentially, making query performance proportional to the total number of chunks.

These limitations are deliberate design decisions tailored for predictable, embedded spatial telemetry.

Chapter 9: Future Improvements & Planned Features

potDB is already fast and functional, but building a database is a never-ending rabbit hole. Here are the features and improvements I'm planning to add down the road:

1. Binary Search on Time (Sparse Index):
   Right now, time queries check chunk headers one by one. Since time is strictly sorted, building a tiny in-memory index of chunk start times will allow us to use binary search, jumping directly to the requested time window without sequentially scanning preceding headers.

2. Spatial Grid Index:
   For bounding box queries across long periods, we still have to check every chunk's MBR. Adding a simple coarse spatial grid (dividing space into large tiles) or a space-filling curve (like Z-order) will let us look up only the chunks touching that specific geographic area.

3. Delta Compression (Gorilla-style):
   Coordinates and timestamps from moving objects often change gradually between consecutive samples. Using delta compression (storing differences between consecutive points instead of full raw values) could noticeably reduce on-disk file size with modest CPU overhead.

4. Circular Ring-Buffer & Auto-Retention (TTL):
   On small devices with limited flash storage (like smartwatches or micro-controllers), disk space is precious. Adding an automatic retention mode to prune chunks older than $N$ days, or rolling over a fixed-size ring-buffer will prevent disks from filling up.

5. First-Party Language Packages:
   Packaging potDB into standard package managers (pip install potdb for Python data science, npm install potdb for Node/Next.js, and an official Go module) so developers can embed it into their favourite stack without manual C linking.

Conclusion

Designing systems is a fundamental part of software engineering, especially in the era of AI-assisted coding. Taking the time to design things from scratch helps you understand how systems truly work under the hood.

I learned an enormous amount while building potDB. And I am not a seasoned database architect who knows everything. These are my thoughts, experiments, and architectural decisions. If you have any suggestions, feedback, or corrections, please feel free to drop a comment.

I hope you learned something new today!

Check out the introductory site and interactive live demo: https://potdb.vercel.app/

Codebase: https://github.com/jsndz/potdb