Slashdot Mirror

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, 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. It tells you to create a BAT file in your base PostgreSQL directory, cut-n-pasting these contents:

@ECHO ON
REM The script sets environment variables helpful for PostgreSQL
@SET PATH="%~dp0\bin";%PATH%
@SET PGDATA=%~dp0\data
@SET PGDATABASE=postgres
@SET PGUSER=postgres
@SET PGPORT=5439
@SET PGLOCALEDIR=%~dp0\share\locale
REM "%~dp0\bin\initdb" -U postgres -A trust
"%~dp0\bin\pg_ctl" -D "%~dp0/data" -l logfile start
ECHO "Click enter to stop"
pause
"%~dp0\bin\pg_ctl" -D "%~dp0/data" stop

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

Thanks to Joshua for the nice review here. There are actually a few more samples from the book than just the one chapter; here's a full list of them:

• Database Hardware - sample chapter PDF
• Reliable Controller Disk Setup - see also Reliable Writes
• Balacing Hardware Spending
• Server Configuration Tuning - see also Tuning Your PostgreSQL Server
• UNIX Monitoring Tools for PostgreSQL
• PostgreSQL Tips and Tricks

In addition to this one and the customer reviews at Amazon, there have been two other reviews by notable PostgreSQL contributors: Buy this book, now and PostgreSQL 9 High Performance Book Review.

As alluded to in the intro, the book tries to cover PostgreSQL versions from 8.1 though 9.0, with a long listing of what has changed between each version to help you figure out what material does and doesn't apply. So most of the advice applies even if you're running an older version. There is also a companion volume to this one of sorts also available, PostgreSQL 9 Admin Cookbook, that was written at the same time and coordinated such that there's little overlap between the two titles. That one focuses more on common day to day administration challenges, less on the theory.

Indeed. About 0.0005 seconds of Googling brought me this as the first link:

http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html

Though I think there are probably a grand total of 3 people on Earth who have used MS Access in any serious capacity and don't loathe it.

To provide some better references to what you've said here: the article PostgreSQL 8.4: Common Table Expressions (CTE)... covers this feature in PostgreSQL, and includes links to the documentation of other database products that support this feature to compare against. Same author also did Using recursive CTEs to represent tree structures on this topic.

To provide some better references to what you've said here: the article PostgreSQL 8.4: Common Table Expressions (CTE)... covers this feature in PostgreSQL, and includes links to the documentation of other database products that support this feature to compare against. Same author also did Using recursive CTEs to represent tree structures on this topic.

I don't see what this has over EXPLAIN and an appropriate graphical display tool like PgAdmin-III. There are large numbers of tools that display graphical query plans - and unlike this simple SQL parser, they know how the database will actually execute the query once the query optimiser is done with it.

Furthermore, a simple SQL parser has no idea about what indexes are present, available working memory for sorts and joins, etc. It can't know how the DB will really execute the query, without which it's hard to tell what performance issues may or may not arise.

See comment 24461217 for a more detailed explanation of why this whole idea makes very little sense.