Bulk loading into PostgreSQL: Options and comparison
1–10 of 30 posts
Re: Bulk loading into PostgreSQL: Options and comparison
#2I feel like the article misses most of the 'meat' of bulk loading setups. e.g. Using partitioned tables, attaching partitions with little to no locking, indexing after loading, `COPY ... FREEZE`, etc.
One other note:
> Goto solution for bulk loading into PostgreSQL is the native copy command. But one limitation with the copy command is that it requires the CSV file to be placed on the server.
You can use `COPY ... FROM STDIN` and stream the data from the client, this is basically what `/copy` does in psql.
Re: Bulk loading into PostgreSQL: Options and comparison
#3What 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?
Re: Bulk loading into PostgreSQL: Options and comparison
#4I feel like the article misses most of the 'meat' of bulk loading setups. e.g. Using partitioned tables, attaching partitions with little to no locking, indexing after loading, `COPY ... FREEZE`, etc. One other note: > Goto solution for bulk loading into PostgreSQL is the native copy command. But one limitation with the copy command is that it requires the CSV file to be placed on the server. You can use `COPY ... FR…
The article did compare loading with index vs index after load.
What good do partitions do? Loading in parallel?
Re: Bulk loading into PostgreSQL: Options and comparison
#5I feel like the article misses most of the 'meat' of bulk loading setups. e.g. Using partitioned tables, attaching partitions with little to no locking, indexing after loading, `COPY ... FREEZE`, etc. One other note: > Goto solution for bulk loading into PostgreSQL is the native copy command. But one limitation with the copy command is that it requires the CSV file to be placed on the server. You can use `COPY ... FR…
The article did compare loading with index vs index after load. What good do partitions do? Loading in parallel?
One use case I've used them for is when you have a table that is 'under fire', and you need to add bulk data to it. Doing a large insert into a table like that can cause various issues. Instead I a partitioned table and each bulk load is a separate partition. This allows doing the bulk load to a 'clean' table out of the line of fire. You can then apply indexes, do cache warming or whatever else you may want to do to it, then attach the partition to the parent table to make it 'active'. As long as you have covering constraint on the partition that matches the partition constraint, this results is minimal impact to query performance. We do bulk loads of partitions with ~100-200M rows this way and it's barely noticeable to the query side latency.
Re: Bulk loading into PostgreSQL: Options and comparison
#6What 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?
[deleted]
Re: Bulk loading into PostgreSQL: Options and comparison
#7Nit: The plots are hard to follow to the point of being useless. I need to skip the entire group (4 bars) to compare against each other. Bar charts work best when the bars you want to compare are adjacent, not organized by groups. Anyways, the table is far more useful here.
Re: Bulk loading into PostgreSQL: Options and comparison
#8There are some good tips from a previous HN post on the same topic here: https://news.ycombinator.com/item?id=20403284
Re: Bulk loading into PostgreSQL: Options and comparison
#9What 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?
Not sure if this solves it in all cases, but foreign key constraint checks can be deferred until the end of the transaction.
https://www.postgresql.org/docs/9.1/sql-set-constraints.html
Re: Bulk loading into PostgreSQL: Options and comparison
#10What 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.