title: Am I using aiohttp together with psycopg2 correctly?
body: <p>I'm very new to using asyncio/aiohttp, but I have a Python script that read a batch of URL:s from a Postgres table, downloads the URL:s, runs a processing function on each download (not relevant for the question), and saves back the result of the processing to the table.</p>
  
  <p>In simplified form it looks like this:</p>
  
  <pre><code>import asyncio
  import psycopg2
  from aiohttp import ClientSession, TCPConnector
  
  BATCH_SIZE = 100
  
  def _get_pgconn():
      return psycopg2.connect()
  
  def db_conn(func):
      def _db_conn(*args, **kwargs):
          with _get_pgconn() as conn:
              with conn.cursor() as cur:
                  return func(cur, *args, **kwargs)
              conn.commit()
      return _db_conn
  
  async def run():
      async with ClientSession(connector=TCPConnector(ssl=False, limit=100)) as session:
          while True:
              count = await run_batch(session)
              if count == 0:
                  break
  
  async def run_batch(session):
      tasks = []
      for url in get_batch():
          task = asyncio.ensure_future(process_url(url, session))
          tasks.append(task)
  
      await asyncio.gather(*tasks)
      results = [task.result() for task in tasks]
      save_batch_result(results)
      return len(results)
  
  async def process_url(url, session):
      try:
          async with session.get(url, timeout=15) as response:
              body = await response.read()
              return process_body(body)
      except:
          return {...}
  
  @db_conn
  def get_batch(cur):
      sql = "SELECT id, url FROM db.urls WHERE processed IS NULL LIMIT %s"
      cur.execute(sql, (BATCH_SIZE,))
      return cur.fetchall()
  
  
  @db_conn
  def save_batch_result(cur, results):
      sql = "UPDATE db.urls SET a = %(a)s, processed = true WHERE id = %(id)s"
      cur.executemany(sql, tuple(results))
  
  
  loop = asyncio.get_event_loop()
  loop.run_until_complete(run())
  </code></pre>
  
  <p>But I have the feeling that I must be missing something here. The script runs but it seems to become slower and slower with each batch. Specially it seems like the call to the <code>process_url</code> function becomes slower over time. Also the used memory keeps growing so I'm guessing there might be something that I fail to clean up properly between runs?</p>
  
  <p>I also have problems increasing the batch size much, if I go much over 200 I seem to get a much higher proportion of exceptions from the call to <code>session.get</code>. I have tried playing with the <code>limit</code> argument to the TCPConnector, setting it both higher and lower but I can't see that it helps much. Have also tried running it on a few different server but it seems to be the same. Is there some way to think about how to set these values more effectively?</p>
  
  <p>Would be grateful for some pointers to what I might do wrong here!</p>