Picodata 25.2 is out!

The Picodata development team is pleased to announce the release of Picodata 25.2, our core software product. This version introduces support for new SQL commands, revamped data type handling, better PostgreSQL compatibility, new metrics for Grafana, improved CLI experience as well as a plethora of fixes and small enhancements.

More SQL features

In Picodata 25.2 we improved the support for SQL standard and implemented several new commands:

• TRUNCATE for clearing a table’s contents
• ALTER TABLE t ADD COLUMN for adding new columns to a table
• ALTER TABLE old_table_name RENAME TO new_table_name for renaming a table

Also, we added the new instance_uuid scalar function.

New data type system

One of our biggest novelties in this release is the new data type system, which not only lets you write more advanced SQL queries, but also renders Picodata much more compatible with PostgreSQL. In the past, you would explicitly cast data types in parametrized queries in Picodata. This could be achieved by using c client library, although not all clients were compatible with the syntax. Consequently, the lack of compatibility led to extra efforts (such as using the CAST command for declaring the desired data type). With the new data type system things got a lot easier, even though the previous CAST commands are still valid. Additionally, the new data type system throws more useful and human-readable errors for invalid queries and uses more sane and expected defaults .

Some examples of error handling:

SELECT int_col + text_col;
--
-- error: could not resolve operator overload for +(int, text)

SELECT COALESCE(int_col, text_col);
--
-- error: COALESCE types int and text cannot be matched

VALUES (1, 2), (2, false);
--
-- error: VALUES types unsigned and bool cannot be matched

SELECT CASE WHEN true THEN 1 ELSE false END;
--
-- error: CASE/THEN types unsigned and bool cannot be matched

UPDATE t SET numeric_col = false;
--
-- error: column "numeric_col" is of type numeric, but expression is of type bool

INSERT INTO t (int_col, numeric_col) VALUES (false, 1);
--
-- error: INSERT column at position 1 is of type int, but expression is of type bool

In some case you now need to explicitly state the type. For instance, instead of

SELECT 1 + (SELECT $1);

use

SELECT 1 + (SELECT $1)::int;

The new data type system tries to guess the type by analyzing the context. It means that in certain cases you can omit data types in your queries. Some examples:

SELECT * FROM t WHERE a = $1

(type of $1 will be determined based on the a column)

INSERT INTO t (int_col) VALUES ($1)
UPDATE t SET int_col = $1

(in both examples the type of $1 will be determined based on int_col).

If there is no context (eg. SELECT $1), the data type system will assume that the parameter is a string.

PostgreSQL compatibility

In Picodata 25.2 we dropped our legacy built-in console (picodata connect) in favor of improved PostgreSQL support that would allow anyone to seamlessly connect to Picodata via any PostgreSQL client. Our recommended solution for that is psql. Picodata packages will pull psql as a dependency, and as for other cases, you can easily install psql in almost any OS without grabbing the whole PostgreSQL server. Also, connecting via PostgreSQL protocol now enables SSL by default.

Better monitoring

The new Picodata 25.2 boasts much-improved dashboard for monitoring Picodata clusters in Grafana. We also added a bunch of new picodata-specific metrics, making the whole experience noticeably better:

…and much more!

Other notable new features and changes in Picodata 25.2:

• By default, users with the public role can now read _pico_instance and _pico_peer_address system tables
• DROP privilege doesn’t allow dropping system tables
• Administrative console (picodata admin) in Lua mode (\lua) doesn’t require command delimiter anymore
• Partial use of either HOST or :PORT is no more allowed when stating addresses. The full HOST:PORT combo is required
• picodata expel doesn’t require stating the cluster name. The --cluster-name parameter is deprecated and will be removed in the future major Picodata release
• picodata status outputs more useful information, which is also better formatted now
• User creation or altering in SQL with a password and LDAP authentication method has been forbidden
• Plugin RPC requests will now be executed locally whenever possible.

See the full list of changes and fixed bugs in the CHANGELOG for this release.

Picodata for Ansible

Additionally, we keep on improving the Picodata role for Ansible, which enables convenient management of a distributed cluster. All Picodata features are available within the picodata-ansible role, including working with tiers and deploying plugins, as well as many additional features (e.g., managing instances via supervisord instead of systemd).

Get a hand on Picodata!

Get a hand on Picodata 25.1 by downloading the pre-built packages for supported Linux distributions in the download section at our site. By the way, we have updated the list of supported operating systems and now provide packages for the following Linux distributions:

• Astra 1.8
• Debian 12 (bookworm)
• RHEL/Rocky 9
• Fedora 41–42

You can find installation advisory as well as documentation for installing software, creating instances, cluster configuration and deployment at our dedicated documentation site.

Send your feedback and get in touch with Picodata developers in our Telegram chat at @picodataen or @picodataru.