Mahesh

context:

I was working on persisting cart items in database. The tech stack is React, Nest, Drizzle and Postgres.

approach:

I thought it would be simple as its just POST and GET requests for syncing, but I went deep diving postgres DB.

the saving:

Cart Item Table:

• id
• product id
• user id
• quantity

Approach 1: I can add a product to the cart in frontend, a POST request gets initiated and the cart item get stored in the table. Each “add to cart”(ATC) will have a backend api call for saving.

Issues:

• multiple api calls
• if user keeps on adding frequently, the api calls increases in a small amount of time, leading pressure on DB

Approach 2: Debounce api calls. A debounce time of 1000ms will make things better. Each ATC will trigger 1s of timer and unless user does not add anything to the cart within time span of 1s, frontend will flush all the items at once in a single api call. This fixes the multiple api calls in a small amount of time issue.

Issues:

• this is only about adding cart items
• what about quantity updates, if the user adds same product multipel items or increases the quantity of the cart items?
• what if the user remove items from product?

Approach 3: Sending a list of product ids if in case something is removed.

• delete the items if there is a list of ids to delete
• bulk insert in backend and update if conflict occurs while inserting
• do both the operations in a single transaction

      await this.db.transaction(async (tx) => {

        if (cartData?.deleteIds && cartData.deleteIds.length > 0) {
          await tx.delete(cartItems).where(
            and(
              eq(cartItems.userId, userID),
              inArray(cartItems.prodId, cartData.deleteIds)
            )
          )
        }

        if (cartData.items.length > 0) {
          const bulkPayload = cartData.items.map(item => ({
            userId: userID,
            prodId: item.prodId,
            qty: item.qty
          }))

          return await tx.insert(cartItems).values(bulkPayload).onConflictDoUpdate({
            target: [cartItems.userId, cartItems.prodId],
            set: {
              qty: sql`EXCLUDED.qty`
            },
            setWhere: sql`${cartItems.qty} IS DISTINCT FROM EXCLUDED.qty`
          })
        }
      })

Notes:

• “insert” with “onConflictDoUpdate”: This will try to bulk insert and if any row is already present, it will update it’s qty as per the data sent by users.
• Postgres will create a virtual table just for this query with new data sent by users and name it as “EXCLUDED”. This query will check the user id and product id combination and if it already exists, then it will check the “qty” with the virtual table and updates the original table if mismatch(or the current table qty is distinct from virtual table) occurs.

few more things:

• before bulk insert, we need to sort the items wrt product id. This will prevent the DB to get into deadlock.
  • Postgres locks the row before it does any operation
  • if user B comes to update row 1 while user A is doing some operation on row 1, it will wait once the operation finishes only when the rows are sorted
  • Updating unsorted: user A comes with ids 101 and 102, starts transaction, locks 101 and starts processing. Meanwhile, user B comes with ids 102 and 101, starts transaction, locks 102 and starts processing.
  • After few mins, user A finishes 101 and tries to lock 102, but its already being used by user B, so it waits. Then, user B finishes 102 and tries for 101, which is being used by user A. This leads to deadlock.
  • This is just an example in small scale, but when the scale increases, the chances of deadlock increases with higher race condition. To avoid this, we need to sort the data so that until one user finishes processing another should wait. If the data is sorted, both tries to lock 101 initially, which will make sure to be waited for exactly the same set of order rather than different making it predictable.
• For small scale applications, this setup is fine. But, as the application grows, we can make change the “fillfactor” of the cart items table to 80, making it easier to query.
  • fillfactor: postgres maintains pages(gen. 8KB in size) for storing the data internally. Unless specifically changed, it is set to 100, meaning every update of a row might get written on another page rather than on the same page, which leads to updation of indexes as the new updated row pointer is on the other page. These index updations are generally costly operations. Reducing the fillfactor leads to store the updated data in the 20% space that is only allowed for updates rather than inserts, which does not involve index updations as the data is still in the same page. This helps in reducing the work of “vacuum”(the GC or memory optimizer) of the postgres that runs periodically to clean and seggregate the data.
  • Postgres does not update the row, rather it inserts a new row with the older one marking as dead and updates the indexes to the new memory of insertion. When vacuum starts cleaning, it deletes the row marked as dead and makes that memory to be ready for new inserts. Fillfactor is what restricts the inserts as per its value in the same page. The default is 100, making the whole page available for inserts, reducing it will make the space limited for inserts which would help in reducing the index updations due to updation of rows. Bdw, indexes are generally pointers to the combination of page and row called CTID. If the row memory changes due to updation to another page, then postgres has to update the CTID as well. This is massive for a large database. Reducing fillfactor reduces the frequent changes of these CTIDs making the postgres DB performant.
• Although, we are not indexing the quantities of the cart items, it does not matter in this case, but the findings like these will help while creating tables where we index columns a lot, like products table, where product name is indexed for faster search.
• Also, when the cart items list increases for each user or data to be updated increases, it s basically better to update in chunks rather than updating all at once, or we also can increase the timer in the frontend if the repetitive data are being processed, so that we can consolidate before processing. It is actually a mix of all these which will make the system perform in long run for large no. of users.

Just a small feature of persistent cart items taught me all of this. How postgres updates, how its indexing works internally and why the costs are higher when indexed columns are updated, how deadlock can be prevented when bulk inserts or updates are done, how postgres cleans up its memory, manages indexes, etc. Hopefully, whoever is reading this takes all of this account and have a deep dive on systems on which they are building things for better performance and stability in long run of application maintenance, coz that’s what counts.