#PostgresMarathon 2-010: Prepared statements and partitioned table lock explosion, part 2 In 2-009, we focused on Lock Manager's behavior when dealing with prepared statements and partitioned tables. And observed a lock explosion in our simple synthetic example: from 8 locks (custom plans) during first 5 calls, to 52 locks (building generic plan) in the 6th call, to 13 locks (using cached generic plan) in the 7th and subsequent calls. We left with questions: - this lock explosion at the 6th call – why is it so exactly and can it be avoided? - why do we lock all 12 partitions even though runtime pruning removes 11 of them? Let's dig deeper: why 52 locks at the 6th call? In 2-008, we studied the code flow for unpartitioned tables. The same pattern applies here, but with a critical difference: while for the first 5 calls we had very efficient planning-time partition pruning, it is not used during generic plan building in the 6th call. Let's trace the execution (using PG18 sources; `//` comments are mine): Step 1: [Acquire planner locks](github.com/postgres/postgres…) GetCachedPlan() (github.com/postgres/postgres…) starts by locking the Query tree via AcquirePlannerLocks() (github.com/postgres/postgres…): AcquirePlannerLocks(plansource->query_list, true); The Query tree (parser output) contains only the parent table reference. Result -- 4 locks acquired (parent table 3 parent indexes). Partition locks will be acquired later during planning. Step 2: Decide plan type (github.com/postgres/postgres…) Function choose_custom_plan() (github.com/postgres/postgres…) decides whether to use custom or generic plan: if (plansource->num_custom_plans < 5) return true; // NOT taken (num_custom_plans = 5) if (plansource->generic_cost < avg_custom_cost) return false; // TAKEN (generic_cost = -1, meaning "not yet calculated") Result here: use generic plan. Step 3: build generic plan github.com/postgres/postgres…) Since no cached plan exists yet (CheckCachedPlan() -- github.com/postgres/postgres… -- returns false), we build one: plan = BuildCachedPlan(plansource, qlist, NULL, queryEnv); // ^^^^ NULL = no bound parameters Next, inside step 3: partition pruning fails. Let's see: During planning, prune_append_rel_partitions()(github.com/postgres/postgres…) attempts to prune partitions. It calls get_matching_partitions()(github.com/postgres/postgres…), which eventually hits this critical check (github.com/postgres/postgres…): paramids = pull_exec_paramids(expr); if (!bms_is_empty(paramids)) // TRUE - $1 detected as PARAM_EXEC { context->has_exec_param = true; if (context->target != PARTTARGET_EXEC) // TRUE - we're in planner return PARTCLAUSE_UNSUPPORTED; // PRUNING FAILS } Why pruning fails: - Our query has "WHERE event_time = $1" - No boundParams provided (generic plan building) - Parameter detected as PARAM_EXEC - Context is PARTTARGET_PLANNER (not executor) - Returns PARTCLAUSE_UNSUPPORTED Result -- get_matching_partitions() returns all partitions: /* If there's nothing usable, return all partitions */ if (pruning_steps == NIL) return bms_add_range(NULL, 0, rel->nparts - 1); // ALL 12 PARTITIONS Next, still inside step 3 -- expand all partitions Since pruning failed, the planner opens every partition via try_table_open(): childrel = try_table_open(childOID, lockmode); All 12 partitions and their 36 indexes are opened and added to the PlannedStmt's rtable (range table - the list of all tables and indexes referenced in the plan). Next, back to the top level: cache the plan (github.com/postgres/postgres…) After BuildCachedPlan() returns, the plan is cached: plansource->gplan = plan; // Cache plan with ALL partitions The generic plan is now cached with all 52 relations in its range table. The 52 planner locks remain held until transaction end. To summarize the mechanics of the 6th call: GetCachedPlan() ├─ AcquirePlannerLocks() → 4 locks ├─ choose_custom_plan() → decide generic └─ BuildCachedPlan() └─ pg_plan_queries() └─ standard_planner() └─ build_simple_rel() / expand_inherited_rtentry() ├─ prune_append_rel_partitions() → pruning fails ├─ try_table_open() for each partition → 12 locks (tables) └─ get_relation_info() for indexes → 36 locks (indexes) [returns plan with all 52 rels] └─ Cache it → plansource->gplan = plan So, on execution 6, we build a generic plan but cannot prune at planning time without parameter values. The planner must consider all partitions, locking all 52 relations. To be continued.

