Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Bulk loading into PostgreSQL: Options and comparison

highgo.ca

Post reply on HN
Print view View original

21–30 of 30 posts

Re: Bulk loading into PostgreSQL: Options and comparison

#21

Post by mslot »

devbug wrote: post #18
mslot wrote: post #16

The 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...

One caveat to keep in mind when using the binary format is that arrays of custom types are not portable across databases because the serialized array contains the OID of the custom type, which may be different on the other end.

The other thing to keep in mind is that text or CSV can be much more compact for data sets with many small integers or NULLs. On the other hand, the binary format is much more compact for timestamps and floating point numbers. In general, binary format has lower parsing overhead.

Re: Bulk loading into PostgreSQL: Options and comparison

#23

Post by ohnoesjmr »

systemvoltage wrote: post #7

Nit: 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.

Also, the choice of colours is incredibly poor, different shades of blue don't work with people for some forms of color blindness.

Re: Bulk loading into PostgreSQL: Options and comparison

#26

Post by phoe-krk »

nemoniac wrote: post #25
phoe-krk wrote: post #20

This article, for whatever reason, doesn't mention pgloader! https://pgloader.io/

Could you comment on how it compares to the methods benchmarked?

Sadly can't - I have never needed to use any bulk loading Postgres utilities, so I don't have the necessary experience. Still, I have had some glances at pgloader as a Common Lisp program, and I'm aware that it can migrate from X to PostgreSQL, where X includes multiple distinct databases and CSV files. That's why I've mentioned it here.

Re: Bulk loading into PostgreSQL: Options and comparison

#27

Post by ilikepi »

gigatexal wrote: post #19
orobinson wrote: post #15

Earlier quoted context omitted.

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

> 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.

You can definitely have PG ignore rows that would violate constraints by specifying "ON CONFLICT DO NOTHING"[1] as part of your INSERT statement.

If you wanted to capture those rows on the fly, one way would be to perform the load via a PL/PgSQL program and include an exception handler specific to the particular constraint violation(s) you expect to hit. But I guess then you'd have to load them one row at a time...and that's not too different from just doing that in your high-level language of choice. Also the PL/PgSQL docs specifically recommend against this idea.[2]

[1]: https://www.postgresql.org/docs/11/sql-insert.html#SQL-ON-CO...

[2]: https://www.postgresql.org/docs/11/plpgsql-control-structure...

EDIT: formatting and clarity

Re: Bulk loading into PostgreSQL: Options and comparison

#28

Post by gigatexal »

ilikepi wrote: post #27
gigatexal wrote: post #19

Earlier quoted context omitted.

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

> 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. You can definitely have PG ignore rows that would violate constraints by specifying "ON CONFLICT DO NOTHING"[1] as part of your INSERT statement. If you wanted to capture those rows on the fly, one way would be to perform the load via a PL/PgSQL program and inc…

Of course! I forgot about that option. Makes sense.

Re: Bulk loading into PostgreSQL: Options and comparison

#29

Post by dilatedmind »

hamandcheese wrote: post #4
mbell wrote: post #2

I 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?

The best use case I've had for partitioning tables is when there is a lot of churn that would otherwise result in significant vacuuming. For example, a table holding the past 7 days of data would require deleting 1/7 of its rows every day.

Swapping out a child table and dropping the old one is free in that sense, but does require an exclusive lock which can cause issues if you have long running queries.

Re: Bulk loading into PostgreSQL: Options and comparison

#30

Post by ilikepi »

I'm slightly surprised to not see any mention of materialized views.

We're in the middle of a project in which we use file_fdw to provide an interface to CSV files which are replaced nightly, but we then also have indexed materialized views to provide faster access to the data in those files. The CSVs are only read once per day when REFRESHing the materialized views after a new set of files is dropped in.

One of the nice things file_fdw is that it allows you to create foreign tables even when the source files don't exist on the filesystem. PG only cares about the files at the moment it's trying to read them. There is one downside, though, which is that the files have to be specified via absolute path. This is something that has to be considered given the heterogeneous machine environments on which the application runs.

Post reply on HN