PostgreSQL 8.1 Available

atani writes "PostgreSQL 8.1 has been posted, though not officially announced as of this moment. This release includes two-phased commits, improved SMP and overall performance, a new role system replaces the older user/group, autovacuum is now within the backend rather than a separate contrib module, and various improvements, performance enhancements, and bugfixes. " You can also read the developer notes for the popular database. One thing is clear- with the newest Postresql and MySql, you have much to choose from.

Comments

Congratulations to the PostgreSQL Team!

[mw]

I've tested RC1 with some applications, and was really impressed by the bitmap-indices. I hope to see soon a multi-master replication now that 2PC is available. I've heard Slony-2 will have it.

Re:Congratulations to the PostgreSQL Team!

[commanderfoxtrot]

Indeed- congratulations.

I'm using PostgreSQL for many things (custom app, Drupal, Apache logging, back-office) and it's great- this new release adds lots of useful bits.

Probably the most useful new feature for those who use it to run mini web sites will be the MAX() and MIN() functions now using indices- this has been a point of contention for some time.

XA will be useful for bigger stuff.

I'm also really pleased auto-vacuum is now part of the DB.

Now, Gentoo- let's get cracking!

Re:Congratulations to the PostgreSQL Team!

[Anonymous Coward]

PostgreSQL has multi master replication, take a look at PGCluster.

Re:Congratulations to the PostgreSQL Team!

[Anonymous Coward]

We've been using RC1 as well; and regarding mult-master, we've been using PGPool which acts as a 'lite' clustering solution that load balances reads but sends writes to each postgresql server in the cluster.

For real multi-master clustering, I think there are commercial options availabile; but yes, it'll be nice when it's included.

Re:Congratulations to the PostgreSQL Team!

[jadavis]

For real multi-master clustering, I think there are commercial options availabile; but yes, it'll be nice when it's included.

Replication may never be "included", in the sense that it's a part of the core distribution. There's really no reason to have it there, and several reasons not to:
(1) New releases of the replication software would have to wait until a new release of PostgreSQL.
(2) There are many completely different things that go under the heading "replication" that are used in different situations, depending on how often you expect the connections to be up, whether you're replicating for redundancy or speed, etc. Often, a real situation will require using multiple types of replication.
(3) It puts an extra burden on the developers to maintain every line of code in every piece of replication software as well as the rest of the database.

What users really want is to have another well-maintained, BSD licensed project that achieves things not possible with Slony-I, PgPool, etc.

Re:Congratulations to the PostgreSQL Team!

[thevoice99]

Multi Master PostgreSQL: http://pgcluster.projects.postgresql.org/

Question for Taco

[Anonymous Coward]

Would you consider changing?
Are you sticking with what you know or are you going to bite the bullet and switch?

Have you upgraded mysql yet? (i seem to recall thats what you use)

Re:Question for Taco

[toofast]

Here's what I think he'll answer to your first question:

"
MySQL works fine on Slashdot. It has all the features and performance we need, it has been running flawlessly for years and we're already familiar with it, so why should we change to anything else? What makes you think there's a bullet that needs biting? Granted, PG looks neat and all, but why exchange a dollar for four quarters?
"

Here's what I think he'll answer to your second question:

"
MySQL 5 doesn't offer us any features we absolutely need (otherwise we'd be using PG, right?) We will upgrade eventually, but we have bigger fish to fry right now, and upgrading our database is not very imperative.
"

Something else he might say:

"
Running slashdot is not as simple as running a basement website that gets 3 hits per hour. Thought needs to be put into these decisions. We can't just run off and install something the day it's released.
"

Re:Question for Taco

[Anonymous Coward]

In a nutshell:

sqlite3 = fastest (same reason many of us used mysql years ago)
mysql = most 3rd-party apps supports this (same reason many of us use Windows)
firebird = undeservedly underrated because license isn't BSD or GPL
postgresql = more modern features but at the price of some speed

So there you have it. I used to use mysql, now I use sqlite3 and postgresql.

- Captain Obvious

Too big?

[Anonymous Coward]

Always good with improvements but will there come a time when both PostgreSQL and MySQL are too big as a small-website database?

Re:Too big?

[jocknerd]

A small website should probably use http://www.sqlite.org/. It doesn't require administration or setup. A complete database is stored in a single file. And it implements most of SQL92. Probably more than MySQL but I'm not positive.

Re:Too big?

[uits]

(3) SQLite lets me insert a string into a database column of type integer!

This is a feature, not a bug. SQLite does not enforce data type constraints. Any data can be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column. Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)

But SQLite does use the declared type of a column as a hint that you prefer values in that format. So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is sometimes call type or column affinity.

Re:Too big?

[bad-badtz-maru]

Web sites generally have a high level of concurrency and, as such, SQLite is probably not an appropriate solution for the web. For a desktop application, however, it would definitely be a viable alternative to low-concurrency databases such as Access.

Re:Too big?

[Anonymous Coward]

From experience, I can tell you that there's nothing wrong with using SQLite for a whole website.

Just don't allow more than 5 concurrent connections.
Oh, and backup often.

Re:Too big?

[CyricZ]

Notice that he said small websites. We're talking perhaps 12 hits a minute, or one ever 5 seconds. Concurrency isn't that much of an issue, unless you're working with massive amounts of data (which then you probably shouldn't be using SQLite) or you're performing extremely complex manipulations and queries.

Most small web-based database applications, just pulling product prices and descriptions with an occasional update, will work just fine with SQLite.

Re:Too big?

[tezza]

The parent is a little disingenuous

sqlite is good. But it does require setup.

You still need to use CREATE TABLE, INSERTs and the like.

You need to connect to the thing in your code, with the same attendent problems as other connection strings. sqlite has a poor jdbc element, if java's your gig.

There are a couple of versions of sqlite. Be aware of this. There is sqlite and sqlite3. Note the jump there from 1 straight to 3. You'll need different DBD drivers there in Perl.

Also, there is no multiple write capability, so you have to synchrinise on this yourself. Who do you trust to write code for ACID commits? Yourself or say, the Postgres team?.

Where sqlite rocks is where the data is entirely read only, which eats out Mysqls lunch.

Also remember that sqlite and Postgres are not mutually exclusive.

In my sqlite setup, I deploy a sqlite database as a file for the fast read only webservers. Where is this data drawn from? My Postgres database with perl to generate the file. So you can get the UPDATE integrity of Postgres with the blazing speed and compactness of sqlite. This works where your application has discreet state where everything is consistent and you can batch update your sqlite.

Re:Too big?

[Anonymous Coward]

Also, there is no multiple write capability, so you have to synchrinise on this yourself. Who do you trust to write code for ACID commits? Yourself or say, the Postgres team?.

You don't _have_ to make your own concurrent-write implementation. SQLite is already ACID compliant; you'll just have to live with blocking writes, which is perfectly fine for most blogs/home pages and that kind of stuff. ;)

And for heavy load you'd always pick postgres over mysql. Bad news for poor mysql ab..

Re:Too big?

[brlewis]

For running a small web site, Apache is probably too sophisticated a server, and Linux/BSD/etc are too sophisticated as operating systems. However, if you already have a sophisticated OS, server and database, there's no reason not to use them.

Re:Too big?

[Zeut]

I don't understand how too big is really relevant. I assume you are thinking that increased features will reduce performance, however that is not the case with PostgreSQL. With each and every release, the performance improves considerably. If all you want to do is setup some simple tables that will work fine, but you will also have the ability to grown and expand with PostgreSQL.

Re:Too big?

[killjoe]

What would be great is a replication mechanism where you can replicate from a remote postgres server to a local embedded sqlite database.

Re:Too big?

[rtaylor]

What would be great is a replication mechanism where you can replicate from a remote postgres server to a local embedded sqlite database.

You might be able to coax Slony into doing these kinds of tricks if you can mark the SQLLite installation as write-only (write only for Slony).

Windows installer location?

[mgkimsal2]

I can't seem to find a Windows binary on the 'ftp browser' on their site. Am I missing something? Or is the Windows version of 8.1 not available yet?

Re:Windows installer location?

[X-ite]

Probably available in source... just not built yet.

Re:Windows installer location?

[mgkimsal2]

Good call - I did find it just a few minutes ago but I did neglect to mention that while I run windows at work, we run linux as the webservers (a move I've found increasingly common over the years), and I run linux at home (part time since late 98 and full time since 2001) and on external servers I manage - at home currently ubuntu (testing it out), normally mandrake/mandriva, and have run RHEL, fedora, debian, knoppix, suse, caldera, RH5/6/7/8/9 and slackware.

Whew! thanks for reminding me - I almost forgot to throw all that in!

nevermind - I'm just hard of reading this morning

[mgkimsal2]

Found it!

http://www.postgresql.org/ftp/binary/v8.1.0/win32/ is the link.

Clicking the '8.1' link on the 'new releases' section on the front page takes you to the 'source' files only. That was the problem I had!

DB wars?

[halleluja]

Lots of DB topics lately, anyone care to speculate?

Re:DB wars?

[brlewis]

anyone care to speculate?

I don't think so. Everyone will read the referenced article and carefully check facts before making any assertions.

Re:DB wars?

[indifferent children]

In case you don't know: you don't get positive karma from people modding your post 'Funny'.

Re:DB wars?

[halleluja]

I was more interested in the fact that there's a lot of movement in the DB area; MySQL/SCO, InnoDB, Oracle, MS Postgres, Ingres issues (w.r.t. frequency of DB associated posts lately).

Hopefully someone could shed some light and give an analysation of the current developments, without involving karma.

The inherit bug?

[kjetiln]

When are they going to fix the inherit bug? The one described on the bottom of this page:
http://www.postgresql.org/docs/8.0/interactive/ddl -inherit.html

It is to bad that objects does not work yet.

Re:The inherit bug?

[shakah]

Not flamebaiting, I swear, but...

do features like that really get used in the real world? I'd file it under "clever" (or maybe "too cute by half"), but I can't see myself opting for "table inheritance" over a standard SQL design.

Re:The inherit bug?

[Lexicon]

Here's an example of table inheritance's usefulness. Let's say we have a set of inventory items that share a common header. We would like to be able to easily search the common header set, for example for rfid tag sku's, then drill down to more specific attributes.

inventoryitems
      -- sku
      -- description
tables inherits inventoryitems
      -- size
      -- color
      -- top material
lamps inherits inventoryitems
      -- numbulbs
      -- bulbtype

Now we can do a select on inventoryitems to search all of the sub-tables for the scanned item at the point of sale system where we don't need the more specific information yet we want to be able to quickly search all the subitems without doing tons of selects (obviously this is useful for more item types than my example of two).

Re:The inherit bug?

[addbo]

Erm... why would you have a table for each type of inventory item? Unless you know you will only be selling tables, lamps, chairs... or some finite number of inventory types... wouldn't you have an inventory type table? and perhaps some othre tables to save the specific attributes of the lamps, tables etc.?... so a inventory type table linked to a inventory attributes table via an intermediate table (inventoryAttrType?)... and then maybe have an inventoryDetails table to capture the attribute values themselves...

so with 5 tables you get the flexibility of having as many different inventory types and get only the details you need... then again... if you only sell tables and lamps I guess you get to have 2 less tables... but I always like to design with flexibility in mind... and still can't really see a place where I would use inheritance versus normal database design...

Addbo

Re:The inherit bug?

[cayenne8]

"It is too bad that objects does not work yet"

"do features like that really get used in the real world?"

I'm with you on that question. These, well at least Postgres, are Relational Database Management Systems. They're not object databases...and IMHO, it isn't worth messing with them in that fashion. Set it up as a relational database, and let your middle tier deal with the object to relational conversions as needed if you use object oriented programming. If not...then, use an object oriented database...if you can find one that is up to par...which I've not run across yet.

Re:The inherit bug?

[shakah]

Lexicon's post (along with reading the 8.1 docs) did clear things up a bit for me. From his example it looks like inheritance lets you avoid the abstract InventoryAttrs key/value table, and probably makes some queries simpler.

That said, I still can't see using the feature, probably too stuck in my (old) ways for it.

Re:The inherit bug?

[rycamor]

For those who really understand relational database design, there is nothing that object inheritance can represent that you can't handle relationally.

In fact, the PostgreSQL team has been musing for years on whether to drop object inheritance completely, because it just muddies the waters of relational design, and causes implementation details to affect logical design.

But interestingly, table inheritance has become a solution to a lower-level implementation problem: "horizontal" table partitioning. Using inheritance together with tablespaces (available since 8.0), you can break a large table into multiple subtables, each on a different physical storage device, then make a table that inherits from them all to present unified querying to the whole range of data. This is a *big* advantage for very large databases.

Re:The inherit bug?

[Lexicon]

Your example explains the reason to use inheritance quite well; it is much simpler and easier to maintain than keeping track of many cross-referencing index tables and considerably easier to code for in middleware.

You're right that for generic items where you wouldn't need unique tables for custom attributes, your method would be much better. But whenever you need those tables anyway, why not inherit and gain the benefits of those cross reference tables without the complexity?

Re:The inherit bug?

[bigtrike]

You can use the crosstab features of the tablefunc contrib to get a fake table (can be used in joins iirc) from those key/value tables. It's probably not nearly as fast, but it is flexible

Re:The inherit bug?

[Notrace]

Bizgres seems to use it for an implementation of partitioning in Postgres.

Here is an example of how to use it.

Notrace

Re:The inherit bug?

[einhverfr]

This is IMO better done by relational design. Data types may vary according to your needs.

create table inventory_items (
sku text,
description text
);

create table table_attributes (
sku text REFERENCES inventory_items,
length int,
width int,
height int,
color text,
top_material text);

create table lamp_attributes (
sku text REFERENCES inventory_items,
numbulbs int,
bulbtype text
);

Now a better example might be if you often searched your entire sales history by lamp types or table types. In this case you might want to use inheritance to make your sales tables partitioned appropriately for a sufficiently large data set.

Now if I want to have the data model you required, I can also do:

CREATE VIEW "tables" AS
SELECT i.sku, i.description, table_attributes.* FROM inventory_items i INNER JOIN table_attributes USING (sku);

etc....

The inheritance index entry issue is something that may eventually need to be fixed but it is as yet so seldom the right tool for a job where this is an issue that it is a bit of a low priority issue at the moment.

Re:The inherit bug?

[einhverfr]

But interestingly, table inheritance has become a solution to a lower-level implementation problem: "horizontal" table partitioning. Using inheritance together with tablespaces (available since 8.0), you can break a large table into multiple subtables, each on a different physical storage device, then make a table that inherits from them all to present unified querying to the whole range of data. This is a *big* advantage for very large databases.

This is why I think that the index entry issue will eventually be addressed. Inheritance and other OR features were the main reasons why PostgreSQL was started. However, it has faded from the spotlight and is not a priority primarily because it is not commonly used aside from table partitioning.

Let me explain this bug for those who don't want to read the entire email thread:

Index entries are not inherited to child tables. Since indexes are used to enforce unique values of primary keys and referential integrity, it means that you have no cheap (performance-wise) way to get these guarantees, and the standard methods only reference tuples physically located in one table. So such table partitioning currently breaks the relational model. I think that sooner or later a real discussion is going to have to take place regarding how to fix this one area where PostgreSQL doesn't do a steller job of allowing you to easily maintain the integrity of your data (yes there are workarounds but these are not very efficient).

PostgreSQL 8.1 Release Notes

[dpage]

The URL for the release notes in the story should be: http://www.postgresql.org/docs/8.1/interactive/rel ease.html#RELEASE-8-1

Much to choose from?

[ltning]

I count two (2) things: PostgreSQL and MySQL.

Since when was two a crowd? ;)