#PostgresMarathon 2-008: LWLock:LockManager and prepared statements As was discussed in #PostgresMarathon 2-002, for a simple SELECT from a table, at planning time, Postgres locks the table and all of its indexes with AccessShareLock. A simple demo to remind it (let me be a bit weird here and save some bytes when typing SQL): test=# create table t(); CREATE TABLE test=# create index on t((1)); CREATE INDEX test=# create index on t((1)); CREATE INDEX test=# create index on t((1)); CREATE INDEX test=# create index on t((1)); CREATE INDEX test=# create index on t((1)); CREATE INDEX test=# \d t Table "public.t" Column | Type | Collation | Nullable | Default -------- ------ ----------- ---------- --------- Indexes: "t_expr_idx" btree ((1)) "t_expr_idx1" btree ((1)) "t_expr_idx2" btree ((1)) "t_expr_idx3" btree ((1)) "t_expr_idx4" btree ((1)) test=# test=# begin; explain select from t; BEGIN QUERY PLAN ----------------------------------------------------- Seq Scan on t (cost=0.00..39.10 rows=2910 width=0) (1 row) test=*# select relation::regclass, mode from pg_locks where pid = pg_backend_pid(); relation | mode ------------- ----------------- t_expr_idx2 | AccessShareLock pg_locks | AccessShareLock t_expr_idx3 | AccessShareLock t_expr_idx4 | AccessShareLock t_expr_idx | AccessShareLock t_expr_idx1 | AccessShareLock t | AccessShareLock | ExclusiveLock (8 rows) test=*# – indeed, all indexes locked. ## Using prepared statements to reduce locking To mitigate it, we can just use prepared statements. Let's create one: prepare test_query (int) as select from t; And then run this snippet 7 times: begin; explain (verbose) execute test_query(1); select relation::regclass, mode from pg_locks where pid = pg_backend_pid() and relation::regclass <> 'pg_locks'::regclass; rollback; Six (6) times, we'll see that all indexes are locked: relation | mode ------------- ----------------- t_expr_idx2 | AccessShareLock t_expr_idx3 | AccessShareLock t_expr_idx4 | AccessShareLock t_expr_idx | AccessShareLock t_expr_idx1 | AccessShareLock t | AccessShareLock (6 rows) And on the 7th time, we'll see this: relation | mode ---------- ----------------- t | AccessShareLock (1 row) – only the table is locked. ## The mystery of the 6th execution I was surprised to see single lock on the 7th call, not on the 6th. I expected that for the first 5 times, so-called custom plan would be used and all 6 relations (table 5 its indexes) locked, and on the 6th time, we switch to generic plan and lock only the table itself. Why 5 calls? Per the docs: postgresql.org/docs/current/… > By default (that is, when plan_cache_mode is set to auto), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters. The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable. But why do we see, the locking behavior changes only on the 7th call, not 6th? Checking prepared statements stats: test=# select * from pg_prepared_statements \gx -[ RECORD 1 ]--- ------------------------------------------- name | test_query statement | prepare test_query (int) as select from t; prepare_time | 2025-10-15 02:06:25.570003 00 parameter_types | {integer} result_types | {} from_sql | t generic_plans | 2 custom_plans | 5 -- indeed, we had custom plan used 5 and then it switched to generic plan, using it for 6th and 7th call. But why on the 6th, call we saw all indexes being locked? That's a mystery. So, why does the 6th execution use generic plan but still locks all indexes? The answer lies in two different locking mechanisms in PostgreSQL's plan cache: 1. Planner locks - lock everything in the Query tree (all potential access paths) 2. Executor locks - lock only what's in the PlannedStmt (what's actually used) Let's look at source code (I use PG18 code here). ## The first five As already mentioned, for the first 5 executions, it uses custom plans. Looking inside GetCachedPlan, we see the sequence of steps: github.com/postgres/postgres… 1. Acquire planner locks: github.com/postgres/postgres… AcquirePlannerLocks(plansource->query_list, true); - Locks the Query tree (parser output) - Query tree contains: table all 5 indexes in our case - In result, it locks the table and its 5 indexes – overall, 6 locks 2. Decide plan type: github.com/postgres/postgres… /* Generate custom plans until we have done at least 5 (arbitrary) */ if (plansource->num_custom_plans < 5) return true; // Use custom plan 3. Build custom plan: github.com/postgres/postgres… plan = BuildCachedPlan(plansource, qlist, boundParams, queryEnv); // ^^^^^^^^^^^ actual parameter value - Planner creates plan with specific parameter value - Plan is not cached (thrown away after execution) - Result: 6 planner locks remain until the end of transaction ## The number six On the 6th execution, the start is the same as for the first five. It all looks the same, except one thing – the process of building cached plan is different. 1. Acquire planner locks – the same as before (see above) 2. Decide plan type – now it's different, for default plan_cache_mode (auto), the switch to generic plan happens: github.com/postgres/postgres… /* * Prefer generic plan if it's less expensive than the average custom * plan. (Because we include a charge for cost of planning in the * custom-plan costs, this means the generic plan only has to be less * expensive than the execution cost plus replan cost of the custom * plans.) * * Note that if generic_cost is -1 (indicating we've not yet determined * the generic plan cost), we'll always prefer generic at this point. */ if (plansource->generic_cost < avg_custom_cost) return false; // Use generic plan - this time num_custom_plans = 5, so threshold reached - generic_cost = -1 (not calculated yet) - -1 < avg_custom_cost – this means try generic plan 3. Check for cached plan: github.com/postgres/postgres… if (CheckCachedPlan(plansource)) // Returns FALSE - no plan yet 4. Build generic plan: github.com/postgres/postgres… plan = BuildCachedPlan(plansource, qlist, NULL, queryEnv); // ^^^^ NULL here means 'generic' plansource->gplan = plan; // Cache it - Planner builds plan without parameter values - Creates PlannedStmt with only table in our case (seq scan chosen) - Plan is cached for future use - Result: still 6 planner locks (from Step 1) - no executor locks acquired yet (!!) So, here is what I didn't understand properly when I was reading just the docs: on execution 6, we build the generic plan but don't go through the "use cached plan" path that would acquire executor locks. We still use planner locks – it means, we lock all indexes. ## Finally there – number seven and beyond Starting with execution 7 and further, we use cached generic plan, and executor locks – in our case (seq scan), it means only the table is locked: 1. Acquire planner locks (table 5 indexes) 2. Decide plan type → use generic 3. Check for cached plan: github.com/postgres/postgres… if (CheckCachedPlan(plansource)) // Returns TRUE - plan exists! { plan = plansource->gplan; // Use cached plan } 4. Inside CheckCachedPlan executor locks are acquired: AcquireExecutorLocks(plan->stmt_list, true); - Locks the PlannedStmt (planner output) - PlannedStmt contains: only table (seq scan chosen) in our case - 1 executor lock acquired - Result: Only 1 lock ## What we learned To summarize, here is what's happening: | Execution | Plan type | What happens | Locks | |-----------|-----------|------------------------------------------|-----------------------| | 1-5 | Custom | Build plan with params → planner locks | 6 (table 5 indexes) | | 6 | Generic | Build generic plan → planner locks | 6 (table 5 indexes) | | 7 | Generic | Use cached generic plan → executor locks | 1 (table only) | Prepared statements can dramatically reduce LWLock:LockManager contention - but only starting with the 7th execution, not 6th. During executions 1-6, with default plan_cache_mode (auto), Postgres still uses planner locks that lock all tables involved in the query, with all their indexes.

