User
id
...

Conversation
id
user_id1
user_id2
cnt_new_msg
last_msg_id

Message
id
conversation_id
sender_id
type
is_read
created_at

SELECT *
FROM conversation c
LEFT JOIN message m ON m.id = c.last_msg_id l.last_ms
WHERE user_id1=[current_user_id] OR user_id2=[current_user_id]

CREATE TABLE users (user_id serial PRIMARY KEY, ...)

CREATE TABLE conversation (
  conversation_ id serial PRIMARY KEY
, user_id1         int NOT NULL REFERENCES users
, user_id2         int NOT NULL REFERENCES users
-- cnt_new_msg   -- redundant
-- last_msg_id
, created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE message (
message_id      serial PRIMARY KEY
conversation_id int  NOT NULL REFERENCES conversation
sender          "char" NOT NULL CHECK (sender IN ('1', '2')
message         text NOT NULL
type            ???
unread          boolean NOT NULL DEFAULT TRUE
created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE VIEW conversation_plus_ct AS
SELECT c.*
     , count(m.unread OR NULL) AS cnt_new_msg
     , max(m.created_at) AS last_msg_id
FROM   conversation c
JOIN   message m USING (conversation_id);

SELECT *
     , count(m.unread OR NULL) AS cnt_new_msg
     , max(m.created_at) AS last_msg_id
FROM   conversation c
LEFT   JOIN message m USING (conversation_id)
WHERE  [current_user_id] IN (user_id1, user_id2);