But neither of the two have a good, cross-platform clustering- or multi-master replication solution, which makes things kinda difficult in our end. For MySQL I can use circular replication, but this is undocumented at best, and very error-prone in extreme situations. The clustering in MySQL doesn't count - in-memory storage limited to half of your physical mem - come on, that's ridicolous. And for Postgres, any multi-master solution there would (currently) dramatically reduce performance. Not the best thing either.

Oh well. Maybe by this time next year.

Re:Much to choose from?

[Anonymous Coward]

Well there is also the Firebird Superserver. It's a released under a Mozilla license (not affiliated with the mozilla project, of course.) (and a couple other licenses also).

http://firebird.sourceforge.net/

So that's three open source SQL database systems; MySQL, Firebird, and PostgreSQL.

Here is a comparision between aviable open source relational databases.
http://www.geocities.com/mailsoftware42/db/ (it's a bit dated)

Here is the migration guide for MS-SQL to Firebird, in which I doubt your interested.
http://firebird.sourceforge.net/manual/migration-m ssql.html

Re:Much to choose from?

[Anonymous Coward]

And Ingres, mentioned 4 articles down..

Re:Much to choose from?

[Anonymous Coward]

SAPDB, aka MaxDB. Here

Ingres. Here

Mysql Here

Postgresql Here

Firebird Here

Re:Much to choose from?

[namekuseijin]

there's also:
http://firebird.sourceforge.net/

Firebird is kind of a bastard step brother of PostgreSQL, since they share the common Ingres heritage... it's got foreign keys, triggers and stored procedures for far longer than MySQL, for instance. It's Borland Delphi's Interbase code gone open-source. However, it's a relative newcomer and most who care are Delphi developers.

Its license is neither BSD nor GNU, which i think also makes up for some caution people have against it.

Re:Much to choose from?

[killjoe]

Open ingres does have clustering and multi master replication. I suggest you check it out. Yes it's open source.

By the way clustering and multi master replication is a massive feature. You have to pay for SQL server enterprise edition ($16,000 per processor) before you get it with shared nothing architecture and even then it doesn't work all that great.

Re:Much to choose from?

[Anonymous Coward]

Pleeease, what "Ingres heritage" are you talking about? AFAIK, Firebird, better said Interbase, was built completely from scratch as a wholly new architecture at the time.

Re:Much to choose from?

[ltning]

Sure I have to pay for that when going for the commercial bigwigs. However, MySQL pretends to be enterprise-class these days, which makes their efforts look even more ridicolous. Hell, the NDB-cluster idea isn't so bad, if it wasn't for NDB... Why the heck couldn't they do some kind of disk backed storage instead/as an alternative??
Last I checked I couldn't even use swap for the purpose of extending the DB, not to mention that would be dog slow, very inefficient (no db-page alignment on reads/writes, no sorting, no indexing) and unbelieveably dirty...

Open ingres would be interesting if it worked on FreeBSD and had a working jdbc driver that works in 64-bit environments. Not to mention the database would have to manage a 64-bit environment, but it might, I haven't checked.

Nope, I'm not flaming :)

Re:Much to choose from?

[killjoe]

"Open ingres would be interesting if it worked on FreeBSD and had a working jdbc driver that works in 64-bit environments. Not to mention the database would have to manage a 64-bit environment, but it might, I haven't checked."

It's build on unix, at a minimum you could compile it on freebsd I would think.

You should check it out. If you can save 16,000 per processor it would be worth it.

By the way I should also point out that Microsoft SQL server does not run on freebsd either.

Re:Much to choose from?

[ltning]

Microsoft SQL server has never been an option, and will (hopefully) never be ;)

As mentioned in another thread, we use MySQL for now. It "works".

I'll check out Open Ingres either whenever it shows up in FreeBSD ports, or when I have time to play around with it in my spare time. Thanks for the tip.

Re:Much to choose from?

[einhverfr]

And for Postgres, any multi-master solution there would (currently) dramatically reduce performance. Not the best thing either.

Actually, I think that it is quite possible to design a performant multimaster system on PostgreSQL with Slony-I. It would not be administratively transparent and would require some administrative knowhow and effort to set up but it could be done (actually it is a set of n master/slave systems where n is the number of nodes). Insert-only is really easy in this case. Insert/update/delete is a little more complex and you would need to determine conflict-resolution policies and triggers on a case-by-case basis.

So it is work but hardly impossible. It might be fairly interesting to make a community project of automating the management of this process.

As for synchronous replication, I would suggest you look at pgPool and pgCluster. They are not yet perfect either but are coming along too. Unfortunately they are statement-level :-(

Re:MS SQL 2005?

[xtracto]

Leaving Trolling aside, it would be interesting to read a review of the lastest versions of the 3 SQL engines, with benchmarks and everything. Does anyone knows a site/magazine which still do those kind of performance tests?

Trollvoidance

[minginqunt]

One thing is clear- with the newest Postresql and MySql, you have much to choose from.

Worst attempt to stifle a fledgling Slashdot flamewar EVER.

Re:Trollvoidance

[mw]

Seems quite unrelated to my posting. Where's the quote from?

Re:Trollvoidance

[GreyWolf3000]

The quote is from TFSS (slashdot summary). Probably didn't hit the correct 'reply' button.

Just installed Win32 version

[mgkimsal2]

Just installed the Windows version. Just a quick couple notes here...

1. Installer was seemingly faster than 8.0 version.
2. Installer could do a little bit better job of hiding all the 'options' (ISBN, Fuzzy search, etc) you can install. Put them behind an 'advanced' button or something - it's a little intimidating to see so many options at first. Also the PL language choices are odd - 'pl/Perl' and 'pl/Perl (untrusted)' ??? These are things that could probably be hidden from the majority of people just testing it out for the first time - either install everything by default, or nothing, but put some of these things behind 'advanced' tabs.
3. pgAdmin III bundled tool is 1.4 - I think I was using 1.3 last time I installed. Visually it looks a bit nicer - I'm assuming they've fixed some bugs or something similar to warrant a number change. :)

I'll probably get flamed for #2, but I'm just putting out some suggestions. The fact that there *is* a Windows installer at all is a good thing - I'd just like to see it improve to help reach a wider audience for future releases.

Re:Just installed Win32 version

[dpage]

pgAdmin III bundled tool is 1.4 - I think I was using 1.3 last time I installed. Visually it looks a bit nicer - I'm assuming they've fixed some bugs or something similar to warrant a number change. :)

1.3 was the development series for 1.4, the new stable release as of yesterday. There are a heap of new features over 1.2 (which shipped with PostgreSQL 8.0) - see http://www.postgresql.org/about/news.419 for details.

Re:Just installed Win32 version

[bigHairyDog]

That's the PostGreSQL philosophy - don't hide the complexity. MySQL did that, and was rewarded by popularity and a generation of people who didn't understand database administration. PostGreSQL makes you pay attention to these options, and educates you in the process.

Re:Just installed Win32 version

[Anonymous Coward]

Windows users don't like to be educated though.

Re:Just installed Win32 version

[moz25]

I'll probably get flamed for #2

That's for sure, you JERK!!!11!!1111111111!!!

Seriously though, I see it as positive that high quality open source projects are truly cross-platform and also include windows. Even if one is entirely anti-MS, it is still beneficial for that person's goal that there is a very easy migration path: move from OS software running on windows to the same OS software on linux/mac/etc.

Re:Just installed Win32 version

[electroniceric]

No flames from me. I happen to agree with you, especially since Postgres is now well-position to be desktop database engine of choice, thanks to its small footprint, unrestrictive license, and ease of manageability. So a simplified installer and some wizards (make a user), would make it a great database to get started doing Access-like things.

Re:Just installed Win32 version

[archen]

I'm not sure that Postgres is gaining much then. When I install on FreeBSD I get about 8 checkboxes for both MySQL and Postgres. In postgres I just check the box that says "hemidal kerberos" and the installer does the rest for me. I highly doubt Gentoo,Redhat,Debian/whatever are more complicated.

Making the installation complex isn't really helping anyone, that should be left to good documentation for those who need it - while everyone else gets reasonable defaults.

Re:Just installed Win32 version

[electroniceric]

I don't think I quite agree with that. You don't have to understand all the stuff that goes on with indexing, query planning, system tables, etc, to get going with Postgres. But when you need it, it's open, extensible and documented. I say that because I think Postgres is doing an increasingly good job making the software work for people of various levels of expertise.

Re:Just installed Win32 version

[Anonymous Coward]

