Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17553

Enable setting start datetime for interval partitioned history of system versioned tables

Details

    Description

      System Versioning allows storing the historical data in separate partitions partitioned by a time interval. (see https://mariadb.com/kb/en/library/system-versioned-tables/#storing-the-history-separately). This would be a helpful functionality if it were possible to set the start datatime for the interval, e.g. the start of the fiscal year. This way the data could be partitioned by fiscal year which would make it easy to drop ald partitions for which the retention time as expired. Without this interval adjustment removing old data would result in a DELETE ... WHERE ...

      Proposal for syntax 

      CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
      		 
        PARTITION BY SYSTEM_TIME INTERVAL 1 YEAR STARTS '2018-10-01 00:00:00'  (
      		 
          PARTITION p0 HISTORY,
      		 
          PARTITION p1 HISTORY,
      		 
          PARTITION p2 HISTORY,
      		 
          PARTITION pcur CURRENT
      		 
        );

      Default STARTS rounding depending on INTERVAL type

      If STARTS clause is omitted, a default one is assigned with value
      derived from query timestamp. The rounding is done on STARTS value
      depending on INTERVAL type:

      SECOND: no rounding is done;
      MINUTE: timestamp seconds is set to 0;
      HOUR: timestamp seconds and minutes are set to 0;
      DAY, WEEK, MONTH and YEAR: timestamp seconds, minutes and hours are
      set to 0 (the date of rotation is kept as current date).

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-17554 Auto-create history partitions for system-versioned tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18535 Order of setting query timestamp breaks partition rotation

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Task - A task that needs to be done. MDEV-21003 Per-partition INTERVAL for history partitions

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            f_razzoli Federico Razzoli added a comment -

            With RANGE method, when you create a table partitioned by time, you don't write in the stone the bounds of partitions that will be created in the future. You can start by 1 year per partition, and then if the workload becomes more write-intensive, future partitions could contain 6 months of data.

            So I'm suggesting to also implement this syntax:

            ALTER TABLE t PARTITION BY SYSTEM_TIME INTERVAL 6 MONTH STARTING FROM NOW();

            If this is implemented, I would expect to see the new and the old intervals in SHOW CREATE TABLE and information_schema.

            f_razzoli Federico Razzoli added a comment - With RANGE method, when you create a table partitioned by time, you don't write in the stone the bounds of partitions that will be created in the future. You can start by 1 year per partition, and then if the workload becomes more write-intensive, future partitions could contain 6 months of data. So I'm suggesting to also implement this syntax: ALTER TABLE t PARTITION BY SYSTEM_TIME INTERVAL 6 MONTH STARTING FROM NOW(); If this is implemented, I would expect to see the new and the old intervals in SHOW CREATE TABLE and information_schema.

            People

              midenok Aleksey Midenkov
              umoser Ulrich Moser (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.