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.