What utter bullshit. You know why people in tech companies use a product (and I'm using product is a general for an "IT thingumy") be it an app, a router or a PC?

THEY USE IT BECAUSE IT WAS THE FIRST THING THEY GOT WORKING... BECAUSE THEY ARE UNDER CONSTANT TIME PRESSURE. They usually don't have time to weigh up the finer points of each product. The one that they get working first is very likely to be the one that gets used.

Make setting up software easy for a role easy. It doesn't have to be perfect at first. Fine-tuning comes later once you know it better. If you make people read 200 pages of documentation just to get something working, then you are fucked and no-one but a few zealots will use your software. MySQL... despite it's manifest shittiness... used to do this. Install... connect... works. Boom... there's your "solution".

You know why Microsoft often wins with its dev tools? Because they understand this and follow it.

Re:Just installed Win32 version

[cayenne8]

"...don't hide the complexity. MySQL did that, and was rewarded by popularity and a generation of people who didn't understand database administration."

Nor understand relational database design...kind of like the nightmare ms access has given many of us. Geez...I'd often wished access had been banned from the desktop. I've had to inherit and straighten out so many access "databases" that some PHB started out messing with...usually with only 1 or 2 tables kludged together, that was passed around the office and became a standard tool....that became too unwieldy.

Then...they come to me, "Hey, lets put this in Oracle...it will make it faster". Yeah...you have to basically do the whole data model from scratch...learning the business rules and helping them define them...and THEN...try to massage the data to fit and run in the new model.

Oh well...is frustrating, but, does keep the paychecks coming in.

But yes...anything that makes creators of db's have to learn a little before they use a rdbms, sure saves headaches later on down the line...

Re:Just installed Win32 version

[Anonymous Coward]

Aah! So MySQL is Gnome and PostgreSQL is KDE? :P

Re:Just installed Win32 version

[johnnyb]

Access was the most useless tool. Any job that it was appropriately sized for was easier to do in Excel, and anything which was too large for managing in Excel handily exceeded the knowledge and design skill of those using Access, and usually quickly outpaced access itself.

Re:Just installed Win32 version

[LDoggg_]

THEY USE IT BECAUSE IT WAS THE FIRST THING THEY GOT WORKING...

I think there is some merit to this statment.

I'd love to see postgreSQL added to the xampp package.

The nice thing about xampp is that you simply unzip it into a directory and suddenly you have a huge technology stack of apache, mysql, perl, php all ready to go for stand alone development.

I'm betting there are plenty of windows only developers that wouldn't mind giving either database a shot if they understood how easy it was to get started.

Re:Just installed Win32 version

[Anonymous Coward]

So it doesn't matter what they "just got working" was an utter pile of drivel and will ultimately cause the downfall of the company...?

Your boss: We need a way to share the server passwords with all the other sys admins
You: Ok. I've put them on our website so everyone can view them. Took 5 seconds.
Your boss: Great. Good work.

Of course, here the better approach would have been to think about the problem, rather than just charging ahead and doing something mind-blowingly stupid.

Re:Just installed Win32 version

[GCP]

...makes you pay attention to these options...

Making the user pay attention to irrelevant options is extremely poor usability design, not some sort of badge of honor. From what I hear, Postres is outstanding in terms of technical functionality. To have a product like that crippled by such poor usability design (if it is as you describe) would be a real waste of its potential.

Re:Just installed Win32 version

[Anonymous Coward]

So it doesn't matter what they "just got working" was an utter pile of drivel and will ultimately cause the downfall of the company...?

No... it doesn't matter. And no, clubie, one IT decision, out of the thousands made every day, is rarely in a position to "cause the downfall of the company"... especially when it's a decision given to your average IT guy to make.

Of course, here the better approach would have been to think about the problem, rather than just charging ahead and doing something mind-blowingly stupid.

Of course, Mr. Patronising, everyone would like to "think about the problem"... but then not everyone works in research departments where you have two years to play with problems and then come up with an experimental prototype that will never be used in production but only ever be the subject of a conference paper. Was there something complicated about the original post "£XTREME TIME PRESSURE." You get something to work quickly, and it has a much better chance of being used. So make your software easy to set up and get working, and not have to read pages of documentation and make lots of unnecessary choices up front-- only a stupid clueless fuckhead would disagree with this.

Re:Just installed Win32 version

[Anonymous Coward]

What utter horseshit. Access is a front-end application to a database engine. That engine can be JET (the most common), MSSQL, Oracle, Postgresql, MySql... anything at all. The fact that you don't know this shows me that Access itself has "handily exceeded your design skill."

Access is actually quite a capable database front-end... even if visual basic is a disgrace. Incidentally, JET (the database engine MS used for a long time), was a great deal faster and more fully featured than MySQL... despite the criticism heaped on it.

Re:Just installed Win32 version

[johnnyb]

"What utter horseshit. Access is a front-end application to a database engine."

Delphi is exponentially better in this regard. Paradox managed to be good at both.

"Incidentally, JET (the database engine MS used for a long time), was a great deal faster and more fully featured than MySQL... despite the criticism heaped on it."

I never liked MySQL, either.

Re:Just installed Win32 version

[Anonymous Coward]

Delphi is exponentially better in this regard.

Nonsense. Delphi is a nice language for apps... but it's obvious that you never used it for databases. I did, and as much as I preferred Delphi pascal over VB, Delphi itself was nowhere near as good for developing database applications. For one thing, Access came with a very capable report generator. The one supplied with Delphi was dire and needed third party ones to make it work well. Whereas Access was designed around building databases, Delphi was a general purpose environment... and it showed.

Paradox managed to be good at both.

Paradox was shit... no other word for it. Its database engine was crap, unreliable, broken and lacked most of the features you would normally associate with an RDBMS -- certainly compared to JET.

Re:Just installed Win32 version

[einhverfr]

The GP was almost entirely full of crap. PostgreSQL does hide the complexity of a great many things. It is not the PostgreSQL philosophy to hide complexity. PostgreSQL is, however, very transparent and allows you to see as much of the complexity as you need.

Now, it is true that the Windows installer project is still fairly new and I am sure it could use some improvement (however, since I don't run Windows I wouldn't know). So it is better credited to the lack of maturity of the Windows port than to the PostgreSQL philosophy....

Not announced

[Anonymous Coward]

PostgreSQL 8.1 has been posted, though not officially announced as of this moment.

They were probably waiting for their mirrors to finish syncing before some yo-yo posted to /. and caused every other yo-yo to start downloading.

Re:Not announced

[StrawberryFrog]

They were probably waiting for their mirrors to finish syncing before some yo-yo posted to /. and caused every other yo-yo to start downloading. ..not to mention the /. editor in the middle that put it on the main page before the anouncement, yo.

Re:Not announced

[atani]

By the time this story posted to /. it had been announced, as I figured it would be since the www.postgresql.org home page was linking to 8.1.0 as their Latest Releases and all that was lacking was the article text for the release.

You assume they were "probably waiting" while I assume that if their linking to the release from their homepage, maybe they've put that link their because they're ready for the downloads — or as some would postulate: ready for the publicity.

RC1/RC2

[PhYrE2k2]

Been using RC1/RC2 for some time now. I'm impressed with the role feature, although it won't add much to the average user. The speed has been night and day with 7.3. I also haven't had any problems with the RC's in the slightest except one: a renamed table then wouldn't let me delete the sequence it depended on after the renamed table was deleted. Apparently I wasn't the only one who found it and it has been reported as fixed, though who knows.

My only beef with PgSQL has been there since before the 7's. There is still no way to not show the list of databases to users who have no right or access to those databases. Why should userA with rights to databaseA see that there is a databaseB or databaseC? This really seems like a simple feature, yet nobody will accept it into the release.

-M

Re:RC1/RC2

[kannibal_klown]

I thought that was common within Oracle? I take it Postgres "databases" are similar to Oracle "schemas." I'm pretty sure in Oracle even Joe Sixpack can see a list of all of the schemas but can't see what's inside. Though perhaps Oracle has a way of hiding a set of schemas from Joe.

I don't see what the big deal is. Then again I'm not a big Postgres user so I don't know if there's more to this than I'm realizing.

Re:RC1/RC2

[PhYrE2k2]

it seems like a simple check, in the same way when it checks for permissions, when doing a list of databases. Seems to make sense.

In a shared database server, it can be important. Although it in a way is security-through-obscurity, many would rather not have their database name 'companyfinances' visible to those with no access. Additionally, on a shared database services, you don't want your customers to know if there are 20 or 200 databases on that server (the number means nothing depending on the size anyway, but looks bad).

-M

Re:RC1/RC2

[chriskl]

No, it's mind-numbingly complicated. If it was easy, it'd have been done by now.

Chris

Re:RC1/RC2

[electroniceric]

This is a good point, and something I was always sort bothered by. However, all the shared MySQL installations I've ever used have this same setup...

Re:RC1/RC2

[shakah]

I take it Postgres "databases" are similar to Oracle "schemas."

FWIW, Postgres has schemas. Quite similar to Oracle in that regard.

Re:RC1/RC2

[Anonymous Coward]

Why should userA with rights to databaseA see that there is a databaseB or databaseC? This really seems like a simple feature, yet nobody will accept it into the release.

If I understand the talk on the postgresql archvies, you have the SQL Standard to thank for that when they defined how the INFORMATION_SCHEMA works. For almost everything the PostgreSQL team defers to the standard for questions of what will be accepted or not. If you can get the SQL-201X standard to support alternative definitions for the INFORMATION_SCHEMA views, I'm sure the postgresql community will accept your changes.

Re:RC1/RC2

[jBabel]

select * from pg_database is not good enough for you?

Re:RC1/RC2

[LordHunter317]

My only beef with PgSQL has been there since before the 7's. There is still no way to not show the list of databases to users who have no right or access to those databases.
Because, unless you configure pg_hba.conf to forbid them from authenticating to that database, all users have the privilege to connect to any database they wish. They may not be able to do anything, but they can connect. IOW, what databases you can connect to are handled by the connection authentication mechanisms, and the GRANT system has no control over it.
When you think about it, this really makes a ton of sense.

Re:RC1/RC2

[PhYrE2k2]

For example, a 'sameuser' pg_hba allows connections to a database with the same name. Similarly the grant situation you describe.

Why should I be able to connect to databases I have no right to be in? If I can't do anything in them, what benefit is there for me to be able to see hundreds of database names cluttering up my list of databases?

I understand the reason why it doesn't work, but isn't a part of being a developer making things work rather than just accepting that they dont? For a corporate database it's okay, but as the number of databases, users, and distinct entities use the same postgreSQL server, it makes little sense to scroll through a few hundred databases.

-M

Re:RC1/RC2

[PhYrE2k2]

MySQL has an option to hide databases that the user doesn't have rights to.

The solution is simply making the database list a view, and having that view join on a grant table of some kind.

-M

Re:RC1/RC2

[WuphonsReach]

MSSQL also allows you to see the existence of other databases on a shared db server, but you can't look inside them.

A good hosting company should use semi-random names for the user databases rather then using a more identifiable name. The downside is that it relies keeping track of those names elsewhere (in yet another database!).

at last "SELECT ... FOR UPDATE NOWAIT"

[Anonymous Coward]

I always wondered how one could create a multiuser database frontend reliably without this statement:

SELECT ... FOR UPDATE NOWAIT;

If you do

SELECT ... FOR UPDATE;

you either get exclusive access OR you block waiting for the person editing a record to finish. Obviouslt blocking isn't good in a frontend. Hacks like timeout=100, because this can trigger just because the database is being used heavily at that moment, and at best is an ugly work around.

Now with the NOWAIT option you can return instantly and say "Someone else has the row locked", and give control back to the user.

Nice feature to have, but people have been using postgres (and other databases) sucessfully for years. How did they work around it?

Re:at last "SELECT ... FOR UPDATE NOWAIT"

[Daniel_Staal]

Most handle it by giving you the view of the data before the other user started to modify it. The person who is editing's edits do not show until all of the edit is finished.

Postgres docs actually have a chapter on this: Concurrency Control. Like most high-end databases Postgres can handle this situation in different ways, depending on how it is set up...

A database should never tell you that the data is not avalible. It should always give you the best version of the data it has.

Re:at last "SELECT ... FOR UPDATE NOWAIT"

[Anonymous Coward]

Sorry, I can't have been clear enough when I asked that.

I wasn't asking about selecting a row, I was talking about selecting a row FOR UPDATE (I.E. obtaining an exlusive lock on the row, prior to doing an update on the row), not just getting a copy of the data. When just plain selecting a row, the database should indeed return that last committed version of a row. However when doing select for update I expect that it is currently only open for me to edit.

My question was how to deal with this in a fronend to a database. If user1 does "select * from blah where id=1 for update;" he gets an exclusive lock on the row.

If user2 then does the same "select for update" before user1 calls commit (which is quite likely with a frontend, as the row is locked while user1 manually updates the data),
then user2 will block while waiting for an exclusive lock on the same row. With the new NOWAIT extension, user2's frontend knows to say "Data is currently being accessed by user1, come back later", and user2 can get on with another task and do that.

So back to my question, how do existing frontends get around that limitation prior to this very useful feature?

Re:at last "SELECT ... FOR UPDATE NOWAIT"

[Anonymous Coward]

"So back to my question, how do existing frontends get around that limitation prior to this very useful feature?"

By attempting a read-only select with an exclusive lock first, and then, after obtaining the read-only lock, upgrading it to a read-write selection.

Re:at last "SELECT ... FOR UPDATE NOWAIT"

[rtaylor]

You used to do it using a short statement timeout.

set statement_timeout = 200; -- 200ms
SELECT * FROM UPDATE; -- Waits at most 200ms

Catch the exception indicating the query was cancelled (row locked) or continue.

Not the greatest but it has worked well enough for me with a few hundred automated processes pulling things out of a queue several times per second.

Re:at last "SELECT ... FOR UPDATE NOWAIT"

[gwicks]

Personally, I use the old "Oracle" trick... only "lock" if you have to.

You remember the contents of the record when you originally "selected" the record (without any FOR UPDATE clause) and in the update statement, you compare the current contents of the record with the contents the original contents.  If the record has changed then you'll get "0 records updated" and you know that "eh - that didn't work!" and you can then go into an exception routine.

The idea is that you will SELECT many times (so don't lock) and only UPDATE and small % of the records - and a smaller % will be in contention.  So only do work when you have to.

e.g. (sorry about the Oracle syntax - but I'm sure you can convert for your purposes)

SELECT custname, custadd1
INTO :p_oldname, :p_oldadd1
FROM customer
WHERE custid = :p_custid;

(1 row selected)

UPDATE customer SET
custname = :p_newname,
custadd1 = :p_newadd1
WHERE custid = :p_custid
AND custname = :p_oldname
AND custadd1 = :p_oldadd1

IF SQL%ROWCOUNT = 0 THEN
  ...error code...
END IF

Yes - it involves extra typing - but if you want it done right - then do it right first time!!!

Re:at last "SELECT ... FOR UPDATE NOWAIT"

[daBass]

Sybase (and SQL server) have a special "timestamp" column to compare against, which is easier. One to remember if you ever move from Oracle to one of these.

Re:at last "SELECT ... FOR UPDATE NOWAIT"

[LordHunter317]

Nice feature to have, but people have been using postgres (and other databases) sucessfully for years. How did they work around it?

You either move the blocking operation elsewhere (i.e., a trigger) or you don't. Data consistency is more important than performance (normally). The user can wait.

Re:at last "SELECT ... FOR UPDATE NOWAIT"

[slamb]

So back to my question, how do existing frontends get around that limitation prior to this very useful feature?

If I understand your question, you're talking about data entry applications doing a "select ... for update" before making changes, waiting for the user to make them, then doing the update and commit. You want to know how applications avoided the "Modify record" page hang until another user was done prior to NOWAIT.

This scheme actually isn't that common. You don't want to block read-only operations on user input, and this would do that. Besides, in a web application, you don't even want to keep a database connection between hits. (You have no way of knowing if the user has closed the browser and isn't coming back. Keeping a connection open for an hour just in case is undesirable, to say the least.)

One way would be to hold a modification lock in an application table or in-memory on the application side. But the most common scheme is called "optimistic locking", which is sort of a misnomer. There's no lock. It works like this:

When you present the modification page to the user, do a select with all of the columns. Keep two copies - the user's modified version and the original. When making the changes, execute an UPDATE like this:

update table set col1 = newval1, col2 = newval2 where id = id and col1 = oldval1 and col2 = oldval2;

If this statement returns a row count of 1, you know that no other changes have been made in this time and you can safely go on. If it returns a row count of 0, the record is missing or changed. Send a "Sorry, another user beat you to it" page back to the user. If you want to get fancy, you can show a three-way diff sort of thing to make their changes anyway.

In the vast majority of cases, this is totally transparent. Only if two people happen to be updating the same columns of the same record in the same table at the same time do you ever get an error. It's almost as simple as completely ignoring concurrency, and it has much better behavior - the user's change being rejected while they still remember what it was, rather than someone's change being silently clobbered.

Firebird already does all of this

[Anonymous Coward]

Seriously, Firebird already does all of these "new" features that have been added to this... How come it doesn't get the same press attention? Two-Phase commits? Come on, this has been around for over a decade in the major databases...

Re:Firebird already does all of this

[fok]

Firebird lacks the scalability of PostgreSQL.

Re:Firebird already does all of this

[namekuseijin]

It also lacks decent documentation. PostgreSQL has got one of the best out there, like most BSD documentation. And the plain C API is just as sane and smooth.

Then, i go to the Firebird site and search for documentation and it's not easy to even have a glance at what a Firebird stored procedure looks like.

Re:Firebird already does all of this

[davegaramond]

I feel for the Firebird community, they probably do need more recognition and press. But then, maybe if they could release stuffs more often, they would get mentioned more often too.

It's hard to get more attention if your "competitors" are of PostgreSQL caliber. Postgres do major releases every 6-12 months and is getting better all the time at faster rate. It's getting picked up by companies and has the strongest open source support around it.

Meanwhile, when will FB 2.0-final be released? When will I be getting index longer than 240 (or was it in the order of 500) bytes? When will the documentation be half-decent?

I consider my decision to go with Postgres over Firebird several years ago was the best decision I've ever made.

Re:Firebird already does all of this

[Anonymous Coward]

Firebird has a LOT of Documentation, since it is based on Interbase 6.0, all the docs of Interbase still applies to Firebird.

If you get Interbase Docs + Release notes for every version of Firebird you have a complete set of docs.

Firebird/Interbase has a number of features for long time than it's competitors (even invented it !)

The release cycles are not short ? I agree with you, but it's stable enough to not need to release often ! ;-)

see you !

Re:Firebird already does all of this

[Anonymous Coward]

I have a 1000+ page book on my desk that covers EVERYTHING I would ever need to know about Firebird. So as for docs, go to your local bookstore and pick up a copy of Helen Borrie's 'The Firebird Book'. Its all there. And as for releases, this isn't MS Word that needs a release every 5 mins to meet some business income model. Or some software riddled with bugs that needs constant updating. Firebird is one of the most stable, predictable and powerful DBMS engines out there. For that, I don't want it changing. After all a database is a database. We get suckered into the idea that we need regular releases of software, mainly from the commercial world where its in their business interest to do that. It doesn't mean that it needs to be done. When you have a database that has outclassed PostgreSQL & MySQL for years, why change? Vlad

Re:Firebird already does all of this

[k31dar]

It's hard to get more attention if your "competitors" are of PostgreSQL caliber.

LOL

Postgres do major releases every 6-12 months and is getting better all the time at faster rate.

Firebird has, as I understand it, gone through major internal changes over the last several years, this has all been geared around making the internal code more flexible, easier to maintain and easier to code.

With this in mind, I'm pretty sure that the features people have been requesting will be easier to add post v2 release.

A new major release every 6 - 12 months is not always a good thing, working from a business side you need long term stability. What business is prepared to upgrade their product line that often, with all the risks and expense involved. To pick M$ as an example, do you think that waiting several years between SQL Server 2000 and SQL Server 2005 [approximately 5 years :-)] is going to damage their product offering?

Personally I feel that Firebird is holding its own, it has released several versions over the last five years, its user base *is* growing, this is evident by the number of people joining the communinty, so what that it isn't on version 18.2.3.123, it offers a lot of features which people want, coupled with high reliability, availability and extremly low overhead.

Re:Firebird already does all of this

[einhverfr]

Having programmed against both PostgreSQL and Firebird, I found a number of Firebird's limitations well frustrating.

The most serious was not type for storing long text strings. Something like a CLOB or TEXT field. As you can get is VARCHAR(n) where you *must* specify a length. This was the case as of 1.0 but I have not checked out 1.5 yet. For many applications this is a real showstopper.

Also, I have found the documentation fairly hard to work through.

But I had to admit that up until very recently, Firebird had more advanced features in some areas than PostgreSQL.

However....

Between the extensible language system and the rich amount of native data types, I have tended to find PostgreSQL to be the most usable of the FOSS RDBMS's.

Re:MS SQL 2005?

[Anonymous Coward]

The "big 3" DBMS vendors all include clauses in their license agreements which preclude head-to-head identified benchmarks and comparisons. You can run benchmarks and call them "System A, B, and C", but you cannot tell which is which.

Re:MS SQL 2005?

[BarryNorton]

why didn't you mention MSSQL 2005 [...] You shooldn't forget about one of slashdot's main sponsor, Microsoft

You get your knickers so far into a knot because a major version update of a Microsoft product gets a story here that you don't even notice the irony of still bitching about it when a minor version update of the alternative is posted!

Thanks

[mgkimsal2]

Thanks for the explanation link, and thanks for the work you've put in to pgAdmin.

It's pretty sweet

[Mark Round]

I've been running my Blastwave packages[1] of the betas and RCs since the first snapshots became available, and I've been massively impressed with this release. Moving autovacuum into the main package is a really nice touch - all you have to do now is uncomment a few lines in postgresql.conf and it handles it all for you.

The new roles system is also amazingly useful. You can set up a range of roles with a variety of permissions, and then let users "assume" those roles. So you can log in with a day-to-day account, and when you need to do some admin work just SET ROLE [name of your super-user role] and then revert back once you're done. Great if you want to give a junior DBA the ability to create databases, but not the ability to modify other things (such as creating new roles).

Congratulations to the PostgreSQL team anyway - for doing things "the right way" :)

