Partitioned collections

[samwillis]

A very common use case, and question, is how to handle collections where you don't want to download all of it. Such as issues in an issue tracker, downloading by project/status/createdData etc. The current solution is to have separate collections for each part you would want to download, managing this all yourself.

I would like to propose "Partitioned Collections", these are collections that are automatically partitioned in the background, progressively downloading data as you query them. With the issue track example you would have a single issuesCollection and as you query it (or preload it), it will download the partitions - however many they are - that are covered by the query.

For example say it's partitioned by archived and the "month" from its created value when you perform this query:

await todos.preload((q) =>
  q.from({ t: todos })
   .where(({ t }) =>
     and(
       eq(t.archived, true),
       and(
         gte(t.created, new Date('2025-06-01')),
         lt(t.created, new Date()) // today
       )
     )
   )
)

the collection will see that the collection is partitioned by both archived and created (by month) and ensure that those covered partitions are downloaded.

I have been considering the API for defining these collections, with these objectives:

1. must be as similar to the existing api as possible
2. reuse the same collection option factories
3. have a simple way to define the partitioning, ideally using the same expression api we have for queries and the new indexing functionality (aside: this needs documenting).

This is what I am thinking:

import { createPartitionedCollection, datePart } from '@tanstack/db'
import { queryCollectionOptions } from '@tanstack/query-db-collection'
import { todoSchema } from './schema'

// datePart('day', date) -> Date at 00:00:00 of that day
// this is a new expression function, we would model this and similar additions on the standard SQL functions

export const todos = createPartitionedCollection<Todo>({
  id: 'todos',

  partitionBy: (row) => ({
    archived: row.archived,
    createdDay: datePart('day', row.created),
  }),

  partitionOptions: ({ partitionKey, parentId }) =>
    queryCollectionOptions({  // <=== this is a standard queryCollectionOptions but inside a callback
      queryKey: [parentId, partitionKey.archived, partitionKey.createdDay.getTime()],
      queryFn: async () => {
        const from = partitionKey.createdDay
        const to = addDays(from, 1)

        const res = await fetch(
          `/api/todos?archived=${partitionKey.archived}` +
          `&from=${from.toISOString()}` +
          `&to=${to.toISOString()}`
        )
        return res.json()
      },
      getKey: (item) => item.id,
      schema: todoSchema,
    }),
})

For an Electric collection it would look like:

export const todos = createPartitionedCollection<Todo>({
  id: 'todos',

  partitionBy: (row) => ({
    archived: row.archived,
    createdDay: datePart('day', row.created),
  }),

  partitionOptions: ({ partitionKey, parentId }) =>
    electricCollectionOptions({  // <=== this is a standard electricCollectionOptions but inside a callback
      id: `${parentId}:${partitionKey.archived}:${partitionKey.createdDay}`
      shapeOptions: {
        url: 'https://example.com/v1/shape',
        params: {
          table: 'todos',
          where: 'archived = $1 and created >= $2 and created < $3',
          params: [
            key.archived,
            key.createdDay,
            addDays(key.createdDay, 1),
          ],
        },
      },
      getKey: (t) => t.id,
      schema: todoSchema,
    }),
})

There are a few things to consider here:

1. the getKey and schema likely really want to be part of the parent collection config, not the partition.
2. type inference on the partitionBy expression builder would require the outer config options being typed with the schema - this may be complex, or super simple...
3. what do we do when a query doesn't fully provide the bounds to infer all partitions covered? can we infer an upper/lower bound? Provide defaults?
4. complex queries with multiple and and or could result in some complex code to work out the partitions covered - although I think this is all very doable.

There is also a cross over with the functionality of React Query infinite queries. These provide a way to page through an infinite list of results, but they don't necessarily define a firm start/end of a page that can be inferred from a query. I think there are two ways we could use these:

1. We could use them to support progressively loading results when used with an orderBy, starting loading from the beginning and paging through until we reach the limit or bound that was set in the query.
2. For standard collections support them as a way to just load very large collections, split across multiple requests.

React Query infinite queries his need more thought.

[KyleAMathews]

Very nice! The callback approach keeps things easily debuggable & understandable. I agree with o3 that we'd want some sort of controls on how many partitions can be created in parallel — maybe throw if it gets weird or something https://chatgpt.com/share/68823cb0-4874-800c-bfc1-fbd51193815f

