Tsurugi 1.13.0 - Release Notes (en)

[akirakw]

1.13.0 Release Notes

Overview

Tsurugi 1.13.0 improves the storage efficiency of the index structure and reduces memory usage, especially for tables that use secondary indexes.

Tsurugi stores table and index data in memory in a layered structure that splits each key into 8-byte slices from the beginning.
In previous versions, even after a key could be distinguished from other keys, the rest of the key was still stored one layer per 8 bytes, so longer keys used more memory.
This was noticeable in secondary indexes, whose keys consist of a column value followed by the primary key, and in long string primary keys such as UUIDs.

This version improves this by storing that remaining part together.
For example, in a benchmark (Phone-Bill) that uses a table with two secondary indexes, memory usage (RSS) was reduced by more than half, from about 130 GB to about 62 GB.

Tsurugi 1.13.0 also allows arbitrary expressions to be specified in the GROUP BY clause.

In previous versions, only columns could be specified in the GROUP BY clause, and to group by the result of an expression, you had to retrieve the result of the expression as a column in a subquery.
In this version, you can write an expression directly in the GROUP BY clause, as in GROUP BY EXTRACT(YEAR FROM ts).
Combined with the EXTRACT function supported in Tsurugi 1.12.0, aggregations of time-series data, such as per year or per month, can be written in simpler SQL.

This version also includes several important bug fixes and stability improvements.
We strongly recommend all users of previous versions to upgrade to this release.

New Features and Improvements

New Features and Improvements - SQL

• Added support for specifying arbitrary expressions in the GROUP BY clause
  • In addition to columns, expressions such as function calls and operations can now be specified as elements of the GROUP BY clause.

    SELECT EXTRACT(YEAR FROM ts) AS y, COUNT(*) AS cnt
    FROM tbl
    GROUP BY EXTRACT(YEAR FROM ts);

  • An expression specified in the GROUP BY clause can be referred to from the SELECT clause, the HAVING clause, and the ORDER BY clause. It can also be referred to as a part of an expression.

    SELECT a + 1, (a + 1) * 2, COUNT(*)
    FROM tbl
    GROUP BY a + 1
    HAVING a + 1 > 2
    ORDER BY a + 1;

  • An expression referred to from the SELECT clause and others must be written in the same form as the expression specified in the GROUP BY clause. For example, for GROUP BY a + 1, SELECT 1 + a is not regarded as the same expression and results in an error (column must be aggregated).

Note

In this version, the following restrictions apply to expressions in the GROUP BY clause.

• The following cannot be specified as elements of the GROUP BY clause:
  • Literals (e.g., GROUP BY 1)
  • Placeholders
  • Expressions that contain aggregate functions (e.g., GROUP BY a + COUNT(*))
  • Expressions of the BLOB or CLOB type
• The following can be specified as elements of the GROUP BY clause, but cannot be referred to from the SELECT clause and others:
  • Expressions that contain subqueries
  • Expressions that contain BETWEEN or IN predicates
  • Simple CASE expressions (e.g., CASE a WHEN 1 THEN ... END)
  • NULLIF expressions

To refer to the result of grouping by these expressions, retrieve the result of the expression as a column in a subquery and specify that column in the GROUP BY clause, as in previous versions.

SELECT g, COUNT(*)
FROM (SELECT CASE a WHEN 1 THEN 'one' ELSE 'other' END AS g FROM tbl) AS q
GROUP BY g;

Important

Starting with this version, specifying COLLATE in the GROUP BY clause results in an error (collation in GROUP BY is not yet supported).
In previous versions, COLLATE specified in the GROUP BY clause did not cause an error and was ignored.

Note

For other detailed specifications and current restrictions on SQL features, see the following document in tsurugidb:

• Available SQL features in Tsurugi

New Features and Improvements - Transaction