[1]=http://www.blastwave.org/testing/

Re:It's pretty sweet

[Zeut]

Moving autovacuum into the backend not only makes it easier to use, it makes it much more effective. It no longer loses activity data (the info it needs to know when to vaccum) on database restart. Also you can now set vacuum and analyze thresholds as well as vacuum delays settings on a per table basis. This was one of the major problems people had with the contrib autovacuum.

Indeed, SQLite is an excellent choice.

[CyricZ]

Indeed, SQLite is an excellent choice for small web sites.

I recently set up a small site for a local business. Basically they wanted to be able to upload and edit product information, in addition to other such tasks.

Using a combination of OpenBSD, Python and SQLite, it was very easy to get a system set up for them. Best of all for them, it turned an otherwise obsolete computer into a working server.

Making backups of the database is quite easy too, considering a single file (or three, in their case) can be copied and archived.

While I did not benchmark the system, there is a good chance that the SQLite solution is far more efficient than that of MySQL, and most likely moreso than PostgreSQL. But then again, SQLite doesn't offer many of the more advanced features of larger database systems. If you don't need those features, you might as well trade them off for faster execution and reduced RAM consumption.

Re:Indeed, SQLite is an excellent choice.

[Anonymous Coward]

> If you don't need those features, you might as well trade them
> off for faster execution and reduced RAM consumption.

It's really not entirely that simple; faster execution is a toss-up, since sqlite has routines that probably need to be loaded per-page (if you're using CGI), whereas a running database is ready to execute queries immediately.

And the biggest RAM consumption factor in any DBMS is the cache; this is generally tunable in any case, and having things cached (strategically, ie. a cache of the most used key indices, rather than just a less intelligent disk cache) leads back to a) faster execution time.

