> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Drop data from continuous aggregates

> Drop a view or raw data from a continuous aggregate or its underlying hypertable

export const SERVICE_LONG = 'Tiger Cloud service';

export const CAGG_CAP = 'Continuous aggregate';

export const CAGG = 'continuous aggregate';

export const HYPERTABLE = 'hypertable';

export const PolicyVisualizer = () => {
  const [timeBucket, setTimeBucket] = useState(1);
  const [timeBucketUnit, setTimeBucketUnit] = useState('day');
  const [caggStartOffset, setCaggStartOffset] = useState(30);
  const [caggEndOffset, setCaggEndOffset] = useState(2);
  const [retentionOffset, setRetentionOffset] = useState(90);
  const [caggRetentionOffset, setCaggRetentionOffset] = useState(365);
  const [copied, setCopied] = useState(false);
  const timeUnits = ['hour', 'day', 'week', 'month'];
  const convertToHours = (value, unit) => {
    const multipliers = {
      hour: 1,
      day: 24,
      week: 168,
      month: 720
    };
    return value * multipliers[unit];
  };
  const timeBucketHours = convertToHours(timeBucket, timeBucketUnit);
  const caggStartHours = convertToHours(caggStartOffset, timeBucketUnit);
  const caggEndHours = convertToHours(caggEndOffset, timeBucketUnit);
  const retentionHours = convertToHours(retentionOffset, timeBucketUnit);
  const caggRetentionHours = convertToHours(caggRetentionOffset, timeBucketUnit);
  const errors = [];
  if (caggEndHours <= timeBucketHours) {
    errors.push("Set your end_offset to be older than one time bucket interval");
  }
  if (caggStartHours <= caggEndHours) {
    errors.push("Your start_offset must be greater than end_offset");
  }
  if (retentionHours < caggStartHours) {
    errors.push("Don't drop raw data within your refresh interval");
  }
  if (caggRetentionHours < caggStartHours) {
    errors.push("Don't drop downsampled data within your refresh interval");
  }
  if (caggRetentionHours < retentionHours) {
    errors.push("Drop raw data before downsampled data");
  }
  const generateSql = () => {
    const pluralize = (count, unit) => count !== 1 ? `${unit}s` : unit;
    return `-- Add refresh policy
SELECT add_continuous_aggregate_policy('continuous_aggregate_name',
  start_offset => INTERVAL '${caggStartOffset} ${pluralize(caggStartOffset, timeBucketUnit)}',
  end_offset => INTERVAL '${caggEndOffset} ${pluralize(caggEndOffset, timeBucketUnit)}',
  schedule_interval => INTERVAL '1 hour');

-- Add retention policy for raw data
SELECT add_retention_policy('hypertable_name',
  drop_after => INTERVAL '${retentionOffset} ${pluralize(retentionOffset, timeBucketUnit)}');

-- Add retention policy for downsampled data
SELECT add_retention_policy('continuous_aggregate_name',
  drop_after => INTERVAL '${caggRetentionOffset} ${pluralize(caggRetentionOffset, timeBucketUnit)}');`;
  };
  const minEndOffset = timeBucket + 1;
  const minStartOffset = caggEndOffset + 1;
  const minRetention = caggStartOffset + 1;
  const minCaggRetention = Math.max(retentionOffset + 1, caggStartOffset + 1);
  const handleCopy = () => {
    navigator.clipboard.writeText(generateSql());
    setCopied(true);
    setTimeout(() => setCopied(false), 2000);
  };
  return <div className="bg-gray-50 dark:bg-gray-800 border border-gray-300 dark:border-gray-600 rounded-lg p-6 my-6">
      <div className="space-y-4">
        <div>
          <label className="block text-sm font-medium mb-2">Time bucket interval</label>
          <div className="flex gap-2">
            <input type="number" min="1" value={timeBucket} onChange={e => setTimeBucket(parseInt(e.target.value) || 1)} className="w-20 px-2 py-1 border rounded" />
            <select value={timeBucketUnit} onChange={e => setTimeBucketUnit(e.target.value)} className="px-2 py-1 border rounded">
              {timeUnits.map(unit => <option key={unit} value={unit}>{unit}(s)</option>)}
            </select>
          </div>
        </div>
        <div>
          <label className="block text-sm font-medium mb-1">Refresh end_offset: {caggEndOffset} {timeBucketUnit}{caggEndOffset !== 1 ? 's' : ''}</label>
          <input type="range" min={minEndOffset} max="30" value={caggEndOffset} onChange={e => setCaggEndOffset(parseInt(e.target.value))} className="w-full" />
        </div>
        <div>
          <label className="block text-sm font-medium mb-1">Refresh start_offset: {caggStartOffset} {timeBucketUnit}{caggStartOffset !== 1 ? 's' : ''}</label>
          <input type="range" min={minStartOffset} max="365" value={caggStartOffset} onChange={e => setCaggStartOffset(parseInt(e.target.value))} className="w-full" />
        </div>
        <div>
          <label className="block text-sm font-medium mb-1">Raw data retention: {retentionOffset} {timeBucketUnit}{retentionOffset !== 1 ? 's' : ''}</label>
          <input type="range" min={minRetention} max="730" value={retentionOffset} onChange={e => setRetentionOffset(parseInt(e.target.value))} className="w-full" />
        </div>
        <div>
          <label className="block text-sm font-medium mb-1">Downsampled retention: {caggRetentionOffset} {timeBucketUnit}{caggRetentionOffset !== 1 ? 's' : ''}</label>
          <input type="range" min={minCaggRetention} max="1825" value={caggRetentionOffset} onChange={e => setCaggRetentionOffset(parseInt(e.target.value))} className="w-full" />
        </div>
        <div className="relative">
          <pre className="bg-gray-900 text-gray-100 rounded p-4 overflow-x-auto text-sm"><code>{generateSql()}</code></pre>
          <button onClick={handleCopy} className="absolute top-2 right-2 px-3 py-1 bg-gray-700 hover:bg-gray-600 text-white text-xs rounded">
            {copied ? 'Copied!' : 'Copy'}
          </button>
        </div>
      </div>
    </div>;
};

