Query Millions of VoIP CDRs with DuckDB on Budget VPS

DuckDB is a column-oriented database that can analyze millions of call records from Asterisk and ViciDial faster than MySQL, on a budget VPS with just 1GB of RAM. While MySQL scans every row and decompresses fields to run queries, DuckDB pre-compresses data by column, packing related values together for quick access. This allows queries to complete in seconds instead of minutes.

To get started, install the DuckDB CLI binary on the VPS, verify the installation, and create a CSV export of the call records from the ViciDial database. This involves using the MariaDB client to select the desired date range and outputting the data to a CSV file. Verify the file creation and content, then load the CSV into DuckDB.

Create a DuckDB database and import the CSV using the `CREATE TABLE` command. DuckDB will automatically detect and convert column types from the CSV. Verify the import by running a simple query that counts records and finds the earliest and latest dates in the data.

To automate the process, create a shell script that runs daily via cron. The script pulls the previous day's records from the MariaDB database, exports them to a new CSV file, and appends the file to the DuckDB database. This way, the live call records in MySQL remain unmodified, while the DuckDB database serves as a read-only analytics repository.