Volkan Unsal <spocksplanet@xxxxxxxxx> writes: > I'm trying to remove a key from a jsonb column in a table with 10K rows, > and the performance is abysmal. When the key is missing, it takes 5 > minutes. When the key is present, it takes even longer. How wide are the jsonb values? It seems likely that most of this is TOAST overhead [1], ie time to reassemble wide jsonb values and then split them up again. As Tumasgiu already mentioned, it'd likely be useful to suppress updates of rows that don't actually need to change, assuming that the key appears in a minority of rows. And an index could help even more, by avoiding the need to reconstruct wide values to see if the key appears in them. Of course, if most of the rows need an update, neither of these will help and you just gotta live with it. Possibly reconsider your approach of using a large JSONB value to contain fields you need to update individually. That's never going to be great for performance. SQL (or at least Postgres) is incapable of updating portions of columns efficiently. regards, tom lane [1] https://www.postgresql.org/docs/current/storage-toast.html
