0

I have a large PostgreSQL table (NOT indexed) with duplicates so I ran the following query to eventually end up with a unique rows table:

INSERT INTO newTable(Field1, Field2, Field3)
SELECT DISTINCT Field1, Field2, Field3 
FROM oldTable ;

It ran until I ran out of disk space which is more than the size of the oldTable, and I'm positive that at least a 3rd is duplicate.

If anyone would know why it behaves like this. I couldn't run explain analyze because the table is over a Tb in size.

I can't remove duplicates using SELECT with functions min() or max() as I don't have an ID column.

I expected a smaller table with unique rows.

Edit : Does running a slightly different query change the the in-depth mechanism of the process ? I know that the following creates a new table unlike the first query that Inserts into an existing one:

SELECT DISTINCT Field1, Field2, Field3
INTO newTable
FROM oldTable;
0

2 Answers 2

2

Running SELECT DISTINCT on a table with no (useful) indexes will require a sort on the entire result set, which will likely spill to disk given the table size, in addition to the actual rows inserted into the target simultaneously. This is likely what happened to you.

Changing it to SELECT DISTINCT ... INTO won't make any difference, as it would do the same thing.

2
  • You're probably right, I had a doubt about this, thus I created a B-tree Index on it, it also surpassed the table size until disk got full. I guess I just need to get more storage. I don't know how feasible this is, to split the table into 4 quarters and deduplicate them one by one. I'll post an update.
    – Anvil
    Mar 7, 2023 at 20:13
  • Creating an index requires sorting as well, doesn't it
    – mustaccio
    Mar 7, 2023 at 22:27
0

So I finally deduplicated it but not inside the PostgreSQL DB, I used COPY to export the table in CSV format, then used sort -u to deduplicate it, once it's done I re-used COPY to import the CSV file into a new clean table and let AUTOVACUUM passively do the rest. this proved to use less disk space for processing and is also much much faster.

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.