Re: PostgreSQL vs Firebird SQL

Hi;

On Wed, Feb 10, 2016 at 5:10 AM, ioan ghip <ioan(at)pangea-comm(dot)com> wrote:

> I have a Firebird SQL database running on one of my servers which has
> about 50k inserts, about 100k updates and about 30k deletes every day.
> There are about 4 million records in 24 tables. I have a bunch of stored
> procedures, triggers, events and views that I'm using.
> Firebird works fairly well, but from time to time the database gets
> corrupted and I couldn't figure out yet (after many years of running)
> what's the reason. When this happens I run "gfix -mend -full -ignore",
> backup and restore the db and everything is fine until next problem in a
> week, or a month.
>

Is this running as an embedded engine or a standalone server? One thing
about Firebird is that since it is embeddable, in that mode other
application bugs could corrupt the database. In the other case, I would
expect you may want to run hardware diagnostics to rule out hardware
problems going forward. If you find hardware problems fix them first, then
look further.

But the low hanging possible things to look at here are moving from an
embedded mode to a standalone server if applicable, and checking out your
hardware. If these turn out not to be the problem, then I would recommend
moving.

> I never used PostgreSQL. Yesterday I installed it on my development
> machine and after few tests I saw that it's fairly easy to use.
>
> Does anyone have experience with both, Firebird and PostgreSQL? Is
> PostgreSQL way better performing than Firebird? Is it worth the effort
> moving away from Firebird? Would I gain stability and increased performance?
>

I have never seen database corruption on PostgreSQL that was not a result
of either:

1. Use cases WAY out of the ordinary (and then only years ago and I
reported a bug on this and it was very quickly fixed)
2. Hardware problems
3. Heat management problems (sticking a db server in a hot closet, and
then only indexes were corrupted).

I do think on decent hardware you will have no trouble. In other words,
outside of horrible abuse, PostgreSQL does very well.

The largest PostgreSQL database I have worked with had hundreds of tables,
some containing over a hundred million rows, and took up 9TB+ of storage.
And it processed millions of inserts, deletes, and updates every day (24x7
scientific computing cluster processing the data in the db). Granted at
that scale performance requires very good hardware and an attention to
detail but with those it runs fine.

I do have experience on both and am generally happier with PostgreSQL but I
can imagine there are cases where the move may be painful. Stored
procedures are one (though probably not so bad). The bigger issue I think
you will run into is case folding. Firebird follows the SQL standard and
folds to upper case. The PostgreSQL community really doesn't like this and
folds to lower case. This can require some changes in application code to
make work properly.

So there are my $0.02

>
> Thanks.
>

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more