python efficiency and large objects in memory

Since the collection is shrinking at the same rate that rows is growing, your memory usage will remain stable. The gc.collect() call is not going to make much difference.

Memory management in CPython is subtle. Just because you remove references and run a collection cycle doesn't necessarily mean that the memory will be returned to the OS. See this answer for details.

To really save memory, you should structure this code around generators and iterators instead of large lists of items. I'm very surprised you say you're having connection timeouts because fetching all the rows should not take much more time than fetching a row at a time and performing the simple processing you are doing. Perhaps we should have a look at your db-fetching code?

If row-at-a-time processing is really not a possibility, then at least keep your data as an immutable deque and perform all processing on it with generators and iterators.

I'll outline these different approaches.

First of all, some common functions:

# if you don't need random-access to elements in a sequence
# a deque uses less memory and has faster appends and deletes
# from both the front and the back.
from collections import deque
from itertools import izip, repeat, islice, chain
import re

re_redshift_chars = re.compile(r'[abcdefg]')

def istrjoin(sep, seq):
    """Return a generator that acts like sep.join(seq), but lazily

    The separator will be yielded separately
    """
    return islice(chain.from_iterable(izip(repeat(sep), seq)), 1, None)

def escape_redshift(s):
    return re_redshift_chars.sub(r'\\\g<0>', s)

def tabulate(row):
    return "\t".join(escape_redshift(str(v)) if v is not None else '' for v in row)

Now the ideal is row-at-a-time processing, like this:

cursor = db.cursor()
cursor.execute("""SELECT * FROM bigtable""")
rowstrings = (tabulate(row) for row in cursor.fetchall())
lines = istrjoin("\n", rowstrings)
file_like_obj.writelines(lines)
cursor.close()

This will take the least possible amount of memory--only a row at a time.

If you really need to store the entire resultset, you can modify the code slightly:

cursor = db.cursor()
cursor.execute("SELECT * FROM bigtable")
collection = deque(cursor.fetchall())
cursor.close()
rowstrings = (tabulate(row) for row in collection)
lines = istrjoin("\n", rowstrings)
file_like_obj.writelines(lines)

Now we gather all results into collection first which remains entirely in memory for the entire program run.

However we can also duplicate your approach of deleting collection items as they are used. We can keep the same "code shape" by creating a generator that empties its source collection as it works. It would look something like this:

def drain(coll):
    """Return an iterable that deletes items from coll as it yields them.

    coll must support `coll.pop(0)` or `del coll[0]`. A deque is recommended!
    """
    if hasattr(coll, 'pop'):
        def pop(coll):
            try:
                return coll.pop(0)
            except IndexError:
                raise StopIteration
    else:
        def pop(coll):
            try:
                item = coll[0]
            except IndexError:
                raise StopIteration
            del coll[0]
            return item
    while True:
        yield pop(coll)

Now you can easily substitute drain(collection) for collection when you want to free up memory as you go. After drain(collection) is exhausted, the collection object will be empty.