database Postgres 与 Firebird

Postgres vs Firebird

提问by Tedi

I'm looking to use either Firebird or Postgres in my next development project ... largely because both are available under a BSD-like license.

我希望在我的下一个开发项目中使用 Firebird 或 Postgres ......主要是因为两者都在类似 BSD 的许可下可用。

I found a great comparison of the two database at http://web.archive.org/web/20100305134128/http://www.amsoftwaredesign.com/pg_vs_fb

我在http://web.archive.org/web/20100305134128/http://www.amsoftwaredesign.com/pg_vs_fb找到了两个数据库的很好的比较

But this comparison is a good 4+ years old and both databases have come a long ways since.

但是这种比较已经有 4 年多的历史了，而且这两个数据库从那以后都取得了长足的进步。

Does anyone mind updating the comparison table to be relevant for the current versions of both Firebird and Postgres ... or have a link to a site that does a good recent comparison between the two database?

有没有人介意更新比较表以使其与 Firebird 和 Postgres 的当前版本相关...或者有一个链接到一个网站，该网站在两个数据库之间进行了良好的近期比较？

回答by otzi

You have to choose technology like RDBMS not (only) on comparing list of features but on answering questions like these:

您必须选择 RDBMS 之类的技术，不仅（仅）用于比较功能列表，还必须回答以下问题：

• is it reliable enough for your needs?
• does it suit all your needs?
• what are perspectives/support/development/documentation?

• 它是否足以满足您的需求？
• 它是否满足您的所有需求？
• 什么是观点/支持/开发/文档？

For me FB is not a good choice. I can point two stories:

对我来说FB不是一个好的选择。我可以指出两个故事：

• I wrote constraint on table. Users add some strings to table. After a while user is trying to add one more string, but FB complains on constraint. What's wrong? This new string is surely under constraint. Problem is in a last string that was added before it! That string was added some time ago, it was wrong, and so far it is in DB, and who knows how many wrong data are now in DB. Very bad. FB implements constraints in a wrong manner.
• I wrote a Stored Procedure (SP). FB stored it in DB, FB can run it, and it works - select data as expected. No one warning. Then backup is done without errors. And only on restore FB complains that something is wrong with SP and ... what ... you have no restoreable backup copy now.

• 我在桌子上写了约束。用户向表中添加一些字符串。过了一会儿，用户试图再添加一个字符串，但 FB 抱怨约束。怎么了？这个新字符串肯定受到约束。问题出在在它之前添加的最后一个字符串中！那个字符串是前段时间加的，错了，到现在都在DB里，谁知道现在DB里有多少错误的数据。很坏。FB 以错误的方式实施约束。
• 我写了一个存储过程（SP）。FB 将其存储在 DB 中，FB 可以运行它，并且可以正常工作 - 按预期选择数据。没有人警告。然后备份完成，没有错误。并且只有在恢复时 FB 会抱怨 SP 有问题，并且......什么......你现在没有可恢复的备份副本。

I can point two or three more but these are enough for me to say: no, FB is not my choice, it's not RDBMS, it's a toy.

我可以再指出两三个，但这些足以让我说：不，FB 不是我的选择，它不是 RDBMS，它是一个玩具。

And they have bad documentation. In fact there is no actual references with each release. When people ask in forums: what is a full list of functions in release XX? The standard answer is: take a list from last official Interbase release and add (remove) functions from all later releases's "release notes".

而且他们的文档很差。事实上，每个版本都没有实际引用。当人们在论坛上问：发布 XX 中的完整功能列表是什么？标准答案是：从上一个官方 Interbase 版本中获取列表，并从所有后续版本的“发行说明”中添加（删除）功能。

And they have strange support/development strategy. They are working on what are interesting for them, on deep technical issues, not on what are really critical or annoying for users.

他们有奇怪的支持/开发策略。他们正在研究他们感兴趣的东西，深入的技术问题，而不是对用户来说真正重要或令人讨厌的东西。

