SQLite 3.45 has been released, and it brings several improvements to the popular SQL database library. One major enhancement is the optimization of the SQLITE_DIRECT_OVERFLOW_READ feature, which is now enabled by default. This optimization can significantly improve the read performance for applications that heavily rely on SQLite and read large BLOBs or strings. Another, perhaps more exciting update in SQLite 3.45 is the rewriting of all JSON functions to utilize the JSONB format. The JSONB version of SQLite’s JSON functions offers several times better performance compared to the existing JSON support within SQLite.

JSONB is a new internal-use binary representation of JSON that is stored as an SQL BLOB. This format allows SQLite’s internal binary representation of JSON to be directly stored in the database, eliminating the overhead of parsing and rendering JSON when reading and updating JSON values. Furthermore, the JSONB format takes up slightly less disk space than text JSON.

Source: Phoronix.

In a significant development for the PostgreSQL database server, incremental backup support has been successfully implemented and merged into the latest version, PostgreSQL 17. Spearheaded by Robert Haas, the implementation comprises three key components. Firstly, a background process named the walsummarizer continuously reads the Write-Ahead Log (WAL) to generate small WAL summary files containing vital information for determining which parts of the database require backing up. Secondly, the pg_basebackup tool now features an incremental backup mode, necessitating a backup manifest from a prior full backup. It reads WAL summary files between the previous full backup and the incremental backup to identify changed relation files. Lastly, a new utility, pg_combinebackup, validates and combines a full backup with one or more incremental backups to create a synthetic full backup or data directory.

To execute an incremental backup, users can leverage the new replication command UPLOAD_MANIFEST to upload the manifest for the prior backup, which could be a full backup or another incremental backup. The BASE_BACKUP command with the INCREMENTAL option is then employed to take the backup, and the pg_basebackup tool includes an --incremental=PATH_TO_MANIFEST option to trigger this behavior. Incremental backup files closely resemble regular full backups, but some relation files are replaced with those having names like INCREMENTAL.${ORIGINAL_NAME}. The backup_label file is also modified to indicate its status as an incremental backup. This feature is anticipated to be available in PostgreSQL 17, slated for release in September, promising an array of exciting changes for users of the database server.

Source: Phoronix.

SQLite, the leading open-source embedded database solution, has recently introduced JSONB, a rewrite of its JSON functions that promises to be “several times faster” than the previous implementation. The JSONB rewrite brings significant performance improvements by changing the internal binary representation of JSON into a contiguous byte array that can be read or written as an SQL BLOB. This optimization helps to reduce the overhead of parsing JSON text and representing JSON for storage. Despite the changes, JSONB maintains all the legacy functionality of the previous JSON functions. In fact, all JSON functions can also accept JSONB binary contents, ensuring compatibility with existing code and workflows.

Source: Phoronix.

PostgreSQL 16 has been released has been released with several new features and performance improvements. The update includes enhancements in query parallelism, bulk data loading, and logical replication. One of the notable additions is SIMD support for both x86 and ARM architectures, which improves ASCII and JSON string processing, array operations, and sub-transaction searches. The release also introduces more SQL/JSON syntax, pg_stat_io for key I/O metrics, and various security and access control enhancements. Bulk loading using COPY can see up to a 300% performance improvement in some cases.

Source: Phoronix.