Practical Firebird Performance Diagnostics: A Structured Approach

IBPhoenix is pleased to announce the release of a new free ebook:

Practical Firebird Performance Diagnostics: A Structured Approach

Unlike traditional performance tuning guides, this ebook focuses on the reasoning behind successful diagnostics. It introduces a structured framework that helps Firebird professionals move from observed symptoms to defensible explanations by classifying problems, selecting appropriate investigation strategies, interpreting evidence correctly, and using diagnostic tools with purpose.

The book covers the complete diagnostic process, from understanding why investigations fail to interpreting monitoring tables, trace sessions, profiler output, gstat reports, operating system metrics, and modern hardware behavior in the context of Firebird performance.

Whether you’re a database administrator, developer, consultant, or support engineer, this ebook provides a practical methodology for approaching complex performance problems with greater confidence and consistency.

The book is available for free from IBPhoenix store.

Happy reading!

Firebird Adds Unix Domain Socket Support

Firebird is gaining support for Unix Domain Sockets (UDS) through PR #9034, a feature aimed at improving local inter-process connectivity, especially in constrained environments like Android and iOS. The change introduces a new unix:// connection string format and a RemoteServiceUnixSocket configuration option, allowing Firebird clients and services to communicate over filesystem-based sockets instead of TCP.

This is a meaningful enhancement for mobile and sandboxed platforms, where filesystem permissions can be easier to manage than network ports. It also opens the door for scenarios where applications can launch a Firebird server process and connect to it locally even when embedded mode is not viable, which was the core motivation described in linked issue #9033.

From an implementation standpoint, the PR updates both client and server networking layers to recognize and handle AF_UNIX sockets, adds cleanup and auxiliary event-socket handling, and extends support to Windows environments that expose Unix socket APIs. It also updates configuration, build detection, and documentation so the new transport is visible and usable across supported platforms.

At a glance 

• PR: #9034 — Feature #9033 - Support for Unix Domain Sockets
• Author: asfernandes
• Status: Open, non-draft, mergeable
• Scope: 13 files changed, +544 / -27
• Commits: 2
• Linked feature request: #9033
• CI: Check runs completed on a broad platform matrix, including Linux, macOS, Windows, Docker, and Android builds

Why it matters

• Better fit for mobile and sandboxed environments
• Enables secure local connections based on filesystem permissions
• Reduces dependence on TCP for local-only communication
• Expands Firebird deployment flexibility for modern app runtimes

How Firebird’s New Parallel Sort Changes Everything

 

Breaking the Single-Thread Barrier: How Firebird’s New Parallel Sort Changes Everything

1. Introduction: The Multi-Core Paradox

There is a specific economic and technical frustration well-known to database architects: authorizing the purchase of high-end silicon with 64 or 128 cores, only to watch the OS scheduler show a single thread redlining while the rest of the hardware sits idle. Despite the massive processing power available, the internal logic of many database engines remains tethered to a sequential execution model. In this scenario, your return on investment for modern hardware is effectively throttled by legacy code, turning expensive multi-core chips into underutilized silicon.

Firebird SQL, with its architectural roots in the InterBase 6.0 legacy, has long been celebrated for its modularity and transactional robustness. However, it too faced the "serial bottleneck" where individual requests were confined to a single thread. Pull Request 8990 represents a paradigm shift for the engine. By moving from sequential processing to internal parallelism within the Journaling and Recovery Database (JRD) layer, Firebird is fundamentally evolving to meet the demands of modern hardware. This post explores the five most impactful technical breakthroughs from this architectural advancement.

2. Takeaway 1: Moving Beyond the "One Request, One Thread" Legacy

Firebird’s server models—Classic, SuperClassic, and SuperServer—were designed for an era where memory was the primary constraint and single-core performance was king. While SuperServer introduced multi-threading to manage concurrent connections, the execution of any single SQL statement remained strictly sequential. Even with dozens of cores available, a large sort operation was a monolithic task that could only utilize one.

PR 8990 is not a simple patch; it is a fundamental rethinking of the JRD layer. Specifically, it involves the transition of the JRD Task Manager from a simple serial queue to a robust central dispatcher. This new dispatcher model allows the engine to decompose complex operations into parallelizable segments that can be distributed across a global thread pool. By rethinking the core task-handling logic, Firebird can now treat a single query as a coordinated multi-threaded effort.

"As the database industry transitions from a focus on raw clock speed to the exploitation of massive core counts, the internal mechanisms of relational engines must evolve to avoid becoming performance bottlenecks."

3. Takeaway 2: The "Snapshot" Secret to Parallel ACID Compliance

