search

public-activity / public_activity

Easy activity tracking for models - similar to Github's Public Activity
MIT License
2.96k stars 334 forks source link

How to properly change parameters type from text to jsonb? #342

Closed apraditya closed 2 years ago

apraditya commented 4 years ago

I'm considering to change parameters type to jsonb. Here's what I did:

  1. Added new column parameters_json with type jsonb, convert all parameters to that column and set AR callback to copy parameters to parameters_json before saving the record.
  2. Dropped parameters column & rename parameters_json to parameters.

I don't see any issues after deploying the first step. However, on the 2nd step, the performance gets slower by 6 times. For example, on the following simple query:

SELECT  "activities".*
FROM    "activities"
WHERE   "activities"."owner_id" IS NOT NULL
ORDER BY "activities"."created_at" DESC
LIMIT 12 OFFSET 24

running it on a PostgreSQL client, normally it takes 0.22s, after dropping & renaming the column, it takes 1.45s.

Currently I have 18M records in the table. Appreciate any suggestion, help or thoughts.

ur5us commented 2 years ago

@apraditya As this is >2.5y old, have you managed to resolve the issue?

In general, I don’t think this is an issue with PublicActivity itself but rather an issue with PostgreSQL. The reason I say that is because your query doesn’t use the parameters text/jsonb. So there’s something else going on unless you changed some indices along the way. Take a look at https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-STATISTICS. This could be the culprit of your performance problem.

I’m going to close this issue for now as this is more about PostgreSQL than PublicActivity. However, I’d be interested to hear back in case the vacuum helps or if you’ve identified any other issues with jsonb. So feel free to re-open the issue.