I am considering jumping ships in the great scheme of open-source databases.
After running into "trouble" in mysql - it doesn't optimize the use of "OR"
in queries, amongst other concerns, I am considering porting to PostgreSQL.
However, a few things worry me.
1. Replication
I see that PostgreSQL doesn't natively support replication. However, I've
found rserv and pgreplicate. Both look like decent projects, but I'm curious
if anyone here has toyed with them at any length or used them in a
production environment. I will be needing production quality replication.
2. Performance (particularily many small queries and inserts)
Due to the nature of the app I work on, and historical reasons as well, I
perform many small queries that need to go quite quickly. I rewrote some
code to use one big query and the speed increased by a factor of 10.
However, since mysql lacks subselects, I haven't been able to rid myself of
the many-small-queries approach (specifically, i'm getting transaction
groups with - say - 2 day's history, but from within an initial time window
of 3 hours). This can be done with subselects, but i am curious about the
performance of subselects.
Also, what is the insert performance of PostgreSQL? Compared to mysql? I'm
doing transaction processing, and receive up to ten transactions per second.
Don't worry, I can throw hardware at the problem, but I'd rather not.
I hope some of you got some experiences to share with me, positive or
negative.
Best regards,
Jeppe