CodeWithAntonio•16mo ago

Recursive query performance

Hi again 👋 I am working on a Notion clone, currently focusing on the sidebar element. Which looks like this: https://imgur.com/itFF5qX My schema for this entity is as follows:

export default defineSchema({
  documents: defineTable({
    title: v.string(),
    userId: v.string(),
    isArchived: v.boolean(),
    parentDocument: v.optional(v.id("documents")),
    content: v.optional(v.string()),
  })
  .index("by_user_id", ["userId"])
});

export default defineSchema({
  documents: defineTable({
    title: v.string(),
    userId: v.string(),
    isArchived: v.boolean(),
    parentDocument: v.optional(v.id("documents")),
    content: v.optional(v.string()),
  })
  .index("by_user_id", ["userId"])
});

As you can see, documents which are created at the top level have undefined as their parentDocument field, where children have a proper relation with their parent. This is my API to fetch both parent documents, or child documents if I provide a parentDocument argument:

export const get = query({
  args: {
    parentDocument: v.optional(v.id("documents")),
    isArchived: v.optional(v.boolean()),
  },
  handler: async (ctx, args) => {
    const identity = await ctx.auth.getUserIdentity();

    if (!identity) {
      throw new Error("Not authenticated");
    }

    const userId = identity.subject;

    const documents = await ctx.db
      .query("documents")
      .withIndex("by_user_id", (q) => q.eq("userId", userId))
      .filter((q) =>
        q.and(
          q.eq(q.field("isArchived"), !!args.isArchived),
          q.eq(q.field("parentDocument"), args.parentDocument),
        )
      )
      .order("desc")
      .collect();

    return documents;
  },
});

export const get = query({
  args: {
    parentDocument: v.optional(v.id("documents")),
    isArchived: v.optional(v.boolean()),
  },
  handler: async (ctx, args) => {
    const identity = await ctx.auth.getUserIdentity();

    if (!identity) {
      throw new Error("Not authenticated");
    }

    const userId = identity.subject;

    const documents = await ctx.db
      .query("documents")
      .withIndex("by_user_id", (q) => q.eq("userId", userId))
      .filter((q) =>
        q.and(
          q.eq(q.field("isArchived"), !!args.isArchived),
          q.eq(q.field("parentDocument"), args.parentDocument),
        )
      )
      .order("desc")
      .collect();

    return documents;
  },
});

I will continue in the additional comments because the message is too long 😅

Imgur

12 Replies

CodeWithAntonioOP•16mo ago

This api query is run inside a recursive react component which renders itself again, as many times as user expands to the child document

interface DocumentListProps {
  parentDocumentId?: Id<"documents">;
  level?: number;
}

export const DocumentList: React.FC<DocumentListProps> = ({ 
  parentDocumentId,
  level = 0,
}) => {
  const params = useParams();
  const router = useRouter();
  const [expanded, setExpanded] = useState<Record<string, boolean>>({});

  const onExpand = (documentId: string) => {
    setExpanded(prevExpanded => ({
      ...prevExpanded,
      [documentId]: !prevExpanded[documentId],
    }));
  };

  const documents = useQuery(api.documents.get, {
    parentDocument: parentDocumentId
  });

  const onRedirect = (documentId: string) => {
    router.push(`/documents/${documentId}`);
  };

  return (
    <>
      <p
        style={{
          paddingLeft: level ? `${(level * 12) + 25}px` : undefined,
        }}
        className={cn(
          "hidden text-sm font-medium text-muted-foreground/80",
          expanded && "last:block",
          level === 0 && "hidden",
        )}
      >
        No pages inside
      </p>
      {documents?.map((document) => {
        return (
          <div key={document._id}>
            <Item
              id={document._id}
              onClick={() => onRedirect(document._id)}
              label={document.title}
              icon={FileText}
              active={params.documentId === document._id}
              level={level}
              onExpand={() => onExpand(document._id)}
              expanded={expanded[document._id]}
            />
            {expanded[document._id] && (
              <DocumentList
                parentDocumentId={document._id}
                level={level + 1}
              />
            )}
          </div>
        )
      })}
    </>
  );
};

interface DocumentListProps {
  parentDocumentId?: Id<"documents">;
  level?: number;
}

export const DocumentList: React.FC<DocumentListProps> = ({ 
  parentDocumentId,
  level = 0,
}) => {
  const params = useParams();
  const router = useRouter();
  const [expanded, setExpanded] = useState<Record<string, boolean>>({});

  const onExpand = (documentId: string) => {
    setExpanded(prevExpanded => ({
      ...prevExpanded,
      [documentId]: !prevExpanded[documentId],
    }));
  };

  const documents = useQuery(api.documents.get, {
    parentDocument: parentDocumentId
  });

  const onRedirect = (documentId: string) => {
    router.push(`/documents/${documentId}`);
  };

  return (
    <>
      <p
        style={{
          paddingLeft: level ? `${(level * 12) + 25}px` : undefined,
        }}
        className={cn(
          "hidden text-sm font-medium text-muted-foreground/80",
          expanded && "last:block",
          level === 0 && "hidden",
        )}
      >
        No pages inside
      </p>
      {documents?.map((document) => {
        return (
          <div key={document._id}>
            <Item
              id={document._id}
              onClick={() => onRedirect(document._id)}
              label={document.title}
              icon={FileText}
              active={params.documentId === document._id}
              level={level}
              onExpand={() => onExpand(document._id)}
              expanded={expanded[document._id]}
            />
            {expanded[document._id] && (
              <DocumentList
                parentDocumentId={document._id}
                level={level + 1}
              />
            )}
          </div>
        )
      })}
    </>
  );
};

