Small example how to select N last rows from categories

top_n_from_categories.sql

create table category
(
    category_id serial primary key,
    title       varchar not null
);

create table post
(
    post_id     serial primary key,
    category_id int         not null references category,
    text        varchar     not null,
    created_at  timestamptz not null
);

insert into category (title)
values ('sport'),
       ('art'),
       ('music');

insert into post (category_id, text, created_at)
select (random() * 1000)::int % 3 + 1,
       '',
       timestamp '2019-01-10 20:00:00' +
       random() * (timestamp '2019-04-20 20:00:00' -
                   timestamp '2019-01-10 10:00:00')
from generate_series(1, 1000000);

create index on post (category_id, created_at desc);

select grouped_posts.*
from (
    select p.post_id,
           c.title,
           p.text,
           p.created_at,
           row_number() over (partition by category_id order by created_at desc) as row_number
      from post p
      left join category c using (category_id)
    ) grouped_posts
where row_number <= 5;