Maintaining Multi-Version Concurrency Control (MVCC) while multiple threads sort data simultaneously is a significant technical hurdle. Every record version’s visibility must be checked against the Transaction Inventory Page (TIP), which tracks whether transactions are active, committed, or rolled back. In a standard sequential execution, this check is a linear process; in a parallel environment, it risks becoming a nightmare of disk contention and locking.

To solve this, PR 8990 utilizes a snapshot-based approach to the TIP. Before a parallel sort begins, the primary thread captures an immutable list of transaction states. This snapshot is shared with all worker threads. Because this view of the database state is "frozen" for the duration of the sort, worker threads can perform visibility checks independently. This is a brilliant architectural trade-off: by creating an immutable local copy of transaction states, the engine eliminates the need for workers to lock or even access the actual TIP pages on disk, significantly increasing concurrency and throughput.

4. Takeaway 3: A Mathematical Facelift for Big Data

The transition to parallel sorting fundamentally alters the mathematical complexity of query execution. The engine now employs a parallel external merge sort that utilizes a "cascading merge" strategy. This is specifically designed to address Amdahl’s Law by minimizing the "serial fraction"—the part of the code that cannot be parallelized. By allowing worker threads to merge groups of runs (e.g., four threads each merging 25 runs into intermediate blocks), the engine prevents the primary thread from becoming a bottleneck during the final merge phase.

Operation Phase	Sequential Complexity	Parallel Complexity (PR 8990)
Data Fetching	O(n)	O(n/p) (I/O dependent)
In-Memory Sort	O(n \log n)	O((n/p) \log (n/p))
Disk I/O (Runs)	O(n)	O(n) (Often I/O bound)
Merging Phase	O(n \log k)	O((n/p) \log k)

Key: n = number of records, p = number of parallel workers, k = number of runs being merged.

By partitioning the data stream and allowing worker threads to handle blocks and merges simultaneously, the engine achieves high efficiency even on systems with 32 or 64 cores.

5. Takeaway 4: The Hidden Performance Multiplier for Indexing

While the benefits to ORDER BY and GROUP BY are the most visible, the secondary impact on metadata maintenance is a game-changer. A CREATE INDEX statement is essentially a massive sort operation followed by B-tree construction. On tables with billions of rows, these operations traditionally took hours, creating massive maintenance windows.

PR 8990 applies the parallel sort framework to indexing, reducing these windows to a fraction of their former time. Furthermore, the architecture prioritizes a "Safety First" design. The SortManager is engineered to handle exceptions with surgical precision: if a worker thread fails due to a disk error or resource constraint, the SortManager catches the exception, terminates the remaining workers, and cleans up temporary files. This ensures that even under failure conditions, the database remains in a consistent state without orphaned temporary data.

6. Takeaway 5: Surgical Precision in Resource Management

Administrators gain granular control over this new power through updates to firebird.conf and databases.conf. This is facilitated by Firebird's support for scoped-value configuration, allowing different performance profiles for different databases on the same instance.

Key parameters include MaxParallelWorkers and ParallelWorkerStackSize. Tuning the stack size is particularly crucial; it allows architects to prevent memory exhaustion when high worker counts are required. To minimize overhead, the engine utilizes a "warm thread pool"—threads stay alive and wait for signals from the JRD Task Manager, avoiding the expensive lifecycle costs of creating and destroying threads for every task.

# databases.conf example: Scoped-value support
# High-parallelism for analytical workloads
AnalyticalDB = /databases/analytics.fdb { 
    MaxParallelWorkers = 16 
    ParallelWorkerStackSize = 512K
    TempCacheLimit = 2G 
}

# Restricted parallelism for high-concurrency OLTP
OLTP_DB = /databases/transactions.fdb { 
    MaxParallelWorkers = 2 
}

Importantly, these operations are fully observable. Administrators can monitor parallel utilization in real-time via the MON$STATEMENTS table using the new MON$PARALLEL_WORKERS column, providing the transparency needed to fine-tune production environments.

7. Conclusion: The Future is Parallel

Pull Request 8990 is more than an optimization; it is a foundational milestone. The task manager and worker thread framework established here serve as the infrastructure for future parallel advancements, such as parallel table scans, join algorithms, and background garbage collection.

As datasets grow toward terabyte scales, the criteria for selecting a database engine must change. In an era where 64-core processors are the standard entry point for servers, a database that cannot parallelize its internal tasks is no longer a tool; it’s a liability. With this shift, Firebird demonstrates its ability to maximize local resource utilization and secures its trajectory as a high-performance, open-source RDBMS ready for the most demanding modern workloads.

Help Us Test the New Firebird Docker Images