export const PricePlanBadge = ({plans: propPlans}) => {
  const PLANS = ['free', 'performance', 'scale', 'enterprise'];
  const capitalize = str => {
    return str.charAt(0).toUpperCase() + str.slice(1);
  };
  let plans = propPlans;
  if (!plans && typeof window !== 'undefined') {
    const frontmatter = window.__mintlify_frontmatter__;
    if (frontmatter && frontmatter.price_plans) {
      plans = frontmatter.price_plans;
    }
  }
  if (!plans || !plans.length) {
    return null;
  }
  const validPlans = plans.filter(p => PLANS.includes(p));
  if (validPlans.length === 0) {
    return null;
  }
  return <a href="/deploy-and-operate/tiger-cloud/understand/pricing-and-account-management" className="inline-flex items-center gap-1 px-3 py-1 bg-green-50 border border-green-200 rounded-full text-sm font-medium text-green-700 mb-6 no-underline hover:bg-green-100 transition-colors">
      <svg width="16" height="16" viewBox="0 0 16 16" fill="currentColor" className="shrink-0">
        <path fillRule="evenodd" clipRule="evenodd" d="M8 16A8 8 0 108 0a8 8 0 000 16zm3.707-9.293a1 1 0 00-1.414-1.414L7 8.586 5.707 7.293a1 1 0 00-1.414 1.414l2 2a1 1 0 001.414 0l4-4z" />
      </svg>
      Available on: {validPlans.map(plan => capitalize(plan)).join(', ')}
    </a>;
};

<PricePlanBadge plans={['scale', 'enterprise', 'performance']} />

When you are working with {CAGG}s, you can drop a view, or you can drop raw data from the underlying
{HYPERTABLE} or from the {CAGG} itself. A combination of [refresh][refresh-policy] and data retention
policies can help you downsample your data. This lets you keep historical data at a lower granularity than
recent data.

However, you should be aware if a retention policy is likely to drop raw data from your {HYPERTABLE} that
you need in your {CAGG}.

To simplify the process of setting up downsampling, you can use the
[visualizer and code generator](#set-up-downsampling-and-data-retention).

## Drop a continuous aggregate view

You can drop a {CAGG} view using the `DROP MATERIALIZED VIEW` command. This command also removes refresh
policies defined on the {CAGG}. It does not drop the data from the underlying {HYPERTABLE}.

To drop a {CAGG} view:

```sql theme={"dark"}
DROP MATERIALIZED VIEW view_name;
```

## Drop raw data from a hypertable

If you drop data from a {HYPERTABLE} used in a {CAGG} it can lead to problems with your {CAGG} view. In
many cases, dropping underlying data replaces the aggregate with NULL values, which can lead to unexpected
results in your view.

You can drop data from a {HYPERTABLE} using [`drop_chunks`][drop-chunks] in the usual way, but before you
do so, always check that the chunk is not within the refresh window of a {CAGG} that still needs the data.
This is also important if you are manually refreshing a {CAGG}. Calling
[`refresh_continuous_aggregate`][refresh-cagg] on a region containing dropped chunks recalculates the
aggregate without the dropped data.

If a {CAGG} is refreshing when data is dropped because of a retention policy, the aggregate is updated to
reflect the loss of data. If you need to retain the {CAGG} after dropping the underlying data, set the
`start_offset` value of the aggregate policy to a smaller interval than the `drop_after` parameter of the
retention policy.

For more information, see the [data retention documentation][data-retention-with-continuous-aggregates].

## Set up downsampling and data retention

Maximize your storage by keeping downsampled historical data and dropping raw data.

Once you've [created a {CAGG}][create-cagg], you can automatically downsample your data by configuring the
following policies:

1. **Refresh policy**: control when and how often your {CAGG} is updated
2. **Raw data retention policy**: control how long to keep data in the underlying {HYPERTABLE}
3. **Downsampled data retention policy**: control how long to keep data in the {CAGG}

The following widget creates a valid policy for you. Run the generated SQL in your {SERVICE_LONG}.

<PolicyVisualizer />

Dropping raw data within your refresh interval can cause data loss. To fix, only drop raw data older than your
refresh interval.

[create-cagg]: /manage-data/capabilities/continuous-aggregates/setup-continuous-aggregates

[data-retention-with-continuous-aggregates]: /manage-data/capabilities/data-retention#data-retention-with-continuous-aggregates

[drop-chunks]: /api-reference/timescaledb/hypertables/drop_chunks

[refresh-cagg]: /api-reference/timescaledb/continuous-aggregates/refresh_continuous_aggregate

[refresh-policy]: /manage-data/capabilities/continuous-aggregates/refresh-policies