title: SQLite 3 not releasing memory in Golang
body: <p>I'm having issues getting Go to play nicely with SQLite. I fixed it in the past without problems but it has been some time and I can't remember what I did to get it to work properly. I'm using the <a href="https://github.com/mattn/go-sqlite3" rel="nofollow noreferrer"><code>mattn/go-sqlite3</code></a> package to process and insert a lot of data into an SQLite database but somehow Go always ends up eating all my RAM until it finally exits with an error status code. Just to make sure I had isolated the memory starvation problem to SQLite I wrote the following simple program to test it:</p>
  <pre><code>package main
  
  import (
      &quot;database/sql&quot;
      &quot;fmt&quot;
      &quot;log&quot;
      _ &quot;github.com/mattn/go-sqlite3&quot;
  )
  
  func main() {
      db, err := sql.Open(&quot;sqlite3&quot;, &quot;./test.db&quot;); if err != nil {
          log.Fatal(err)
      }; defer db.Close()
  
      ddl := `
          PRAGMA automatic_index = ON;
          PRAGMA cache_size = 32768;
          PRAGMA cache_spill = OFF;
          PRAGMA foreign_keys = ON;
          PRAGMA journal_size_limit = 67110000;
          PRAGMA locking_mode = NORMAL;
          PRAGMA page_size = 4096;
          PRAGMA recursive_triggers = ON;
          PRAGMA secure_delete = ON;
          PRAGMA synchronous = NORMAL;
          PRAGMA temp_store = MEMORY;
          PRAGMA journal_mode = WAL;
          PRAGMA wal_autocheckpoint = 16384;
  
          CREATE TABLE IF NOT EXISTS &quot;user&quot; (
              &quot;id&quot; TEXT,
              &quot;username&quot; TEXT,
              &quot;password&quot; TEXT
          );
  
          CREATE UNIQUE INDEX IF NOT EXISTS &quot;id&quot; ON &quot;user&quot; (&quot;id&quot;);
      `
  
      _, err = db.Exec(ddl); if err != nil {
          log.Fatal(err)
      }
  
      queries := map[string]*sql.Stmt{}
  
      queries[&quot;user&quot;], _ = db.Prepare(`INSERT OR REPLACE INTO &quot;user&quot; VALUES (?, ?, ?);`); if err != nil {
          log.Fatal(err)
      }; defer queries[&quot;user&quot;].Close()
  
      tx, err := db.Begin(); if err != nil {
          log.Fatal(err)
      }
  
      for i := 0; i &lt; 10000000; i++ {
          user := map[string]string{
              &quot;id&quot;: string(i),
              &quot;username&quot;: &quot;foo&quot;,
              &quot;password&quot;: &quot;bar&quot;,
          }
  
          _, err := tx.Stmt(queries[&quot;user&quot;]).Exec(user[&quot;id&quot;], user[&quot;username&quot;], user[&quot;password&quot;]); if err != nil {
              log.Fatal(err)
          }
  
          if i % 32768 == 0 {
              tx.Commit()
              db.Exec(`PRAGMA shrink_memory;`)
  
              tx, err = db.Begin(); if err != nil {
                  log.Fatal(err)
              }
  
              fmt.Println(i)
          }
      }
  
      tx.Commit()
  }
  </code></pre>
  <p>When I run the above code, Go eats more than 100 MiB of memory every second without every releasing any, and after a minute or so, it ends up consuming 6/7 GiB and then the process gets killed. I've tried variations with and without defining the SQLite PRAGMAs but with no luck.</p>
  <p>According to the defined PRAGMAs, SQLite should never use more than 128 MiB of RAM.</p>
  <p>Have I made any mistake or is there something wrong with either mattn/go-sqlite3 or Go GC?</p>
  <hr />
  <p>Profiling with <code>davecheney/profile</code> as per <a href="https://medium.com/code-adventures/profiling-golang-851db2d9ae24" rel="nofollow noreferrer">these instructions</a> yields this not so helpful output:</p>
  <pre><code>alix@900X4C:~/Go/src$ go tool pprof --text ./test /tmp/profile102098478/mem.pprof
  Adjusting heap profiles for 1-in-4096 sampling rate
  Total: 0.0 MB
       0.0 100.0% 100.0%      0.0 100.0% runtime.allocm
       0.0   0.0% 100.0%      0.0 100.0% database/sql.(*DB).Exec
       0.0   0.0% 100.0%      0.0 100.0% database/sql.(*DB).conn
       0.0   0.0% 100.0%      0.0 100.0% database/sql.(*DB).exec
       0.0   0.0% 100.0%      0.0 100.0% github.com/mattn/go-sqlite3.(*SQLiteDriver).Open
       0.0   0.0% 100.0%      0.0 100.0% github.com/mattn/go-sqlite3._Cfunc_sqlite3_threadsafe
       0.0   0.0% 100.0%      0.0 100.0% main.main
       0.0   0.0% 100.0%      0.0 100.0% runtime.cgocall
       0.0   0.0% 100.0%      0.0 100.0% runtime.gosched0
       0.0   0.0% 100.0%      0.0 100.0% runtime.main
       0.0   0.0% 100.0%      0.0 100.0% runtime.newextram
  </code></pre>
  <p>This was just for 1000000 iterations and memory still grows like there's no tomorow.</p>
  <p>I also tried the same code on two MacBook Pros, both running the latest version of Go from brew (1.3.1), in one of them the memory usage topped 10 GiB and the other averaged 2 GiB of RAM consumption. This looks like an odd behavior, what can I do to trace down the discrepancies and fix the memory hogging?</p>