I actually like sqlite, but I think the biggest advantage is just the flexibility it affords you (easy to set up, don't need access to an existing DBMS). There are probably cases where it outperforms the heavyweights (I have not done benchmarks either), and I greatly admire the compactness of the sqlite design, but I doubt it is as simple as to say that sqlite is, in every case, faster/lighter. At a minimum, I doubt that it scales nearly as well.

Also, alot of DBA's would cringe at someone calling enforced type constraints an "advanced feature"..

Super!

[tcopeland]

Time to upgrade my Jabber server from PostgreSQL 8.0.4. And with RubyForge getting up past 3.5M records now, performance improvements are good news...

Re:Super!

[Anonymous Coward]

Hi, I'm a ruby and rubyonrails coder. I find his comment useful. Let me point to another set of links then which is useful with postgresql. http://www.rubyonrails.org/ http://www.ruby-lang.org/ and yes http://www.rubyforge.org/ is useful :)

Re:Super!

[lorcha]

And with RubyForge getting up past 3.5M records now

Looks like you're in for some fun, then...

E.1.2. Migration to version 8.1

A dump/restore using pg_dump is required for those wishing to migrate data from any previous release.

Re:Super!

[tcopeland]

Nah, no big deal, a pg_dump of that only takes about a minute, even with the database processing queries. Not sure how long a pg_restore will take; probably not much longer since I'll turn off Apache for that.

Re:Super!

[jadavis]

Not only that, if you really wanted to you could use Slony-I to do a fast switchover after the data has already caught up on the slave.

Re:MS SQL 2005?

[mha220]

PostgreSQL 8.1 is a major version upgrade. The first two digits are major versions, the third one is minor. Sure, not as major as MS SQL 2005, but not as late either...

MySQL vs. PostgreSQL

[ChrisF79]

I run a very small website as a hobby and I've just always used MySQL because that's what my PHP book featured. I hear quite a bit about mysql in forums on PHP that I go to, and on slashdot itself so I'm asking you... how popular is Postgre? If I used forum posts as any proxy, it would look like MySQL is dominant. Is that really the case?

Re:MySQL vs. PostgreSQL

[boinger]

You're free to research that yourself...
But you're not going to find much looking for "Postgre". It's PostgreSQL or "Postgres" for short.
Just FYI.

Re:MySQL vs. PostgreSQL

[jadavis]

All indicators seem to point to MySQL having more installed systems.

However, if you look at the traffic on the PostgreSQL mailing lists and #postgresql on irc, I'm sure you'll find plenty of community members.

I would say that both databases are well beyond the "critical mass" of informed users to sufficiently support other users.

Re:MySQL vs. PostgreSQL

[elp]

If I used forum posts as any proxy, it would look like MySQL is dominant.

In the webhosting world that is the understatement of the year. In my business I have a little over 1000 mysql clients and all of 3 postgresql clients. I've been advertising Postgres along side mysql for 2 years now. Its not worth the space or staff training costs.

I think the problem was that postgres was always much more complicated to set up and it used to have some pretty serious performance problems in the real world. It was also much harder for a beginner to learn with, error messages like: syntax error near "'" on a 20 line create statement dont help either.

I think too many people started on mysql and stayed with it as their skill improved, to the point where postgres has become an also-run, no matter what impression slashdot might give you. As for the postgres features, looking through some of my customers databases and code it looks as though a lot of them have never heard of indexes much less normalised data, stored procedures, views etc. SELECT DISTINCTS on 30000 row tables... <shudder>

I would love to hear from other people with some real world usage stats.

Re:MySQL vs. PostgreSQL

[Anonymous Coward]

Why are you looking in your client's databases?

Re:MySQL vs. PostgreSQL

[jd]

For a website, MySQL is probably the superior option. There is (almost) always a tradeoff between functionality and performance, so it is (usually) wisest to go for the option that is closest to what you want, as that will perform the best.

If the site is very small and the database code is not particularly advanced, you might even do better with SQLite. However, this does require using PHP 5. If you're on PHP 4 or earlier, that's not an option.

Now, this changes dramatically if you need very advanced database features. Postgres is certainly far more advanced than MySQL (although the gap is not as great as it once was). It can also scale better, but for a small site that probably won't matter so much. A website that allows you to manipulate geographical data could be small/hobbyist but would still require an awesome database to drive it.

It all depends on what you're doing.

Re:MySQL vs. PostgreSQL

[ouzel]

PostgreSQL has much better support for spatial features than MySQL, via PostGIS, which allows the PostgreSQL server to be used as a backend spatial database for geographic information systems (GIS). The question of which one to use is pretty much a no-brainer if you're working with GIS and spatial databases.

Re:MySQL vs. PostgreSQL

[kashani]

I'm and ISP/hosting geek as well and see exactly the same thing. Hundreds of Mysql users and one Postgres user. And pretty much the same thing in the users code and databases. My favorite being the 30 minute query which I fixed by suggesting that a 2 million row tables should probably use a int(11) as a primary key instead of a (128)varchar containing the users email address. They still think I'm a genius.

kashani

Re:MySQL vs. PostgreSQL

[thing12]

For a very small website SQLite is your best choice - fast enough and no maintenance. For small to medium, Postgres and MySQL are comparable in terms of the amount of management you need to do to keep them running. MySQL does have a more interesting SQL dialect, so if you use its nonstandard features you can get by writing less code. With large numbers of concurrent users - or if you're using complicated queries with many joins - Postgres is going to come out ahead since it's just better at handling that sort of thing.

Re:MySQL vs. PostgreSQL

[StrawberryFrog]

If poularity is your criterion, then why not run your website on MS Access, and allow only Internet explorer clients, while you listen to Britney Spears, drink coke and eat a big mac?

I'm not sure exactly what you mean by "dominant", since both databases are free, so neither takes sales from the other. Both are actively developed. PostgreSQL is IMHO better engineered and has more features, and has had them for longer. But postgreSQL has a reputation as not beeing as drool-proof as mySQL. I wouldn't know, I've been using all kinds of databases for a long time; I find PostgreSQL quite pleasant, and mySQL painfully juvenile.

Re:MS SQL 2005?

[BarryNorton]

http://en.wikipedia.org/wiki/Version

Here Here!, TCO

[PhYrE2k2]

Agreed. I have never seen up-to-date comparisons of MySQL/PgSQL/MsSQL in so many years. We need to once and for all get a few good test sets and see what these puppies can do objectively.

Of course MsSQL has a big lead weight for its cost, although M$ will claim its TCO is still lower- so it damn-well better perform like it's worth $50-$100 per concurrent connection more than the free alternative.

-M

Re:Here Here!, TCO

[xyvimur]

There was at least one that I am aware of. It was comparison of 3 DBs with respect to its application for quite a big project.
The report (made few years ago) is here:
http://dcdbappl1.cern.ch:8080/dcdb/archive/ttraczy k/db_compare/db_compare.pdf
However the machine seems to be down right now, so google html version:
http://64.233.183.104/search?q=cache:ijRVyqqJ_8EJ: dcdbappl1.cern.ch:8080/dcdb/archive/ttraczyk/db_co mpare/db_compare.pdf+dcdb+postgresql+mysql&hl=en&c lient=firefox-a

Embedded version?

[Kunta Kinte]

I would switch to PostgreSQL if there were an embedded version.

SQLite is great but concentrates on being a small database. This design choice is great for many applications probably, though poor handling of large rows ( can't read partial blobs, etc. ), weak concurrency model, etc. inconveniences others.

Re:Embedded version?

[Anonymous Coward]

You should look into Firebird or Ingres for an embedded relational database.

Re:Embedded version?

[Anonymous Coward]

or HSQLDB http://hsqldb.org/

Simple workaround...

[PhYrE2k2]

Perfect Workaround:

"And the performance benchmarks are System A: ???, System B: ???, System C: ???.

We were impressed with all of the systems listed here. Unfortunately, system B seemed to have a high purchase price tag *wink* which worries us in its value proposition. System A's corporate backing provides some additional stability to the database's presence. Finally, System C's has always been known for its great concurrency control and Oracle-like operation.

*wink* *wink*"

We didn't name anything, but the first one is a dead giveaway, and I'm sure the second two aren't rocket-science.

Re:Postgresql for large databases

[Anonymous Coward]

Handling terabytes of data and 100s of millions of records with pgSql is nothing new.

For details, see

http://www.powerpostgresql.com/Downloads/terabytes _osc2005.pdf

Re:MS SQL 2005?

[Yahweh Doesn't Exist]

http://www.apple.com/macosx/

quick question.

[CDPatten]

I am not trolling here, I honestly don't know allot about database software.

How does this compare to SQL 2k and the newly released SQL 2k5? I already know the comments about open source, free, etc.

THAT ASIDE, how do the released compiled programs compare? Does anyone know?

Re:quick question.

[jadavis]

PostgreSQL is rock solid, and very extensible (user-defined aggregates, user-defined procedural languages, user defined functions, triggers, user defined types, table functions, and much more). It probably also performs better in many situations due, in part, to MS SQLs locking vs. PostgreSQL's MVCC.

However, there are more tools available for MS SQL, and there is some form of multi-master replication and probably better table partitioning. MS SQL is not really a bad database, but I think PostgreSQL has it beat except on those two points.

Any real performance analysis is heavily application dependent, however. If that's what you care about, you need to do your own tests.

Re:quick question.

[FatherOfONe]

I am not about to start the holy war here, but you might want to define your question a bit more.

What do you want to do with your database(s)?
How many users do you expect?
How many writes per second do you expect?
How many reads per second do you expect?
Are you comfortable with SQL?
What programming languages do you use to acess data?
How important is it to you to be flexible in what OS you run your DB on?
How important is cost to you?
Do you plan on running this on multipe processors or multi-core chips?
What reporting tools do you plan on using?
How critical is the data?
If the system goes down for some time, how much will it cost you?

I have worked a bit with various databases and from a developers view I can say that all offer some features that I like. In short here is my list of what I think is cool about each of the ones I have worked with.

1. SQL Server (not 2005, but I have loaded it and to me, as a developer not much has changed) - The enterprise manager is a good gui and the DTS system is very strong. I wish other vendors had something similar to this. If you need to get data in and out of systems and are not strong with Transact SQL then this is a very cool tool.

2. Oracle - Great support, runs very fast, and runs on just about everything out there. It is cool to mount XML/Excel and CSV files as tables to work with them. The personal version of this is also free. It is also nice to know that you almost never here these words with Oracle "Our DB doesn't support that". If it is a feature then Oracle more than likely supports it.

3. PostGreSQL - FREE, supports everything I need to do development. Speed is good.

4. MySQL - Wow the GUI tools have really improved, and it now supports most of the database features that I need for development. Also this is FREE.

5. FileMaker - I have had little experience with this directly, but I will say that if you are not a developer or a strong SQL guy, it is by far the best DB I have seen for getting an entire application to the web in the shortest amount of time. A specific example would be that things I have seen done in Microsoft SQL Server + C# can be done in around one tenth the amount of time.

Now judging by your question I would say that you should take a long look at FileMaker.

Re:quick question.

[killjoe]

1) It's free. SQL server costs 5000 per processor or 16,000 per processor depending on the version
2) It runs on every platform. SQL server only runs on windows. It's the only database in wisespread use that locks you to one operating system.
3) It has no limits on how much memory it uses. SQL server standard edition limits itself to 2 gigs as of SQL server 2K (don't know if they fixed that by now).
4) it supports text fields that are only limited by your OS and uses them extremely efficiently. These are not like SQL server blob fields but they are like HUGE text fields that can be indexed or used in aggregate functions.
5) It has user definable data types, user definable operators, user definable functions.
6) It can use perl, python, java, tcl or PG/Pqsl as it's stored procedure languages. YOu can also use C and even compile your C stored procs in with the server if you want super speed.
7) It has multi version concurrency control. This means readers never block writers, ever.
8) No lock escalation. SQL server users know the value of this, everybody else takes it for granted.
9) Lots of built in datatypes like arrays, IP address, geometric types, GIS types etc. Yes it's possible to write a query that asks "select all rectangles that contain this point" or "select all ip addresses in this address mask"
10) Support for hierarchies (in the contrib) so you can natually and intuitively model graphs without writing code or using complex self joins and such. Look up ltree.
11) A fantastic rule system. You can make anything look like a updateable recordset if you are willing to code it.
12) PostGIS.

I am just scratching the surface. I am sure I have missed some other features but that should whet you appetite.

Re:quick question.

[Anonymous Coward]

> 6) It can use perl, python, java, tcl or PG/Pqsl as it's stored procedure languages. YOu can also use C and even compile your C stored procs in with the server if you want super speed.

There is also plPHP:
http://www.commandprompt.com/community/plphp

Re:quick question.

[Eivind Eklund]

... and plRuby. (Another poster also mentioned plPhp.)

Eivind.

Re:quick question.

[rtaylor]

4) it supports text fields that are only limited by your OS and uses them extremely efficiently. These are not like SQL server blob fields but they are like HUGE text fields that can be indexed or used in aggregate functions.

It should be mentioned that PostgreSQL will only index the first few kilobytes of the data in the field. The remainder will be compared against at the heap level.

Re:quick question.

[einhverfr]

6) It can use perl, python, java, tcl or PG/Pqsl as it's stored procedure languages. YOu can also use C and even compile your C stored procs in with the server if you want super speed.

And plenty of others as well..

PL/sh, PL/J, PL/R, PL/Java, PL/Mono, etc....

If you *really* want to compose your SP's in other languages you can write a langauge handler.

SQL Server has an edge in OLAP capabilities, and it is easier to set up replication, but PostgreSQL is more extensible and in general more powerful.

Headline wording

[d_54321]

It's interesting that this is worded as "PostgreSQL 8.1 Available" and just a short while ago there was a story entitled "MSSQL 2005 Finally Released." Would it have been so painful to name the MS story without bias?

Re:Headline wording

[Anonymous Coward]

You must be new here.

Re:Headline wording

[Anonymous Coward]

Maybe that's because MSSQL has experienced numerous delays and setbacks (much like Longhorn/Vista) whereas the Postgres team released a major version just ten months ago. Today's release is on schedule.

