Please take note that the example below is just an example, my scenario is way more complex and the way i'm trying to model it really makes sense

Let's say i'm creating a table for audit events in one of my apps - so all of the "event_created", "user_created" etc. kind of thing. The table contains several columns, some of them are a foreign keys to other tables. Over time, this single table can grow to several milion of records.

From performance perspective, is it faster and more performant to use a single table for all of them or to use separate table for each kind of event and operate on separate tables? Or it doesn't make much differrence? It might sound silly to create a separate table for each kind of event but you need to trust me that in my real world scenario, it really makes sense.

  • 5
    For only "several millions" I would not bother at all. Postgres can handle single tables efficiently that are much larger - but if you really think you need to, then at least use partitioning – a_horse_with_no_name Oct 27 '18 at 9:13

Denormalize only as a last resort

Do not denormalize your table design for imaginary performance problems. Avoid falling into premature optimization.

Design a proper structure. Generate fake data to populate the tables. Run tests under circumstances akin to your deployment scenario. If a significant performance problem is proven:

Only after having exhausted all avenues to fixing a proven performance problem should you consider denormalizing.

Postgres is a powerful enterprise-quality database system. Several million rows on modern hardware with sufficient RAM and wise indexing should be no problem at all.

On the other hand, if your different types of events represent different entities, then they should be kept in separate tables. How do we know if similar kinds of rows are different entities or not? Clues might be found in asking: Do they have mostly the same columns with the same semantics? Do your users ever want to display or report together? Might you ever want to aggregate (calculate count, average, median, etc) together?

Be aware that as a product with a long history dating back to the days when computer hardware was much more limited in capabilities and configurations that today’s hardware, Postgres by default has quite conservative settings on initial installation. For example, by default Postgres runs on an older Raspberry Pi! So anyone running a larger database on more capable hardware should be doing some tuning.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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