Considering your table has some ID column, you can get it this way:
with ct as
unnest(regexp_split_to_array(t, '\+')) part
id, string_agg(part, '|' order by part) parts
set t = ct2.parts
where ct2.id = a.id;
id | t ...
The 4.6 minute COMMIT is nearly definitive that your IO system is either grossly inadequate, or just completely hosed. Which would explain all the rest of the problems. If connections can't commit, they won't ordinarily be closed, so they build up. Eventually you get "ERROR: no more connections allowed". If a client gives up on waiting for a commit ...
The long commit times indicate that I/O on the database server is seriously overloaded. The machine seems to come to a crawl, so that it cannot respond to connection requests in a timely fashion any more.
Examine the database workload and see what causes the overload.
You can still use DISTINCT ON. Just wrap it into an outer query to sort to your needs. See:
Get distinct on one column, order by another
PostgreSQL DISTINCT ON with different ORDER BY
SELECT DISTINCT ON (col1)
col1, col2, col3
ORDER BY col1, col3 DESC
ORDER BY col3 DESC, col2;
Assuming that col2 ...
This is a classic greatest-n-per-group problem. They frequently arise in a whole host of areas and, like Analytic functions (see below) are well worth studying.
Nowadays, it is typically solved by using Analytic (aka Window) functions - see the fiddle here.
You can use this query -
WITH cte AS
ROW_NUMBER() OVER (PARTITION BY col1, col2 ...