回答by Alexey Sviridov

I've been working with Firebird for a long time and recommend to NOT use it in any way. They have many fundamental problems that Postgres does not have. Not to say many, just say they don't still have 'alter view' command. To alter a view you must drop and recreate it and all views depending on it. This is one of many misfeatures of FB. It's not correct to compare these DB Engines at all. I've had many cases when Firebird database was corrupted even without power loss, with usual workloads, and so on. If you still deciding, don't use Firebird! If you already chose FB, you will remember my words some time later :)

我已经与 Firebird 合作了很长时间，建议不要以任何方式使用它。它们有许多 Postgres 没有的基本问题。不是说很多，只是说他们仍然没有“改变视图”命令。要更改视图，您必须删除并重新创建它以及依赖于它的所有视图。这是 FB 的众多缺陷之一。根本不比较这些数据库引擎是不正确的。我遇到过很多情况，即使没有断电，通常的工作负载等等，Firebird 数据库也会损坏。如果您仍然决定，请不要使用 Firebird！如果你已经选择了FB，过段时间你会记住我的话:)

回答by Douglas Tosi

I can comment on Firebird. The following changed since the first comparison:

我可以评论火鸟。自第一次比较以来发生了以下变化：

Multi Threaded Architecture Available? Yes (super server)

多线程架构可用？是（超级服务器）

Actually, Classic Server is the preferred server type for scalability. Starting from Firebird 2.5 there is also an architecture called SuperClassic. I wrote a small post on the differences: SuperServer, ClassicServer or SuperClassic?

实际上，Classic Server 是可扩展性的首选服务器类型。从 Firebird 2.5 开始，还有一个叫做 SuperClassic 的架构。我写了一篇关于差异的小文章：SuperServer、ClassicServer 还是 SuperClassic？

Stores Transaction Information in same file as data? Yes (in red)

将交易信息与数据存储在同一个文件中？是（红色）

I don't see how this could be a problem to be written in red. Firebird uses careful writes to ensure the database structure is always consistent.

我不明白这怎么可能是一个用红色写成的问题。Firebird 使用谨慎的写入来确保数据库结构始终一致。

Temporary Tables? No

临时表？不

Available in Firebird 2.1. Release Notes

在 Firebird 2.1 中可用。发行说明

Rich Built in Functions? No

丰富的内置功能？不

Firebird 2.1 also improved this. Release Notes

Firebird 2.1 也改进了这一点。发行说明

Multiple authentication methods(i.e. LDAP) No

多种认证方式（即LDAP） 否

While there is no LDAP support, Firebird 2.1 allows Windows authentication. Release Notes

虽然不支持 LDAP，但 Firebird 2.1 允许 Windows 身份验证。发行说明

Create user defined types? No

创建用户定义的类型？不

I'm not sure what the author means here, but Firebird has had domains forever.

我不确定作者在这里的意思，但 Firebird 永远拥有域名。

Require backup/restore to compact? Yes (in red)

需要备份/恢复到压缩？是（红色）

Arguable. Database space is reused. I find it hard to imagine the need to shrink a database in production.

有争议的。数据库空间被重用。我发现很难想象在生产中需要缩小数据库。

Use POSIX Regular Expressions in queries? No

在查询中使用 POSIX 正则表达式？不

Firebird 2.5 supports regular expressions. Release Notes

Firebird 2.5 支持正则表达式。发行说明

Database Monitoring? No

数据库监控？不

Yes, Firebird 2.1 supports database monitoring via system tables. Firebird 2.5 adds support to trace facilities.
I know at least one excellent database monitoring tool for Firebird. ;)

是的，Firebird 2.1 支持通过系统表进行数据库监控。Firebird 2.5 添加了对跟踪工具的支持。
我知道至少一种用于 Firebird 的优秀数据库监控工具。;)

Ability to query databases on other servers local or remote. No

