Syncing Large Notion Databases: Choosing a Pagination and Filtering Strategy

The Challenge: Syncing High-Volume Notion Data

When integrating a Notion database into a downstream system—such as a data warehouse or a custom dashboard—you face three primary constraints: a maximum page_size of 100 records per request, strict API rate limits, and eventual consistency for formula or rollup properties. If your database exceeds a few thousand rows, a naive fetch loop can exhaust your rate limit or fail mid-process, leaving your downstream system in a partial, inconsistent state.

Comparing Read Strategies

Depending on your data volume and the frequency of your sync, you must choose between full traversal, server-side filtering, or narrow projection. The following table compares these approaches based on the Notion Public API behavior.

Strategy	Primary Benefit	Trade-off	Best Use Case
Unfiltered Pagination	Guaranteed completeness	High API consumption	Full backups or audit logs
Server-Side Filtering	Reduced payload/requests	Filter syntax complexity	Incremental syncs (e.g., "Last Edited")
Narrow Projection	Lower memory footprint	Still requires full traversal	High-volume rows with many properties

Evaluating the Trade-offs

Unfiltered Pagination is the safest method for ensuring no record is missed. However, because you must follow the start_cursor until has_more is false, a database with 10,000 rows requires at least 100 sequential API calls. This increases the risk of hitting a 429 (Too Many Requests) error.

Server-Side Filtering pushes the logic to Notion's servers. By filtering for records where last_edited_time is greater than your last successful sync, you drastically reduce the number of pages retrieved. The risk here is that complex compound filters can occasionally be slower to execute or may behave unexpectedly if the filter grammar changes in API updates.

Narrow Projection involves requesting only the specific properties needed for your application. While this reduces the size of the JSON response, it does not reduce the number of API calls required to traverse the database. It is a memory optimization, not a rate-limit optimization.

Implementation: Robust Pagination Loop

To implement a reliable sync, you must track the cursor and handle rate limits. Run this logic in your backend environment using an integration token with read access to the target database.

// Pseudo-code for a resilient Notion sync loop
let cursor = null;
let hasMore = true;
const results = [];

while (hasMore) {
  try {
    const response = await notion.databases.query({
      database_id: process.env.NOTION_DATABASE_ID,
      start_cursor: cursor,
      page_size: 100,
      // Example filter for incremental sync
      filter: {
        property: "Last Edited",
        date: { gte: lastSyncTimestamp }
      }
    });

    results.push(...response.results);
    cursor = response.next_cursor;
    hasMore = response.has_more;

    // Persist cursor to DB here to allow resume on failure
    await saveProgress(cursor);

  } catch (error) {
    if (error.status === 429) {
      // Implement exponential backoff
      await sleep(retryAfter || 1000);
      continue;
    }
    throw error;
  }
}

Operational Guardrails

• Rate Limit Handling: Always implement exponential backoff when encountering a 429 status code. Do not retry immediately, as this may extend the lockout period.
• State Persistence: Save the next_cursor to a persistent store after every successful page fetch. If the process crashes at page 50 of 100, you can resume from the saved cursor rather than restarting the entire scan.
• Avoid Parallelism: Do not attempt to parallelize reads of the same database using different filters unless you have a strict strategy to deduplicate records by their page_id.
• View Independence: Remember that Notion API queries ignore the filters and sorts configured in the Notion UI "Views." You must explicitly define your filter and sorts objects in the API request body.

Verification and Validation

To verify your implementation, use a test database with a known record count (e.g., 250 records). Perform the following checks:

1. Count Validation: Ensure the total number of records returned by your loop matches the known count in the UI.
2. Cursor Integrity: Log each start_cursor used; verify that no cursor value repeats, which would indicate an infinite loop.
3. Filter Diff: Run an unfiltered scan and a filtered scan (by date) over the same period. Compare the resulting ID lists to ensure the filter is not omitting valid records.

Rollback and State Recovery

Because this operation reads data, it does not change the state of the Notion database. However, it changes the state of your downstream system. To roll back a corrupted sync, truncate the downstream table and clear the persisted last_cursor and lastSyncTimestamp to force a full re-sync from the beginning of the database.