How to optimize a PostgreSQL table for heavy writes, yet still maintaining atomicity?

Question

There are plenty of guides online on how to speed up writes on a PostgreSQL table, from tweaking the WAL configuration, turning off synchronous_commit, to creating unlogged tables. I'm currently trying to design a table that will receive around 300 inserts per second, not much, but I'm worried about what would happen at 2x, 5x and 10x that. So far, my design is simple:

create table event (
    visitor_id uuid not null,
    site_id uuid not null,
    target_id uuid,
    type char(1) not null,
    meta jsonb,
    created_at timestamp not null default now(),
);

Basically, I'm recording events that happen on a website by a user and, sometimes, a target. type is an internal enum that I store as minimally as possible (that's why the char(1)).

If you were aiming for 300 or 600 requests per second, what would you remove from this design? Some people recommended me to remove the not null and the default now(), but I'm not sure about that. I have no indexes, because those do affect performance.

Answer 1

Your schema design is already pretty minimal. Indeed, probably too minimal as it is generally not realistic to have no indexes. If you can't get adequate performance out of that (which apparently you have not tested yet?) then you need to look at your storage hardware, not your schema design. You could also address other non-schema aspects of the design, like transaction boundaries and how many processes can be inserting at once.