• Improved the storage efficiency of the index structure and reduced the memory usage of tables and indexes

  • Table rows and index entries are stored in memory in a layered structure that splits each key into 8-byte slices from the beginning. In previous versions, even after a key could be distinguished from other keys partway through, the remaining part was still stored one layer per 8 bytes, so longer keys used more memory. This version improves this by storing the remaining part together.

  • For each table and index, the reduction is larger when keys can be distinguished from other keys closer to the beginning and the remaining part is longer.

    • Secondary indexes on columns with few duplicate values, such as UUIDs, email addresses, and timestamps, see a large reduction in memory usage. The key of a secondary index consists of the column value followed by the primary key, so the longer the column value and the primary key, the larger the effect. For tables that have long string primary keys such as UUIDs, the memory usage of the table itself is also reduced.
    • On the other hand, secondary indexes on columns with many identical values, such as flags and status codes, show almost no change in memory usage, because keys cannot be distinguished until the primary key at the end. Even for datetime columns, the effect is small if many rows have the same time. For example, when keys fit within 8 bytes, as in a table whose primary key is BIGINT, there is little room for reduction.

  • In the Phone-Bill benchmark, the memory usage (RSS) of the database was reduced by more than half, from about 130 GB to about 62 GB. The call history table in this benchmark has a primary key of about 32 bytes on average, consisting of phone numbers, the call start time, and so on, and has secondary indexes such as one on the call start time. The call start time is almost unique and is followed by the long primary key, so the improvement in this version is very effective.

  • The following shows other examples of the effect with a single index configuration. A secondary index was created one at a time on columns with different value characteristics in a table of 3 million rows whose primary key is BIGINT, and the memory usage per entry was measured.

    Values of the indexed column	1.12.0	1.13.0	Reduction
    UUID-format strings (CHAR(36))	2,148 bytes	582 bytes	73%
    Email-address-format strings (VARCHAR(64))	1,436 bytes	587 bytes	59%
    Timestamps increasing by 1 second (TIMESTAMP)	1,134 bytes	513 bytes	55%
    10-digit codes, random (CHAR(10))	1,123 bytes	523 bytes	53%
    Sequential numbers (BIGINT)	811 bytes	512 bytes	37%
    Amounts, random (DECIMAL(12,2))	722 bytes	514 bytes	29%
    Random timestamps within a 28-day range (TIMESTAMP)	832 bytes	683 bytes	18%
    1,000 distinct values (BIGINT)	481 bytes	482 bytes	0%
    Codes, 10 distinct values (INT)	448 bytes	449 bytes	0%

New Features and Improvements - Client

• Tsurugi Python DB-API
  • Added the Cursor.explain method, which retrieves the execution plan of SQL
    • The arguments of the explain method are the same as those of the execute method. For details, see the following document:
      • Tsurugi Python DB-API 使用方法 - SQLの実行計画の取得方法 (Japanese)
  • Changed to release the Python GIL (Global Interpreter Lock) during communication with Tsurugi
    • In previous versions, the GIL was held during communication, so even when called from multiple threads, the threads were executed one at a time. Starting with this version, other threads can run during communication, and processing runs in parallel in multi-threaded applications.
  • Changed to explicitly close the transaction when Connection.commit or Connection.rollback is called
    • In previous versions, a new thread was started in the destructor to dispose of the transaction. Starting with this version, resources are released when commit or rollback is called.
• Tsurugi MCP Server
  • Added the startup option --query-pool-size, which specifies the maximum number of resources held by the query tool
    • When a result exceeds the response size limit, the query tool holds resources such as the session and transaction for reading the rest. If the rest was never read, these resources remained without being released.
    • When --query-pool-size is specified and the number of held resources exceeds the limit, resources are released starting from the oldest. The default is 0 (unlimited).

New Features and Improvements - Distribution

• Changed the source of the Apache Arrow package repository configuration package used by the installer and documentation from apache.jfrog.io to packages.apache.org
  • This change follows the official Apache Arrow guidance, which recommends packages.apache.org. The same packages are available from both hosts, and the installed version of Apache Arrow does not change.

Bug Fixes

Bug Fixes - SQL

• Fixed an issue where executing DML on a table during TRUNCATE TABLE on the same table could cause the database to terminate abnormally
  • There was a period during TRUNCATE TABLE processing in which DML on the same table could not be excluded. If a statement without placeholders was executed or prepare was performed during this period, TRUNCATE TABLE could fail with an error (DDL operation was blocked by other DML operation), and subsequent DML execution could cause the database to terminate abnormally. In addition, the table data could be deleted even though TRUNCATE TABLE returned an error.
• Fixed an issue where DECIMAL values that are numerically equal but have different internal representations (e.g., 1.0 and 1) could be treated as different values in GROUP BY, SELECT DISTINCT, COUNT(DISTINCT ...), and equi-joins, producing incorrect query results
  • This occurs when DECIMAL values computed by expressions are used as keys, for example when branches of a CASE expression or COALESCE function have different types, or when results of expressions with different scales are combined by UNION ALL. It does not occur when table columns are used as keys as they are.
• Fixed an issue where COUNT(DISTINCT ...) on REAL and DOUBLE values treated NaN values as different from each other and returned an incorrect value
  • Starting with this version, NaN values are treated as equal to each other, in the same way as in GROUP BY and SELECT DISTINCT.
