PostgreSQL, INSERT INTO SELECT DISTINCT generates more rows?

Question

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;

Answer 1

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.

Answer 2

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.