URL Click Column DB Migration - opengovsg/GoGovSG GitHub Wiki

Problem

When the click count of a URL in the url table is being incremented, that corresponding row is locked. This could pose a problem for bulk operations as they would not be able to acquire the necessary locks if a link is constantly being visited.

Objective

Migrate the count column from the url table to a separate url_clicks table.

Constraints

  • No downtime.

  • No loss of data.

Proposed approach

  1. Deploy application code that introduces a new UrlClicks model, allowing sequelize to create a new table, albeit unused.

  2. Run a migration script that, within the same transaction,

    • Copies the url table's click count over to url_clicks.
    • Adds a trigger on url table: On click count increment, do the same on url_clicks table.
    • Adds a trigger on url table: On row insert, insert a corresponding row on url_clicks table.

  3. Deploy application code that lets sequelize read/write on the new url_clicks table instead of url table.

  4. (OPTIONAL) Run a migration script that deletes the count column on url table.

Discussion

Importance of synchronisation:

Synchronisation of data is important for us to not have to worry about various edge cases:

  • url table's clicks count not being equal to url_clicks table's click count (possible data loss).

  • Attempting to increment a url_clicks table row that does not exist.

  • Attempting to read from a url_clicks table row that does not exist (nullable results).

Achieving synchronisation:

Start by exhaustively handling all DB operations that could cause our data to go out of sync:

  • url table's click column incremented.

  • New row inserted into url table.

Utilising a single transaction, carry out all necessary migration and adding of triggers to mitigate foreseen problems above.

Possible challenges

  • DB Locks: In step 2, the migration script needs to read every row in the url table to copy the count column over. A lock is needed to prevent dirty reads, and its acquisition could take awhile if done during a period of high traffic on our site.