• Fixed an issue where executing string functions such as CHAR_LENGTH on a string column that stores an invalid UTF-8 string loaded from a Parquet file could reference memory outside the string and return incorrect results
  • This occurs for values that end with an incomplete UTF-8 multi-byte character. Starting with this version, such values are treated as invalid UTF-8 strings, and CHAR_LENGTH returns NULL.
  • Values sent via SQL or clients never store invalid UTF-8 strings, so this issue does not occur for them.

Bug Fixes - Transaction

• Fixed an issue where, after an OCC transaction inserted a row with a long key, if another transaction inserted into the scan range of the same transaction, the serializability violation was not detected and the commit succeeded
• Fixed an issue where, if another transaction executed DROP TABLE or TRUNCATE TABLE on a table specified in WRITE PRESERVE of an LTX while the LTX was running, the database could terminate abnormally when the LTX was committed or rolled back
• Fixed an issue where online compaction could, in rare cases, cause committed data to be lost after the database was restarted
• Fixed an issue where online compaction could cause BLOB data being committed to be lost

Bug Fixes - Endpoint

• Fixed an issue where, when BLOB or CLOB data was sent in relay mode, the files of the sent data remained under the var/blob/sessions directory on the database side
  • Relay mode has been the default for each client library since Tsurugi 1.11.0. This issue occurs when data is sent through parameters of PreparedStatement or through LargeObjectClient in Tsubakuro/Java. It does not occur with the UDF BLOB client API.
  • Starting with this version, the files of the sent data are deleted when the processing of the request that uses the data finishes, or when the session is closed. Files left by previous versions are deleted when the database is restarted.

Bug Fixes - UDF

• Fixed an issue where the database terminated abnormally when a query that calls a user-defined table-valued function using the APPLY operator failed with an error partway through
  • This issue is fixed in Tsurugi UDF 0.5.1. To apply the fix, regenerate and redeploy UDF plugins using udf-plugin-builder 0.5.1 or later.
• Fixed an issue where, when a user-defined table-valued function was called using the APPLY operator and the result rows contained VARCHAR or VARBINARY values of 16 bytes or more, those values could be overwritten by values of other columns in the same row
  • This occurs when the same row has multiple variable-length columns of 16 bytes or more, or when a variable-length column of 16 bytes or more follows a BLOB or CLOB column. No error occurs; only the values change.
• Fixed an issue where, when multiple values were specified for the endpoint and secure parameters in the UDF plugin configuration file (<plugin-name>.ini) with spaces around the pipe character |, the second and subsequent values were not applied correctly

Bug Fixes - Client

• Tsubakuro/Rust, Tsurugi Python DB-API: Fixed an issue where, when an OCC transaction was aborted due to a serialization failure while executing a query, no exception was raised and existing rows could be returned as 0 rows
  • The transaction itself has been aborted, so the commit fails, and no updates based on the incorrect results are committed.
• Tsurugi Python DB-API: Fixed an issue where, when SQL was executed on the same connection after Connection.commit failed with an error, the failed transaction continued to be used and subsequent SQL also failed with InactiveTransactionException
• Tsurugi MCP Server: Fixed an issue where, when SQL failed with an error in the query tool, the session and transaction remained without being released

Upgrade Notes

Client Compatibility List

Tsurugi 1.13.0 supports the following clients.
Please upgrade each client as needed when upgrading Tsurugi.

Client	Version
Tsubakuro/Java	1.16.0+
Iceaxe	1.16.0+
Tanzawa	1.16.0+
Tsubakuro/Rust	0.10.0+
Tsurugi Python DB-API	0.10.0+
Tsurugi MCP Server	0.8.0+
Tsurugi JDBC	0.5.0+
Tsurugi UDF	0.5.0+

Transaction Log Format Changes

In this version, the transaction log format version has been changed to Version 8. When you start version 1.13.0 using a transaction log that was used with version 1.12.0, the transaction log format version will be automatically updated.

As a result, transaction logs started with version 1.13.0 cannot be used with version 1.12.0. Also, please note that backups created with version 1.13.0 or later cannot be restored with version 1.12.0.

Upgrade Procedure

For instructions on upgrading from previous versions, see:

• Tsurugi Upgrade Guide

Other Information

For a full list of changes in this version, see the Changelog:

• Tsurugi 1.13.0 - Changelog

For known issues and additional information about fixes in this version, see:

• Tsurugi 1.13.0 - Known Issues(en)

---

This discussion was created from the release 1.13.0.