But while this is an advanced technique, it seems approachable.

On Query's infinite queries — we could perhaps just expose their functions on the utils object? So you could do something like call fetchNextPage & await its promise & then increment the page in the useLiveQuery to transition the page. There is overlap between infinite queries and this but infinite queries is fairly low level so just preserving it within a single QueryCollection could make sense while still also allowing people to use partitioned queries as well.

There's an imperative vs. declarative dichotomy there as infinite query allows you to push and pull on your collection while partitioning is more magic "ask for data and it shows up". Though now that I write that — we'd probably want partitioned collections to also have imperative methods for loading/preloading specific partitions.

[samwillis]

Yep, agree on infinite queries, making it part of the standard query collection seems ideal - I like exposing it on the utils!

we'd probably want partitioned collections to also have imperative methods for loading/preloading specific partitions.

I was thinking we could add something like:

await todos.ensurePartitionLoaded({
  archived: true,
  createdDay: dateObj,
})

we'd want some sort of controls on how many partitions can be created in parallel

Yep, some sort of default limit that can be raised if needed/wanted.

[KyleAMathews]

The nice thing about query.preload() though is it just ensures all necessary partitions are loaded — so imperatively preloading a partition should be a rare edge case.

[khalil-omer]

This is great. I have a few thoughts:

1. I kind of feel like infinite queries would be an anti pattern if combined with partitioning. I like the example you gave where even for a time based query, the partitioning is such that no row would ever logically be in two partitions at once. The problem with the infinite query approach is that it assumes consistent pagination rather than discrete sets that can grow and shrink but do not overlap. I think it is much easier to model a partial sync system using discrete boundaries rather than ordering and pagination. This is a little more tricky for the user to implement in some instances but will probably be beneficial in avoiding strange bugs. For example, let's say I'm building a Slack style chat app. The easier to write live query would load the most recent 50 messages and let the library manage the nuance that the same orderBy and limit values might point to a different set over time. But in my view, it is better for the library to force the user to load the messages from the last day (a static partition boundary). It's not that difficult to write an if statement that loads the previous day if the count of current day is less than whatever I want the first page of data to be.
2. Some sort of TLL approach would be a pretty important feature. As you described in your example, archived data might be rarely queried and therefore stored in a partitioned collection. This means, however, that if I end up syncing 2,000 rows of archived tasks in my project management app and then for the next twenty four hours there are no live queries for that partition, I probably want the library to clean up the partition, otherwise I am going to slow down the user's experience over time due to a bunch of unused synced data. You could also use the Slack example here. I sync a new partition because I've scrolled back in time in some channel to find some history. I probably won't do that any time soon after the 24 hour period in which I did it.

[KyleAMathews]

I was just reminded of this old crazy experiment 😆 https://sqlite-wasm-http.momtchev.com/

[KyleAMathews]

@khalil-omer @samwillis already implemented GC for unused collections a month ago actually — so cleanup should just work with this approach #198

[KyleAMathews]

