Implementation of a Hot-Backup mechanism for decoupled AI analysis

[JonanOribe]

The idea of use a single SQLite database for both high-frequency SDR data ingestion and future heavy AI/ML analysis (like YOLO training or signal classification) will create a significant I/O bottleneck. Heavy queries for data processing can increase latency in the ingestion pipeline, potentially leading to dropped samples or "Database is locked" errors during peak SDR activity.

Implementing a Hot-Backup architecture could be useful. The idea is to maintain two separate database files:

• Ingestion DB (Hot): Optimized strictly for high-speed writes from the SDR hardware, running in WAL mode with minimal indexing.

• Analysis DB (Cold/Mirror): A near-real-time clone created via the SQLite Local/Online Backup API.

This would allow our future AI models to perform intensive analysis and training on the "Analysis DB" without interfering with the live signal capture. This decoupling ensures data integrity and system stability.

Using a single database with complex PRAGMA tuning, which helps but does not fully eliminate the hardware contention between the SDR bus and the GPU's data-fetching needs.

By using a decoupled approach, we can also implement different retention policies for each DB and ensure that the AI training process has a consistent "point-in-time" snapshot to work with.

Technical Justification: CPU vs. GPU for SDR Workloads

• Parallel Throughput: CPUs process signals sequentially, leading to bottlenecks at high sample rates, whereas GPUs use thousands of cores to compute FFTs and AI inferences in parallel.

• Real-Time Processing: GPUs handle massive bandwidth in real-time without "dropped samples," while CPUs often struggle to balance OS tasks, database management, and heavy math simultaneously.

• AI Efficiency: Running YOLOv12 on a CPU results in low frame rates and missed signals, while a GPU analyzes the spectrogram instantly for rapid pattern recognition.

• Latency Reduction: Offloading DSP (Digital Signal Processing) to the GPU minimizes system latency, ensuring the "Analysis DB" is processed as fast as the "Ingestion DB" fills.

Storage Justification: Reliability over Redundancy

• I/O Isolation: Doubling the disk footprint is a minor trade-off for preventing "Database is locked" errors. By separating the databases, we ensure that the high-speed SDR write stream is never interrupted by the heavy read-intensive AI training or signal analysis.

• Transient Analysis Workspace: The "Analysis DB" does not need to store data indefinitely. Once the GPU has processed the signals and extracted the relevant metadata/inferences, the raw data in the Analysis copy can be discarded. If we want to audit the data source in the future, we have the original database.

• Data Integrity: The primary "Ingestion DB" remains the "Single Source of Truth." If an analysis process crashes or corrupts the temporary database, the original signal data remains untouched and safe.

• Buffer Management: This architecture allows us to implement a "Circular Buffer" strategy: we only clone the most recent or relevant signal chunks for analysis, keeping the secondary disk usage lean while maintaining the primary reference on the main drive.

I would be happy to take on this task and submit a PR. I have already researched the implementation using sqlite3.backup() in Python and can provide a draft that includes a configurable redundancy toggle (Local vs. Cloud).

[smittix]

Looks good!

[JonanOribe]

Okay, I'll take care of it.

[docsteel]

Is the use of PostgreSQL for better Performance a Solution for the Future? SQLite was not designed for high Workload Scenarios, so PostgreSQL DB could help here.

[JonanOribe]

Here's where I have a question. PostgreSQL is a very robust enterprise standard, but it's also resource-intensive. If our goal is for Intercept to run on small, mobile devices, it's going to consume a lot of RAM and power (especially on battery-powered devices).

Personally, I'd go with PostgreSQL, but we'd have to consider how that would affect the devices' use in open environments.

I've done a small analysis with GTP to see how this would affect us.

Here is the breakdown of the resource consumption and performance differences between PostgreSQL, SQLite, and DuckDB, specifically for battery-powered Raspberry Pi devices with SSD storage.

---

Resource Consumption: Orders of Magnitude

When working with embedded systems, the differences aren't just percentages; they are "orders of magnitude".

1. RAM Usage (Idle/Baseline)

This is the "ghost" drain on your battery while the system is doing nothing.

• SQLite / DuckDB: $10^0$ MB (~1–5 MB). It's just a library loaded into your Python script. If the script isn't running, consumption is 0.

• PostgreSQL: $10^2$ MB (~150–300 MB). This is the minimum "tax" just to have the server running, managing background processes, and shared buffers.

• Difference: 100x (2 orders of magnitude) more RAM overhead for Postgres.

2. Ingestion Latency (Single Write)

The time the CPU must stay "awake" before it can return to low-power (idle) mode.

• SQLite: $10^{-1}$ ms (0.1–0.5 ms). A local function call writing directly to a file.

• PostgreSQL: $10^0$ ms (5–10 ms). It must go through the network stack (even on localhost), handle the client-server protocol, and manage transaction logs.

• Difference: SQLite is ~25x faster for small writes, meaning the CPU spends significantly less time in high-power states.

3. Analytical Efficiency (AI Tasks)

When your AI processes large datasets, DuckDB is the clear winner.

• PostgreSQL: Being row-based, it reads unnecessary data (the whole row) just to access one column. This wastes CPU cycles and SSD bandwidth.

• DuckDB: Being columnar and vectorized, it processes data in chunks that fit perfectly into the CPU cache.

• Difference: For heavy AI/Analytical tasks, DuckDB can be 10x to 50x more CPU-efficient.

---

---

Final Recommendation for your Project

Since you are on battery power with an SSD:

1. Skip PostgreSQL: The power cost of keeping a database server "alive" is a luxury a battery-powered Pi cannot afford. It will drain your battery 20-40% faster just by being idle.

2. Use SQLite for Ingestion: It is the "Gold Standard" for low-power logging. Use PRAGMA journal_mode = WAL; to make it incredibly fast on your SSD.

3. Use DuckDB for AI: When it's time to run your AI models or search for vectors, call DuckDB. It will finish the task much faster than the others, allowing the CPU to power down immediately ("Race to Sleep" strategy).

The "Golden Rule" for Battery:

Don't pay for what you aren't using. SQLite and DuckDB are "passive" files; they only consume energy when your code specifically asks them to. PostgreSQL is "active" and consumes energy every second it is turned on.

What do you think about this?

[xdep]

The SQLite/DuckDB split is the right call for resource efficiency, but we need to implement a tiered retention policy—using a high-speed circular buffer for the Hot Ingestion DB to prevent storage overflow and a 'decaying detail' archive that prioritizes high-value metadata (timestamps, frequencies, and decoded logs) over raw signal samples.