Question :
We maintain a list of all the SAN Switch Ports that are either free or allocated to hosts. Currently, we enter the information on the company portal. But we need to move if off to a database because as of now we have about 2200 rows of data in it and each row has 10 columns. I got the number after extracting the data into an excel sheet. I am expecting that atleast 1000 more rows will be added in a few months.
All of our SAN Mgmt servers are windows 2003, but none of them have MS Office installed, so cannot use MS Excel. Hence the need for an open source database.
I have tried MySQL and PostgreSQL. I find MySQL easier to deal with, but I have read that data reliablity wise PostgreSQL is better. I tried both databases for a few days, imported CSV files to both as that was easier compared to manually entering 2200 entries in it. Both MySQL and PostgreSQL seem to be doing good. Though, I had to edit the CSV file because the field seperator “,” was used in some places in of the cells and that made it break and skew the entries in MySQL. PostgreSQL didnt have any trouble with that.
A request to experienced Database Admins – kindly let me know that for my need will MySQL suffice? Is there a chance of Data Corruption with MySQL? Or would it be better to move to PostgreSQL right now itself?
My apologies for not using database specific terminologies here because I do not hail from a DB Background.
Please let me know.
Answer :
PostgreSQL has a lot more modern SQL features compared to MySQL.
Some of those featurese you might not find necessary right now but you’ll deeply miss them once your application grows and your needs get more complex. Here are just some:
- No check constraints
- No deferrable constraints
- No windowing functions
- No common table expressions
- No recursive queries (think hierarchical data)
- Non transactional FK evaluation
- No table functions
- No function based index
- No partial index
- No full text search on transactional tables
- No GIS features on transactional tables
- Incomplete trigger implementation
Performance wise both will be pretty much the same (especially with such a tiny dataset). I wouldn’t expect to see any difference between the two with your data.
If the database size grows, PostgreSQL seems to cope better with a lot of concurrent read and write access, while MySQL has some read optimizations that are not (yet) available in PostgreSQL (like index-only scans) which tends to make MySQL perform better in heavy read-only situations. But then every workload is different and it’s basically impossible to generally say “one is faster than the other”.
If you go for MySQL make sure you run it in ANSI and strict mode right from the beginning to get rid of some of gotchas that the default installation keeps for surprises (e.g. silently truncating data or accepting February 31st as a valid date).
(You might also want to look at the comment of ypercube here: https://stackoverflow.com/questions/9963012/what-does-the-sql-standard-say-about-dependent-conditionals-in-update/9963473#9963473 to get a feeling on what I mean with “gotchas”)
This blog post also lists some of the really nice things about PostgreSQL (compared to SQL Server):
http://facility9.com/2011/12/ten-reasons-postgresql-is-better-than-sql-server/
MySQL and PostgreSQL are quite close to each other by scope of capabilities and benchmarking results. They both have their strengths and weaknesses. MySQL is a little bit faster but less featured of the two systems. Both systems are used by very large projects: Google AdWords is implenented over MySQL database engine, while Yahoo uses Postgres.
The most in-depth comparision of MySQL and PostgreSQL can be found on the official Wiki page:
http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL
And if you need to migrate data from Postgres to MySQL, take a look at this tool: