Обсуждение: Import large data set into a table and resolve duplicates?

Hello!

I have a huge dictionary table with series data generated by a third-party
service. The table consists of 2 columns

- id : serial, primary key
- series : varchar, not null, indexed

From time to time I need to apply a "patch" to the dictionary, the patch file
consists of "series" data, one per line.

Now I need to import the patch into the database, and produce another file as
- if the passed "series" field exists in the database, then return ID:series
- otherwise insert a new row to the table and generate new ID and return ID:series
for each row in the source file.

So the new file will contain both ID and series data, separated by tab or
something.

While reading and writing the data is not a question (I simply described the
whole task just in case), I wonder what is the most efficient way of importing
such a data into a table, keeping in mind that

- the dictionary table already consists of ~200K records
- the patch could be ~1-50K of records long
- records could not be removed from the dictionary, only added if not exist

Thanks!

--
Eugene Dzhurinsky

On Sun, Feb 15, 2015 at 01:06:02PM +0100, Francisco Olarte wrote:
> You state below 200k rows, 50k lines per path. That is not huge unless
> "series" really big, is it?

series data is in between of 100-4096 chars

> 1.- Get the patches into a ( temp ) table, using something like \copy, call
> this patches_in.
> 2.- create (temp) table existing_out as select series, id from dictionary
> join patches_in on (series);
> 3.- delete from patches_in where series in (select series from
> existing_out);
> 4.- create (temp) table new_out as insert into dictionary (series) select
> patches_in.series from patches_in returning series, id
> 5.- Copy existing out and patches out.
> 6.- Cleanup temps.

That sounds cool, but I'm a bit worried about the performance of a lookup over
the series column and the time to create index for the "temp" table on
"series" column. But perhaps it's better to try this and if a performance will
go really bad - then do some optimizations, like partitioning etc.

Thank you!

--
Eugene Dzhurinsky

On Sun, Feb 15, 2015 at 10:00:50AM -0600, John McKown wrote:
> UPDATE patch_data SET already_exists=((SELECT TRUE FROM dictionary WHERE
> dictionary.series = patch_data.series));

Since the "dictionary" already has an index on the "series", it seems that
patch_data doesn't need to have any index here.

> At this point, the table patch_data has been updated such that if the
> series data in it already exists, the "already_exists" column is now TRUE
> instead of the initial FALSE. This means that we need to insert all the
> series data in "patch_data" which does not exist in "dictionary" ( i.e.
> "already_exists" is FALSE in "patch_data") into "dictionary".
>
> INSERT INTO dictionary(series) SELECT series FROM patch_data WHERE
> already_exists = FALSE;

At this point "patch_data" needs to get an index on "already_exists = false",
which seems to be cheap.

> UPDATE patch_data SET id=((SELECT id FROM dictionary WHERE
> dictionary.series = patch_data.series));

No index needed here except the existing one on "dictionary".

That looks really promising, thank you John! I need only one index on the
"patch_data" table, and I will re-use the existing index on the "dictionary".

Thanks again!

--
Eugene Dzhurinsky

On Sun, Feb 15, 2015 at 06:48:36PM +0100, Francisco Olarte wrote:
> One thing that strikes me is you are either at the beginning of your usage
> of this or you have A LOT of already present lines in the path ( I mean,
> path has one fourth of the lines of dictionary ). Which is the case?

Right now I'm on the very beginning stage, yes, and I expect to have "cache
miss" for the dictionary at ratio of at least 70%, which eventually will drop
down to 5-10%.

> When doing bulk operations ( like this, with a record count of 25% of the
> whole file ) indexing is nearly always slowed than sorting and merging. As
> I said, I do not know the distribution of your data size, but assuming the
> worst case ( 200k 4k entries dict, which is 800Mb data, 50k 4k patches,
> which is 200Mb data), you can do it with text files in 5 minutes by sorting
> and merging in 8Mb RAM ( I've done similar things, in that time, and the
> disk where much slower, I've even done this kind of things with half inch
> tapes and it wasn't thar slow ). You just sort the dictionary by series,
> sort patch by series, read both comparing keys and write a result file and
> a new dictionary, and, as the new dictionary is already sorted, you do not
> need to read it the next time. It was the usual thing to do for updating
> accounts on the tape days.

So you suggest to take this off the Postgres? Thats interesting. Simply put,
I'll do a dump of the dictionary, sorted by series, to some file. Then sort
the file with patch by series. Then merge the dictionary (left) and the patch
(right) files. And during the merge if the (right) line doesn't have a
corresponding (left) line, then put a nextval expression for sequence as an ID
parameter. Then truncate existing dictionary table and COPY the data from the
merged file into it.

Is it what you've meant?

Thank you!

--
Eugene Dzhurinsky

Hi Eugene,

> Now I need to import the patch into the database, and produce another file as
> - if the passed "series" field exists in the database, then return ID:series
> - otherwise insert a new row to the table and generate new ID and return ID:series
> for each row in the source file.

I think Francisco's approach is good, and I agree that ~200k rows is
hardly anything. My approach is similar but uses CTEs to combine a lot
of Francisco's queries into one. I still have a separate COPY command
though. (It'd be great if you could COPY into a CTE, but I guess
COPYing into a temporary table is pretty close.) Anyway, when I run
this on my machine, the import finishes in a few seconds:

# Makefile
database=dictionary
port=5432
words=/usr/share/dict/american-english
SHELL=/bin/bash

initial.txt:
  for i in {1..3}; do \
    cat "${words}" | while read line; do \
      echo $$i "$$line"; \
    done; \
  done > initial.txt

tables: initial.txt
  sudo su postgres -c 'psql -p ${port} ${database} -f tables.sql < initial.txt'

a.txt:
  for i in {1,4}; do \
    cat "${words}" | while read line; do \
      echo $$i "$$line"; \
    done; \
  done > a.txt

b.txt:
  for i in {4,5}; do \
    cat "${words}" | while read line; do \
      echo $$i "$$line"; \
    done; \
  done > b.txt

a: a.txt
  sudo su postgres -c 'psql -p ${port} ${database} -f import.sql < a.txt'

b: b.txt
  sudo su postgres -c 'psql -p ${port} ${database} -f import.sql < b.txt'

clean:
  rm -f initial.txt a.txt b.txt

.PHONY: tables a b clean


# tables.sql
DROP TABLE IF EXISTS dictionary;
CREATE TABLE dictionary (id SERIAL PRIMARY KEY, series VARCHAR NOT NULL);
\copy dictionary (series) from pstdin
CREATE UNIQUE INDEX idx_series ON dictionary (series);


# import.sql
CREATE TEMPORARY TABLE staging (
  series VARCHAR NOT NULL
);
\copy staging (series) from pstdin
CREATE INDEX idx_staging_series ON staging (series);

WITH already AS (
  SELECT  id, staging.series
  FROM    staging
  LEFT OUTER JOIN dictionary
  ON      dictionary.series = staging.series
),
adding as (
  INSERT INTO dictionary
  (series)
  SELECT  series::text
  FROM    already
  WHERE   id IS NULL
  RETURNING id, series
)
SELECT  id, series
FROM    adding
UNION
SELECT  id, series
FROM    already WHERE id IS NOT NULL
;


Good luck!
Paul