Top 10 Benefits Administration Software Vendors, Market Size and Forecast 2024-2029 1. @ADP 2. @Benefitfocus 3. @businessolver 4. @Workday 5. @AlightSolutions 6. @Benify_Global 7. @Oracle 8. @MarshMcLennan 9. @PlanSource 10. @hellobenifex appsruntheworld.com/top-10-h…

The cloud banking software provider is finishing up the year with a bang, announcing a partnership with a leading loan servicer and recruiting PlanSource veteran Srini Venkatramani as the company's new head of product. inman.com/2024/12/13/blend-l…

@PlanSource your employee Matthew Feinberg is harassing a Palestinian civilian in Gaza. Take a look at her post - did she deserve this spiteful response from your company? @LinkedIn helped @PlanSource hire Feinberg.

Take a look at the 7 solutions on the ShortList for Benefits Administration by @holgermu bit.ly/3QtRY3i @Benefitfocus @businessolver @CastlightHealth @TELUSHealth @PlanSource @ThomsonsOnline @WageWorks

Final Thoughts on #HRTechConf - The Magic of Being There. It hasn’t been that long since I've been allowed in this event; I don't take the ability to interact in live space for granted. It also reminded me of the good things that can come from chance encounters. For example, I was in a conversation with the @PDSVista team, a midmarket HCM that partners with our i9 client, and I mentioned they should consider Plansource as an integration partner. I offered to make an introduction to @PlanSource's Mitch Hansen when, almost on cue, Mitch appeared from around the corner. I thought I’d parlay my good luck at the craps tables but instead made introductions. It’s rewarding when I can live up to my name - @Chief_Connector - and it paves the way for a productive collaboration. - All in on AI. Many of my conversations centered on Generative AI. Four themes emerged: 1. ROI (Return On Information): How do AI tools contribute to talent management and productivity? Are we getting a measurable return on the wealth of data being processed? My conversations with @RichardRosenow of @OneModel gave me further insights into their pioneering advancements in data-driven #worktech decision-making. A decade ago, they took on my challenge to model #QualityofHire, even when competitors scoffed: "No one is asking for QofH!" 2. Data Governance- Ethical Use & Compliance: Balancing data security and ethical integrity, the aim is to make HR-focused AI algorithms unbiased. We are currently vetting several vendors in this space to simplify risk auditing and ensure ongoing compliance. We met with one of them at the show, HolisticAI, a platform for ethical AI management. Simplifying AI risk auditing and ensuring compliance with evolving regulations will propel #HRtech’s use of generative AI.  3. ROI (Return On Integrations): Evaluating the intricacies of fusing AI with current HR infrastructure is crucial. When executed effectively, as consistently demonstrated by @CloudConnectors, the rewards in system compatibility and scalability are substantial. 4. Workforce ROI (Return On Impact): As AI starts automating more HR functions, how will it affect applicants and employees? Are there plans for reskilling, is it promoting productivity and well-being? Refer.io’s Ryan Kohler has successfully built a job seeker community using AI that discovers/matches/upskills relevant interests and skillsets to open positions. - The Power of the Pen - for over 20 years, I have been giving out ink pens at tradeshows. I make many of them from olive branches imported from Israel. I will see people I gave a pen to years ago and they'll tell me they still have it! It gets such a warm reaction and has resonance beyond our shared professional interests. It’s amazing how meaningful a small gesture like that can be. What were your main takeaways?

