SQLite3 manages data in memory to enhance performance for insert, update, delete, and query operations, while simultaneously persisting data to disk. When data is continuously added to the database without removal, both the database size and the resident memory usage of the process increase according.

The following C code demonstrates this behavior:

#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <string.h>
#include "sqlite3.h"

#define LOG_MSG(d,a,...) printf(a,##__VA_ARGS__)

static sqlite3* db_connection = NULL;
static int serial_counter = 0;
static char sql_buffer[255];

int execute_query(const char* query)
{
    if (NULL == db_connection || NULL == query || strcmp(query, "") == 0)
    {
        return -1;
    }
    char* error_message = NULL;
    if(sqlite3_exec(db_connection, query, NULL, NULL, &error_message) != SQLITE_OK)
    {
        LOG_MSG(err_LogLevel,"sqlite3_exec failed. Error: %s, Query: %s", error_message, query);
        sqlite3_free(error_message);
        return -2;
    }
    int changes = sqlite3_changes(db_connection);
    sqlite3_free(error_message);
    
    return changes;
}

int initialize_database()
{
    const char* create_table = "CREATE TABLE IF NOT EXISTS DeviceInfo("
        "serial TEXT PRIMARY KEY, "
        "ip TEXT, "
        "user TEXT, "
        "pwd TEXT);";
    execute_query(create_table);
    
    const char* create_index = "CREATE INDEX idxT1 ON DeviceInfo(ip);";
    execute_query(create_index);
    
    return 0;
}

int open_database()
{
    int result = sqlite3_open_v2("./db.db", &db_connection,
        SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, 
        NULL);
    if(result != SQLITE_OK){
        LOG_MSG(err_LogLevel,"Failed to open database [%s]", sqlite3_errmsg(db_connection));
        return -2;
    }
    
    int threadsafe = sqlite3_threadsafe();
    if (!threadsafe)
    {
        LOG_MSG(err_LogLevel,"SQLite3 is not thread safe");
    }
    
    return 0;
}

int insert_record(char* ip_addr, char* username, char* password)
{
    char identifier[255];
    sprintf(identifier, "id-%d", serial_counter++);
    sprintf(sql_buffer, "INSERT INTO DeviceInfo(serial, ip, user, pwd) "
        "VALUES('%s', '%s', '%s', '%s') ",
        identifier, ip_addr, username, password);
    
    printf("Inserted ID: %s\n", identifier);
    return execute_query(sql_buffer);
}

int remove_record()
{
    char identifier[255];
    sprintf(identifier, "id-%d", serial_counter - 10);
    sprintf(sql_buffer, "DELETE FROM DeviceInfo WHERE serial='%s' ", identifier);
    
    printf("Deleted ID: %s\n", identifier);
    return execute_query(sql_buffer);
}

int main()
{
    open_database();
    initialize_database();
    printf("Process started with PID=%d\n", getpid());
    
    usleep(10000 * 900);
    
    while(1)
    {
        insert_record("192.168.1.12", "user2", "pwd2");
        
        // Uncommenting the next line causes memory consumption to grow over time
        // remove_record();
        
        usleep(2000);
    }
    
    return 0;
}

When running this application and monitoring memory usage with top -p <processID>, it becomes evident that memory remains stable when records are periodically removed. Without deletion operations, memory usage continues to rise.

To prevent unbounded memory growth, consider the following practices:

1. Regularly delete obsolete records from the database.
2. Close and reopen database connections between operations to release enternal buffers.
3. Execute the VACUUM command periodically to reclaim space from deleted records.

These measures help maintain consistent memory footprint even under continuous write loads.