I don't understand MS apologists. If you aren't a shareholder and you don't work for MS, why constantly sling mud on their behalf? Are you trying to rationalize away the guilt you feel for using their software? Or does competition make you nervous that your MCSE's value in the marketplace might be reduced? That all the time you spent studying MS products might have been wasted? That goes for those who mod up apologist posts as well.

Re:Headline wording

[Disoculated]

There's Ingres and Firebird articles in the past day too. Just because one database is mentioned, all of them have to be? Is there a technology diversity movement I didn't hear about? Do we have to be inclusive of all software without making any value judgements lest we hurt the feelings of the zealo(:cough:) users of said software?

C'mon, it's tools, not religion. And if it is religion, then go set up some faith based software community and see how far that gets you.

Re:Headline wording

[eventDriven]

Postgres 8 Release Date: 2005-01-19

SQL Server 2000 Release Date: Well, in 2000.

Re:Headline wording

[Anonymous Coward]

1993 - 4.21
1995 - 6.0
1996 - 6.5
1999 - 7
2000 - 2000

MS Has historically released a new version of SQL server about every 2 years (3 years once). Don't you think that 5 years warrents a "Finally"? Postgresql 8.0 just came out a few months ago and there have been about 2 releases per year.

Re:yoopaa

[froi]

Uh, sorry about that one.

It's incredible

[photon317]

I've been using 8.1 beta releases for a while now, and even compared to 8.0.x (which was really good), 8.,1 is very impressive and well worth the upgrade. Performance improvements alone are worth it (esp on SMP).

But the biggest thing to me in 8.1, which the blurb didn't mention, is native support for inheritance-based table partitioning optimizations, which is a huge performance win for large and/or ever-growing tables.

Awesome - later

[Soong]

Sounds great, but with everything up and stable at the moment, I'll wait to switch to it when I'm developing something new again.

/. Meta question: a wheelbarrow?

[markhb]

Okay, I'm going to bite: what on earth does a red wheelbarrow have to do with databases?

Re:/. Meta question: a wheelbarrow?

[amorico]

I assume it is from the William Carlos Williams Poem, Red Wheelbarrow

so much depends
upon
a red wheel
barrow

glazed with rain
water

beside the white
chickens.

A database being the red wheelbarrow of course. Don't ask about the chickens.

Re:/. Meta question: a wheelbarrow?

[markhb]

That poem was actually the only match on /. for "wheelbarrow" when I searched prior to posting... it was quoted in a response to an April Fools' Day story.

The rain water implies liquid cooling. The chickens imply... dinner?

Re:/. Meta question: a wheelbarrow?

[Anonymous Coward]

Data mining?

Re:/. Meta question: a wheelbarrow?

[Anonymous Coward]

I assume that it is because both can be used to hold heaping piles of crap.

Still no FULLTEXT indexes?

[inio]

I'd really like to move a few projects at work to Postgres, but there's one essential feature that it still appears to be lacking: FULLTEXT indexes. I don't even need the fancy ranking stuff MySQL does - a boolean word match would be enough.

Re:Still no FULLTEXT indexes?

[Anonymous Coward]

fulltext seach as tsearch2 is included within contrib on unix platforms; and just a cross away on windows. It is not installed without the admin deciding that he needs it, though.

Re:Still no FULLTEXT indexes?

[Anonymous Coward]

Huh, of course there are. What do you think The Release Notes mean when they speak of "PostgreSQL's full-text indexing".

Is there a distinction in the hyphonization or ALLCAPS between "full-text" and "FULLTEXT" that has you concerned?

Or is it simply the case that not every odd index type (fuzzy, array, text, spatial, etc) is built-in in the default ./configure options. IMHO that's a feature rather than a bug -- because that way people who used one of the other full-text engines like this one built on inverted trees rather than GiST indexes can do so.

Re:Still no FULLTEXT indexes?

[Anonymous Coward]

PostgreSQL can do FTI, and has done it for several releases now. In order to use it, you just have to install tsearch2 which uses GiST indexes. It works for lots of people, check it out.

Re:Still no FULLTEXT indexes?

[rtaylor]

Full text searching has existed for a number of years and is actively used by a few Russian search engines on large volumes of content (millions of text documents), among other places.

Take a peak in the contrib directory of the source tree for tsearch.

Documentation for TSearch

Re:Still no FULLTEXT indexes?

[jabbo]

Take a look at openFTS.

Having used both MySQL and Postgres pretty close to each system's limitations, for years (~5 years in each case), I must admit that I now prefer PostgreSQL (again). I've gone back and forth.

One major problem with MySQL is that you have to choose EITHER fulltext indices OR transactional tables (InnoDB). You can't have both. With Postgresql, you can.

Re:Still no FULLTEXT indexes?

[inio]

Thanks for the pointer. It looks like it'll work assuming that I can get the DB admin to install it and figure out how to use it. Seems like an awful lot of code for functionality I need though (compared to "FULLTEXT", "MATCH" and "IN BOOLEAN MODE").

Re:Still no FULLTEXT indexes?

[otisg]

If PostgreSQL's full-text indexing is anything like MySQL's, I urge you not to use it. Things I heard about MySQL's full-text index are horrible! Instead, integrate Lucene with your application/database. If you need a book, there is Lucene in Action with free code and sheap eBook version. Full disclosure: I'm one of the authors. Simpy is a good example of PostgreSQL + Lucene integration.

Oh, and if you want non-Java solution, there are several Lucene ports available: C++, Python, Perl, C#, Ruby...

Re:Still no FULLTEXT indexes?

[Decibel]

It's generally a safe assumption that PostgreSQL doesn't do things the way MySQL does, because MySQL generally does them wrong.

Here's a fun example:

CREATE TABLE t(t tinyint);
INSERT INTO t VALUES(300);
SELECT * FROM t;

What, you don't think 300 = 127?

Re:Still no FULLTEXT indexes?

[rawg]

You really should do a Google search first. Postgresql has had Full Text Indexing/Searching in Contrib for a very long time.

Re:Still no FULLTEXT indexes?

[Anonymous Coward]

Defaults matter.

Re:Still no FULLTEXT indexes?

[rtaylor]

Yes. A little bit of polish could be added to hide all of the actual working components.

Re:Still no FULLTEXT indexes?

[frostman]

I never install without tsearch2. It's amazing. Super flexible, super smart, and fast fast FAST.

I normally attach it to template1 and tune it to my environment, then just basically forget about it.

However, I must say it's one of PG's big failures of marketing that it hasn't been included in default installations. I know the PG folks are perfectionists, and I love them for it, but in winning users over you would do well to keep websites in mind, almost all of which need an FTI.

Anyway, if you don't know tsearch2, check it out. Great stuff.

Re:Article text for your convenience

[IdleTime]

Ahhhh truth is modded flamebait now... Nice work!

Database Engine is enterprise ready

[Anonymous Coward]

Congratulations to PostgreSQL team, with the 8.1 release (roles and autovacuum) PostgreSQL is ready to cover more than 90% of the enterprise needs.

What still I see as a missing piece are the developer tools. The engine is great, but you also need to be be able to make diagrams easily, generate DDL for differences between a modified diagram and a production database, reverse engineer a database and create a diagram. There are two working solutions
1) dia + tedia2sql + zongle
2) druid.sf.net
but they don't compare with tools such as JDeveloper (also having pl/sql procedures editing, with code completion etc)

Would be nice to focus on these too. Red Hat Database Visual Explain is an example of a good tool for PostgreSQL.

Thanks for PostgreSQL!

Not too much to choose from

[-_broken_watchman_-]

> One thing is clear- with the newest Postresql and MySql, you have much to choose from.

Let's hear what MySQL has to say:

From http://dev.mysql.com/downloads/mysql/5.0.html
> MySQL Community Edition has not been certified and is
> not considered ready for enterprise production use.

Well, not much to choose if you plan something serious

Re:Not too much to choose from

[Disoculated]

Er, that's blatantly misleading. The community version is the same thing as the paid version, minus support. They're just encouraging you to buy support from them.

Re:Not too much to choose from

[-_broken_watchman_-]

They can not be more clear, and the source of this information can not be more trustable. If their information is blatantly misleading...

Re:Not too much to choose from

[imemyself]

The community version is what would be in Linux distros, most people would probably just get support from their Linux vendor, not MySQL.

Re:CentOS

[Anonymous Coward]

Just use the RHEL version for your CentOS version

http://www.postgresql.org/ftp/binary/v8.1.0/linux/ rpms/redhat/

Postgresql has multimaster replication.

[Some Random Username]

Its just not part of the default postgresql install, its 3rd party. Search for pgcluster.

Re:Postgresql has multimaster replication.

[ltning]

Sure, but that one is 1 inflexible and 2 inefficient and 3 not able to fulfill the requirements we had when evaluating it (about 4 months ago). I can't remember exactly what the conclusion was, but even though we hungered for something else than MySQL we ended up sticking with it for another maintenance cycle...

Re:Postgresql has multimaster replication.

[Some Random Username]

It is not inflexible or inefficient. I don't know what magic requirements you had, but it is a full, synchronous, multi-master replication solution. It handles things like random() and sequences properly, and allows for load balancing, failover and online re-syncing of new members/failed members.

If you can't give actual facts to backup your claims, then you are just spreading FUD.

Re:Postgresql has multimaster replication.

[ltning]

Sorry, that (spreading FUD) was not my intention.

I just dug out the report from our evaluation (I was not the one evaluating Postgres - for the simple reason that I love Postgres too much), but the main reasons for not recommending it for our environment was the combination of two things: Transaction throughput dropped to about one-third when using multi-master replication with two nodes, with dedicated 100mbit links between the data nodes, and the replication was not synchronous. If it was synchronous, then the drop in performance would not be such a big deal, but when it's not, the loss of performnace was difficult to explain and increased the risk of conflicting updates.
MySQL, on the other hand, remained at >85% efficiency in the same setup (not synchronous, though).

If any of the conclusions were wrong, or if anything has changed during the last 6 months, then we will obviously have to re-evaluate at the next crossroads, which would be in another 6 months or so.

And please DO prove me wrong here, as Postgres would be very much preferred...

Re:Postgresql has multimaster replication.

[Some Random Username]

Are you sure they evaluated pgcluster and not something else? PGcluster is sync, there's no option to be async, and there never has been. So the claim that its not synchronous means they either tried something else and not pgcluster, or they don't know what they are talking about.

As for performance, I haven't had such a big slowdown. Then again, I am using pgcluster and not whatever you are talking about.

Re:Postgresql has multimaster replication.

[einhverfr]

Well, there are quite a number of possible things one can mean by multimaster replication. Are we talking sync? Async? Pgcluster is a synchronous solution which makes it quite inappropriate for certain circumstances.

If you are looking at something like MySQL's replication, I would assume you need something async. I would suggest sending me an email at sales@metatrontech.com. I would be happy to discuss your situation and send you some free pointers on how to use Slony-I and some custom rules/triggers to accomplish whatever you might need on this. It would not be willing to engineer a solution for you but at least I can help you frame the solution.

Re:Postgresql has multimaster replication.

[einhverfr]

Are you sure they evaluated pgcluster and not something else? PGcluster is sync, there's no option to be async, and there never has been.

Sounds like MySQL was set up for async replication while PostgreSQL was using sync replication, possibly without proper performance tuning. PostgreSQL does *not* have a package for multimaster async replication but I do believe that it is reasonably possible to engineer a solution to whatever your specific needs are using a combination of various tools. Slony-I can provide part (though not all) of the framework of such a solution. Insert-only is pretty straightforward in this case. Insert/Update/Delete is a little more complex. I have been hashing out these ideas with a few other individuals and am getting to the point where if someone sponsors a project, I think I could develop one for less than commercial multimaster replication solutions charge for licensing :-)

Re:CentOS

[WindBourne]

If you feel that Florida L/P does a bad job, then buy a generator. They are reasonable in cost (you can have them for a couple of hundred to a couple of thousand), and you can get them to run off LP, natural gas, gas, or diesel. Even if you are in an apartment, simply get a small generator and run it on your balcony with an extension cord going inside. Considering that you are in Florida, make sure that you have one big enough to power a small window a.c., a microwave, and your laptop/network hardware.

Oops... should be openfts.sf.net!

[jabbo]

Should have linked to OpenFTS.sourceforge.net.

Oopsie.

OMFG!!

[RelliK]

Finally! 2-phase commits. Distributed transactions can't be far behind. Is work already being done on them?

THANKYOU THANKYOU THANKYOU PostgreSQL team!!!

(I feel like a little kid who just got a new toy as a present).

Upgrading between 8.0 and 8.1

[caluml]

