title: How to reduce SQLite memory consumption?
body: <p>I'm looking for ways to reduce memory consumption by SQLite3 in my application.</p>
  
  <p>At each execution it creates a table with the following schema:</p>
  
  <pre><code>(main TEXT NOT NULL PRIMARY KEY UNIQUE, count INTEGER DEFAULT 0)
  </code></pre>
  
  <p>After that, the database is filled with 50k operations per second. Write only.</p>
  
  <p>When an item already exists, it updates "count" using an update query (I think this is called UPSERT). These are my queries:</p>
  
  <pre><code>INSERT OR IGNORE INTO table (main) VALUES (@SEQ);
  UPDATE tables SET count=count+1 WHERE main = @SEQ;
  </code></pre>
  
  <p>This way, with 5 million operations per transaction, I can write really fast to the DB. </p>
  
  <p>I don't really care about disk space for this problem, but I have a very limited RAM space. Thus, I can't waste too much memory.</p>
  
  <p>sqlite3_user_memory() informs that its memory consumption grows to almost 3GB during the execution. If I limit it to 2GB through sqlite3_soft_heap_limit64(), database operations' performance drops to almost zero when reaching 2GB.</p>
  
  <p>I had to raise cache size to 1M (page size is default) to reach a desirable performance.</p>
  
  <p>What can I do to reduce memory consumption?</p>