Skip to main content

Updating shares

Data replication speed​

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

Prerequisites: You should have shares or read-only Ducklings 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 Ducklings 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. Starting with DuckDB 1.5, checkpoints run in the background, so reads, writes, and deletes can continue while a checkpoint is in progress. For read scaling Ducklings, you can force a snapshot using CREATE SNAPSHOT to make data available sooner.

For read scaling Ducklings, 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 Ducklings immediately.

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

Read-only Ducklings​

Data replication to read-only Ducklings 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.

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 latest 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 url> 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 Duckling after the producer has made updates.

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

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

By default, shares and read scaling Ducklings 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

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

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.