With a materialized view like that, a simple "final" for deduplication won't work anymore, right? And the other two deduplication methods will probably cause performance problems on large-ish tables.
As for the other two deduplication methods, you're also correct that they might cause performance problems on large-ish tables. The DISTINCT ON method can be slow due to the need to sort the entire table, while the ROW_NUMBER() method can be resource-intensive due to the need to assign a unique row number to each row.
one possible solution to this problem is to use a combination of a materialized view and a secondary deduplication method. For example, you could create a materialized view that includes a ROW_NUMBER() or RANK() function to assign a unique identifier to each row, and then use a secondary query to eliminate duplicates based on this identifier.
you could consider using a different data modeling approach, such as using a separate table to store the deduplicated data, or using a data warehousing tool that supports more advanced deduplication techniques.
That won't work, as the materialized view's query is applied per inserted chunk, i.e. to each row separately in the most extreme cases.