0

In my DB's central table, "Profiles," I want to collect activity statistics that will be updated often, although not all profiles will have statistics. The "Profiles" table is already heavily updated and has some performance issues (I'm working on that...). My idea is to store statistics in a separate "Profile Statistics" table with the same PK and with the PK as foreign key pointing to "Profiles" in a 0:1 relationship. The linking field is already indexed on the referenced table. DDL:

CREATE TABLE profile 
(
      user_id VARCHAR(50) PRIMARY KEY,
      attr1....
      attr2....
      attr3....
)

CREATE TABLE profile_statistics
(

     user_id VARCHAR(50) PRIMARY KEY,
     stat1...
     stat2...
     stat3...
     CONSTRAINT FK_user_id FOREIGN KEY (user_id) 
            REFERENCES profile(user_id)
)

My hope is that separating these heavily updated (but rarely retrieved) statistics into a separate table is that updates on them will be much faster and will avoid increasing bloat on the main table, which is already heavily updated via API. Is this a common approach, or is this a bad idea? The alternative is to just have the statistics remain in the main table, which would prevent the creation of a new table w/ index.

  • Yes this approach makes sense, especially if the update frequency of stat* columns is quite different from the other columns, or if they contribute a lot to row lengths. It also allows to tune the tables separately concerning autovacuum or fillfactor... – Daniel Vérité Jan 10 at 15:51

Your Answer

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

Browse other questions tagged or ask your own question.