Back to articles
DevOps & Systems

Real-Time Data Sync Between DuckDB and PostgreSQL for Exam Performance Reporting

Technical guide on real-time data synchronization between DuckDB and PostgreSQL to support exam performance analytics, including configuration, tools, and testing.

DPTCloud Engineering4 min read
Real-Time Data Sync Between DuckDB and PostgreSQL for Exam Performance Reporting

Image credit: nattanan23 · Pixabay Content License

Introduction

Real-time data synchronization between DuckDB and PostgreSQL is a critical strategy in exam performance reporting systems. DuckDB excels at fast analytics on small to medium datasets, while PostgreSQL provides scalability, data integrity, and support for complex queries. Synchronizing these two systems ensures reports are always up to date, accurate, and reflect immediate data changes.

Requirements and Prerequisites

To implement real-time synchronization, you need:

  • PostgreSQL 12+ running on a server or VPS (you can use NVMe Cloud VPS or High-Performance Dedicated Servers).
  • DuckDB with remote connection support (via duckdb-server or pgwire).
  • Network connectivity between the two systems (same internal network or secure tunnel).
  • Software like pgloader or Debezium for change data capture (CDC).
  • Access rights to both PostgreSQL and DuckDB databases.

Synchronization Method

Use Debezium with PostgreSQL CDC and Apache Kafka as follows:

Step 1: Enable CDC on PostgreSQL

In postgresql.conf, add the following lines:

wal_level = logical
max_wal_senders = 10
max_replication_slots = 10

Restart PostgreSQL. Create a replication slot:

CREATE REPLICATION SLOT my_slot LOGICAL pgoutput;

Step 2: Configure Debezium

Create a file debezium-connector.json:

{
  "name": "postgresql-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "localhost",
    "database.port": "5432",
    "database.user": "debezium",
    "database.password": "debezium",
    "database.dbname": "exam_db",
    "database.server.name": "exam_server",
    "plugin.name": "pgoutput",
    "slot.name": "my_slot",
    "publication.name": "exam_publication",
    "table.include.list": "public.exam_results"
  }
}

Step 3: Stream Data to DuckDB

Use pgloader to load data from PostgreSQL into DuckDB. Install and run:

pgloader postgresql://user:pass@localhost/exam_db duckdb://localhost/exam_results.db

Alternatively, use python with psycopg2 and duckdb to read from Kafka and write to DuckDB.

Testing and Validation

To verify synchronization:

  1. Insert a new record into the exam_results table on PostgreSQL.
  2. Run a query on DuckDB:
  3. SELECT * FROM exam_results ORDER BY timestamp DESC LIMIT 5;
  4. Ensure the new record appears within a few seconds.

Troubleshooting

  • No changes received: Check Debezium and Kafka logs for errors. Ensure the replication slot is not deleted.
  • Low performance: Optimize Kafka and DuckDB configuration. Consider using a smaller batch_size.
  • Connection issues: Verify firewall rules and ports. Ensure PostgreSQL allows connections from Debezium.

References

For more on real-time synchronization, see:

DPTCloud Tier III Infrastructure

Recommended NVMe Cloud VPS Plans to Deploy This Setup

Enterprise RAID 10 NVMe SSDs, CMC & FPT District 9 uplinks with Anycast Anti-DDoS and 99.99% SLA.

View 22 VPS Plans
ECO · vn-02

VPS-2E

94.600 ₫ / mo

10% VAT included

  • CPU: 2 vCPU
  • RAM: 2 GB
  • SSD: 30 GB NVMe
ECO · vn-02Recommended

VPS-3E

170.500 ₫ / mo

10% VAT included

  • CPU: 2 vCPU
  • RAM: 4 GB
  • SSD: 50 GB NVMe
BASIC · vn-01Recommended

VPS-05

457.600 ₫ / mo

10% VAT included

  • CPU: 4 vCPU
  • RAM: 8 GB
  • SSD: 50 GB NVMe

Related Articles

View all
ZaloZalo SupportDiscord0384 753 478