Posted
by
timothyon Saturday August 18, 2012 @01:26AM
from the seems-like-a-reasonable-suspicion dept.

mpol writes "Sergei from MariaDB speculated on some changes within MySQL 5.5.27. It seems new testcases aren't included with MySQL any more, which leaves developers depending on it in the cold. 'Does this mean that test cases are no longer open source? Oracle did not reply to my question. But indeed, there is evidence that this guess is true. For example, this commit mail shows that new test cases, indeed, go in this "internal" directory, which is not included in the MySQL source distribution.' On a similar note, updates for the version history on Launchpad are not being updated anymore. What is Oracle's plan here? And is alienating the developer community just not seen as a problem at Oracle?"

Postgresql is also a Free Software multi-platform database. It was designed properly (unlike MySQL, Postgresqlwas designed with transactions in mind), has excellent internationalization support (proper 3 and 4 byte UTF, unlike MS SQL-Server with its UCS-2 or blob unicode [unless the very latest version has fixed this]).

Personally I prefer Postgresql to MySQL. While Postgresql looks more 'plain vanilla' I actually find it more straightforward to get easy things done (that is, pgadminIII doesn't look so flashy but I found it is much easier to get connected and get going than mysqlworkbench). YMMV of course, but if you are concerned about corporate control and the future of MySQL taking a look at Postgresql won't harm you - it is a nice(r) place to land if you have to.

Can someone please explain the sweet spot in which MySQL is a better option than both SQLite and PostgreSQL?It seems to me that SQLite is a much better option on the very low end, and by the time MySQL would be a better choice, PostgreSQL is an even better choice.

if they want to use open source database. Try Firebird SQL [firebirdsql.org] if you want to go light (lighter than mysql in most cases I've seen), or go with the big boys with PostgreSQL [postgresql.org].

Perhaps you should be looking at Sqlite [sqlite.org], which is a "a self-contained, serverless, zero-configuration, transactional SQL database engine" (as it says on their webpage).

You can run it interactively (or through a bash script or something) with the sqlite3 command line shell, or (most efficiently) hook it into your own programs and use it to do all kinds of clever SQL stuff directly within your program.

Oh yeah, it's also explicitly public domain, so you can use it for any purpose and in any application whatsoever.

Unfortunately, for most LAMP-stack applications that M is going to be MySQL or shit doesn't work. They're written to MySQL, and Postgres support is typically volunteer-maintained by one person. This sucks, yes.

>Java is free like in libre, the reference implementation is under the GPLv2: OpenJDK

Java is NOT free as in Libre. While OpenJDK is the "reference implementation" (which in Oracles terminology means "Blame the Apache Foundation") it does not and will never include the closed source goodies in Oracle Java.

In addition, Linux distros are no longer allowed to redistribute Oracle Java due to some bizarre Ellinsionian fiat. (probably they want you to use Snoracle Linux). So If you still need Oracle Java for whatever reason, you have to install it yourself. However despite Larry's best efforts, the Community (thanks!) has come through with scripts and native packages to install recent Oracle Java on any Linux distro you can think of.

Now, you can configure your version of postgresql to be on a different port than others, you can run it as any non-privileged user. You can configure it to be occupy a pretty small resource footprint or configure it to be huge. You can put a database on almost any file system.

I have to disagree. I remember reading the mailing lists when StarOffice was first open sourced. The developers of StarOffice had a very closed source approach to development, and outsiders were not encouraged whatsoever. You'll notice the leaps and bounds taken by LibreOffice - from all the way to improving the build tools to improving the code base.

For me, the one advantage MySQL (and MariaDB, and even Apache Derby!) have over PostgreSQL is that there are versions that can be run stand-alone "out of the box" as a non-root user. PostgreSQL (AFAIK) needs to be installed, and needs to be installed as root (and you need to create a postgres user, etc.).

There is a reason why no one bothers to make an XAMPP-style "portable" version of PostgrSQL, as they have with MySQL. The reason is that this is dead-simple to accomplish even with the out-of-the-box binaries available on the PostgreSQL site.

On the PostgreSQL download page [postgresql.org], you would download the "zip archive of the binaries", rather than the one-click installer. Unzip the archive's contents wherever you like (including on a USB thumb drive), and then refer to this 3-paragraph PostgreSQL article [postgresonline.com]. It tells you to create a BAT file in your base PostgreSQL directory, cut-n-pasting these contents:

The very first time you run this script, you comment-out the bold-face "REM" line... which will initialize a fresh PostgreSQL environment, with admin user "postgres" having a blank password. Then put the "REM" comment back on that line, and you have a complete portable PostgreSQL environment that can be moved from directory to directory and machine to machine.

This information is obviously Windows-centric... but the whole "portable" concept (in the USB thumb drive sense) is Windows-centric in the first place. If you're on Ubuntu, just "sudo apt-get postgres" and then remove it when you're done tinkering! By the way, you don't need administrator privileges to use the one-click installer on Windows.

A lot of the discussion that I'm seeing in this thread has more to do with phpMyAdmin vs. pgAdminIII than with MySQL vs. PostgreSQL themselves. To be perfectly frank, if one's biggest concern is what the admin or SQL workbench tool look like... then it doesn't really matter which of these two databases you use. You'll be fine either way.

The real consideration is whether you need (or would like to explore and learn about) the more "enterprise"-y features offered in PostgreSQL. If you're interested in more enterprise-level functionality, then PostgreSQL is by far the best free game in town. If you're not really interested in that stuff, then you might as well build around MySQL since it's more commonly offered by web hosts and cloud providers.

By "enterprise"-y, I'm talking about the concept of assuming that more than one application might eventually be using your database (and that the applications might be based on more than one language or technology stack). If you are only using your database through one application, and letting its ORM framework (Java JPA, Ruby Rails, PHP Doctrine, Python SQLAlchemy, etc) be responsible for enforcing all the persistence rules and business logic, then it doesn't make much difference to you as an application developer which database lies behind the framework.

However, let's say that you have a Rails web application writing to your database on the front-end, and a Java application working with it on the back-end. Maybe you even have some Python or Perl scripts kicked off by a nightly cron job, which build reports based on the data. To give a very trivial example, let's say that one of your table columns holds "customer type", and must be one of 7

I've run two big sites on PostgreSQL now, the first is a content management system for schools across the US with ~3M users with 1K to 3K active at the same time. 300G db on machines with 128G RAM, 34 15kSAS drives, HW RAID controllers and 48 AMD opteron cores. We ran a very aggressive autovacuum schedule, 8 threads with 1ms sleep and max work limit in the 5000 or higher range. We ran several other services on top of pgsql as well, a search engine, stats db, and session servers on it there. Each setup somewhat differently, all 24/7 and quite reliable. The main ~300G cms database was replicated by Slony, since it was setup some time ago when slony was the only real reliable etc replication engine at the time we started. Honestly, it was great performance wise, and reliability wise. Working around Slony for ddl updates is a gigantic pain in the ass tho. Things have gotten better.

The second site, in my current job is also 24/7 and on big hardware but is used as a core db for a storage system consisting of literally thousands of TB sized HDs. The actual db is only a fraction of our storage.

Once you teach your developers to be db centric (start with the data model and work upwards) and the little tricks of postgresql they usually are pretty happy with it. But if you don't have a pg specialist on staff the developers often hate using pgsql because it's NOT what they're used to.

An example is when I was teaching a new guy to use it and he was bitching at how poor insert performance was. He had a 10k file to import and it was about 10% imoprted after 10 minutes. I had him truncate the table and wrap the inserts in begin;commit; and the whole thing imported in about 10 seconds. This was back on pg 7.1 and php3.0 days. Pg has gotten much faster but the ratio of 10k transactions / individual inserts versus batching all 10k together is still quite large.

The fact that you can wrap anything except create / drop database / table or setval/nextval in a transaction and roll it back makes it amazing for development work. You can wrap some huge db updates in a transaction and if there's an error anywhere the whole thing rolls back and you can try again without cleaning up a half-updated db.

It's not perfect, but once you get a handle on performance tuning, autovacuum tuning, and replication it's pretty amazing both performance and reliability wise.