Not logging with pg_bulkload will win every time. However, not logging transactions is not something that should be taken lightly nor used for every bulk load operation. For instance, if replicating or synchronizing data to another db or using logs for such activity than you'll need the load transactions to perform the same operation on that other db.
Bulk loading into PostgreSQL: Options and comparison
11–20 of 30 posts
Re: Bulk loading into PostgreSQL: Options and comparison
#12What about cases where there's multiple tables with foreign keys between them? Should you just drop the constraints, import tables individually, and then re-apply the constraints? Any alternatives?
Postgres allows for deferring constraints, so you can do an alter command at the start to defer them, then upon commit all of the foreign keys are checked. Makes it hugely easier to load when you don’t need to do it in dependency order.
What happens to rows that fail the constraint check after load?
Re: Bulk loading into PostgreSQL: Options and comparison
#13Earlier quoted context omitted.
Postgres allows for deferring constraints, so you can do an alter command at the start to defer them, then upon commit all of the foreign keys are checked. Makes it hugely easier to load when you don’t need to do it in dependency order.
What happens to rows that fail the constraint check after load?
It'll just throw an error when COMMITing, most likely.
Re: Bulk loading into PostgreSQL: Options and comparison
#14Re: Bulk loading into PostgreSQL: Options and comparison
#15Earlier quoted context omitted.
It'll just throw an error when COMMITing, most likely.
And roll back the whole load? Yikes
Yup.. I’ve been there and it sucks. Only realising after your load of ~1 billion records has almost completed after several hours that there were a couple of duplicated rows and the PK constraint failed.
Re: Bulk loading into PostgreSQL: Options and comparison
#16The important thing to remember is to use COPY .. FROM STDIN, not insert, for bulk loading into PostgreSQL. Most PostgreSQL drivers support COPY from the client, though it's not always available through generic database APIs.
COPY commands typically write hundreds of thousands of rows per second on a large server/cluster. It's useful to write over multiple connections, but rarely more than ~16.
Re: Bulk loading into PostgreSQL: Options and comparison
#17The important thing to remember is to use COPY .. FROM STDIN, not insert, for bulk loading into PostgreSQL. Most PostgreSQL drivers support COPY from the client, though it's not always available through generic database APIs. COPY commands typically write hundreds of thousands of rows per second on a large server/cluster. It's useful to write over multiple connections, but rarely more than ~16.
This is true. I'm the maintainer of pg8000, a Python driver for PostgreSQL. Here are the docs for how you'd do this using pg8000:
https://github.com/tlocke/pg8000#copy-from-and-to-a-file
The standard API for database access in Python is DB-API 2 and it doesn't include support for COPY , so each driver may implement it differently.
There's another aspect to this, and that's the format of the file to be ingested as it can be 'text', 'CSV' or 'binary'. If you're generating the file yourself then you have a choice, but whether 'binary' is faster than 'CSV' I just don't know.
Re: Bulk loading into PostgreSQL: Options and comparison
#18The important thing to remember is to use COPY .. FROM STDIN, not insert, for bulk loading into PostgreSQL. Most PostgreSQL drivers support COPY from the client, though it's not always available through generic database APIs. COPY commands typically write hundreds of thousands of rows per second on a large server/cluster. It's useful to write over multiple connections, but rarely more than ~16.
Worth noting that you pass in data in a proprietary binary format. [1]
I've done it, not out of performance concerns, but because transcoding between proprietary binary formats with types is a lot saner than the alternative.
[1]: https://www.postgresql.org/docs/current/sql-copy.html#id-1.9...
Re: Bulk loading into PostgreSQL: Options and comparison
#19Earlier quoted context omitted.
And roll back the whole load? Yikes
Yup.. I’ve been there and it sucks. Only realising after your load of ~1 billion records has almost completed after several hours that there were a couple of duplicated rows and the PK constraint failed.
I wish there were a way for the db to filter out or not surface rows that violated a constraint but that then loaded the data that did meet the constraints. I guess the fix here is before letting the db validate constraints one needs to run data quality checks to either delete or fix rows that would have failed such checks. But then depending on transaction level you might not be able to see uncommitted rows? I dunno
Re: Bulk loading into PostgreSQL: Options and comparison
#20This article, for whatever reason, doesn't mention pgloader!