Running out of memory in Python using dict of namedtuples
<p>I have a set of questions that I'd like to be able to answer without having to use SQL to answer them. I was about 9 sub selects into a query when I decided that it's easier to do tree walks in Python.</p>

<p>Problem is when I try to get all of the data (3.2 billion rows) I care about into my machine in Python I run out of memory.</p>

<pre><code>from collections import namedtuple, defaultdict

state_change = namedtuple("state_change", ["event", "phase", "datetime"])

sql = """
SELECT *
FROM job_history
WHERE job = 'job_name'"""

# Now we need to assosciate job states, and times.
jobs = defaultdict(list) # Key is the job id, list of state changes

for row in get_media_scan_data(sql):
    jobs[row[7]].append(state_change(
        row[8],
        row[10],
        row[5],))
</code></pre>

<p>an individual row looks like</p>

<pre><code>datetime                job         job_id  event               impact_policy   phase
2000-06-10 08:44:04.000 job_name    4165    begin inode verify  NULL            4
</code></pre>

<p>One solution to this problem is doing my computation on a window of data. So say the first 200,000 rows, then doing 200,001 to 400,000, etc.</p>

<p><strong>Is there a more memory efficient way to store this data locally?</strong> It would be a huge time saver if I could avoid having to redownload the dataset (using windows) multiple times.</p>