Percona ClusterSync for MongoDB (PCSM) replicates data between MongoDB clusters to keep migrations with near-zero downtime. Prior to version 0.9.0 it required the source and target to run the same major version, which ruled out the lift-and-shift move most migrations want: going from an older major like 6.0 straight onto a newer one like 8.0. … Continued

The post Migrating from MongoDB 6.0 to 8.0: How Percona ClusterSync Handles Cross-Version Replication appeared first on Percona.

Last week, I traveled to San Jose to attend the ACM CAIS conference. On Day 0, I gave a short talk at the Supporting our AI Overlords (SAO) workshop. And yes, I promise to write a summary of our paper, "A Case for Simulation-Driven Resilience in Agent-First Data Systems" soon! 

To start with an overall impression of the conference: much of the work presented felt exploratory and anecdotal. Since the compound AI space is still so new, many work seemed to share on-the-ground best practices that worked for them rather than principled results. Some talks really leaned into the "agent, act like a senior engineer and don't make mistakes" vibe. This was especially apparent in the "Agent Skills Workshop". I am not saying this is a bad thing, I learned some valuable lessons from that workshop, which I'll share below.

CAIS defines the conference's scope broadly as "research on compound AI architectures, optimization, and deployment". Unfortunately, this broadness seemed to work against the main track. Attendance at the primary conference talks was low, and it often felt like attendees were there solely to present their own work rather than engage with others, likely because the subject matter was spread too thin.

In contrast, the workshops were highly focused, which led to much better engagement and active listening from the audience. Moving forward, I think CAIS would benefit greatly from narrowing its focus, maybe specifically focusing more on data systems and infrastructure in support of AI.

On that note, what happened to our collective attention span? CAIS limited paper presentations to 7 minutes with just 2 minutes for questions. This year, SIGMOD also shifted to 9-minute talks. Our own paper, "LeaseGuard: Raft Leases Done Right!", got a mere 9 minutes in the spotlight after Jesse traveled all the way to Bangalore to present it. I've even heard that USENIX Security is down to 3-minute talks now. Should we maybe consider slowing down? After all, isn't attention all we need?

Agent Skills workshop (Day 0)

In the first talk, Graham Neubig discussed OpenHands, their open-source AI developer agent platform that's getting a lot of traction in highly regulated fields like finance and healthcare to speed up software development. A big theme of his talk was skill induction: "the process of inducing/verifying programmatic or prompt-based capabilities through testing/evaluation to enable single-agent systems complete complex long-horizon tasks". Through leveraging offline human-annotated examples or online user feedback, skill induction kicks off a rapid learning phase. In web navigation tests like WebArena, an agent's success rate can ramp up dramatically over a small number of trials before settling into a robust repeatable skill set.

Later on, Kanav Garg (co-founder of Core Automation) walked us through the lifecycle of a Reinforcement Learning (RL) environment. He defined it as a continuous loop made up of an actionable prompt, a starting state, a runtime environment (like a Docker container), configuration, and a reward system. The main takeaway here was that successful RL needs careful difficulty calibration and precise reward shaping to keep agents from hacking the reward system. To get this right, engineers have to actually look at the agent's traces instead of blindly trusting that the numbers on a chart are going up. Kanav also said that data environments are living projects with a shelf life of at most two months, and this means continuous learning from task failures and automated data pipelines are far more important/effective than relying on static expensive human data.

SAO: Supporting our AI overlords (Day 0)

The core theme of the SAO workshop was that agents are rapidly becoming both the primary users and the builders of data systems, and this shift is creating a vast new design space demanding entirely new abstractions. The workshop featured three keynote speakers and was incredibly well attended. With no seating left, people were standing in the doorway just to watch the talks.

