AFAIU and from docs, RealDictCursor is a specialized DictCursor that enables to access columns only from keys (aka columns name), whereas DictCursor enables to access data both from keys or index number.
I was wondering why RealDictCursor has been implemented if DictCursor offers more flexibility? Is it performance-wise (or memory-wise) so different (in favor of RealDictCursor I imagine...)?
In other words, what are RealDictCursor use cases vs DictCursor?

The main advantage of real dictionary cursor is the easiness to get a query output as json.

Compare:

with psycopg2.connect('dbname=test') as connection:
    with connection.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute("select * from my_table")
        print(json.dumps(cursor.fetchall()))

versus

with psycopg2.connect('dbname=test') as connection:
    with connection.cursor() as cursor:
        cursor.execute("select * from my_table")
        columns = [desc[0] for desc in cursor.description]
        real_dict = [dict(zip(columns, row)) for row in cursor.fetchall()]
        print(json.dumps(real_dict))

There is no important difference between these options when it comes to performance.

You cannot get an expected json using json.dumps(cursor.fetchall()) for regular or dictionary-like cursors and need the conversion showed above. On the other hand, real dictionary cursor produces a much larger result so you should not use it if you really do not need it.