Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

SQLite vs Firebird vs ...

by sedusedan (Monk)
on Mar 09, 2013 at 02:13 UTC ( #1022519=perlquestion: print w/ replies, xml ) Need Help??
sedusedan has asked for the wisdom of the Perl Monks concerning the following question:

SQLite used to be my go-to database for small programs and various other cases, but lately I've been more annoyed by two things. First is its poor concurrency. My last program is an email analyzer/parser and the program is installed in .qmail. Even with the rate of incoming mails of merely 2-3/sec, and concurrency of 3-5 processes, I keep getting locked out when wanting to access the database.

Second is its almost utter lack of ALTER TABLE support. It only allows renaming a table and adding columns. Even renaming columns is not possible (and I wonder what the technical difficulty for that would be).

There are other annoyances like little choices of data types (making date handling very bothersome, for example), but those two above are what drive me nuts.

I've gone Postgres for almost everything nowadays, but still wonder whether Firebird is a better suit for some cases. Firebird is another SQL database engine that has embedded mode and looks to have better support in those two regards. Plus it has stored procedures and other "advanced" SQL features that SQLite lacks.

Does anyone have experience with Firebird to share? Or with SQLite, for that matter, like what tools to use to ease the pain of altering tables? Searching the monastery only yields mostly threads about installing Firebird/IB.

Comment on SQLite vs Firebird vs ...
Re: SQLite vs Firebird vs ...
by erix (Vicar) on Mar 09, 2013 at 09:14 UTC

    gone Postgres for almost everything nowadays [...]

    Does anyone have experience with Firebird to share?

    And there you have it; you really said it yourself in a nutshell. I'd be somewhat interested in firebird too. But who is going to mess with a relative unproven option like firebird? (except perhaps for the embeddedness -- which Pg does not offer)

    IMHO, even if one assumes that firebird can catch up with postgres at all, it would take quite some time before it will be able to match postgres' stability and 'ecosystem'.

      Although I usually use Postgres now, embedded/serverless mode still has its uses, for example when a program which is run by normal users wants to store data/statistics in some relational database without having to deal with system-level permissions or a system-wide daemon. Another approach would be to run a per-user database daemon, like in KDE's akonadi which starts its own MySQL daemon; this is not really feasible for a command line-based program.

      Firebird is not unproven, it has a long (as in decades-long, in some form or another) track record. Though perhaps it's still not popular in Unix/Linux circles. Kind of sad, because SQLite really sucks "SQL-wise".

      I think I'll bite my finger and just start to use it. I'll share my experiences later.

        SQLite really sucks "SQL-wise".

        SQLite rules for what it is: an utterly free, stable, embeddable, minimalistic engine. Saying it sucks because it doesn't match your use case is like saying a saw sucks because it won't drive nails well.

        I used Firebird a long time ago and remember it was as solid as Postgres is today, providing concurrency, transactions and everything else on the flakiest Windows versions, which is something. The problem I see with using it now is that it has very little support in hosting services, so your application would have to remain in a server you have complete control of.

      Drifting a bit off-topic perhaps, but I thought this was interesting (we love lists after all, and both your (lightweight?) db's are in the list too).

      Below is this month's list from a (apparently) monthly updated list of database "popularity" (measured by mentions on google, bing, by job offers, and some other things).

      Rank March 2013 DBMS Database Model Scor +e Changes Rank Last Month ---------------------------------------------------------------------- +----------- 1. 1. Oracle Relational DBMS 1533. +39 -34.54 2. 2. Microsoft SQL Server Relational DBMS 1318. +36 +7.75 3. 3. MySQL (Oracle-owned) Relational DBMS 1295. +22 +10.44 4. up 5. PostgreSQL Relational DBMS 177. +16 -6.31 5. down 4. Microsoft Access Relational DBMS 170. +17 -15.88 6. 6. DB2 Relational DBMS 159. +33 -2.72 7. 7. MongoDB Document store 124. +22 +8.09 8. 8. Sybase Relational DBMS 85. +41 +0.89 9. 9. SQLite Relational DBMS 83. +26 +2.25 10. 10. Solr Search engine 43. +16 -4.19 11. 11. Cassandra Wide column store 36. +36 +0.20 12. 12. Redis Key-value store 32. +43 +0.40 13. up 14. Memcached Key-value store 24. +97 +1.69 14. down 13. Informix Relational DBMS 23. +90 -1.53 15. 15. HBase Wide column store 20. +45 -0.29 16. 16. CouchDB Document store 18. +29 -1.40 17. 17. Firebird Relational DBMS 13. +77 +0.18 18. 18. Sphinx Search engine 9. +47 +0.62 19. up 20. Neo4j Graph DBMS 7. +44 +0.32 20. up 21. Vertica Relational DBMS 7. +40 +0.97 21. down 19. Elasticsearch Search engine 6. +76 -0.64 22. up 23. Riak Key-value store 6. +09 -0.07 23. down 22. Ingres Relational DBMS 5. +51 -0.88 24. 24. Interbase Relational DBMS 5. +16 +0.19 25. up 30. RavenDB Document store 4. +23 +0.60 26. up 27. Couchbase Document store 4. +18 +0.20 27. down 26. Sybase IQ Relational DBMS 3. +97 -0.02 28. up 34. MarkLogic Native XML DBMS 3. +91 +0.84 29. down 25. DynamoDB Key-value store 3. +83 -0.24 30. up 33. Jackrabbit Content store 3. +81 +0.72 31. down 28. SQL Anywhere Relational DBMS 3. +52 -0.40 32. down 31. Derby Relational DBMS 3. +26 -0.21 33. down 32. Berkeley DB Key-value store 2. +94 -0.36 34. down 29. Adabas Multivalue DBMS 2. +78 -1.10 35. 35. MariaDB Relational DBMS 2. +64 -0.42 36. 36. SimpleDB Key-value store 2. +47 -0.11 37. 37. mSQL Relational DBMS 1. +99 -0.09 38. 38. H2 Relational DBMS 1. +90 +0.06 39. 39. Virtuoso Relational DBMS 1. +85 +0.06 40. MaxDB Relational DBMS 1. +71 41. 40. TimesTen Relational DBMS 1. +57 -0.11 42. Caché Object oriented DBMS 1. +50 43. 41. Db4o Object oriented DBMS 1. +32 -0.11 44. 44. Versant Object Database Object oriented DBMS 1. +28 +0.20 45. 43. Accumulo Key-value store 1. +28 +0.08 46. 42. HyperSQL Relational DBMS 1. +11 -0.09 47. IMS Navigational DBMS 0. +97 48. 45. ObjectStore Object oriented DBMS 0. +90 -0.06 49. 47. Drizzle Relational DBMS 0. +85 -0.05 50. 46. Tamino Native XML DBMS 0. +80 -0.13 51. 51. Infinispan Key-value store 0. +70 -0.03 52. 49. Oracle NoSQL Key-value store 0. +64 -0.14 53. 50. Sedna Native XML DBMS 0. +63 -0.11 54. 52. Jena RDF store 0. +62 -0.04 55. IDMS Navigational DBMS 0. +60 56. Mnesia Document store 0. +50 57. 48. VoltDB Relational DBMS 0. +50 -0.34 58. 53. Sesame RDF store 0. +48 -0.07 59. 57. OrientDB Graph DBMS 0. +41 +0.09 60. 54. solidDB Relational DBMS 0. +39 +0.01 61. 55. GT.M Key-value store 0. +36 +0.00 62. 56. Xapian Search engine 0. +33 -0.03 63. 65. Compass Search engine 0. +33 +0.10 64. 59. DEX Graph DBMS 0. +29 -0.02 65. 60. MonetDB Relational DBMS 0. +26 -0.05 66. 58. Hypertable Wide column store 0. +26 -0.06 67. 61. Tokyo Cabinet Key-value store 0. +26 -0.03 68. 63. Clustrix Relational DBMS 0. +25 -0.00 69. Pervasive PSQL Relational DBMS 0. +24 70. 64. OpenBase Relational DBMS 0. +24 -0.00 71. 67. BaseX Native XML DBMS 0. +23 +0.02 72. 66. InfiniDB Relational DBMS 0. +22 -0.00 73. 62. VistaDB Relational DBMS 0. +21 -0.06 74. 70. Cubrid Relational DBMS 0. +17 +0.01 75. 68. NuoDB Relational DBMS 0. +16 -0.03 76. 80. Kdb+ Relational DBMS 0. +15 +0.09 77. 71. AllegroGraph RDF store 0. +15 +0.00 78. 75. Tokyo Tyrant Key-value store 0. +14 +0.02 79. 72. Aerospike Key-value store 0. +14 -0.00 80. 73. Project Voldemort Key-value store 0. +14 +0.01 81. 69. Perst Object oriented DBMS 0. +13 -0.04 82. Hibari Key-value store 0. +13 83. 76. Akiban Relational DBMS 0. +11 -0.00 84. 77. LevelDB Key-value store 0. +11 +0.01 85. 78. CloudSearch Search engine 0. +10 +0.02 86. 81. eXtremeDB Relational DBMS 0. +05 -0.01 87. 74. Scalaris Key-value store 0. +05 -0.08 88. 82. Meronymy RDF store 0. +04 -0.01 89. 79. MemSQL Relational DBMS 0. +03 -0.03 90. 85. Stardog RDF store 0. +03 -0.00 91. 83. LucidDB Relational DBMS 0. +02 -0.02 92. 88. 4store RDF store 0. +02 +0.00 93. 86. Mulgara RDF store 0. +02 -0.00 94. 89. Exasol Relational DBMS 0. +01 -0.00 95. 90. ObjectDB Object oriented DBMS 0. +01 -0.00 96. 84. InfoGrid Graph DBMS 0. +00 -0.03

      I haven't looked much at the scoring precedure yet.

      There is the expected top-3, with the expected downward trend of "Big" Oracle. That the trend of "Small" Oracle (=mysql) is still upward is therefore surprising. (Then again, these numbers are over a single month; I should probably look further back for real trends)

      There is a huge gap between mysql and postgres, numbers 3 and 4. One would expect that that gap is closing; this is obviously not happening in these month's numbers. Ah well...

      PostgreSQL has overtaken both DB2 and MS-Access in the last 6 months. Surprising, as I had expected DB2 to be much higher on any such list.

      Anyway, the whole list is here. (They also seem to publish blog posts discussing the changes every now and then, like here)

Re: SQLite vs Firebird vs ...
by stefbv (Chaplain) on Mar 09, 2013 at 13:29 UTC

    I have a long time experience using Firebird, more than 10 years, and I can say that it is very stable even on Windows XP. It is also robust, easy to install and use, there is a good DBD module for it (DBD::Firebird) and there is also a good administration tool named Flamerobin.

    I use it with small databases, but I see that is also your intent so I have no reason not to recommend it.

    Regards Stefan.

    Update: This doesn't mean it can't handle large databases, it just means that I don't have experience with large databases.

      Thanks for sharing.

      Do you use it with a multithreaded program?

      I'm particularly interested in startup/connection speed, though I'll be doing some benchmarking myself later on.

        No, not with a multithreaded program.

      I agree with what Stefan has said above Firebird is very robust on Windows but also on Posix systems and is used in large scale applications

      Firebird on Large Big Iron servers : 512G-2TB of ram 100.000 users http://www.firebirdnews.org/?p=6797

      Where firebird shine is embeeded mode also : full multithreaded and is easy to carry around with you

      DBD::Firebird does have support for embedded mode Damyan created some of the test to run with embedded mode

      The dabatabase that is to be used in LibreOffice Base is Firebird Embedded and the reasons are that Firebird is lot more powerfull than sqlite and a lot faster and easier to deploy/integrate than other databases https://wiki.documentfoundation.org/Development/Base/FirebirdSQL

Re: SQLite vs Firebird vs ...
by sedusedan (Monk) on Mar 13, 2013 at 03:02 UTC
    I completely forgot another option: MySQL embedded. It's also available via DBI (DBD::mysql), but last time I check you have to pay a licensing fee for commercial uses.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1022519]
Approved by bitingduck
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (7)
As of 2014-09-02 10:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (21 votes), past polls