Major Update: Help Us Test the New Firebird Docker Images

We have been working on a significant overhaul of the official firebird-docker images, and a pre-release version is now available for testing at:

Pre-release Container Registry

We would love to get feedback from the community before these changes are merged upstream.

---

What’s New

Firebird 6 Snapshot Images

The new images include a 6-snapshot tag built daily from the master branch of FirebirdSQL/firebird. This is the first time a Firebird 6 development snapshot has been available as a Docker image.

# Pull the latest Firebird 6 development snapshot
docker pull ghcr.io/fdcastel/firebird:6-snapshot

# Pull the latest Firebird 5 patch snapshot
docker pull ghcr.io/fdcastel/firebird:5-snapshot

Snapshot images are rebuilt daily, so you always get the latest development build.

Expanded Version Coverage

The image matrix has been significantly expanded to cover more versions and distributions:

• Firebird 3.x: 3.0.8 through 3.0.13 (amd64 only; bookworm, bullseye, jammy)
• Firebird 4.x: 4.0.0 through 4.0.6 (amd64; bookworm, bullseye, jammy, noble)
• Firebird 5.x: 5.0.0 through 5.0.3 (amd64 + arm64; bookworm, bullseye, jammy, noble)
• Firebird 6 snapshot: Daily build from master (amd64 + arm64)
• Firebird 5 snapshot: Daily build from v5.0-release (amd64 + arm64)

---

How to Test

Pull the images directly from our pre-release registry to see how they perform in your environment:

# Latest stable (Firebird 5.0.3 on bookworm)
docker pull ghcr.io/fdcastel/firebird:latest

# Firebird 6 development snapshot ← Most wanted testers here!
docker pull ghcr.io/fdcastel/firebird:6-snapshot

# A specific version + distro
docker pull ghcr.io/fdcastel/firebird:5.0.3-jammy
docker pull ghcr.io/fdcastel/firebird:4.0.6-bookworm

We are especially looking for feedback on:

• Starting a container and connecting via isql or your preferred driver.
• ARM64 performance if you have compatible hardware (M-series Macs, Ampere, etc.).
• Firebird 6 snapshot behavior: Any connection issues, startup errors, or unexpected behavior changes.

⚠️ Important: Pre-Release Notice

These images have not yet been merged into the official repository and may contain bugs. The Firebird 6 snapshot, in particular, is built from unreleased, in-development code.

Warning: Do not use pre-release or snapshot images in production environments. Snapshot tags (6-snapshot, 5-snapshot) are rebuilt daily and do not carry stability guarantees.

---

Feedback

Your input is vital to making these images solid before the official merge. Please report any issues, successful test results, or suggestions at the upstream pull request:

🔗 GitHub Pull Request #36

Whether it’s a simple "works great on my ARM64 machine" or a detailed bug report, all feedback is welcome.

Thank you for helping us improve the Firebird ecosystem!

— F.D. Castel

FlameRobin 0.9.16 released

FlameRobin 0.9.16 released focuses on: modernizing CI/build tooling fixing compiler/linker issues , improving packaging (Flatpak), and delivering a set of Firebird metadata/DDL extraction and SQL editor correctness improvements

https://github.com/mariuz/flamerobin/releases/tag/0.9.16

PSFirebird: PowerShell Automation for Firebird on Windows and Linux

PSFirebird is a PowerShell module focused on automating Firebird environments, databases, and common administrative workflows. The main goal is to make Firebird easier to script end-to-end without depending on a manual installer flow or a machine-specific setup.

The problem is trying to solve was simple: working with Firebird in automation often means mixing shell scripts, ad hoc local installs, version-specific quirks, and CI setup glue. That gets especially painful when you need to test multiple Firebird versions, rehearse upgrades, or spin up disposable databases for integration tests. PSFirebird is designed to reduce that friction.

What PSFirebird Does

Today the module can:

• Download official Firebird release assets and prepare portable environments
• Run multiple Firebird versions side by side
• Create, inspect, read, test, and remove databases
• Execute SQL through isql from PowerShell
• Read and update firebird.conf
• Start ad hoc Firebird instances
• Register Firebird as a Windows service or Linux systemd service
• Backup and restore databases
• Convert databases across Firebird versions with streaming backup/restore
• Lock and unlock databases for filesystem-level copy using nbackup
• Parse gstat output into structured PowerShell objects for tables and indices

The current codebase targets PowerShell 7.4+ on Windows and Linux. On Linux, the environment bootstrap currently targets Debian-based systems.

Why I Think This is Useful

PSFirebird is not meant to be another GUI admin tool. It is aimed at repeatable automation.

