Task:
Given this HashMap structure: Map<String, Map<String, String>> mainMap = new HashMap<>()
I want to INSERT
or COPY
each value of the inner Map
into its own cell in my database.
- The
size()
ofmainMap
if 50,000. - The
size()
of the innerMap
is 50. - The table to be inserted into has 50 columns.
- Each column's header is the key for the inner
Map
.
EDIT: Originally the user uploads a large spreadsheet with 35 of the 50 columns. I then "cleanse" that data with various formatting, and I add my own 15 new pairs into the innerMap for each mainMap
entry. I can't directly COPY
from the user's source file to my database without cleansing/formatting/adding.
Once I'm done iterating the spreadsheet and building mainMap
, that's when I need to insert into my database table efficiently.
Research:
I've read that COPY
is the best approach to initially bulk populate a table, however I'm stuck on whether my requirements warrant that command.
This post states that Postgres has a Prepared Statement parameter limit of 34464 for a query.
I'm assuming I need 50 x 50,000 = 2,500,000 parameters in total. This equals out to ~ 73 individual queries!
Question:
- Is
COPY
the proper approach here instead of all these parameters? - If so, do I convert the
HashMap
values into a.sql
file, save it on disk on my web app server, and then reference that in myCOPY
command, and then delete the temp file? Or can I directly pass a concatenatedString
into it, without risking SQL injection?
This command will be happening often, hence the need to be optimized.
I can't find any examples of converting Java objects into compatible Postgres text file formats, so any feedback helps.
How would you approach this problem?
Additional Info:
My table is pre-existing and can't be deleted since it's the back-end for my webapp and multiple users are connected at any given time.
I understand temporarily removing indexes prior to using COPY
can increase performance, but I'm only requiring max 50,000 rows to be inserted or copied at a time, not millions.
StackExchange told me to ask here.