For those who ask "Why Not Just use NUMERIC" versus BIGINT for keys.
I have a large table (5 billion+ rows). I freshly loaded this. And I created indexes.
One was (bigint,bigint) and another was (numeric, bigint).

3x longer to create the (numeric,bigint). And it was all basically I/O.
I started them both at the same time, to take advantage of reading the same file.
The first one was done in 8hrs.
The second one, was the only thing running on the machine. And it took an additional 16hrs to complete.

The size of your columns matter.

Do you have write queries that take forever to run?
This is an approach to Batch Based processing, with independent transactions.
And the ability to keep track and display a Progress Bar.

It leverages PSQL \watch to work it's magic...
Well worth reading.

https://postgres.ai/blog/20220114-progress-bar-for-postgres-queries-lets-dive-deeper

Deleting ONLY the duplicates (leaving 1 row behind), with full control over batch sizes.

I've seen this question come up NUMEROUS times in various forums.
I've based this implementation on the work of others. (@Nikoay_S)
This is a GOOD LEARNING PIECE.

This seems straight forward enough.

Effectively, you can limit the number of rows you have coming back.
I have it set to 100 here.

The trick is HAVING count(1) > 1. This means each time you run, you will get at new set to work with.
Notice the way this runs, using the \WATCH command as a loop. (but it's an infinite loop. Version 16 of psql has iteration control!) [Also, this means it doesn't work in GUI tools]

Each iteration is it's own transaction. When you are done, the table is bloated (it should be vacuumed).
[I guess you could also use ROW_NUMBER OVER (PARTITION) rn ... WHERE rn > 1)]

— SETUP
drop table if exists big;
create temp table big ( other_val int );
insert into big select gs % 1000 + 1 from generate_series(1, 5000000) as gs;
create index on big (other_val);
vacuum analyze big;


DELETE from big
USING (select count(1) as cnt, other_val, min(ctid) as min_ctid from big group by other_val having count(1)>1 order by other_val desc limit 100) as dupes
where big.Other_val = dupes.other_val AND big.ctid > dupes.min_ctid;
\watch 0.5

TRAINING...
EnterpriseDB Offers Free Training here:

https://www.enterprisedb.com/training

Things to consider when Converting from Oracle to Postgresql
This is going to be a CHAT conversation of knowledge to help others.

PITR For Beginners!

https://www.highgo.ca/2023/05/09/various-restoration-techniques-using-postgresql-point-in-time-recovery/

Adding some More Ideas...

https://www.youtube.com/watch?v=FsUcaPJlYjY&list=PLS-kiDL9KrIh-E5eadtnp-8l55H7e2LN0
https://www.youtube.com/watch?v=wByi8mk8JWg&list=PLxtrcgLvnR2Zx2h4VYqCkwIb_vQ6WKp3L
https://www.youtube.com/watch?v=nyQ_MVmKcgE&list=PLk1kxccoEnNHlAR2ggnzIkOc7jxqI-_w2
https://www.youtube.com/watch?v=-pUTNi2MStQ&list=PLVISlbl_zTWI00-tis9dkCguVUxFUPOcK
https://www.youtube.com/watch?v=mdMfOYn-H4I&list=PLH8y1BNPAKjI8DOvNV-qKmhokO_oeICnO

For those Going from Oracle to PostgreSQL
https://databaserookies.wordpress.com/2023/05/13/subtle-art-of-code-conversion-in-oracle-to-postgresql-migration/

Hey, we just uncovered a conversion bug, going from Oracle to PG.
BEWARE... The difference between ROWNUM and LIMIT...

Oracle use a WHERE condition: "ROWNUM <= 10" to limit the number of rows.

PG uses "LIMIT 10" to do the same thing.

The difference is that Oracle operates on the INCOMING ROWS (so as the rows are being selected, it applies this logic to stop early).

Whereas PG is NOT using this as a "predicate" for choosing rows... It uses it AFTER the query has been run/sorted.

So in Oracle:
SELECT max(some_val) from table1 where ROWNUM <= 10; — Will tell you the max of the first 10 rows it finds.
PG:
SELECT max(some_val) from table1 LIMIT 10; — Will find the TABLE1 max value, and then apply the LIMIT to that row

This means the ORDER OF OPERATION is different, and the LIMIT clause should be (likely) applied to a sub-query:

SELECT max(some_val) from (SELECT some_val from table1 LIMIT 10) vbsq;

Now, that takes the first 10 records it finds. Creates a set. Then the max is applied to that set.

Again, the simplest way to understand it is that Oracle operates on INCOMING values, and PG operates on OUTGOING value.

The UPSIDE to the PG approach is that the LIMIT is applied AFTER the sort (ORDER BY).

This is an interesting Roadmap on learning to become a DBA and Understanding PostgreSQL

https://roadmap.sh/postgresql-dba

Here is another useful resource, by Nikolay...
He just started this... With a goal of adding to it every day for a year...
This will certainly be a treasure.

https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/tree/first-3-howtos?ref_type=heads

On High-performance SQL

https://stackoverflow.blog/2023/09/28/how-to-write-high-performance-sql-for-your-postgres-database/?_hsenc=p2ANqtz-9nGH4mITy2YCSbO5iO1BZu8oSHs4jMspWDYftXFtfLKSSO1dHhkRS4aPuSvwiqoDpLw1GfduMBl_r0Z0clqqUex2KdHxSAiK0N2CH5QXkP_Fg5ecE&_hsmi=277235435&utm_content=277235953&utm_medium=email&utm_source=hs_email

Another great page (INTRO to Postgresql Style)
https://zaiste.net/posts/postgresql-primer-for-busy-people/