Is the data format the same between 8.0.x and 8.1.x - i.e. can someone just upgrade the software, and stop and restart the daemons? Or will it require a pg_dumpall, stop, upgrade, start, and restore?

Re:Upgrading between 8.0 and 8.1

[thesman]

You can check that almost instantly by reading the release notes. Scroll down to "E.1.2. Migration to version 8.1"

Re:Upgrading between 8.0 and 8.1

[egoots]

From the Release notes:

E.1.2. Migration to version 8.1

A dump/restore using pg_dump is required for those wishing to migrate data from any previous release.

EVIL functionality

[daBass]

That kind of functionality is rather EVIL if you ask me; it is likely to end up locking someone out because someone selected a record this way and then went out to lunch. For web applications - by far the biggest use of Postgres no doubt - you won't ever want to do this kind of thing anyway.

If you are paranoid about two people editing at the same time and then overwriting each other's changes, build it into the app some other way, like checking a "last update" timestamp before saving changes to the record and diff'ing it for the user.

Chances of that happening are astronomicaly small in most apps anyway.

Re:EVIL functionality

[einhverfr]

That kind of functionality is rather EVIL if you ask me;

Depends. It can prevent lost updates. For certain types of applications it is required.

Yes, only lock when you have to, but certainly lock when you have to.

Also such behaviors won't lock everyone out of the database. You can still select rows after all using MVCC to take a snapshot of committed values. You just cannot update the locked rows.

Re:quick question. (DB comparison is hard.)

[Zeut]

It's tough to say how they compare. You really need to take time and investigate what is right for you. MS SQL Server is windows only, that is enough to turn some people off. There are lots of differences, each is going to outperform the other in certain circumstances.

because only one was actually late

[jbellis]

"Yukon," which became Sql Server 2005, was originally targetted for "early 2004"

Queue Postgres Trolls here

[GabboFlabbo]

For all you Postgres trolls saying how Postgress isn't a real database. Please put all your flames/comments into this thread. thx.

oh wait minute...

Some ancient history

[jd]

The word "wheelbarrow" originates from the ancient burial mounds called barrows and are traditionally the same shape as the more ancient of these, called "long barrows". The common theme being dead stuff buried under large mounds of earth.

Databases are often used for dead projects, buried under large mounds of data. The parallel would seem to be pretty exact.

If it's not default, it's not useful

[mgkimsal2]

People bitch about MySQL not having transactions, then other people respond with 'just choose the table type innodb!'. Point is, transactions aren't something someone writing a mysql app can assume will be available without some gyrations on the end user's part. And the same thing exists for Postgres and fulltext indexes. Unless they're default and can be assumed to be on every installation of 8.2 (or whatever) they essentially don't exist.

Re:If it's not default, it's not useful

[bigtrike]

The funny thing is, you can't have fulltext indexes and transactions on the same table in mysql because fulltext indexes are incompatible with innodb.

Re:If it's not default, it's not useful

[mgkimsal2]

Wouldn't call it 'funny', but yes, I'm completely aware of this limitation, and it irks the heck out of me. It's why many people won't move to innodb as a table type - because they're already got a lot of legacy tables they want to be able to fulltext search. At least, that's been my experience.

Re:If it's not default, it's not useful

[bigtrike]

There's also the little problem that you can't use InnoDB in non-GPL apps without buying a license.

"The GNU GPL license, under which both MySQL and InnoDB are published, does not allow, without a permission from MySQL AB and Innobase Oy, linking of InnoDB and MySQL, or the client libraries of MySQL, to a product which you distribute but which does not itself satisfy the GNU GPL license."

Re:If it's not default, it's not useful

[ldspartan]

Maybe in your crappy shared-hosting web environment, but over here in reality, where people are building large applications, getting the RDBMS installed and configured correctly is cake.

--
lds

Re:If it's not default, it's not useful

[jbellis]

right... that must be why everybody hates the debian approach of "only install what you need."

wow, two phase commit

[protomala]

And it just happens that I have to present about this subject on tomorrow's class of databases 3!
I saw that and it will help me a lot on the work, because my teacher loves postgreSQL as me ;)

Re:Article text for your convenience

[egoots]

Wooww... This looks more and more like Oracle version 7 released in the early 90's. Oracle has had 2PC (2 Phase Commit) for about 15 years now. Nice catchup

So has Firebird... See: http://firebird.sourceforge.net/index.php?op=guide &id=ib6_overview.

Ew! FileMaker!

[Anonymous Coward]

Disclaimer: I generally prefer PostgreSQL, although I usually use MySQL because that's what my workplace runs.

I would argue that it is easier to just learn how to work with MySQL than to use FileMaker for anything. In my opinion, FileMaker is barely a database at all, and if you ever want to migrate it to something else, you will pay dearly. I recently had the displeasure of migrating a FileMaker inventory system over to a MySQL/PHP application that I developed. It was wretched.

Re:Ew! FileMaker!

[FatherOfONe]

I normally don't respond to anonymous cowards, but I will state again that it depends on what you are doing.

If you have a business with no real I.T. support and you don't know jack about any programming language (SQL/PLSQL/Transact SQL etc) AND you need to get your business on the web, or you need to have a web based intranet app them FileMaker appears to fit that better than anything I have ever ran across. Now factor in that it runs on a server unlike Microsoft Access; that it is under $1,200.00, and you have a good package that fills a need.

I have seen guys skilled with Filemaker develop an entire order entry/processing/reporting system in a day that worked well for a business.

Kind of a dumb question

[lorcha]

What is your goal of this hobby website? To run the actual website? Or to learn about many different database engines?

If your site uses one DB or the other and is working just fine, then why would you want to rip out the DB and start over? I can't tell you how to spend your time, but if it were me, I'd spend time adding new features or promoting the site or contributing in other ways to your community. Switching DB engines is a waste of your time and effort.

If it ain't broke...

Re:Kind of a dumb question

[ChrisF79]

"If your site uses one DB or the other and is working just fine, then why would you want to rip out the DB and start over? I can't tell you how to spend your time, but if it were me, I'd spend time adding new features or promoting the site or contributing in other ways to your community. Switching DB engines is a waste of your time and effort."

I was only mentioning the website as a point of reference. My point was that my only introductions to databases at all was because of me starting this hobby. I'm definitely not going to switch. I was just curious if users out there actually used Postgresql for small websites.

Autovacuum Integration Details

[Zeut]

With 8.1, autovacuum is integrated into the main backend, it's no longer a external contrib module. This has several benifits. First, it's easier to setup, just uncoment the setting int he postgresql.conf. Also, you can now set vacuum and analyze thresholds as well as vacuum delay settings on a per table basis. This was probably the biggest problem people had with the contrib version. In addition it is more reliable since DB activity data is no longer lost between database restarts. Also, you can now exclude specific tables from being touched by autovacuum.

OpenBSD port

[chrysalis]

OpenBSD-current users already have PostgreSQL 8.1 in the ports tree :)

Re: there are more tools available for MS SQL

[brennz]

(Note - some content cross posted from the recent MSSQL2005 posting I made)

I take issue with the number of tools.

Postgresql has a great variety of tools, both OSS and commercial that work great. I've been working on an updated list of all the tools. Here are a few of the most popular admin tools:

PGadminIII
http://www.sqlmanager.net/products/postgresql/mana ger [sqlmanager.net]

DBvisualizer
http://www.minq.se/products/dbvis/ [www.minq.se]

EMS Postgresql Manager
http://www.sqlmanager.net/products/postgresql/mana ger [sqlmanager.net]

PHPpgadmin
http://sourceforge.net/projects/phppgadmin [sourceforge.net]

Sybase Power Designer
http://www.sybase.com/products/enterprisemodeling/ powerdesigner [sybase.com]

ERWIN data modeller
http://www3.ca.com/Solutions/Product.asp?ID=260 [ca.com]

CASE Studio 2
http://www.casestudio.com/enu/default.aspx [casestudio.com]

Postgresql has a vibrant tool community. If you want more info on Postgresql tools see
http://techdocs.postgresql.org/v2/Guides/PostgreSQ L%20GUI%20Tools/document_view [postgresql.org]

Zero downtime Upgrading between 8.0 and 8.1

[team99parody]

Using Slony you can do even better, with zero-downtime(!!!).

• Make your old 8.0 system the master in the Slony replication pool.
• Set up a slave using 8.1,
• Wait until the initial sync is done.
• turn off your 8.0 system and failover to the slave (now running 8.1).
• Make your new 8.1 system the master.

Re:Zero downtime Upgrading between 8.0 and 8.1

[caluml]

I started playing with Slony this morning. Will have another try tomorrow. But if I was going to do that, I'd just do a dumpall | pg_restore onto the new one. The problem comes from having both installed at the same time. I could do it to a different location, but that's just hassle.

Re:Zero downtime Upgrading between 8.0 and 8.1

[Anonymous Coward]

The disadvantage with "dumpall | pg_restore" is that any transactions that happened between the beginning of "dumpall" and the completion of "pg_restore" would be missed.

Also, after your "pg_restore" you'd have to stop the old database and restart the new one -- which, practically by definition, has some downtime.

With the replication system in place, you get Zero downtime and keep ALL your data.

Combining sqlite with PostgreSQL is a great idea.

[jocknerd]

I've been wanting to write an app for OS X using PostgreSQL but I really wanted to take advantage of Core Data. Since sqlite is an option in core data, I may try it the way you are using PostgreSQL and Core Data. Thanks for the eye opener.

Re:CentOS

[LWATCDR]

Since you feel the need to respond to my sig.
1. FPL closed a trouble ticket without ever coming out to see if power was restored. I put in the trouble ticket AFTER others on my block got their power restored as requested by FPL and after a week went by.
2. FPL refused to re open the trouble ticket after I called them to let them know that they had closed it improperly. I asked if their mistake would delay me getting my power back I was told "only by a day".
3. On day nine FPL sent a crew to fix the problem. They couldn't find the problem. The crew may have been over worked or tired since the problem as a lead that went from the transformer to the hot line at the top of the pole. Yes I described it to the person that took my report BOTH TIMES.
4. After finding out the crew came and didn't fix the problem I called up again and asked to be notified when the crew would be at my home so I could show them the problem. I was told that wasn't needed and that no they would not notify me.
5. When I asked to make a formal complaint about them dropping my trouble ticket I was told that I couldn't
6. When I asked if I could file a complaint with their regulating agency I was told no. I then asked again I was told no. I then asked, "You mean I can not place file a complaint with the Florida Public Service Commission?" I was told well if you feel you must you can.
7. Two hours later a crew came and got the last 4 homes on my block back up. When I told them that a crew had already come and didn't find they problem they just shook their heads and said they where sorry because it was a simple problem that should have been fixed days ago. Time to fix... 4 minutes.
8. FPL is trying to get a rate hike.

I have no choice in what power company I can go with so I am voicing my opinion that they should not get any rate hike unless they can improve power restoration. Many poles snapped because they where old and rotten. They should have been fixed a long time ago. FPL has been turning a good profit for years and letting the grid go down hill and now they want a rate hike to pay for fixing the damage.

As a "PUBLIC" regulated utility they suck. Last year when we got hit by a 2 and a 3 I understood that the area had not been hit by a storm in a long time. This is just one year later and the storm when it hit this area was only a category 1 and several counties where completely blacked out. The town just north of us has a city utility company and they where restored in 3 days.
I give my thanks to the crews but the fact that our power grid seems to do worse in a hurricane than many mobile homes is just wrong.

BTW running a generator on your balcony is often not allowed and is very stupid. Many people have died in fires and from CO poisoning from generators. You shouldn't run them closer than 10 ft from your home and never on a screened patio or in a garage.

What's in a name? No seriously, tell me

[joelpt]

Frankly, I think PostgreSQL would considerably more popular if it simply had a better name. Not only is the meaning of the name not apparent (etymology anyone?), but it's not even clear how it should be pronounced.

Then again, FireBird isn't nearly as popular as either MySQL Or Postgres. But Postgres is the clear winner in breadth and power of features.

Firebird

[Urusai]

Get on the Firebird train, toot toot!

http://firebird.sourceforge.net/

MySQL is more popular

[lorcha]

For a small website, MySQL is overwhelmingly more popular then Postgres. The reasons for this are:

1. MySQL is very simple to learn
2. MySQL is supported by virtually all webhosting companies, whereas you have to look pretty hard to find a host that provides PostgreSQL.
3. MySQL historically has performed much better than Postgres for both reads and writes. Nowadays, MySQL only outperforms Postgres on reads. So for a website, where most database calls are SELECTs, MySQL will often (but not always) give better performance.
4. Nearly all blog, forum, photo gallery, etc. packages support MySQL. They do not all support Postgres, but some do.

