Comparison of PostgreSQL and Firebird lazarus / 16-Databases ---------------------------------------------------------------------- egsuh · 2019-12-04 07:36 Hi guys, I have a database written in Firebird. It has several tables and stored procedures, and a little bit complex but not so much as to fear to rewrite it in other database (it doesn't contain any real data anyway). Recently I found PostgreSQL provides more functionality than Firebird like inheriting tables, writing procedural functions within PostgreSQL itself using PL/pgSQL or C.  So I'm wondering whether I can write many Pascal code in PL/pgSQL. Do you think that the advantage of PostgreSQL deserve the effort of changing Firebird to PostgreSQL in the long run? Most tables and stored procedures would be easily transformed. But what about learning PL/pgSQL? It's like learning a new language. Yes... it will depend on how much codes are written in Pascal to operate on the data. Please comment on the amount of effort (or labor) to learn PL/pgSQL, and its effectiveness. ---------------------------------------------------------------------- Groffy · 2019-12-04 08:19 Hi, is there so much logic to implement on server side? What is the advantage you want to archive? Is there anything you are planning to do, but can't do it with Firebird? PostgreSQL is powerful for sure and the only open source database which really can challenge Firebird, but for myself Firebird was always my first choice Best ---------------------------------------------------------------------- MarkMLl · 2019-12-04 08:22 I've used PostgreSQL for pushing 20 years. The only thing that prevents my saying that it's been trouble-free is that I don't like to tempt providence :-) A few years ago I investigated writing a program intended to use either Postgres or Firebird (or potentially both) as a backend. My experience was that the SQL was pretty similar, but Postgres won hands-down when looking at things like managing users and- in particular- when considering the relative quality of the documentation. Firebird might have improved since, but I found myself having to refer to multiple manuals and addenda and use several different programs for tasks that Postgres can handle through the normal SQL interface. So to summarise, PostgreSQL is actively developed and is continually gaining features that are either "standards compliant" or seem like an irresistibly good idea. For anything larger than internal storage for a single program I think it is well worth considering seriously. MarkMLl ---------------------------------------------------------------------- egsuh · 2019-12-04 08:49 Quoteis there so much logic to implement on server side? What is the advantage you want to archive? Is there anything you are planning to do, but can't do it with Firebird? Well, not so much logic. But I have to call several "select" statements to keep the integrity of data in Firebird. I'm just thinking if they can be done within database internally, the server codes would be much clean. @MarkMLI Thank you for the comments. I'll take a serious consideration. ---------------------------------------------------------------------- MarkMLl · 2019-12-04 09:01 It goes without saying that for strictly single-program and single- user operation SQLite might be better. But remembering the deep water in which Borland (Paradox) and others ended up with lockfiles etc. and considering the variable "strength" of file locking implemented by different OSes I think that if there's any doubt whether those two criteria can be met it's the wrong choice. And even for single-session operation, I can envisage situations where an overwhelming requirement for e.g. robust transaction handling over multiple tables points towards PostgreSQL etc. MarkMLl ---------------------------------------------------------------------- sash · 2019-12-04 09:47 Quote from: MarkMLl on December 04, 2019, 09:22:13 amI've used PostgreSQL for pushing 20 years. Postgres won hands-down when looking at things like managing users I wouldn't call PG's user management exceptional: users and roles there and there. I've used IB/FB for the similar period of time (I have a good Postgres experience too) and never had a chance to work on application, where user management paradigm (of application) fully fits capabilities provided by database. Simply because access to internal database's objects <> access to application's entities. No matter, which database you're using. I believe PG's strong parts are some extended SQL features and datatypes. But you can also call it unnecessary complexity. On other hand, FB excels in easiness of maintenance and portability. ---------------------------------------------------------------------- Groffy · 2019-12-04 10:25 Quote from: egsuh on December 04, 2019, 09:49:08 am But I have to call several "select" statements to keep the integrity of data in Firebird. Thats sounds to me that you should rethink your data model. Keeping the data integrity is one of Fb strongest parts. ---------------------------------------------------------------------- Groffy · 2019-12-04 10:35 Quote from: MarkMLl on December 04, 2019, 09:22:13 am... - in particular- when considering the relative quality of the documentation. Firebird might have improved since, but I found myself having to refer to multiple manuals and addenda and use several different programs for tasks that Postgres can handle through the normal SQL interface. Good point, the PostgreSQL documentation is awesome. The Firebird development is trying to keep track with the big competitors. Firebird 3 was a major step. User administration by sql statements, external procedure plugins (c# / java), encryption and lots more-  but FTS and replication still not build in. ---------------------------------------------------------------------- sash · 2019-12-04 11:17 Quote from: MarkMLl on December 04, 2019, 10:01:20 amIt goes without saying that for strictly single-program and single-user operation SQLite might be better. In the terms of simplicity in-memory TBufDataset is even "better". No external dependencies at all. For the same "amount of maintenance" you'll "buy" much more features from FB-embedded (SPs, real transactions, etc) than from SQLite. Not to mention an ability to turn your database to "real server" with zero coding. Quote from: MarkMLl on December 04, 2019, 10:01:20 amBut remembering the deep water in which Borland (Paradox) and others ended up with lockfilesHow does a past-century (serverless by design) technology relates to current situation, especially knowing that other competing vendors suffered from this same exact issue of that time? ---------------------------------------------------------------------- MarkMLl · 2019-12-04 12:03 Quote from: sash on December 04, 2019, 12:17:28 pmQuote from: MarkMLl on December 04, 2019, 10:01:20 amBut remembering the deep water in which Borland (Paradox) and others ended up with lockfilesHow does a past-century (serverless by design) technology relates to current situation, especially knowing that other competing vendors suffered from this same exact issue of that time? I do believe that I explicitly used the words "and others" in the text you quoted. The point I'm making is that at about the time that Paradox (and others) came out networks on PCs were in their infancy- in fact they were barely a glimmer in most suppliers' eye. But over the next few years an increasing number of people started using peer- to-peer networks from Microsoft, Novell (and others) and trying to graft the single-user database libraries and applications that they were used to onto their office network. In fact people still try to do that sort of thing with applications such as Quickbooks (and others). That just about worked in the 1990s. But when client-server databases started becoming fairly accessible in the early 2000s (PostgreSQL, MySQL, SOLID and of course others) and with the ready availability of interface APIs such as ODBC (I'm sure that I need not add that there were others) using something which had been designed as a single-user in-program library really did become difficult to justify. OK, so it worked... usually, until things needed to be cleaned up. This was recognised even by the developers of some of the most popular database libraries, notably by C-Tree who as well as their libraries started shipping a standalone server (although I can't remember what network API it used). I used to sell such things. So while I note somebody's recent comments that SQLite will support multiuser operation I'm not convinced that it's necessarily a good idea particularly since when one moves from Windows to variants of unix with NFS etc. file locking tends to be rather an afterthought. MarkMLl ---------------------------------------------------------------------- devEric69 · 2019-12-04 13:37 Hello, I suggest you should also consider the suite of components you can use. IBX makes Firebird very easy to use (the "active record" pattern associated with the master-details articulation can easily be implemented with TIBDataset; Lookup requests can be launched with TIQuery, ...), and adds smart components (to monitor requests passing over the network, or to simplify updates with SQL scripts, manage database events, etc). I've only heard good things about PostgreSQL, but unfortunately, I don't have time to be interested in it. On the other hand, I know that those who make Firebird evolve are aware of their weaknesses, and are working to improve them (akaik: creating of an invisible root like on mySQL for future installations, so that people can create databases, connect to them, and start developing without first having to understand privileges and roles; recursive SQL presence; the transactional layer is solid...). ---------------------------------------------------------------------- snorkel · 2019-12-20 04:54 In regards to SQLite , it indeed can support robust multi user operation by enabling it's WAL support, when you do that it uses a shared memory file and if you have a queue to do the writes it can support large numbers of concurrent reads.  I use it for a managed file transfer system created with Lazarus Indy and Securebridge from Devart and it works outstanding if setup properly. The key is that all the access has to be on the same server, if you start having it access via windows shares then the WAL and shared memory don't work and you will get the locking.  You can even have two processes on the same server access it with no issues as long as you use a queue doing things like writing to a log table etc. The big benefit to using SQLite like that is it keeps your server app from being scanned by the corporate overlords since it does not have any network interface unless you build a custom one with JSON or something like that.