Case Study: DigiTwin High‑Performance Database System

The DigiTwin research project at Politecnico di Torino required a database system to monitor structural integrity of buildings using IoT sensors. The system faced several critical challenges:

• High-frequency data ingestion - Sensors generating readings every few seconds, 24/7
• Real-time analysis requirements - Structural engineers needed immediate access to aggregated data
• Historical data queries - Research required fast access to months of historical sensor data
• Cross-platform deployment - System needed to run on Linux servers and embedded devices
• Reliability - No data loss acceptable for critical infrastructure monitoring

I designed and implemented a high-performance C++ data processing pipeline with optimized PostgreSQL integration, focusing on throughput and query performance.

Architecture Overview

1. Data Ingestion Layer (C++) - Buffered batch inserts for high throughput
2. PostgreSQL Database - Optimized schema with strategic indexing
3. Query Optimization - Materialized views and partitioning
4. CMake Build System - Cross-platform compilation

Key Technical Decisions

1. Batch Processing Pipeline

Instead of inserting each sensor reading individually, I implemented a batching system that accumulates readings and performs bulk inserts:

// Pseudocode - actual implementation is proprietary
class SensorDataBuffer {
  std::vector<SensorReading> buffer;
  std::mutex mutex;
  
  void addReading(const SensorReading& reading) {
    std::lock_guard<std::mutex> lock(mutex);
    buffer.push_back(reading);
    
    if (buffer.size() >= BATCH_SIZE) {
      flushToDatabase();
    }
  }
  
  void flushToDatabase() {
    // Bulk insert using COPY command
    // 10x faster than individual INSERTs
    executeBulkInsert(buffer);
    buffer.clear();
  }
};

2. Database Schema Optimization

• Time-series partitioning by month for efficient queries
• Composite indexes on (sensor_id, timestamp)
• Materialized views for hourly/daily aggregates
• Automatic vacuum and analyze scheduling

3. Query Performance

Implemented several optimization techniques:

• Prepared statements to reduce parsing overhead
• Connection pooling to minimize connection overhead
• Index-only scans for common query patterns
• Parallel query execution for aggregate calculations

4. Cross-Platform Build System

# CMakeLists.txt structure
cmake_minimum_required(VERSION 3.15)
project(DigiTwinDB)

# PostgreSQL client library
find_package(PostgreSQL REQUIRED)

# Compiler optimizations
if(CMAKE_BUILD_TYPE STREQUAL "Release")
  set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -O3 -march=native")
endif()

add_executable(digitwin_processor
  src/main.cpp
  src/database.cpp
  src/sensor_buffer.cpp
)

target_link_libraries(digitwin_processor
  PostgreSQL::PostgreSQL
  pthread
)

1. Memory Management

C++ manual memory management required careful attention to prevent leaks in long-running processes. Implemented RAII patterns and smart pointers throughout.

2. Concurrent Access

Multiple sensor threads writing simultaneously required proper synchronization. Used mutexes and lock-free queues to prevent race conditions while maintaining throughput.

3. Database Connection Handling

PostgreSQL connections can fail unexpectedly. Implemented automatic reconnection with exponential backoff and transaction rollback on failure.

4. Cross-Platform Compilation

Different Linux distributions and embedded systems had varying library versions. CMake configuration needed to handle multiple target platforms gracefully.

• Profiling before optimizing - Used Valgrind and gprof to identify actual bottlenecks, not assumed ones
• Database optimization is multi-layered - Schema design, indexing, queries, and application code all matter
• Batch operations win - Bulk inserts were 10x faster than row-by-row
• Error handling is critical - In long-running systems, everything that can fail, will fail
• Testing on target hardware - Performance characteristics differ significantly between development and embedded systems