Postgres Dynamically Create Sequences

This is an area where you probably don't need the key benefit of sequences - that they can be used concurrently by multiple transactions. You may also not want the corresponding downside, that gaps in sequences are normal. It's quite normal to get output like 1, 2, 4, 7, 8, 12, ... if you have concurrent transactions, rollbacks, etc.

In this case you are much better off with a counter. When a user creates an account, create a row in an account_sequences table like (account_id, counter). Do not store it in the main table of accounts, because you'll be locking and updating it a lot, and you want to minimise VACUUM workload.

e.g.

CREATE TABLE account_sequences
(
    account_id integer PRIMARY KEY REFERENCES account(id),
    counter integer NOT NULL DEFAULT 1,
);

Now write a simple LANGUAGE SQL function like

CREATE OR REPLACE FUNCTION account_get_next_id(integer)
RETURNS integer VOLATILE LANGUAGE sql AS
$$
UPDATE account_sequences
SET counter = counter + 1
WHERE account_id = $1
RETURNING counter
$$;

You can then use this in place of nextval. This will work because each transaction that UPDATEs the relevant account_sequences row takes a lock on the row that it holds until it commits or rolls back. Other transactions that try to get IDs for the same account will wait for it to finish.

For more info search for "postgresql gapless sequence".

If you want, you can make your SQL function fetch the prefix too, concatenate it with the generated value using format, and return a text result. This will be easier if you put the prefix text NOT NULL column into your account_sequences table, so you can do something like:

CREATE OR REPLACE FUNCTION account_get_next_id(integer)
RETURNS text VOLATILE LANGUAGE sql AS
$$
UPDATE account_sequences
SET counter = counter + 1
WHERE account_id = $1
RETURNING format('%s%s', prefix, counter)
$$;

By the way, do not take the naïve approach of using a subquery with SELECT max(id) .... It's completely concurrency-unsafe, it'll produce wrong results or errors if multiple transactions run at once. Plus it's slow.