Querying data at a point in time

Beta

This feature is in Beta in the following regions: us-east-1, us-west-2, eu-west-1.

Lakebase Postgres (Autoscaling Beta) is the next version of Lakebase, available for evaluation only. For production workloads, use Lakebase Public Preview. See choosing between versions to understand which version is right for you.

Point-in-time branching allows you to query your database as it existed at any moment within your restore window. This feature creates a new branch with your data from a specific point in time, so you can analyze historical states without affecting your production data.

When to use point-in-time branches

Use a point-in-time branch when you need to:

• Analyze data as it existed at a specific point in the past
• Investigate changes made to your database over time
• Recover specific data values from before recent modifications

Query data at a point in time

To query your data at a point in time:

1. Navigate to the Lakebase App by clicking the apps switcher in the top right corner of your workspace.
2. Identify your database project in the list.
3. Click on the project name to get to the Project dashboard.
4. In the Branches section of the left navigation, click Create branch.
5. In the Create new branch dialog:
   • Enter a branch name (required; for example, historical-analysis or past-data-query)
   • Select the Past data option
   • Use the date and time picker to select the desired date and time you want to query

1. Click Create to create the new branch with data from the selected point in time.

   note

   This creates a new branch with your data as it existed at the selected point in time, without affecting your production branch.

2. After the branch is created, you're automatically taken to the Branch Overview page for your new point-in-time branch. A Connection details modal appears with the connection string for this branch.

   You have two options:

   • Use external tools: Copy the connection string to connect using psql, DBeaver, pgAdmin, or other Postgres clients
   • Use SQL Editor: Close the connection details modal and proceed to the next step

3. To use the SQL Editor, close the connection details modal by clicking Close.

4. In the left navigation, click SQL Editor.

5. Query your database as it existed at the selected point in time.

6. Write and execute SQL queries to analyze your historical data.

   For detailed information about using the SQL Editor, see Query your database from the Lakebase SQL Editor. For information about connecting with external tools, see Postgres clients.

Clean up point-in-time branches

When you've finished your analysis and no longer need the point-in-time branch:

1. Navigate to the Branches page in the left navigation.
2. Find the point-in-time branch you created.
3. Click the menu next to the branch name.
4. Select Delete from the dropdown menu.
5. Confirm the deletion to remove the branch and free up storage space.

Important considerations

• Restore window: You can only query data from within your configured restore window. See Configure your restore window for information about adjusting this setting.
• Branch isolation: The point-in-time branch is separate from your production branch, so you can safely query and analyze without affecting current operations.
• Branch management: Delete point-in-time branches when you're finished with your analysis to avoid unnecessary storage usage.
• Full database state: The branch contains all databases and schemas as they existed at the selected point in time.