On Aug 1, 2017, at 3:42 PM, David Adams via 4D_Tech <[email protected]> wrote: > > I'm starting with standard rows with UUIDs, strings, longs, reals, and > perhaps text. I need to go for maximum speed...most of the work on tuning > is on the Postgres side. Their high-speed entry command is called COPY IN > and Rob's plug-in supports it with a special command that takes the data as > a BLOB. In this case, I don't have any big types, binary formats, or JSON > to move. Since you bring up wire formats, I'm just thinking - 4D natively > stores all text as UTF16 and my range of values all fit comfortably in > Latin1. Character encodings is one of those things that I just have never > take the time to get my head around. Does UTF16 take more space to store > Latin1 data? It seems like it needn't, and yet when I put text into BBEdit, > it takes 2x space as UTF16 than as UTF8.
Is the server on the same LAN as the client? If not, you may want to consider chunking your COPY data in case of network failure. Also, in this case, wire speed is likely to be the bottleneck. It may also be faster to build a UTF8 file and use LEP/psql to copy the data. If this is a one-shot data transfer, as opposed to a scenario where a 4D app generates the data that will be regularly transferred to the Postgresql database, then the simplest/quickest way to generate data files that can be copied to the Postgresql server is probably the right answer. I haven’t seen if anyone has tested yet whether expanding a blob becomes significantly slower as the blob expands into the gigabytes range, vs pre-sizing the blob. > Note: COPY is the go-to bulk load optimization in Postgres, but multi-value > inserts are also supposed to be a big help. I'm working through it now and > can post some summary findings, if people are interested. COPY is supposed to be many times faster than even multi-row inserts, this StackOverflow answer has a short explanation: https://stackoverflow.com/a/32045034/980575 Jim Crate ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] **********************************************************************
