How We Stabilized a Production Splunk SOAR Database (And What I Learned Asking an AI for Help)
By David Burns, Team Lead, Automation Engineering
I want to be upfront about something before this post goes any further: I am not a database person. I’m a SOAR engineer. I build security automation, I write playbooks, I manage integrations. PostgreSQL is the engine running underneath Splunk SOAR and for most of my career I’ve been happy to let it do its thing in the background.
That worked great right up until it didn’t.
The Problem
We run a multi-tenant MDR platform built on Splunk SOAR (formerly Phantom) hosted on AWS. Four production servers, roughly 150 analysts across our customer base, 24/7 operation. No traditional maintenance windows since SOC work doesn’t stop at 5pm.
Earlier this year things started going sideways. Analysts were getting 502 Gateway Errors mid-investigation. Playbooks that normally ran in five minutes were taking twenty. The web interface felt like it was running through mud. Then it started happening multiple times a day.
The timing was bad. We were running an EOL version of SOAR with an upgrade in flight. We had a complex environment. And the database, a PostgreSQL instance sitting at around 450GB, was the thing nobody on the team had deep expertise in.
Learning to See the Problem
The first thing I had to learn was how to actually see what was happening inside PostgreSQL in real time. The query that became my best friend over the next several months was this one:
SELECT pid, wait_event_type, wait_event, state,
now() - query_start AS duration, left(query, 120) as query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC; That query shows you every active database connection, what it’s waiting on, and how long it’s been running. When your system is healthy you see a handful of short-lived queries. When it’s in trouble you see a wall of queries stuck on IO: DataFileRead that have been running for ten minutes.
The first time I ran it during a 502 incident I had no idea what I was looking at. That’s where Claude came in.
AI as a Database Colleague
I want to be very careful about how I describe this, because there’s a lot of breathless content out there about AI doing things for you. That’s not quite what happened here.
What actually happened is I would paste the output of that pg_stat_activity query into Claude and ask what I was looking at. And instead of getting a generic explanation of PostgreSQL, I’d get a specific answer about what was happening in my system right now. “These eight queries are all scanning the artifact table looking for rows where search_vector_version needs updating. There’s no index on that column. The indexer has a backlog that ranges from 0 to 1.9 billion. This is your main IO problem.”
That’s not the kind of answer you get from a documentation page. That’s the kind of answer you get from a DBA colleague who can read a situation.
Over the course of several months I learned, through these conversations:
- what autovacuum is and why it matters
- what dead tuples are and how they accumulate
- what VacuumDelay means when you see it in a wait event
- why a 72GB table with 202 million rows causes problems when you run an hourly DELETE job against it
- what pg_terminate_backend does and when it’s safe to use it.
None of that knowledge came from me reading documentation cold. It came from working through real incidents in real time with something that could explain the “why” while I was dealing with the “what.”
What Was Actually Wrong
By the time we’d worked through several weeks of incidents, the picture was clear. There were four main problems:
Dead tuple bloat. PostgreSQL uses a process called autovacuum to clean up rows that have been deleted or updated but not yet reclaimed. Our autovacuum configuration used the default settings, tuned for databases with modest row counts. On a 450GB database with tables holding 200 million rows, those defaults were far too conservative. Tables were accumulating millions of dead tuples and not getting cleaned fast enough. The worst offender was playbook_run_log. A 72GB table that gets hit by an hourly DELETE job as part of SOAR’s data retention process. When that job ran against a heavily bloated table, it would saturate IO and bring the system to its knees.
The search_vector_version backlog. Splunk SOAR maintains a full-text search index on the artifact and container tables. The indexer tracks which rows need reindexing using a column called search_vector_version. In our environment, the range of values in that column had grown from 0 to 1,969,704,853. Nearly two billion. Because there was no index on that column, the SOAR indexer was doing full sequential scans of an 18-million-row artifact table repeatedly throughout the day, just to find rows that needed updating. This was a known issue with our EOL version. We opened a Splunk support case.
The retention job timing. SOAR’s DataRetentionStrategy job runs every hour and deletes records older than the configured retention window from playbook_run_log. On a well-maintained table this is fine. On a table with a million dead tuples already accumulated, the DELETE operation competes for IO with live traffic. We saw 502 errors regularly in the window after this job ran.
Cascading failures. When the database got overloaded, it wasn’t just slow queries, it caused phantom_decided (SOAR’s automation engine) to crash. When decided crashed, all the engine runners restarted. When the runners restarted, their playbook interpreter cache was empty. So for 30-60 minutes after any major DB incident, playbook execution was slow even after the database itself recovered. One problem cascaded into three.
What We Fixed
Autovacuum tuning. We applied custom autovacuum settings to every significant table in the database, replacing the 20% scale factor default with settings between 0.1% and 1% depending on table size and write volume. This meant autovacuum fired much more aggressively on high-churn tables instead of waiting until a fifth of the table was dead tuples before doing anything.
Manual vacuums. While tuning autovacuum fixed the maintenance going forward, we had to deal with the existing bloat. This meant running manual VACUUM operations on the worst tables during quieter periods. playbook_run_log alone went from 13 million dead tuples to near zero.
Query monitoring and emergency kills. We developed a set of SQL queries and procedures for identifying and killing the specific query patterns causing problems: artifact search_vector scans, indicator dashboard aggregates, lock storms. This became standard operating procedure for the on-call rotation.
Retention policy. We reduced the live operational data in the database to the past six months, archiving everything older to cold storage. This alone significantly reduced the size of several high-churn tables and made the hourly retention job much less impactful.
The upgrade. We moved from our EOL version to Splunk SOAR 8.4. After the upgrade, the search_vector_version backlog issue improved dramatically. Pre-upgrade we’d see 10-15 indexer queries piling up at a time, each running for minutes. Post-upgrade we see one query at a time, running for seconds.
The Numbers
Before we started this work:
- Daily 502 outages affecting the entire analyst team
- Playbooks running 4x longer than normal after incidents
- Database ping response times exceeding 25 seconds during incidents
- phantom_decided crashing multiple times per week
After:
- Ping response time: 0.016 seconds (essentially instant)
- Autovacuum maintaining all major tables automatically
- search_vector_version backlog: 190,000 rows pending (down from millions)
- System stable enough that I can actually take a day off without checking in
What I’d Do Differently
Instrument the database earlier. We were flying blind for too long. The monitoring script we eventually built — a Python container that queries the database every 15 minutes, feeds the results to a local LLM, and posts alerts to Slack — should have existed from day one.
Don’t assume the defaults are right for your workload. PostgreSQL’s default autovacuum settings are reasonable for many databases. They were wrong for ours. It took months of incidents to understand why.
On Using AI for This Kind of Work
I’ve seen the takes about AI replacing engineers. That’s not what this was. What this was is closer to having access to a knowledgeable colleague who happens to have read every PostgreSQL manual ever written and can apply that knowledge to your specific situation at 2am when something is on fire.
The value wasn’t in Claude doing things for me. The value was in Claude helping me understand what I was looking at well enough to make good decisions. After months of working through these incidents, I actually understand PostgreSQL operational concepts now. I know what a dead tuple is. I know why autovacuum matters. I know how to read pg_stat_activity and understand what I’m seeing.
That knowledge lives in my head now. It didn’t before.
Whether you’re a SOAR engineer, a security analyst, or anyone else who finds themselves responsible for infrastructure that’s outside your primary expertise don’t be too proud to ask for help, and don’t assume the help has to come from a human.
Want to improve the performance and reliability of your Splunk SOAR environment?
Whether you’re troubleshooting recurring database issues, optimizing PostgreSQL, or planning an upgrade, TekStream’s experts can help you build a more resilient SOAR platform.
Connect with our team to start the conversation.
About the Author
David Burns is a security engineer with experience working with Splunk Enterprise Security and Splunk SOAR (formerly Phantom) for a large fortune 200 bank. Before that he was a System Security Engineer working on the automation of security testing of OT systems.
He brings his 20+ years programming background to use SDLC in rapid development of playbooks, custom functions, and more leading to modularity, re-use in design, and better long-term maintenance. For example, creating deeper integration for escalation through Slack and creating EDL management for multiple clients.
At TekStream, he developed the slack escalation methodology that notifies customers of events that need their attention as well as a way of process for generating and updating EDLs within Splunk.