Aaron Katz from Clickhouse gave a nice talk on this transition from human to agent users. He said that agents aren't just querying data anymore; they are actively provisioning services, so they need their own identities and budgets. Because agents drive such massive concurrency and require interactive latencies, traditional per-query pricing models are becoming way too punitive. To keep up, platforms have to adapt to headless API-first experiences. He said that Clickhouse is launching the ability to build agents directly inside the database for lower latency and for blending structured and full-text search.

Next, Andy Pavlo talked about databases being the "final boss" for agents. Check out his slides. It is classic Andy humor and style, though a Wu-Tang Clan reference was sorely missing this time. Andy focused heavily on automated database tuning and development, comparing their Proto-X tuning agent with ChatGPT's Lambda-tune. While Proto-X gets better optimization results, it takes 12 hours to train per database, whereas ChatGPT is fast (14 minutes) but performs terribly. To bridge this gap, they adopted LLMs to boost automatic tuning algorithms by leveraging prior history. In the second (and shorter) part of the talk, Andy also noted that while coding agents are making progress, they still completely fail at building complex database components like query optimizers, which require much more support. Although they have successfully "vibed" and manually verified a couple of optimization passes (like DPHyp and Unnesting v2), blindly accepting an LLM's output is fraught with problems because verifying query plan equivalence is notoriously difficult (despite solver efforts from UW, Berkeley, and Microsoft). It seems like coding agents love to add special-case code when an optimizer actually needs to be as general as possible.

Finally, Nikita Shamgunov (ex-Neon, now Databricks) discussed the infrastructure needed for agents, categorizing this down into three pillars: state, compute, and middleware. He argued that because agents speed up the dev loop by 1000x, true serverless architectures are now absolutely critical to avoid the insane costs of overprovisioning. He also described Neon's architecture, highlighting how separating compute from storage allows for instant database branching for agents using microVMs. While he shared some genuinely interesting technical points, the presentation itself was pretty dry and felt like it was missing a clear focus.

The workshop ended with a panel. The organizers tried to spark a debate, but there wasn't much disagreement. The general consensus was that while traditional OLTP and OLAP boundaries will remain, agents will increasingly be the ones conducting tasks that span seamlessly across them. After the panel, we headed to a MongoDB-sponsored happy hour, where the workshop audience had more time to have relaxed conversations about the breakneck hellscape transition our industry is currently going through.

Wednesday (Day 1)

Since CAIS is an AI systems conference, the focus is more on building the systems that surround AI models rather training individual AI models. The dominant theme this year seemed to be [multi-]agent architectures, coordination protocols, and workflow design. The emphasis is on composition: how do we organize these agents, manage their contexts, coordinate their interactions, and handle their lifecycles?

A second major trend is the growing focus on day-to-day operations. Entire sessions were dedicated to evaluation, trace analysis, failure detection, routing, and cost optimization. With so many papers covering efficiency, scheduling, and economic tradeoffs, it is clear that the industry is shifting from just maximizing capability to maximizing capability per dollar.

My shortlist from Day 1:

• TraceFix: Repairing Agent Coordination Protocols with TLA+ Counterexamples
• Tressoir: Unifying Online, Offline, and HIL Design and Evolution of Multi-Agent Systems via Interpretable Blueprints 
• Expansion-Contraction: A Multi-Agent Graph Traversal Pattern for Compound AI Systems
• A Language for Describing Agentic LLM Contexts 
• Understanding and Improving Communication Performance in Multi-node LLM Inference
• Echo: KV-Cache-Free Associative Recall with Spectral Koopman Operators
• Robust Batch-Level Query Routing for Large Language Models under Cost and Capacity Constraints
• CAMI: Practical Cost-Aware Agent-Guided Multi-Indexing for Semantic Retrieval

Here a brief overview of the TraceFix paper, which is most relevant to my interests.

TraceFix: Repairing Agent Coordination Protocols with TLA+ Counterexamples