Some scenarios where it should be useful:

• CI/CD pipelines that need a Firebird server on demand
• Test matrices that need Firebird 3.x, 4.x, and 5.x side by side
• Upgrade rehearsals from older databases to newer engine versions
• Disposable local environments for development and debugging
• Scripting routine admin tasks instead of doing everything manually

One of the most useful parts for me is the ability to create isolated Firebird environments directly from PowerShell, then run the rest of the workflow against that environment in a predictable way.

This project builds on earlier work I’ve done across several Firebird community initiatives such as SqlAlchemy-Firebird, the Firebird .NET Provider, the Firebird ODBC Driver, and the Python driver. Across all of these efforts, maintaining a robust and consistently repeatable testing foundation has been essential.

Example

Here is a small example that prepares two Firebird versions and converts a database from one environment to another:

$tempPath = [System.IO.Path]::GetTempPath()
$fb3 = New-FirebirdEnvironment -Version '3.0.12' -Path (Join-Path $tempPath 'fb3')
$fb5 = New-FirebirdEnvironment -Version '5.0.3' -Path (Join-Path $tempPath 'fb5')
$sourcePath = Join-Path $tempPath 'legacy.fdb'
$targetPath = Join-Path $tempPath 'legacy.fb50.fdb'

$db = New-FirebirdDatabase -Database $sourcePath -Environment $fb3 -Force

$env:ISC_USER = 'SYSDBA'
$env:ISC_PASSWORD = 'masterkey'
Convert-FirebirdDatabase -SourceDatabase $db -SourceEnvironment $fb3 -TargetDatabase $targetPath -TargetEnvironment $fb5 -Force

The module also includes helpers for scoped environment usage, configuration editing, SQL execution, service management, and database statistics collection.

Project Status

This is a real automation-oriented codebase, not just a thin proof of concept. The repository already includes:

• Unit tests
• Integration tests
• Cross-version conversion tests
• Windows and Linux CI coverage
• A GitHub Actions example showing end-to-end module usage in a workflow

Feedback Wanted

If you work with Firebird, I would especially like feedback on:

• Missing administrative workflows
• Cross-platform behavior and Linux packaging assumptions
• Upgrade and migration scenarios
• Command naming and module ergonomics
• CI and testing use cases

GitHub repository: https://github.com/fdcastel/PSFirebird

If this looks useful for your environment, take a look and let us know what would make it more practical for real-world Firebird automation.

Modernizing the Firebird ODBC Driver: Moving to CMake and Cleaning House

As part of an ongoing effort to improve the project's infrastructure, we have just merged Pull Request #281, which introduces a modern CMake build system and drastically cleans up our repository by removing over 62,000 lines of obsolete configurations, old headers, and broken test projects.

This is the second phase of a three-part plan to streamline how the Firebird ODBC Driver is built and maintained.

What’s New: A Modern CMake Build System

Building the Firebird ODBC driver is now easier and more standardized. We've introduced a CMake build system that handles building the FirebirdODBC.dll driver, the IscDbc static library, and running our test suite via CTest.

One of the major improvements is that the build system now automatically downloads the Firebird 5.0.2 public headers from GitHub at build time, meaning we no longer have to manually vendor them in our repository.

For developers who prefer the existing Visual Studio 2022 solution, don't worry—it still works! You simply need to run a one-time CMake configuration step to fetch the headers before opening the solution as usual.

Spring Cleaning: Removing the Old

Over the years, the repository accumulated a lot of build configurations for platforms and compilers that have long been discontinued. In this update, we did some massive spring cleaning:

• Removed 14 Obsolete Build Configurations: We stripped out outdated build environments, including Borland C++ 5.5, Solaris, old macOS/Linux/FreeBSD makefiles, MinGW, and Visual Studio versions ranging from 6.0 (1998) up to 2008.
• Deleted Vendored Headers: We removed roughly 25,000 lines of vendored Firebird headers (FBClient.Headers) and system Windows SDK headers, relying instead on CMake to fetch them or the system SDK.
• Cleaned Up Tests: Old, non-functional test projects (like the JDBC-style test and legacy standalone apps) have been cleared out. They have been fully replaced by the Google Test suite introduced in our previous PR.

What Hasn't Changed?

While the infrastructure has seen a massive overhaul, the driver source code itself remains completely untouched. There are no changes to the source files, no new features, and no performance alterations.

We heavily validated the new CMake build against the old build system, confirming that the output FirebirdODBC.dll is perfectly binary-compatible and exposes the exact same 120 exported symbols as before.

---

Thanks to @fdcastel for authoring this massive infrastructure improvement!