[bambax]

This example is really curious:

    BEGIN;
    SELECT post_count, ... from users where ... FOR UPDATE;
    INSERT INTO posts VALUES(...);
    UPDATE users SET post_count = new_post_count WHERE ...;
    COMMIT;

For one, it's unlikely the number of posts per user is so important and so often requested that it needs to be cached in the database itself.

Secondly, why would that value need to be stored with each new insert? Simply insert posts as they come, and calculate metadata about posts at a later time as a batch process, or when some part of the application actually request them.