Gemini 4

The Bugs:

1. Duplicates: Sorting [5, 5, 3] yields [3, 5, 5]. xs[-2] returns the second element from the end (5) rather than the second largest distinct value (3).
2. Side effects & edge cases: xs.sort() mutates the caller's list in place, and xs[-2] raises an IndexError if there are fewer than two unique numbers.

The Fix: Convert the list to a set to remove duplicates, check that at least two unique values exist, and use sorted() to avoid mutating the original list:

def second_largest(xs):
    unique = sorted(set(xs))
    if len(unique) < 2:
        raise ValueError("Need at least two distinct numbers")
    return unique[-2]

Now second_largest([5, 5, 3]) deduplicates and sorts to [3, 5], returning 3 while leaving the input list unchanged. (If you want $O(n)$ time instead of $O(n \log n)$, you can track the top two distinct values in a single loop.)

The outlier is 90, as it drops sharply from the otherwise steady upward trend ($120 \to 135 \to 150 \to 165$).

Possible causes:

• Operational disruption: A website outage, store closure, or stockout of a key product.
• External factor: A public holiday, severe weather, or delayed order recording.

Averages:

• With the outlier: $132.0$ ($660 \div 5$)
• Without the outlier: $142.5$ ($570 \div 4$)

sales = [120, 135, 150, 90, 165]
outlier = min(sales)  # 90
without_outlier = [x for x in sales if x != outlier]

avg_with = sum(sales) / len(sales)                  # 132.0
avg_without = sum(without_outlier) / len(without_outlier)  # 142.5

1. Index the expiration column with CREATE INDEX CONCURRENTLY so cleanup queries avoid full-table scans and do not block reads or writes.
2. Delete in small batches (e.g., 5,000–10,000 rows per transaction) using a loop with a short pause (100ms) between batches. This prevents long-held locks, WAL spikes, and replication lag.
3. Refresh planner statistics afterward with ANALYZE sessions;. Autovacuum will reuse dead tuples, though pg_repack can reclaim disk space online if table bloat still slows index scans.
4. Prevent future buildup by scheduling the batch-delete job every few minutes, or migrate to time-partitioned tables so old partitions can be detached and dropped cleanly.

SQL for Step 1:

SET lock_timeout = '5s';

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_sessions_expires_at
ON sessions (expires_at);

(Replace expires_at with your expiration column. Run CREATE INDEX CONCURRENTLY outside a transaction block; if lock_timeout aborts it, drop any INVALID index before retrying.)