BTW, we should also expose insert, onInsert, etc. from the partitioned collection wrapper and set those on the inner collection (as part of partitionOptions — it'd be kinda painful to figure out which sub-collection has the item you want to mutate + this nicely completes the efficiency story as e.g. with QueryCollection — with smaller partitioned collection, refetching after the server mutation is much cheaper.

[RJWadley]

I just started playing with TanStack DB recently, but here's my 4 cents

thought 1:

// is this type annotation required, or could it be inferred? I dislike manual type parameters.
export const todos = createPartitionedCollection<Todo>({

thought 2:
Would this also be a solution for server side filtering, sorting, and pagination?

thought 3:
If the dataset is sufficiently large you may run into performance issues even with a partitioned dataset. If the whole point of this proposal is to make data fetching more efficient by fetching less data, I'm not entirely convinced that partitioning is a overall a better option overall than pagination.

thought 4:

What could this look like with some sort of pagination? (warning: yapping)

Consider the following collection (or similar):

const todoCollection = createCollection(
  queryCollectionOptions({
    queryKey: ['todos'],
    queryFn: async () => {
      const response = await fetch('/api/todos')
      return response.json()
    },
    getKey: (item) => item.id,
  })
)

If we only wanted to add pagination, I think it's pretty straightforward:

const todoCollection = createInfiniteCollection(
  queryCollectionOptions({
    queryKey: ['todos'],
    queryFn: async ({ pageParam }) => {
      const response = await fetch('/api/todos?page=' + pageParam)
      const json = await response.json()
      return {
        items: json.items,
        nextPageParam: json.next,
      }
    },
    initialPageParam: 1
    getKey: (item) => item.id,
    // note: because our data is now paginated, we might also need a way to fetch single items?
    fetchSingle: () => { ... }
  })
)

The syntax might need to change (I didn't take the callback approach for convenience), but that's the general idea. If we use this in e.g. useLiveQuery, I'd imagine we'd query page-by-page until we run out of pages in order to fetch all items. useInfiniteLiveQuery naturally follows as the preferred way to query an infinite collection.

A simple query like this:

const { data, isLoading } = useLiveQuery((q) =>
  q.from({ todos: todosCollection })
   .where(({ todos }) => eq(todos.archived, false))
   .select(({ todos }) => ({ id: todos.id, text: todos.text }))
)

Might look like this if it were infinite:

const { data, isLoading, fetchNextPage, fetchPreviousPage } = useInfiniteLiveQuery({
  query: (q) =>
    q.from({ todos: todosCollection })
     .where(({ todos }) => eq(todos.archived, false))
     .select(({ todos }) => ({ id: todos.id, text: todos.text })),
  // does it make sense to allow overriding this value? maybe
  initialPageParam: 1
})

(maybe tanstack DB could change some sort of per_page variable to let it choose a page size on-the-fly, perhaps to get the first page quickly but then fetch a large second page, which might be useful for infinite scrolling or something)

Once we're free from the burden (or pleasure!) of fetching all the items there are lots of options for potential server-side api integrations:

const todoCollection = createInfiniteCollection(
  queryCollectionOptions({

    // I have no specific API in mind for this, but options baby!
    filterBy: (row) => ({
      archived: row.archived, // do we need to know the data type?
      createdDay: datePart('day', row.created),
    }),
    sortBy: (row) => ({
      createdDay: datePart('day', row.created),
    }),
    partitionBy: (row) => ({ 
      // todo: does it make sense to include partition AND filter? are they semantically different here?
      archived: row.archived,
    }),

    queryKey: ['todos'],
    queryFn: async ({ pageParam, filterBy, sortBy }) => {
      const response = await fetch('/api/todos?page='
        // add whatever parameters, based on what you've been given.
        // if we're using a sync engine, can we generate this automatically?
        + pageParam + doSomething(filterBy.archived) + doSomething(sortBy.createdDay)
      )
      const json = await response.json()
      return {
        items: json.items,
        nextPageParam: json.next,
        totalCount: json.count // total count is pretty useful to know for creating efficient queries
      }
    },
    initialPageParam: 1
    getKey: (item) => item.id,
  })
)

(again, syntax might have to change, I didn't use a callback purely for convenience because coding on github is a pain)

[KyleAMathews]

I'll let Sam respond to your other questions but on point 1, if you pass in a schema, it'll use the type from that. That's far nicer IMO.

[Pinpickle]

This seems like a great addition to the API. To add another motivating use-case, consider loading user profiles on demand.

Our use-case is a game, but this works for apps as well (e.g. each todo is associated with a user in a todo app, presence in Figma, user profiles in a chat app).

If I'm understanding Tanstack DB correctly, the only way to do this is have a collection with all of the users, then do joins against todo.authors or whatever field has the user IDs.

Of course its infeasible (performance and security wise) to load all of the users and join against them.

Would the recommended approach be to have a partition against user ID here?

My dream would be for queries to somehow feed back to their collections the universe of data that they want. I'd imagine the "user" collection be empty by default, and only start to load in the necessary data once other queries join against it.

[KyleAMathews]

@Pinpickle yup that's the idea! If you ran a query to render a leaderboard or something, it'd check which of the users have or don't have collections loaded already and kick off loading for the missing ones.

[volkandkaya]

I think this is the relevant place, I have looked at the docs and can't figure out if it is possible to pass say projectId to useLiveQuery so it only fetches the todos from the current project.

[evanheckert]

I think this is the relevant place, I have looked at the docs and can't figure out if it is possible to pass say projectId to useLiveQuery so it only fetches the todos from the current project.

Come join us on discord. Super active community there discussing conventions and possible utils. I just asked the same question and linked to a mockup of one approach here: https://discord.com/channels/719702312431386674/1369767025723052134/1400917311573196961

My attempt is as follows - note that my goal is to still have the fetched data joined into a single unified collection per database table, so it takes it a step beyond what you're asking, but I'll include it since it's relevant to the thread topic.

// src/lib/createCollectionFactory.ts (NEW UTILITY FILE)
import { Collection } from '@tanstack/react-db';

// The TTL should be slightly longer than the collection's gcTime to avoid race conditions.
const CACHE_TTL = 6000; // 6 seconds for a 5-second gcTime

// This is a higher-order function: a function that creates a factory function.
export function createCollectionFactory<TItem, TParams extends string | number>(
  creatorFn: (params: TParams) => Collection<TItem>
) {
  const cache = new Map<TParams, Collection<TItem>>();
  const timeouts = new Map<TParams, NodeJS.Timeout>();

  return (params: TParams): Collection<TItem> => {
    // If a cleanup timer was set for this key, cancel it because we're using it again.
    if (timeouts.has(params)) {
      clearTimeout(timeouts.get(params));
      timeouts.delete(params);
    }

    // If the collection is already in the cache, return it.
    if (cache.has(params)) {
      return cache.get(params)!;
    }

    // Otherwise, create a new collection instance.
    const newCollection = creatorFn(params);
    cache.set(params, newCollection);

    // IMPORTANT: Listen for when the collection is cleaned up by Tanstack DB's gcTime.
    // When it is, we remove it from our factory's cache to prevent memory leaks.
    const unsubscribe = newCollection.subscribe(() => {
      if (newCollection.status === 'cleaned-up') {
        cache.delete(params);
        timeouts.delete(params); // Clean up any stray timeout
        unsubscribe(); // Clean up the subscription itself
      }
    });

    return newCollection;
  };
}

Then use it like this:

// src/features/dispatch/dispatch.source-collections.ts (REVISED FACTORY)
import { createCollection } from '@tanstack/react-db';
import { queryCollectionOptions } from '@tanstack/query-db-collection';
import { createCollectionFactory } from '@/lib/createCollectionFactory'; // <-- IMPORT THE NEW UTILITY

// ... other imports and type definitions ...
type SourceItem = DispatchType | VehicleDispatchType | WorkOrderType;

// --- Factory for the Date-based Query ---
export const getSourceCollectionByDate = createCollectionFactory((date: string) => {
  return createCollection(
    queryCollectionOptions<SourceItem>({
      queryKey: ['dbDispatchDate', { date }],
      queryFn: async () => { /* ... fetch and flatten data ... */ },
      getKey: (item) => `${item.__typename}:${item.id}`,
      // You can configure gcTime here if you want it to be longer or shorter than 5s
      // gcTime: 30000, // e.g., 30 seconds
    })
  );
});

// --- Factory for the Job-based Query ---
export const getSourceCollectionByJob = createCollectionFactory((jobId: string) => {
  return createCollection(
    queryCollectionOptions<SourceItem>({
      queryKey: ['dispatchesByJob', { jobId }],
      queryFn: async () => { /* ... fetch and flatten data ... */ },
      getKey: (item) => `${item.__typename}:${item.id}`,
    })
  );
});

Then to create a unified live query, we'd need to manage the generated collections in global state, right? eg.

// src/hooks/useRegisterSourceCollection.ts
// ...imports

export type SourceItem = DispatchType | VehicleDispatchType | WorkOrderType;
export const activeSourceCollectionsAtom = atom<Collection<SourceItem>[]>([]);

// A module-level map to keep track of removal timers for each collection instance.
const removalTimers = new Map<Collection<SourceItem>, NodeJS.Timeout>();

export const useRegisterSourceCollection = (collection: Collection<SourceItem> | null) => {
  const setActiveSourceCollections = useSetAtom(activeSourceCollectionsAtom);

  useEffect(() => {
    if (!collection) return;

    // Check if a removal timer is pending for this collection.
    // If so, the user has returned to a view using this collection before it was GC'd.
    if (removalTimers.has(collection)) {
      // Cancel the pending removal.
      clearTimeout(removalTimers.get(collection)!);
      removalTimers.delete(collection);
    }

    // Add the collection to the global registry if it's not already there.
    setActiveSourceCollections((prev) => {
      if (prev.includes(collection)) {
        return prev;
      }
      return [...prev, collection];
    });

    // On unmount, schedule the collection for removal from the global registry.
    return () => {
      // Set a timer to remove the collection after the delay.
      const timerId = setTimeout(() => {
        console.log(`Removing collection from active registry due to gcTime expiration...`);
        setActiveSourceCollections((prev) => prev.filter((c) => c !== collection));
        removalTimers.delete(collection);
      }, REMOVAL_DELAY_MS);

      removalTimers.set(collection, timerId);
    };
  }, [collection, setActiveSourceCollections]);
};

and THEN unify with a live query:

// src/features/dispatch/hooks/useUnifiedDispatches.ts
// ...imports 

export const useUnifiedDispatches = () => {
  // 1. Subscribe to the list of active source collections
  const activeSources = useAtomValue(activeSourceCollectionsAtom);

  // 2. Create a live query that depends on the list of active sources
  const { data, ...rest } = useLiveQuery(
    (q) => {
      // Handle edge cases: no sources active yet
      if (activeSources.length === 0) {
        return q.from({ empty: [] }).select(() => ({} as DispatchType));
      }

      // Dynamically build the query with full outer joins
      let query = q.from({ s0: activeSources[0] });
      const aliases = ['s0'];

      // Chain full joins for all other active sources
      for (let i = 1; i < activeSources.length; i++) {
        const alias = `s${i}`;
        aliases.push(alias);
        query = query.fullJoin({ [alias]: activeSources[i] }, (row) =>
          // Join on our unique composite key
          eq(row.s0.id, row[alias].id)
        );
      }

      // Merge the results and filter for only Dispatches
      return query
        .select((row) => {
          // Coalesce finds the first non-null value, effectively merging the rows.
          // We reverse the aliases to prioritize sources added later.
          const aliasedSources = aliases.reverse().map(alias => row[alias]);
          return coalesce(...aliasedSources) as SourceItem;
        })
        .where((merged) => eq(merged.__typename, 'Dispatch'))
        .select((merged) => merged as DispatchType); // Final cast to the correct type
    },
    [activeSources] // CRITICAL: Rerun the query builder when the list of sources changes
  );

  return { data: data ?? [], ...rest };
};

[evanheckert]

I should follow up with the updated version that takes @KyleAMathews suggestion of using a WeakMap rather than doing manual cleanup. I was unfamiliar, so here what gemini 2.5 pro did to leverage that suggestion (with the db docs and recent merged pull requests in context):

---

Kyle's suggestion to use a WeakMap is a very clever, advanced JavaScript technique to simplify this factory pattern. It directly addresses the problem of manual cache cleanup.

Let's break down why it's a more elegant solution.

The Problem with a Standard Map

In the previous "robust factory" example, we used a standard Map to cache the collection instances:

// The old factory's cache
const cache = new Map<TParams, Collection<TItem>>();

The problem, as you correctly identified, is that a standard Map creates a strong reference to the objects it holds (both keys and values). This means that even if every component in your app unmounts and stops using a collection instance, our cache Map will still hold a reference to it. This prevents JavaScript's garbage collector from ever cleaning it up, leading to a memory leak.

Our solution was to manually hook into the collection's lifecycle and delete it from the Map ourselves after a delay:

// The old, manual cleanup logic
const timerId = setTimeout(() => {
  setActiveSourceCollections((prev) => prev.filter((c) => c !== collection));
  removalTimers.delete(collection);
}, REMOVAL_DELAY_MS);

This works, but it's manual, a bit complex, and requires careful timer management.

The WeakMap Solution

A WeakMap is a special type of Map in JavaScript with one magical property: it holds a weak reference to its keys.

• Strong Reference: "I am pointing to this object. As long as I exist, this object cannot be garbage collected." (This is what a normal Map does).
• Weak Reference: "I am pointing to this object, but I don't care if it gets garbage collected. If all other strong references to this object disappear, the garbage collector is free to destroy it, and it will automatically be removed from me."

This is exactly what we want!

How it applies to our factory:

If we use a WeakMap where the collection instance is the key, the lifecycle becomes automatic:

1. A component mounts and gets a collection. The useLiveQuery hook holds a strong reference to this collection. Our WeakMap holds a weak reference.
2. The component unmounts. The useLiveQuery hook cleans up, and its strong reference disappears.
3. Tanstack DB's internal gcTime timer starts.
4. After 5 seconds, the gcTime finishes. Tanstack DB releases its internal reference to the collection.
5. Now, there are no more strong references to the collection instance anywhere in the app.
6. At some point in the future, JavaScript's garbage collector runs. It sees the collection object is no longer strongly referenced and deletes it from memory.
7. Because our WeakMap only held a weak reference, the entry for that collection is automatically removed from the WeakMap.

Result: No manual cleanup, no timers, no memory leaks. It just works.

---

The Code: Refactoring the Factory with WeakMap

The WeakMap key must be an object. In our case, the collection's dynamic parameters (like { date: '...' } or { jobId: '...' }) are the logical key. We can use these parameter objects as the key for our WeakMap.

// src/lib/createCollectionFactory.ts (FINAL, MOST ELEGANT VERSION)
import { Collection } from '@tanstack/react-db';

// The key for the WeakMap MUST be an object.
type ParamsObject = Record<string, any>;

// The factory creator now uses a WeakMap.
export function createCollectionFactory<TItem, TParams extends ParamsObject>(
  creatorFn: (params: TParams) => Collection<TItem>
) {
  // The cache now uses a WeakMap.
  // The key is the params object, the value is the collection.
  const cache = new WeakMap<TParams, Collection<TItem>>();

  return (params: TParams): Collection<TItem> => {
    // If the collection is already in the cache for this params object, return it.
    if (cache.has(params)) {
      return cache.get(params)!;
    }

    // Otherwise, create a new collection instance.
    const newCollection = creatorFn(params);
    
    // Store it in the WeakMap with the params object as the key.
    cache.set(params, newCollection);

    return newCollection;
  };
}

How to use this new factory:

The key is that you must pass a stable object reference for the parameters. useMemo is perfect for this.

// src/features/dispatch/dispatch.source-collections.ts (Using the WeakMap factory)
import { createCollectionFactory } from '@/lib/createCollectionFactory';
// ... other imports

// The creator function is the same as before.
const dateCollectionCreator = (params: { date: string }) => {
  return createCollection(
    queryCollectionOptions<SourceItem>({
      queryKey: ['dbDispatchDate', params],
      queryFn: async () => { /* ... fetch logic ... */ },
      getKey: (item) => `${item.__typename}:${item.id}`,
    })
  );
};

// Create the factory using our new utility.
export const getSourceCollectionByDate = createCollectionFactory(dateCollectionCreator);

// In your DispatchScreen component
import { useMemo } from 'react';
import { getSourceCollectionByDate } from '../dispatch.source-collections';
import { useRegisterSourceCollection } from '../hooks/useRegisterSourceCollection';

function DispatchScreen({ dateString }) {
  // CRITICAL: Memoize the params object so its reference is stable across re-renders.
  // This ensures the WeakMap factory returns the same collection instance.
  const sourceParams = useMemo(() => ({ date: dateString }), [dateString]);

  // Pass the stable params object to the factory.
  const sourceCollection = getSourceCollectionByDate(sourceParams);

  // The rest of the logic is the same.
  useRegisterSourceCollection(sourceCollection);
  // ...
}

Summary of Kyle's Comment

• "Using a weakmap is probably easier than doing manual cleanup": This is exactly what we've demonstrated. The WeakMap automates the removal of cached collections from the factory, eliminating the need for our manual setTimeout and useEffect cleanup logic in the registration hook. It leverages the browser's garbage collector, which is more efficient and reliable.
• "Once nothing is using it it'd get GCed": He's describing the core behavior. When your component unmounts and Tanstack DB's gcTime expires, all strong references are gone. The garbage collector is then free to reclaim the memory, and the WeakMap automatically cleans up its entry.

This WeakMap-based factory is a more advanced but ultimately simpler and more robust pattern for managing dynamically created collections. It's a great example of leveraging modern JavaScript features to write cleaner, more efficient code.