TraceFix tackles the coordination failures that happen when multiple LLM agents try to work together concurrently on shared tasks. It is crucial to understand that this paper is not about generating classical distributed computing algorithms; rather, it focuses on scaffolding the formal "rules of engagement" for multi-agent LLM systems. When agents collaborate on domain tasks requiring fine-grained mutual exclusion over shared mutable resources (such as editing the same codebase or scheduling access to a simulated lab instrument) they naturally run into interleaving-sensitive bugs like deadlocks, missed handshakes, and race conditions. TraceFix solves this by isolating the coordination layer, formally verifying the protocol for how agents use shared locks and message channels, and then allowing the agents to remain completely autonomous in executing their actual domain-specific work.

To achieve this, TraceFix introduces a verification-first pipeline where an orchestration agent first synthesizes a declarative protocol topology and writes the behavioral logic in PlusCal. Before any agent takes action, the TLA+ model checker (TLC) exhaustively searches this proposed protocol for safety violations and feeds concrete counterexample traces back to the agent for iterative repair until the code is fully verified. At runtime, these verified process bodies are compiled into agent prompts, and a monitor strictly enforces the approved topology by rejecting any invalid or out-of-bounds coordination attempts. TraceFix is evaluated across a benchmark of 48 complex tasks. The system achieved a 100% verification success rate within four repair iterations and significantly improved runtime task completion, proving that formal model-checker feedback can effectively eliminate the deadlocks and resource clashes in multi-agent workflows.

Counterintuitively, the introduction of this formal coordination scaffolding did not bog the system down with unnecessary overhead, but rather, it significantly accelerated execution. By structurally preventing agents from colliding over resources (a problem that caused a massive 61.2% contention rate and endless retry loops in unstructured chat-only setups) TraceFix eliminated wasted trial-and-error steps. As a result, the verified topology-monitored protocol executed in an average of just 93 seconds using 62 tool-call steps. This vastly outperformed both the chaotic chat-only baseline (229 seconds and 203 steps) and a sequential single-agent baseline (~304 seconds).

Thursday (Day 2)

On Thursday, the papers were more about making agent systems manageable. Many of these papers are about memory, planning, governance, verification, security, and control. Researchers are increasingly treating agents as long-running software systems that require architecture, interfaces, observability, safety mechanisms, and operational discipline. This resembles the evolution of distributed systems from clever protocols toward operational concerns such as consistency models, monitoring, fault tolerance, and standards.

A second trend was the emergence of agents as compound systems rather than monolithic models. Instead of expecting a single model to solve everything, people arenow  building ecosystems of interacting components with explicit roles. The vibe here is more like systems engineering for AI. 

My shortlist:

• Securing Agents With Tracked Capabilities (putting the agents in a programming-language-based safety harness, Scala 3 with capability types)
• Dossier: Deep Research via Ledger-Driven Branching Search and Query Encoding Learning
• Open Agent Specification: Enabling Cross-Framework Comparison of AI Agents
• Do Agents Need to Plan Step-by-Step? Rethinking Planning Horizon in Data-Centric Tool Calling
• The Verifier Tax: Horizon Dependent Safety–Success Tradeoffs in Tool Using LLM Agents
• Supervisory Control Theory for LLM Revision
• FedMECA: Scalable Federated Learning via Memory-Efficient and Concurrent Aggregation

Friday (Day 3)

In the Friday program, multiple papers study agent societies, debate, persuasion, socialization, consensus formation, and safety in multi-agent environments. Also, several papers focus on practical concerns such as routing requests across models, optimizing energy consumption, serving multi-agent systems, evaluating agent frameworks, and integrating AI into production workflows. 

My shortlist:

• MoltGraph: A Longitudinal Temporal Graph Dataset of Moltbook for Coordinated-Agent Detection
• Why Johnny Can’t Use Agents: Industry Aspirations vs. User Realities with AI Agents
• The Cost of Consensus: Isolated Self-Correction Prevails Over Unguided Homogeneous Multi-Agent Debate
• SEAR: Schema-Based Evaluation and Routing for LLM Gateways 
• AgentStop: Terminating Local AI Agents Early to Save Energy in Consumer Devices

