Update duckdb memor limit but change is not persistant

Question

I have a local duckdb database but I'm running a query where I receive this error: Out of Memory Error: failed to allocate data of size 80 bytes (13.7GB/13.7GB used) So I'm trying to increase the max memory limit in duckdb like this set memory_limit='25GB'; But this change is not persistant because when i check my settings when i'm in my duckdb database i see that it changed to 25gb but when i exit out of the database en go back in it's again 13.7GB. Anyone knows why this change is not persistant? This is how I create my database btw.

import duckdb

def create_db():
    conn = duckdb.connect('rcsc.db', config={'memory_limit':'25GB'})
    conn.sql("create schema if not exists main_bronze")
    conn.sql("create table main_bronze.stg_fan as select * from read_csv('stg_fan.csv', all_varchar=1, auto_detect=1)")
    conn.sql("create table main_bronze.stg_access as select * from read_csv('stg_access.csv', all_varchar=1, auto_detect=1)")
    conn.sql("create table main_bronze.stg_fan_permission_source as select * from read_csv('stg_fan_permission_source.csv', all_varchar=1, auto_detect=1)")
    conn.sql("create table main_bronze.stg_fan_source as select * from read_csv('stg_fan_source.csv', all_varchar=1, auto_detect=1)")
    conn.sql("create table main_bronze.stg_ticket as select * from read_csv('stg_ticket.csv', all_varchar=1, auto_detect=1)")
    conn.sql("create table main_bronze.stg_merchandise as select * from read_csv('stg_merchandise.csv', all_varchar=1, auto_detect=1)")
    conn.sql("create table main_bronze.stg_fan_permission as select * from read_csv('stg_fan_permission.csv', all_varchar=1, auto_detect=1)")
    conn.sql("create table main_bronze.stg_gamification as select * from read_csv('stg_gamification.csv', all_varchar=1, auto_detect=1)")
    
if __name__ == '__main__':
    create_db()

Also tried setting it like this conn.execute("set memory_limit = '25GB'") but this also didn't work. I look forward to all your answers. Thanks in advance!

Answer 1

DuckDB settings are not persistent between sessions, you'll need to set them each time