ACID compliant databases, MySQl v POSTGRESQL

There is so much to learn when planning a new IT infrastructure.  In 1999 we were doing all our quoting by hand with a calculator.  Then we started using Excel to automate it.  Then it was ACCESS with it JET database system. Then it was SQL with an ACCESS front end viua ODBC. Now we are moving to POSTGRESQL. We do use MySQL for web based database stuff and SEO becuase its fast as a web server, but not the mission critical CRM system.

Its all about relaibiity and scalability.  As our business has grown, we outgrew individual XLS sheets very quickly, and ACCESS cannt deal relably with many concurrent users.  MS SQL isn’t bad, but it still does weird things sometimes and we loose data. Basically, I dont have long term confidence in it. 

There is also this whole issue of ACID compliance. Jake from Senokian pointed out that http://tweakers.net/reviews/657 shows some of the advantages of PostgreSQL over mySQL).  Jon from Clocksoft also talked about PostgreSQL in an initial conversation.  One problem we have had is data being partially changed, but not fully.  ACID compliant databases change everything or nothing, so if there is a hardwware or netorking error you dont get half changed instructions.  POSTGRESQL also comes from a more professional stable.

In terms of open source databases, MYSQL will probably also do the job as it can run in ACID compliant mode, but I’m no expert.  We have taken Senokian’s advice on this, and fingers crossed we wont have to change infrastructures again for a good few years.  I just hope that PostgreSQL lives up to expectations of being a very relaible platform.