Updating shares

Data replication speed

Use this when you need to: Understand how quickly data changes become available in shares and read-only instances.

Prerequisites: You should have shares or read-only instances configured in your MotherDuck environment.

You'll know you're done when: You understand the timing characteristics and can optimize data availability when needed.

MotherDuck automatically replicates data to shares and read-only instances with the following timing characteristics:

Auto-updated shares

For shares configured with auto-update enabled, MotherDuck polls for new data once per minute. When new data is detected, it becomes available in the share after the next checkpoint occurs.

Checkpoints and data availability

Data is written to shares whenever there is a checkpoint. Checkpoints occur automatically based on your database's configuration. For read-scaling instances, you can force a snapshot using CREATE SNAPSHOT to make data available sooner.

SQL

For read-scaling instances, to force a snapshot and make data immediately available:

CREATE SNAPSHOT OF <database_name>;

Expected result: A new read-only snapshot is created, ensuring read-scaling connections can access the most up-to-date data.

Use case: Run this when you need to ensure the latest data is available to read-scaling instances immediately.

Important: This command will wait for any ongoing write queries to complete and prevent new ones from starting during snapshot creation.

UI

1. Navigate to your database in the MotherDuck interface
2. Look for snapshot options in the database management section
3. Trigger a snapshot to ensure your latest data is available in read-scaling instances immediately

Expected result: Your latest data becomes immediately available in all read-scaling instances.

Read-only instances

Data replication to read-only instances within the same account follows the same timing as shares - data becomes available after checkpoints, with polling occurring once per minute for auto-updated configurations.

Manual share updates

Use this when you need to: Publish recent changes from your database to make them available in the share.

Prerequisites: You must be the owner of the share and have made changes to the source database since the last share update.

You'll know you're done when: The share reflects the latest version of your database and the last updated timestamp changes.

Sharing a database creates a point-in-time snapshot of the database at the time it is shared. To publish changes, you need to explicitly run UPDATE SHARE <share name>.

When updating a SHARE with the same database, the URL does not change.

SQL

UPDATE SHARE <share name>;

Example: Database 'mydb' was previously shared by creating a share 'myshare', and the database 'mydb' has been updated since. The owner wants colleagues to receive the new version:

# 'myshare' was previously created on the database 'mydb'
UPDATE SHARE "myshare";

Expected result: The share is updated with the latest data from the source database.

Recovery: If you lost your database share URL, you can use the LIST SHARES command to list all your shares or DESCRIBE SHARE <share name> to get specific details about a given share name.

Refreshing shared data (consumer side)

Use this when you need to: Get the most up-to-date data from a share or read-scaling instance after the producer has made updates.

Prerequisites: You must have attached a share or be connected to a read-scaling instance.

You'll know you're done when: Your local copy reflects the latest data from the producer.

By default, shares and read-scaling instances automatically sync every minute. However, if you need the most up-to-date data sooner, you can manually refresh after the producer executes their update command.

Complete workflow for maximum freshness

For the freshest possible data, follow this two-step process:

1. Producer side: Either wait for normal checkpoints or force an update
2. Consumer side: Run REFRESH DATABASE to pull the latest changes

Read-scaling workflow

Producer (writer connection):

-- Make your changes
INSERT INTO my_db.my_table VALUES (...);

-- Option 1: Wait for normal checkpoint (automatic)
-- Data becomes available after the next checkpoint occurs

-- Option 2: Force a snapshot to make data immediately available
CREATE SNAPSHOT OF my_db;

Consumer (read-scaling connection):

-- Refresh to get the latest snapshot
REFRESH DATABASES;       -- Refreshes all connected databases and shares
-- OR
REFRESH DATABASE my_db;  -- Refresh just one specific database

Share workflow

Producer (share owner):

-- Make your changes
INSERT INTO my_db.my_table VALUES (...);

-- Option 1: Wait for normal checkpoint (automatic)
-- Data becomes available after the next checkpoint occurs

-- Option 2: Force a share update to make data immediately available
UPDATE SHARE "myshare";

Consumer (share recipient):

-- Refresh to get the latest share data
REFRESH DATABASES;       -- Refreshes all connected databases and shares
-- OR
REFRESH DATABASE my_share;  -- Refresh just one specific share

Understanding the refresh output

When you run REFRESH DATABASES, you'll see output showing which databases were refreshed:

REFRESH DATABASES;
┌─────────┬───────────────────┬──────────────────────────┬───────────┐
│  name   │       type        │  fully_qualified_name    │ refreshed │
│ varchar │     varchar       │        varchar           │  boolean  │
├─────────┼───────────────────┼──────────────────────────┼───────────┤
│ my_db   │ motherduck        │ md:my_db                 │ false     │
│ myshare │ motherduck share  │ md:_share/myshare/uuid   │ true      │
└─────────┴───────────────────┴──────────────────────────┴───────────┘

The refreshed column shows true for databases that were successfully refreshed with new data.

Learn more about REFRESH DATABASE.