0

I have a huge table. Each row has an ID, some columns which relate to that ID specifically, a short (DNA) Sequence, and some columns which relate to that sequence particularly. If these have not yet been calculated (for this row) then they are null, but the calculations will always come out identical for a given sequence.

In case it proves relevant: the sequence is indexed, and the DBMS is Postgres.

There are lots of duplicate sequences. Obviously this is non-optimal - both because we don't want to store the duplicates, and because we don't want to waste time recalculating those properties. There will already be duplicate calculated properties.

So I want to move the sequence's properties into a new table, using the sequence as a foreign key. The trouble here is the size of the table - hundreds of millions of records, and the properties are quite large as well.

With a small table this would be easy enough, but I need a better strategy for a huge table.

4
  • 1
    It works just like for a small table, except it will take more time. Nov 20, 2019 at 13:36
  • 1
    In some circles, a 100M row table is still considered "a small table" Normalizing it shouldn't be a problem What problem/concern do you actually have? Nov 20, 2019 at 13:57
  • For tables of 60 million records, a DELETE of 56M took 3 mins.
    – Vérace
    Nov 20, 2019 at 15:51
  • Doing it by deletion is interesting - what I'm trying now is copying the relevant columns into a new table without trying to reduce it, and then deleting the duplicates once it's in there.
    – Jivlain
    Nov 20, 2019 at 16:03

1 Answer 1

3

Yes, the delete step is going to be a pain and might cause (slight) performance issues, but see here for a ray of hope in that regard.

The best solution (prevention is better than cure) is to create a UNIQUE index on your dna field (or whatever you call it) and then this problem cannot arise in the future and the steps below become a one-off operation. Then, when loading a new batch/run, simply use a staging table which allows duplicates and then only copy in the UNIQUE DNA sequences and then TRUNCATE the staging table.

CREATE UNIQUE INDEX sequence_idx ON sequence (dna);

You could also consider PARTITIONing before the operation - only HASH partitions make sense in this case. However, if you're have no reason to partition then it's pointless for this operation which will only be a one-off because of the UNIQUE constraint!

Note that you are not normalising, rather you are deduplifying (if that's a word?). Here's a post from a guy who really knows his way around PostgreSQL on this topic.

Simple outline of deduplifcation steps follows (see fiddle here):

CREATE TABLE 
sequence
(
    id SERIAL PRIMARY KEY,
    dna VARCHAR(50) NOT NULL
);

Populate it:

INSERT INTO sequence (dna) VALUES ('agctagctagct');
INSERT INTO sequence (dna) VALUES ('agctagctagct');
INSERT INTO sequence (dna) VALUES ('agctagctagct');

INSERT INTO sequence (dna) VALUES ('tagctagctagc');
INSERT INTO sequence (dna) VALUES ('tagctagctagc');
INSERT INTO sequence (dna) VALUES ('tagctagctagc');
INSERT INTO sequence (dna) VALUES ('tagctagctagc');

INSERT INTO sequence (dna) VALUES ('ggcggcggcggc');
INSERT INTO sequence (dna) VALUES ('ggcggcggcggc');

You can find your dups using this:

SELECT s.dna, COUNT(dna)
FROM sequence s
GROUP BY dna
HAVING COUNT(dna) > 1
ORDER BY s.dna;

Then delete your dups as follows:

DELETE FROM sequence s1 USING sequence s2
WHERE
  s1.id < s2.id AND
  s1.dna = s2.dna;

Check:

SELECT s.dna, COUNT(dna)
FROM sequence s
GROUP BY dna
ORDER BY s.dna;

Result:

dna          count
agctagctagct     1
ggcggcggcggc     1
tagctagctagc     1
4
  • Wouldn't it be faster to CTAS the distinct rows, then "swap" the tables? Nov 20, 2019 at 14:58
  • The PostgreSQL Maestro(tm) thinks inserting records to be kept into a tmp table followed by a TRUNCATE and INSERTing back in from tmp is a good plan - however his post has caveats about how much will fit in memory. Since I've proposed a one-off solution what will never have to be repeated (UNIQUE index), not sure if it matters too much? Time for the OP to get back to us!
    – Vérace
    Nov 20, 2019 at 15:13
  • Sequences are allowed to be associated with multiple rows in the current table, so what I'm trying now is straight copying the relevant data into a new table, then deleting that down using this strategy, and finally setting the sequence string as primary key on the new table. So, yes, this should be a one-time thing when complete.
    – Jivlain
    Nov 20, 2019 at 16:30
  • I would advise against using DNA sequences as PRIMARY KEYs - use a SERIAL type instead - much smaller and more efficient for JOINs. Use a UNIQUE index for the DNA sequences as discussed.
    – Vérace
    Nov 20, 2019 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.