performance of copy_from() vs. raw COPY command

From: Daniel Lenski <dlenski(at)gmail(dot)com>
To: psycopg(at)postgresql(dot)org
Subject: performance of copy_from() vs. raw COPY command
Date: 2011-06-24 18:38:56
Message-ID: BANLkTim2_8V20yf-7WDc0q0yivm62yjT4A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: psycopg

Hi,
I'm using Postgre's COPY feature to populate a large table (~500M
rows, 100 GB) with frequent updates from CSV text files.  Currently
I'm using psycopg's copy_expert():

cur.copy_expert("COPY Table (c1, c2, ...) FROM STDIN WITH CSV",
open(filename))

This avoids any issue with client vs. server file permissions, but I'm
wondering if it carries a significant performance penalty by reading
the text file at the Python level rather than the OS level.  Instead I
could do:

cur.execute("COPY Table (c1, c2, ...) FROM $$%s$$ WITH CSV" % fn)

Of course, this opens up a whole host of permissions and security
issues.  Is there any reason to believe that the Python file interface
significantly slows down COPY FROM?

Thanks,
Dan

Responses

Browse psycopg by date

  From Date Subject
Next Message Sukhbir Singh 2011-06-24 19:05:08 Unpacking a Python list in a query.
Previous Message Marco Beri 2011-06-23 07:14:46 Re: autocommit and Django