Before looking at PostgreSQL, I'll first introduce the problem by showing how MongoDB's document model and multi-key indexes handle compound indexes across a one-to-many relationship. Then we will see how to approximate this in PostgreSQL by denormalizing on the "many" side or the "one" side, how to maintain consistency with cascade foreign keys or triggers, and how to accelerate filtering with sorted pagination using B-tree, GIN, or RUM indexes.

Multi-key indexes in MongoDB allow compound indexes on one-to-many relationships. For example, the following index covers fields from both the children (child_value) and the parent (parent_value) when children are embedded as an array (in a children field) within the parent document:

db.parent.createIndex(
 { "children.child_value": 1, parent_value: 1 }
);

Such an index can efficiently support an equality filter on child_value combined with a sort and pagination on parent_value. For instance, finding the top 10 parents, ordered by parent_value, where at least one child has a child_value of 0.9:

db.parent.find(
 { "children.child_value": 0.9 }
).sort({ "parent_value": 1 }).limit(10);

It is important to understand the semantics of predicates on embedded arrays. This query does not return the top 10 children with this value — a parent may have multiple matching children, but we get one result per parent. This differs from a relational join (or $unwind in a MongoDB aggregation pipeline), which would produce one row per child. Here, the predicate tests only for the existence of at least one child with the specified value. The result set contains distinct parents, not (parent, child) pairs.

A multi-key index stores multiple index entries per parent document — one for each distinct child_value in the array — paired with the parent's parent_value. The number of index entries per parent equals the number of distinct child values, which can be less than the total number of children when multiple children share the same value. For example, given these documents:

{
  _id: 24,
  parent_value: "Y",
  children: [
    { child_num: 1, child_value: 0.9 },
    { child_num: 2, child_value: 0.8 }
  ]
},
{
  _id: 42,
  parent_value: "X",
  children: [
    { child_num: 1, child_value: 0.5 },
    { child_num: 2, child_value: 0.9 },
    { child_num: 3, child_value: 0.9 },
    { child_num: 4, child_value: 0.7 }
  ]
}

The index contains three entries for the second document, because the two children with child_value: 0.9 produce only one index entry:

  (0.5, "X", 42) → record ID of { _id: 42 }
  (0.7, "X", 42) → record ID of { _id: 42 }
  (0.8, "Y", 24) → record ID of { _id: 24 }
  (0.9, "X", 42) → record ID of { _id: 42 }
  (0.9, "Y", 24) → record ID of { _id: 24 }

The index scan for {child_value: 0.9} finds two entries, already ordered by the second field of the compound key, parent_value. MongoDB can read them in order — "X" before "Y" — and fetch the corresponding documents without an additional sort. With a .limit(10), the scan stops after 10 distinct parents are found, making the query efficient regardless of how many total documents exist in the collection.

There is no direct equivalent of this in PostgreSQL. If the one-to-many relationship is stored as JSONB, GIN indexes can locate documents containing {child_value: 0.9} but do not preserve ordering on other fields. The query must fetch all matching documents, sort them on parent_value, and only then return the top 10. If the one-to-many relationship is normalized into two tables, no single index can span columns from both tables — a join is required first, and the sort optimization depends on the join strategy chosen by the planner.

Normalized One-to-Many

Now that the problem is defined, let's see how we can work around this limitation in a normalized model:

drop table if exists parent, child cascade;

create table parent (
 parent_id    bigserial primary key,
 parent_value float
);

create table child (
 child_id     bigserial primary key,
 parent_id    int references parent (parent_id),
 child_value  float
);

insert into parent (parent_value)
 select random() from generate_series(1, 1000)
;

insert into child (parent_id, child_value)
 select parent_id, round(random()::numeric, 4) from parent, generate_series(1, 1000)