As you can see from this part:

{expanded[document._id] && (
  <DocumentList
    parentDocumentId={document._id}
    level={level + 1}
  />
)}

{expanded[document._id] && (
  <DocumentList
    parentDocumentId={document._id}
    level={level + 1}
  />
)}

The component calls itself once it is expanded. My question is, how performant is this for a recursive query? Would this be considered a good practice to do? Before I added the "expand to render next recursive query" It automatically rendered itself into how many times it needs to reach the end, and that very quickly filled up my bandwith. I am assuming with manual pressing of the "expand" button, this is similar to pagination, making it more optimized. I am wondering if this is a good solution, or does Convex perhaps preform better if I introduce a getAll API call with both parents and children, and then create a structure on the frontend, rather than letting backend do it?

CodeWithAntonioOP•16mo ago

I feel like I could reduce the number of query calls by introducing parentDocument index, can we combine 2 indexes?

RJ•16mo ago

You can create one index for multiple fields, see the first example in https://docs.convex.dev/database/indexes/. It would look like so:

  .index("by_user_and_parent_document", ["userId", "parentDocument"])

  .index("by_user_and_parent_document", ["userId", "parentDocument"])

CodeWithAntonioOP•16mo ago

Ok, I've modified my schema as following:

CodeWithAntonioOP•16mo ago

and modified my api call as this

CodeWithAntonioOP•16mo ago

would this improve performance? btw, I also send "undefined" as args.parentDocument for the upmost documents (parents) seems to work fine

RJ•16mo ago

I believe yeah, it should, compared to just using an index for one field and using a filter for the other. But this wouldn't help your bandwidth issues, I don't think.

CodeWithAntonioOP•16mo ago

Got it, thank you a lot for answering!

RJ•16mo ago

Re-reading your comments, I would imagine a getAll query would be more performant overall, assuming the results are never too large. Something else I've done before in this kind of scenario (which has its own pros and cons) is checked to see whether the component which would contain the document is currently in the viewport, and only loaded it if and when it is. But those are just my thoughts (and I don't work for Convex), perhaps others may be able to help you more!

CodeWithAntonioOP•16mo ago

Thanks a lot for the input! I've been playing around with getAll and then used a Map on the frontend to create the structure I expect, and it definitely "feels" faster on the frontend since everything is loaded upfront, just not sure if I am actually saving any bandwidth there 🙂 Nevertheless very interesting to play around with this

jamwt•16mo ago

first of all, although @RJ doesn't work here, his company was a really early adopter of convex, and RJ is one of the world experts on making apps with convex. so we strongly endorse his advice in general 😄 the index will help for sure. and re: database bandwidth, this is another place where we probably need to consider figuring out an idea that we've discussed but not built yet: subquery caching. things are cached at the query function in convex (i.e. the JSON output of the function). and often what's "new" you're recalculating on the backend are compositions of things which haven't changed and/or which are common to a lot of users and so should only be calculated once, etc etc if you had a great way to structure calling other queries from your backend query (and the platform did all the right stuff re: caching and the consistent snapshot MVCC window and so on), it could really help a lot of applications keep load off the database we had a scaling challenge yesterday with another developer where this would have helped, too Also, @CodeWithAntonio , if you wouldn't mind sharing, was the previous architecture also filling up your db quota on the pro plan? how much are we talking about here specifically? I want to double check this feels reasonable

CodeWithAntonioOP•16mo ago

Thanks for that information @jamwt ! I actually have not seen any "unreasonable" bandwidth apart from my very first iteration, where the <DocumentList /> was calling itself recursively until the query function returned no more children elements. Im pretty sure I created around 20 children and then refreshed a bunch of times and even tried modifying the 17th child's title to see how it would update the sidebar in real time, all of this was without the index, and without the "expand to load children" functionality, it just loaded absolutely every relation recursively firing god knows how many queries 😅 At the time that function was called childList and became the champion of eating my bandwidth. Right now I am working with getSidebar, which has the parentDocument Index + it has the expand to load children (pagination lets call it) functionality to not load a billion queries and children at once. And as I can see the getSidebar looks very reasonable now. I will leave it like this for the moment, and occasionally take a look at my useage to see if something out of the ordinary is happening. Thanks so much for the help!

Recursive query performance

Did you find this page helpful?