Now, of course, Postgres has many advantages over MySQL, but for a small hobby website, you should just use whatever you know best and your host supports and your software supports. The answer is normally MySQL for your type of website.

Postgres is generally better than MySQL (though MySQL is closing the gap) in terms of SQL standards compliance and RDBMS-type features. If you're implementing a true DB app, you should definitely give PostgreSQL a second look. But for a web forum, stick with MySQL.

P.S. Did you go to Wisconsin?

Re:MySQL is more popular

[ChrisF79]

Great answer. My post was more "what are other people using" than "what should I use but I don't think I did a great job writing it.

I didn't go to Wisconsin. I went to various Florida schools. Why'd you guess Wisconsin if I may ask?

Re:MySQL is more popular

[lorcha]

I asked about Wisconsin because of your slashdot userid. The fraternity that I am in designates chapter and initiation year as X'YY with X being the letter of the school and YY being the initiation year. I guess if you were F'79, that would make you a pretty old Wisconsin alumnus. :-)

Good luck!

Re:MySQL is more popular

[Anonymous Coward]

• 
  Postgres is generally better than MySQL (though MySQL is closing the gap) in terms of SQL standards compliance and RDBMS-type features. If you're implementing a true DB app, you should definitely give PostgreSQL a second look. But for a web forum, stick with MySQL.
  

For a web forum, it really doesn't matter if you use MySQL or PostgreSQL or Firebird or SQLLite. It's whatever your webhost supports.

I myself do use Postgres as my backend for my hobby webforums but (1) I wrote my own webforums, (2) I host my own servers and (3) I have many years of production experience using PostgreSQL in financial & vendor management apps.

Re:MySQL is more popular

[jadavis]

Nowadays, MySQL only outperforms Postgres on reads.

That's certainly not true in the general case. There are many situations in which PostgreSQL will outperform MySQL on reads. Particularly if you need one of PostgreSQL's features to achieve reasonable performance.

Re:MySQL is more popular

[lorcha]

I see you quoted one sentence of mine. Read the next one, smart guy.

Re:MySQL is more popular

[WuphonsReach]

You missed #5... MySQL had a native Win32 port.

For folks using Windows on the desktop, that made it much easier to install and play with then PostGreSQL. (Having to muck with cygwin was a big turnoff.)

Now that PGSQL has a Win32 package, I'm planning on standardizing on it.

Re:MySQL is more popular

[jadavis]

I assume you are referring to the "not always" parenthetical when you say "read the next one".

Your one sentence that I responded to promoted a misconception without any backing whatsoever. While that one sentence sounded benign due to the general "on the fence" type attitude of the post (i.e. "not always"), I think it can be very misleading.

After all, does PostgreSQL provide better write performance, or worse? Does MySQL provide better read performance, or worse? Both statements highly depend on the application. Not only that, it depends on whether you're really operating at the limits of the RDBMS or not, and which limits. In other words, does the performance scale, and how?

Your generalization provided no information.

Read the writeup, dipshit

[lorcha]

mnovotny writes "Computerworld reports that Microsoft is finally set to release their belated SQL Server 2005. From the article: 'Despite a two-year delay, several users who have tested the software cited the improved performance and new functionality it brings as positive developments that likely will convince them to upgrade soon.' The free version can be downloaded directly from Microsoft."

When Debian Sarge was finally released, the slashdot writeup went so far to assert that "Hell has officially frozen over!". So the sword cuts both ways.

Nice kneejerk reaction though, buddy.

Origin of the name "PostgreSQL", etc.

[WebCowboy]

Not only is the meaning of the name not apparent (etymology anyone?), but it's not even clear how it should be pronounced.

The PostgreSQL project was founded by UCB Professor Dr. Michael Stonebraker--the same person who founded the INGRES database project. At the time, the INGRES academic project was concluded and the product was commercialised and further developed by Computer Associates (I believe there was a non-commercial fork that still exists today). Since Stonebraker's new project was started to do "post-INGRES database research" the project came to be known as PostGRES.

Development on PostGRES was rapid and successful, and combined with the fact the source was pubically available it developed a growing following outside the university (it found a niche as a popular database for storing astronomical data among other things). Soon, the academic researchers were spending too much time supporting the project and the project was officially closed in the early-mid 90s (this was release 4.2...? Version 4.something anyways).

At this point the project forked just as its predecessor INGRES did years before. Stonebraker formed a company called Illustra to commercialise the product. Illustra was eventually acquired by Informix and what started as PostGRES became "Informix Online Dynamic Server". This is now a still-active IBM property.

The second fork came about as a product of two of Stonebraker's graduate students (Andrew Yu and Jolly Chen). There was still great interest in PostGRES and one of the biggest requests was to migrate from the nonstandard QUEL to SQL as the query language. Yu and Chen added this change and released Postgres95 (Illustra/Informix also moved to SQL but I believe Yu and Chen beat them to the punch, although Illustra was much more stable once it was released). The version number was reset to 1.0 but this is generally considered "Version 5".

Improving the stability and performance of Postgres95 at the pace demanded by its growing user base was too much for two busy grad students, so the international development team that exists today was formed to take on the task. The name Postgres95 was rather hated, as it dated the project and created an association with a rather undesirable product (and it made no sense to make such an association as Windows 95 is incapable of running any version of Postgres). The name was changed to PostgreSQL ("Postgres with SQL" as opposed to the original Postgres that used QUEL). The versioning resumed continuity with the original PostGRES project (so 95==5.x and the first PostgreSQL was version 6.0).

Pronounciation: Linux has suffered from this ambiguity as well and it has done nothing to limit its success. As for PostgreSQL it is most commonly pronounced "Postgress Cue Elle". I've heard people say "Postgressequel" as well though it seems that is about as well regarded as pronouncing Linux "Lie nucks".

Thaks to the PostgreSQL team for all your hard work...with every major release you blow me away with pleasant surprises well worth the dump and restore. This time around the integration of auto-vacuum is very nice, and the revamped user/role privlige system even more so.

Postres

[Heembo]

How come Postgres never took off like MySQL? I've been using Postgres in Java projects that take a rather heavy load and it just seems to keep on trucking....

Re:Postres

[spauldo]

When I first had to make the decision on which to learn, PostgreSQL's documentation was rather lacking. MySQL, on the other hand, had fantastic documentation that filled a 3" binder. I was used to MS SQL Server, but for what I did with databases back then there wasn't that big a difference.

Note that it's not that way anymore. I've looked at PostgreSQL's documentation and it seems like it's pretty good. I'll be finding out soon, since I have to migrate some stuff over to it.

Throw in that MySQL used to be the fastest popular database around, most webhosting companies offer it, most people don't need advanced features (MySQL was pretty bare on these for a long time), and that it's had a lot of success stories (like slashdot), MySQL has been able to nearly take over the market for free databases.

Re:Postres

[Anonymous Coward]

Windoze installers are new to postgresql, which I think kept the sheer volume of people away.

I've personally lived my life in a bubble mostly away from Microsoft, and think I've seen at least as much postgresql as mysql everywhere I've been.

thank you #postgresql IRC channel

[kervel]

A big thanks to the very helpful people on the #postgres irc channel. I have been there several times asking for help, and always i have gotten an excellent, very in-depth answer, for what i tought were quite difficult questions (performance problems, weirdness with inherited tables, or just how to build a query to give you what you want in an efficient way). Thank you !

Re:If it's not default, it's Oracle

[Anonymous Coward]

You're very funny.

With Oracle, text search, replication, and practically all features are not default, but for-pay extras.

With MSFTSQLServer, text search, replication, and practically all features are built-in --- but you have to pay extra to enable them -- and then you have to drop them and buy a third-party "not-default" replacement package to make them scale effectively.

Let me guess, you're the kind of guy who never installed *any* extra software on his computer and wonders why Solitare and Minesweeper are the best games in existance -- after all, warcraft/doom/whatever aren't installed by default either.

Don't forget that pgAdmin III 1.4 was released too

[Anonymous Coward]

The Windows version of PostgreSQL 8.1 comes bundled with the newly released pgAdmin III 1.4 (2005-11-07).

pgAdmin III v1.4.0 contains a number of new features over previous releases, including:

Slony-I support.
An SQL/shell job scheduling agent.
PostgreSQL 8.1 support:
Autovacuum administration.
Roles.
Stored procedures (ie. Functions with OUT parameters).
Prepared transactions.
Allow server registration without connect.
Allow connection selection in the query tool.
Major source tree and architecture overhaul to allow further extensibility.
Unix domain socket support.
'Guru Hints' to help new users.
Password can be stored in .pgass/pgpass.conf files.
New graphics throughout.
Alert the user when a query finishes in the query tool if the window is not active.
Graphical EXPLAIN.
Support for Asynchronous Notifications in the query tool.

The complete changelog is at:
http://www.pgadmin.org/development.php#changelog

Re: there are more tools available for MS SQL

[jadavis]

PostgreSQL-based tools and applications are growing fast. Perhaps I should have said "and applications", because PostgreSQL's toolset is certainly competitive. I think the main gap remaining is application support, because there are still many applications that do not recognize PostgreSQL.

Re:MySQL vs. PostgreSQL vs SQLite

[GCP]

Under what circumstances is SQLite a good choice?

I have measurement data that is structurally simple enough that a single "flat file" might even be enough, but I have tens of millions of records (with perhaps a hundred or so fields each, both text and numeric). I'd like to be able to search, filter, sort, do arbitrary SQL queries, etc. as quickly as possible, but this would usually just be on a Windows laptop with a single user or perhaps a Linux server with a single user. Would the power of Postgres be advantageous, or is that the wrong kind of power and would something like SQLite be more "powerful" in a case like this (LOTS of data, simple structure)?

Debian has a package

[Anonymous Coward]

Debian already has a "postgresql-8.1" package (in unstable). I just installed and switched over to this. Works good so far. For some reason the page for the package is not there yet, but I guess that is because its very new?

Re:MySQL vs. PostgreSQL vs SQLite

[jd]

For something like that - especially as it is single-user - I would say SQLite would be far more useful to you. Postgres is better where you've thousands of users and/or tables and/or horribly complex connections - it scales in those directions wonderfully.

For the sort of work you're doing, SQLite would seem to be the best choice, as it has the stuff you want and doesn't have any of the overhead of the stuff you don't want.

A very simple rule of thumb is to look at the number of variables - whether that is the number of relationships, the number of users, the number of tables, etc, but NOT the number of records - that's not really a variable in the same sense.

If the largest number is single-digit, then SQLite will out-perform anything else. If you're between double digits and triple digits, MySQL is so far ahead of the rest that it's not funny. Quadruple digits upwards, you're needing sterner stuff and you're looking at Postgres or (for massively complex databases) Ingres.

It was good enough

[lorcha]

My generalizations were good enough for the original poster's purposes. He has a hobby website, not an enterprise application. Any anyway, I was not answering a question about which db would perform better for his application. He never asked which db would perform better.

He asked if pgsql was more widely-used than mysql, and I said no, it is not, and gave some reasons why. One of the many reasons that mysql is so much more prevalent than pgsql happens to be performance. Since we are talking about many many applications, not his specific applications, I can say with total comfort that in the majority of applications, my generalizations will hold. That mysql will yield better performance than pgsql. In all applications? No. In his application? Who knows? But if you are looking at popularity, you have to look at what is most commonly the case.

Most commonly, for a simple-data-model dynamic content website, mysql will beat the pants off of pgsql. I'm sorry to say it, but it is true. It is certainly not true in all cases. But it will be true in the majority of cases. Deal with it.

And yes, that is one of the many reasons why mysql is vastly more popular than pgsql. Nothing against pgsql. I'd use it if all my apps supported it (I run one app, mythtv, that will not support pgsql, and I see no reason to run two database servers when every app I run supports mysql). I'm glad it's out there and that it's available. But more often than not, it is going to be slower than mysql for simple data applications.

Inheritance is being used more and more in Pgsql

[einhverfr]

Ok, not in the traditional OO sense, but inheritance is a key portion of the way that table partitioning is done in PostgreSQL especially with the improvements in 8.1 involving check constraints and inheritance table selection.

For example, lets say I have a three terabyte table representing sales. Most of the time, my queries are limited to a specific geographical area. I can use a combination of CHECK constraints and INHERITS to create table partitions to speed access to specific data sets and keep things managable. In these environments, it usually is not important to deal with the inherited index entry problem but I expect that if inheritance continues being used for more stuff behind the scenes, this will have to be approached at some point.