1

Let's assume I have one big relation for my web application and analogical java object (JPA Hibernate is used for mapping). This is main relation, which consists of over 20 columns, and is heavily updated, but I can tell right now it's only 5 columns, that are updated over and over, and I can tell they are all small data type integer.

I'm thinking if extracting these 5 often updated values to separate table, and specifying one-to-one relation between them. Would that bring me any performance improvement? My aim is that only this smaller table would be updated so often. It would require a join on every query, but the load sent back and forth would be much much smaller. I will appreciate any advice. Hope the question is clear enough.

4
  • Not a bad idea, but it's important to consider not only the updates, but the load of all SELECTs that will retrieve data from this design. Will the queries be massive or narrow? Commented Feb 6, 2022 at 20:22
  • Why do you think this is a problem? I'm not against the idea, but sometimes just a change in the FILLFACTOR can fix your IO problems. Commented Feb 6, 2022 at 20:35
  • @The Impaler select query for this table will be big, consist of minimum 4 joins plus similiar number of conditions, but will not be sent so often as updates.
    – LSM2236
    Commented Feb 6, 2022 at 21:31
  • @Frank Heikens out of two options, im trying to figure out which will be better in terms of performance, to deliver best possible quality solution. Not neccesarily thinking here, that 1 large table is a huge problem. I take your proposition with FILLFACTOR as a topic to read about(never heard about it before).
    – LSM2236
    Commented Feb 6, 2022 at 21:34

1 Answer 1

1

Since the smaller part of the table is "updated over and over", and the larger part of the table is not, and SELECT "will not be sent so often as updates", your idea is solid.

Of course this is anecdotal information, not hard numbers to calculate with. The resulting overall performance depends on the complete picture.

The main benefit is to involve much fewer data pages in your updates, and to keep table and index bloat at bay. Indexes on the small table will bloat all the same, but indexes on the big table will not be affected by the many updates. So consider aggressive autovacuum setting for the small table. See:

Things to consider:

  • A FILLFACTOR below 100 (like 80?) for the small table (and below 90 for indexes) - depends on access patterns - while keeping default for the big table. See:

  • Create just the right indexes for the small table, as those increase write cost.

  • Create all the right indexes for the big table. Few updates there, so less constraint.

  • It may pay to CLUSTER the big table from time to time. The small table, not so much, as the effect deteriorates with subsequent writes. See:

  • Add a FOREIGN KEY constraint, possibly with ON DELETE CASCADE to enforce referential integrity, or use OUTER JOIN if one of the two parts can be missing. All according to actual requirements.

  • You might add a VIEW to merge the two parts for ease of use. That's just a convenience feature, no impact on performance.

1
  • This is good answer @Erwin . Thank you for that i will make my reading basing on this
    – LSM2236
    Commented Feb 8, 2022 at 12:06

Your Answer

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

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