能够查询本地或远程其他服务器上的数据库。不

Firebird 2.5 can query remote databases. Release Notes

Firebird 2.5 可以查询远程数据库。发行说明

回答by Kev

AFAIK the each No in the Postgres column of the page you cited is still a No, although read-only databases can be effectively had by granting only select permissions on all tables in a database. However, there are many new features since 8.2. Some from 8.4:

AFAIK 您引用的页面的 Postgres 列中的每个 No 仍然是 No，尽管可以通过仅授予对数据库中所有表的选择权限来有效地拥有只读数据库。但是，自 8.2 以来有许多新功能。一些来自 8.4：

1. Windowing Functions
2. Common Table Expressions and Recursive Queries
3. Default and variadic parameters for functions
4. Parallel Restore
5. Column Permissions
6. Per-database locale settings

1. 窗口函数
2. 常用表表达式和递归查询
3. 函数的默认参数和可变参数
4. 并行还原
5. 列权限
6. 每个数据库的区域设置

...and some from 8.3:

...还有一些来自 8.3：

1. Full text search is integrated into the core database system
2. Support for the SQL/XML standard, including new operators and an XML data type
3. Enumerated data types (ENUM)
4. Arrays of composite types
5. Universally Unique Identifier (UUID) data type
6. Add control over whether NULLs sort first or last
7. Updatable cursors
8. Server configuration parameters can now be set on a per-function basis
9. User-defined types can now have type modifiers

1. 全文检索集成到核心数据库系统中
2. 支持 SQL/XML 标准，包括新的运算符和 XML 数据类型
3. 枚举数据类型 (ENUM)
4. 复合类型的数组
5. 通用唯一标识符 (UUID) 数据类型
6. 添加对 NULL 是先排序还是最后排序的控制
7. 可更新游标
8. 现在可以基于每个功能设置服务器配置参数
9. 用户定义的类型现在可以有类型修饰符

...whether Firebird has these, I don't know.

……Firebird 有没有这些，我不知道。

回答by Jonathan Leffler

One more recent comparison (2008):

最近的一项比较（2008 年）：

• http://nikolajlindberg.blogspot.com/2008/03/firebird-vs-postgresql.html

• http://nikolajlindberg.blogspot.com/2008/03/firebird-vs-postgresql.html

回答by pfote

File System based backups possible
Yes(Postmaster must be stopped)

可以进行基于文件系统的备份
是（必须停止 Postmaster）

is not true. since 8.1 (if i remember correctly) it's possible to activate WAL archiving and then creating filesystem based backups without shutting down anything. Look for PITR (point-in-time-recovery) in the docs for more information

不是真的。从 8.1 开始（如果我没记错的话）可以激活 WAL 归档，然后在不关闭任何东西的情况下创建基于文件系统的备份。在文档中查找 PITR（时间点恢复）以获取更多信息

回答by Lars D

One important thing is deployment:

一件重要的事情是部署：

• Firebird doesn't perform well with ext3 on Linux, you should use XFS or something similar.
• As far as I know, PostgreSQL still doesn't run on Windows servers.

• Firebird 在 Linux 上的 ext3 表现不佳，您应该使用 XFS 或类似的东西。
• 据我所知，PostgreSQL 仍然不能在 Windows 服务器上运行。

回答by Lars D

For those who are using Firebird, I would suggest reading the Firebird 2.1 Release Notesto understand a database corruption issue wrt to older Firebird versions running on Linux.

对于那些使用 Firebird 的人，我建议阅读Firebird 2.1 发行说明以了解在 Linux 上运行的旧 Firebird 版本的数据库损坏问题。

In short it has to do with a bug in Linux. For older Firebirds, the workaround is to mount the affected filesystem with the syncoption. The problem is fixed in FB2.1.

简而言之，它与 Linux 中的错误有关。对于较旧的 Firebird，解决方法是使用同步选项挂载受影响的文件系统。该问题已在 FB2.1 中修复。