;

create index on parent (parent_value);
create index on child (child_value);

vacuum analyze parent, child;

The following query finds the top 10 parents, by parent_value, that have at least one child with a child_value of 0.9999:

-- explain (analyze, buffers, verbose, costs off)
select *
from parent p
where exists (
  select 1 from child c
  where c.parent_id = p.parent_id
  and c.child_value = 0.9999
)
order by parent_value
limit 10;

 parent_id |     parent_value
-----------+----------------------
       956 | 0.013518349069161273
       789 | 0.022813950892476287
       307 | 0.024740344416860793
       833 | 0.041118650516225985
       493 |  0.07587347477845374
       402 |  0.08400127026866477
       256 |   0.0980166832909124
       342 |  0.11175505348300807
       846 |  0.12047992766758941
       590 |  0.16840580135419425
(10 rows)

The execution plan shows that:

1. The index on child_value was scanned to find all index entries with the expected value — here rows=99.0 — which are then used to locate the corresponding blocks in the child table (this is a Bitmap Index Scan).
2. The Bitmap Heap Scan on the child table returned all children with child_value = 0.9999 (there are 95 if we count the actual matches — a bitmap scan can have some false positives). The results were stored in memory (Materialize).
3. The index on parent_value was scanned to retrieve parents in the desired order, checking for each whether its parent_id appears in the materialized buffer. It had to read rows=169.00 parents before finding rows=10.00 that satisfied the condition, with lookups on the materialized result (loops=169).

                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.366..2.272 rows=10.00 loops=1)
   Output: p.parent_id, p.parent_value
   Buffers: shared hit=245
   ->  Nested Loop Semi Join (actual time=0.365..2.270 rows=10.00 loops=1)
         Output: p.parent_id, p.parent_value
         Join Filter: (p.parent_id = c.parent_id)
         Rows Removed by Join Filter: 16246
         Buffers: shared hit=245
         ->  Index Scan using parent_parent_value_idx on public.parent p (actual time=0.012..0.080 rows=169.00 loops=1)
               Output: p.parent_id, p.parent_value
               Index Searches: 1
               Buffers: shared hit=142
         ->  Materialize (actual time=0.000..0.006 rows=96.19 loops=169)
               Output: c.parent_id
               Storage: Memory  Maximum Storage: 20kB
               Buffers: shared hit=103
               ->  Bitmap Heap Scan on public.child c (actual time=0.032..0.151 rows=99.00 loops=1)
                     Output: c.parent_id
                     Recheck Cond: (c.child_value = '0.9999'::double precision)
                     Heap Blocks: exact=99
                     Buffers: shared hit=103
                     ->  Bitmap Index Scan on child_child_value_idx (actual time=0.016..0.016 rows=99.00 loops=1)
                           Index Cond: (c.child_value = '0.9999'::double precision)
                           Index Searches: 1
                           Buffers: shared hit=4

The other index on parent_value can serve the sort but not the filter on child_value. In both cases, many more rows must be read to combine the two conditions after a Nested Loop Join. That is the price of normalization.

In a SQL database, if we want a single index to cover both fields, they must exist in the same table.

Denormalize into the "Many" Side (Children)

A common solution is to duplicate the parent value into the child table and create a compound index:

alter table child add parent_value float;

update child
  set parent_value = parent.parent_value
  from parent
  where child.parent_id = parent.parent_id;

alter table child alter parent_value set not null;

create index on child (child_value, parent_value);

The query can now operate on the child table only:

-- explain (analyze, buffers, verbose, costs off)
select distinct parent_id, parent_value
 from child
 where child_value = 0.9999
 order by parent_value
 limit 10;

 parent_id |     parent_value
