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 contentsALTER TABLE t ADD COLUMN
for adding new columns to a tableALTER 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 fullHOST: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 releasepicodata 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.