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.

  • I have no indexes, because those do affect performance. They do slow inserts, but can vastly speed up queries! NOT NULLs have their uses also. You could do some testing of your own. And why not INT(8)s for the id fields?
    – Vérace
    Aug 15 at 22:05
  • You are worried it might not be 10x faster than it is, without knowing how fast it is?
    – jjanes
    Aug 15 at 22:11
  • If you want high transaction performance without setting synchronous_commit=off, then you need storage with fast fsync, either good SSD, or battery backed write cache. Also, setting synchronous_commit=off sacrifices durability, not atomicity.
    – jjanes
    Aug 15 at 22:27
  • meta can be null? At what ratio, and what is the avg size of meta? And why jsonb in the first place? Does it hold regular data? Why uuid? Why timestamp instead of (typically superior) timestamptz? How often target_id IS NULL? How many distinct visitor_id, site_id, target_id, type at most? Aug 16 at 3:26
  • @Vérace, they are UUIDs and I can't really change them to ints (tied to another system). As for the indexes, yes you are correct, I was just referring to the INSERT performance.
    – labega
    Aug 16 at 20:00

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.