-----------+----------------------
       956 | 0.013518349069161273
       789 | 0.022813950892476287
       307 | 0.024740344416860793
       833 | 0.041118650516225985
       493 |  0.07587347477845374
       402 |  0.08400127026866477
       256 |   0.0980166832909124
       342 |  0.11175505348300807
       846 |  0.12047992766758941
       590 |  0.16840580135419425
(10 rows)

The execution plan shows a single index scan which covers:

• the filter: Index Cond: child_value = 0.9999
• the order: Presorted Key: child.parent_value

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.067..0.072 rows=10.00 loops=1)
   Output: parent_id, parent_value
   Buffers: shared hit=36
   ->  Unique (actual time=0.066..0.070 rows=10.00 loops=1)
         Output: parent_id, parent_value
         Buffers: shared hit=36
         ->  Incremental Sort (actual time=0.066..0.066 rows=11.00 loops=1)
               Output: parent_id, parent_value
               Sort Key: child.parent_value, child.parent_id
               Presorted Key: child.parent_value
               Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB
               Buffers: shared hit=36
               ->  Index Scan using child_child_value_parent_value_idx on public.child (actual time=0.014..0.054 rows=33.00 loops=1)
                     Output: parent_id, parent_value
                     Index Cond: (child.child_value = '0.9999'::double precision)
                     Index Searches: 1
                     Buffers: shared hit=36
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.132 ms
 Execution Time: 0.088 ms

There is an additional sort because the result may contain duplicate parents when they have multiple children with the same value, but it is an incremental sort so it does not have to read all children — here only rows=33.00 before producing rows=10.00. This gets the top ten parents by parent_value without reading more child rows than necessary.

This is nearly optimal. The new index resembles a multi-key index, except that it may contain duplicate entries for the same parent — unnecessary for this query, but potentially useful for others that need the detail of individual children. Like the multi-key index, it must be maintained when the parent value changes. We have two solutions: referential integrity constraint and triggers.

Update with Cascade Constraint (Recommended)

One way to keep this column automatically updated is declarative. We add an additional foreign key that enforces that the parent_value in the child table matches the one in the parent, and declare it on update cascade:

alter table parent add unique (parent_id, parent_value);

alter table child
 add constraint fk_child_parent
 foreign key (parent_id, parent_value)
 references parent (parent_id, parent_value)
 on update cascade;

Unfortunately, PostgreSQL cannot use the same index for two unique constraints that share a prefix, so two indexes are maintained on the parent table. This solution is intended for cases where updates are not frequent.

I test the performance overhead when updating one parent:

\timing on
explain (analyze, buffers, costs off, verbose on)
 update parent
 set parent_value = parent_value - 0.000000000000000042
 where parent_id = 789
;

                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Update on public.parent (actual time=0.063..0.064 rows=0.00 loops=1)
   Buffers: shared hit=16
   ->  Index Scan using parent_parent_id_parent_value_key on public.parent (actual time=0.022..0.025 rows=1.00 loops=1)
         Output: (parent_value - '4.2e-17'::double precision), ctid
         Index Cond: (parent.parent_id = 789)
         Index Searches: 1
         Buffers: shared hit=5
 Planning Time: 0.080 ms
 Trigger RI_ConstraintTrigger_a_18059 for constraint fk_child_parent on parent: time=37.950 calls=1
 Trigger RI_ConstraintTrigger_c_18061 for constraint fk_child_parent on child: time=6.767 calls=1000
 Execution Time: 44.874 ms

There is an overhead: 37.9 milliseconds to cascade the update to a thousand of children (parent action), and 6.7 milliseconds to validate each _c_hild row (child check).

This is not problematic for occasional updates. Of course, if we update all parents, the row-by-row cascading constraints can be slow:

\timing on
explain (analyze, buffers, costs off, verbose on)
 update parent
 set parent_value = parent_value - 0.000000000000000042
