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.
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-serverorpgwire). - Network connectivity between the two systems (same internal network or secure tunnel).
- Software like
pgloaderorDebeziumfor 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:
- Insert a new record into the
exam_resultstable on PostgreSQL. - Run a query on DuckDB:
SELECT * FROM exam_results ORDER BY timestamp DESC LIMIT 5;- 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:
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.
VPS-3E
10% VAT included
- CPU: 2 vCPU
- RAM: 4 GB
- SSD: 50 GB NVMe