ARC Fertility and PlanSource have partnered to offer Fertility and Family-Forming Plans in the Broker and Employer Marketplace. Read more here: arcfertility.com/arc-fertili…

#CISO perspective on why #boards don’t fully grasp cyber attack risks buff.ly/44nhE5F @HelpNetSecurity @PlanSource #cyberthreats #cyberattacks #cyberrisk #databreaches #cybersecurity #security #tech #business #leaders #leadership #management #governance #CIO #CTO #CEO

#CISO perspective on why #boards don’t fully grasp cyber attack risks buff.ly/44nhE5F @HelpNetSecurity @PlanSource #cyberthreats #cyberattacks #cyberrisk #databreaches #cybersecurity #security #tech #business #leaders #leadership #management #governance #CIO #CTO #CEO

CISO perspective on why boards don't fully grasp cyber attack risks - helpnetsecurity.com/2023/07/… - @PlanSource @jdchrist #Attacks #CyberRisk #investment #strategy #CyberSecurity #netsec #security #InfoSecurity #CISO #ITsecurity #CyberSecurityNews #SecurityNews

Axonius helped #HR tech company PlanSource get an accurate asset inventory — but having that single source of truth enabled them to identify and prioritize vulnerabilities in their environment. Learn how PlanSource maximized their investment with Axonius. axonius.com/resources/planso…

We are excited to announce @CarrotFertility as our Partner of the Week, where we highlight one of our #PlanSource Marketplace Partners and the value they bring to the #benefits industry. Learn more about our partnership and their unique services! bit.ly/3XgJMVy #HRTech

These senior #security leaders are in our board book: 💥Nicholas Bruno, @SAI_Compliance 💥@dcass001, @GSR_io 💥Benjamin Chang, @GlookoInc 💥James Chatwani, @pointhomeequity 💥Aradhna Chetal, @TIAA 💥Julie Chickillo, @GuildEducation 💥David Christensen, @PlanSource

.@PlanSource cancelled my Cobra insurance due to an accounting error. I have a chronic illness and need my medication before the approval has to start over. I've called 8 times this week. Please help @PlanSource.

Spring is a time of growth & #PlanSource is growing its lead at #employeeengagement, while making interactions with us smoother and richer than ever. Get a sneak peak of our Spring 2023 Product Release here! bit.ly/3nNOr3D #benefitstechnology #HRTech #productrelease

Woohoo! Hi there from Booth 605! #SHRM #SOAHR2023 #ATL

“When we have to identify the impact of a zero day, we’re hitting 99% to 100% accuracy.” — David Christensen, VP and CISO at PlanSource. Read our latest case study about how PlanSource saw value after leveraging Axonius. #cybersecurity #assetinventory axoni.us/3Xfo0zL

Congratulations to @LogicMonitor and @PlanSource for being named 2023 Campus Forward Award winners - an award that recognizes companies embracing innovative recruiting strategies and supporting the next generation of talent. Learn more: bit.ly/3WWLIk6 #CFA2023

Together, @Ameritas and @PlanSource are elevating the member enrollment experience with clean, transparent data and faster change processing on top of the Noyo platform.👏 Learn more: hubs.la/Q01zLtVh0