;
vacuum parent, child;

                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Update on public.parent (actual time=5.944..5.945 rows=0.00 loops=1)
   Buffers: shared hit=10975 dirtied=1
   ->  Seq Scan on public.parent (actual time=0.010..0.156 rows=1000.00 loops=1)
         Output: (parent_value - '4.2e-17'::double precision), ctid
         Buffers: shared hit=11
 Planning:
   Buffers: shared hit=86
 Planning Time: 0.235 ms
 Trigger RI_ConstraintTrigger_a_18059 for constraint fk_child_parent on parent: time=42659.655 calls=506
 Trigger RI_ConstraintTrigger_c_18061 for constraint fk_child_parent on child: time=3398.155 calls=506000
 Execution Time: 46103.577 ms
(11 rows)

Time: 46105.778 ms (00:46.106)

Obviously this is not a solution for frequently updated columns. However, the parent side of a one-to-many relationship often consists of static references, slowly changing dimensions, or immutable event headers, making this overhead acceptable. I also tested with the constraint set as DEFERRABLE. The elapsed time is similar, but you do not see it in the EXPLAIN output because the validation occurs at commit time.

Update with Trigger (Not Recommended)

The cascade constraint has the advantage of being declarative, but it executes internally as a trigger. We can instead create a custom trigger that cascades the update without re-validating the foreign key — assuming that nobody updates the denormalized column directly, and that it is set correctly on insert.

-- function to update the parent value in the children
create or replace function sync_parent_value() returns trigger as $$
begin
  update child set parent_value = new.parent_value
  where parent_id = new.parent_id;
  return new;
end;
$$ language plpgsql;

-- trigger raised for each update
create trigger trg_sync_parent_value
after update of parent_value on parent
for each row
when (old.parent_value is distinct from new.parent_value)
execute function sync_parent_value();

-- drop the previous constraint
alter table child drop constraint fk_child_parent;
alter table parent drop constraint parent_parent_id_parent_value_key;

I test the update of all parents:

\timing on
explain (analyze, buffers, costs off, verbose on)
 update parent
 set parent_value = parent_value - 0.000000000000000042
;
vacuum parent, child;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Update on public.parent (actual time=4.547..4.547 rows=0.00 loops=1)
   Buffers: shared hit=8453 dirtied=26
   ->  Seq Scan on public.parent (actual time=0.009..0.124 rows=1000.00 loops=1)
         Output: (parent_value - '4.2e-17'::double precision), ctid
         Buffers: shared hit=11
 Planning:
   Buffers: shared hit=8 dirtied=2
 Planning Time: 0.108 ms
 Trigger trg_sync_parent_value: time=42719.154 calls=506
 Execution Time: 42724.042 ms
(10 rows)

Time: 42729.150 ms (00:42.729)

postgres=# vacuum parent, child;

VACUUM
Time: 176.585 ms

The performance advantage over the cascade constraint is minimal, and the risk is higher because parent_value in the child table could be updated independently without any check that it still matches the parent. We would also need an insert trigger to set the correct value, and an update trigger to prevent direct modifications. Ultimately, the cascade constraint solution is strongly preferable.

Denormalize into the "One" Side (Parent)

If we want to more closely mimic a MongoDB multi-key index, denormalizing into the child table is not an exact equivalent because there is no deduplication. It has its advantages — it can serve queries that need individual child rows — but a structure analogous to a multi-key index should be stored on the parent side, where each parent holds a summary of its children's values.

One approach is to add an array of child values to the parent table so that we can filter on it:

-- add array column
alter table parent add child_values float[];

-- populate from child
update parent p set child_values = (
  select array_agg(distinct child_value)
  from child where parent_id = p.parent_id
);

A trigger must maintain the array when children change:

create or replace function sync_child_values_array() returns trigger as $$
begin
  update parent set child_values = (
    select array_agg(distinct child_value)
    from child where parent_id = coalesce(new.parent_id, old.parent_id)
  ) where parent_id = coalesce(new.parent_id, old.parent_id);
  
                                    
                                    
                                    

                                        by Franck Pachot