Re: psql is slow and it does not take much resources

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
>> However, I'm wondering if there's a practical limit to how many rows you
>> can insert within one transaction?

> There's a limit of (I think) 2-4 billion commands per transaction. Each
> command can insert any number of tuples.

> So if you're doing one tuple per command that limits you to a few
> billion inserts per transaction. Ofcourse, COPY is always faster
> still...

If you have any deferred triggers (including foreign keys) on the table
then the practical limit is likely to be a lot less, say in the few
millions. However, in a bulk data load situation you probably don't
need to have such triggers; it's better to establish the constraint
after you load the data.

BTW, has the OP read
http://developer.postgresql.org/docs/postgres/populate.html
? Much of this thread seems to be rehashing that page ...

regards, tom lane