Re: [Windows] Feedback on PG?

Richard Huxton wrote:

>> Is the Windows port on par with the *nix version, and with those other
>> alternatives?

> The main Windows problems we see on the mailing lists all revolve around
> (1) installation and (2) anti-virus. PostgreSQL runs as a "unprivileged"
> user in unix terms, and given the complex permissions model on Windows
> and the wide variety of setups on machines that's not always proved easy
> to get right.

I suspect a lot of that comes down to user/admin knowledge as much as
anything. The installer/uninstaller needs to do a lot more hand-holding
than it presently does to handle users who've done things like changed
the data directory permissions, tried reinstalling under a different
user account, tried to run Pg under the account it's being installed
with, etc.

That said, there are also a few bugs lurking that only affect the
Windows version. The "cannot reattach to shared memory" issues come up
here periodically and don't seem to have any good solution. I wouldn't
be too shocked if this was a hook dll / spyware / AV issue at the root
of it, though.

I've used Pg on my laptop at various points when it's been running
Windows, and found it stable and reliable for my purposes (app dev and
testing).

Note that you can run Pg on a UNIX/Linux server and connect Windows
clients to it, too. This is a _very_ common way of using Pg, and works
flawlessly.

> The second problem is with anti-virus scanners locking the database
> files for a fraction of a second - that doesn't help the smooth running
> of any system. Once the scanner is told to ignore PG / switched off the
> problems go away, so it's easy enough to diagnose.

Some antivirus scanners must be fully uninstalled, not just told to
ignore Pg. There can be a few reasons for this:

- Buggy system call hooks or hook DLLs that cause some system calls
to behave in ways they're not meant to, often subtly. These hooks
are often not uninstalled when the AV tool is turned off, just set
to do nothing, but they often still cause problems.

Similarly, if the AV is told "ignore these files/processes" the
hooks still activate, they just choose to do nothing. If the bugs
affect the operation of the hook DLLs / system call replacements
even when they're not actively scanning, you'll still have issues.

( I even have a *webcam* driver that installs a buggy hook DLL
that breaks Pg, gcc, and some other software! It's not just AV. )

- Telling it to ignore `postgres.exe' etc may not prevent it from
scanning PostgreSQL's tempfiles, data files, etc.

- Telling it to ignore the data directory and postgres executables
may not be enough to stop it interfering with other parts of the
system that Pg interacts with.

In short: Virus scanners are *E*V*I*L*. I've seen relatively few issues
with recent versions of a few, but most seem to be way more trouble than
they're worth unless you do only very simple things on your machine.

>> Apart from the fact that, unlike MySQL, PostgreSQL doesn't
>> require buying a license when developping commercial applications, are
>> there technical reasons why I should choose PostgreSQL instead of
>> MySQL or Firebird?

In addition to the list already presented, Pg tends to hold up well when
faced with complex, difficult queries and under highly concurrent
read/write loads. It has transactional DDL, which is something you get
so used to relying on that you'll go quietly nuts trying to manage
changes on DBs without it. It has rather good access control, which
combined with good procedural language support makes it possible to do a
lot of your business logic work in the DB.

If you just want a dumb data store, PostgreSQL is probably overkill. It
does have costs in terms of the need to tune it for optimal performance,
preferably run it as a service, dump & reload during upgrades, etc. It's
not an invisible, admin-free database, though with some work you can
make it seem that way to your app users.

It's certainly not as simple as, say, SQLite, and it can be slower for
simple queries too.

On the other hand, Pg can enforce very complex data integrity rules,
handle big and complicated queries, and otherwise hide lots of
complexity from the application - if you choose to use its facilities
well instead of treating it as a dumb data store.

If you're the kind of developer who thinks triggers and transactions are
unnecessary frills, PostgreSQL is not for you. If you can't imagine
using a database without fully transactional operation and strong data
integrity enforcement, Pg is much more likely to be your sort of thing.

--
Craig Ringer