PostgreSQL 8.0 Enters Beta

gavinroy writes "As announced in pgsql-announce, PostgreSQL 8.0 Beta is now available. New features include native win32 support, Point in Time Recovery, Tablespaces, and much more! here is the beta history if you want more information."

Comments

You can save a few clicks...

[tcopeland]

...and jump right to the beta announcement message.

Re:You can save a few clicks...

[ahodgson]

Since 7.0 PostgreSQL has been pretty fast, assuming you tune it a little. Admittedly some of the default tunings are rather lame.

The concurrent versioning system means some operations are inherently fairly slow (like updates), but it also allows very limited locking and writers don't prohibit readers from viewing data, which in a multi-user environment is very nice.

Most slowness users experience when starting with PostgreSQL is due to them being used to MySQL and not understanding how to do things in a fast way. For instance, every operation is implicitly a transaction. If you want to do multiple inserts at once, wrap them in a single transaction, and they'll be way faster than doing multiple separate transactions without even knowing you're doing that. Things like that will really speed up your experience.

There are lots of ways to make PostgreSQL fly, but it does take more work than MySQL. On the other hand, PostgreSQL won't eat your data. Depends what you value more I suppose.

where to download

[jbellis]

http://developer.postgresql.org/beta.php

the windows installer is at
http://pgfoundry.org/projects/pginstaller

BitTorrent Downloads

[Anonymous Coward]

http://bt.postgresql.org

Re:Here's the thing

[chez69]

why don't you tell us what the issues are instead vague statement with no real substance?

Bittorrent [was Re:where to download]

[dfetter]

http://bt.postgresql.org

Join the torrent! :)

Re:Bittorrent [was Re:where to download]

[sootman]

Holy hell! 12 MB (.mis) in about 30 seconds. Sweet!

Re:Bittorrent [was Re:where to download]

[pete-classic]

Coincidentially enough, today is my 29th birthday. Thirty is really begining to loom. If only I were 17 again . . .

-Peter

Re:Bittorrent [was Re:where to download]

[Pete]

Ah, you don't really want to be 17 again.

No really, you don't.

(hey, if I can convince you, maybe I can convince myself... *grin*)

Pete (31st birthday in a week, argh).

I recommend Mysql users to take a look at PG

[xutopia]

it is the most advanced Open Source database there is. If anything pissed you off with MySQL chances are Postgresql will have a solution for you.

Re:I recommend Mysql users to take a look at PG

[leperkuhn]

Except ready to go full text searching, the ONLY thing keeping me on mysql.

Re:I recommend Mysql users to take a look at PG

[junkymailbox]

Is database replication and fail over working yet?

Re:I recommend Mysql users to take a look at PG

[Jason Earl]

While it is true that the PostgreSQL project doesn't include full text searching there is a full text searching engine for PostgreSQL. The "problem" is that it is licensed under the GPL, not a BSD-style license, and so it is not included in the official distribution. Here's the link.

OpenFTS has been around for quite a while, and is used pretty heavily, so there really is very little reason to put up with MySQL's many shortcomings.

Re:I recommend Mysql users to take a look at PG

[rtaylor]

In short, yes and perhaps.

Replication (master & multiple slaves) works great with Slony.

Fail over should not be done by the database, but by the operating system or an external monitoring system (Big Brother). RedHat Enterprise with the standard failover configuration works fine.

You just need to tell Slony that the new node has been elected as master. It's not as clean as it could be (no gui tools, etc.) but it is functional enough for the .org servers (Affilias).

Re:I recommend Mysql users to take a look at PG

[Svennig]

Okay, I'll bite

Stored Procedures. They arent functions. Functions are different. Functions should be called inline, from within SQL statements. There should be a difference.

When looking towards migrating to an OSS database from MS SQL Server I looked into how easy it was to use the postgres stored procedure/functions/things. I couldn't find any equivalent of returing a resultset. In MS SQL you use:

CREATE PROCEDURE name AS select * from test

The only way that I could find to do that in postgres was:

1. Return a cursor reference, in which case throuch JDBC you need to obtain this horrid postgres specific class from the driver.
2. Return a set of results (or was it records, my mind fails me), but I couldn't get this to work
3. Return a set of created types, but this doesnt work well for dynamic queries and makes maintainance a headache - you'd end up defining hundreds of types!

4. So it seems that we're stuck with MS SQL server.

   If someone DOES know how to do this is a non-evil manner, please tell me! Were planning on doing an upgrade, and I'd rather not have to fork out the money for SQL Server licences etc...

Re:I recommend Mysql users to take a look at PG

[dfetter]

It has full-text searching: tsearch2 http://www.sai.msu.su/~megera/postgres/gist/tsearc h/V2/ :)

Re:I recommend Mysql users to take a look at PG

[ron_ivi]

Sure. Since about march the Slony replication project has had its 1.0 release out. This replication project is used for a while by Afilias to run the .org and .info domains. [best quote from that one "but Oracle was not happy"]

Re:I recommend Mysql users to take a look at PG

[brunson]

CREATE PROCEDURE name AS select * from test

For that you don't need a procedure, you need a view.

Re:I recommend Mysql users to take a look at PG

[LiENUS]

not sure if postgresql supports them but thats not a stored procedure, thats a view, i believe postgresql supports them however, mysql does not last i heard

Re:I recommend Mysql users to take a look at PG

[Svennig]

That was a very very simplified example!

Suppose that I said

CREATE PROCEDURE name ( @someID int ) AS select * from test WHERE ID=@someID

Can I pass parameters to views? Thats not a rhetorical question, I'm really not sure!!

Re:I recommend Mysql users to take a look at PG

[JamesKPolk]

Using postgresql 7.4.2:

create function testfunction()
returns setof record
as 'select name from companies;'
language sql;

select name from testfunction() as names(name char(50));

That will print all the company names in my database.

Re:I recommend Mysql users to take a look at PG

[rycamor]

PostgreSQL doesn't do "implicit" set-returning like this, but it is quite ease to get something almost as simple if you use SQL as your procedural language, instead of PL/pgSQL:

create function GetEmployees?() returns setof employee as select * from employee; language 'sql';

Not too bad, huh? Here's a short article on set-returning functions.

Re:I recommend Mysql users to take a look at PG

[JamesKPolk]

And yes, I could add arguments to that:

create function testfunction(numeric)
returns setof record
as 'select name from companies where length(name) < $1;'
language sql;

Obviously this is a simplified example, but you get the idea. And I assume that this capability extends to other embedded language like perl or ruby.

Re:I recommend Mysql users to take a look at PG

[Svennig]

Thanks, thats definitely something to think about when it comes to the upgrade. I did read that techdocs article, but couldnt get it to work.

Re:I recommend Mysql users to take a look at PG

[Spoke]

Sweet, replication was the main thing that MySQL supported and was easy to setup that PostgreSQL was lacking.

Now that PostgresQL has got good Open Source replication available, I expect to see a lot more people migrate to PostgreSQL. I'll be testing out PostgreSQL + Slony shortly.

Re:I recommend Mysql users to take a look at PG

[johnjaydk]

Try with a view instead of a stored proc. Using a stored proc is like using a nuke for cracking a nut for this sort of thing.

CREATE VIEW name AS SELECT * FROM test;

Then you select from "name" just like you'd select from any other table. Oracle works exactly the same way BTW.

Re:I recommend Mysql users to take a look at PG

[Anonymous Coward]

While PG has more features, don't forget that db adoption has more to do with the broadness of adoption in the community, with 3rd parties (ie. backup, BI, monitoring, etc), and momentum. We are a big company and our experience has been that when we ask MySQL about needing support from XYZ vendor, they return with a timeframe or an actual announcement from that vendor==reduced risk. They get business, they get IT. We haven't been able get that with PG.

With their 5.0 version they cure the lack of stored proc's, views, triggers. Plus native windows (although we aren't a big win shop). We looked at PG a good deal but ultimately it was too risky from a business perspective, not from a feature perspective, but if we need the features we stay with Oracle.

Re:I recommend Mysql users to take a look at PG

[ckaminski]

Yes.

(simplified)
create view products_sold_in_asia as
select * from product_list,
customer_list where customer_list.region = 'APAC'
and customer_list.purchaced_products = product_list.itemcode

And you can do

select * from products_sold_in_asia where country in ('Japan', 'China')

Re:I recommend Mysql users to take a look at PG

[Elwood P Dowd]

No. The proper method for simple parameterized views in MS SQL are inline functions. From the "Inline Functions" topic in transact-SQL help:

Inline User-Defined Functions
Inline user-defined functions are a subset of user-defined functions that return a table. Inline functions can be used to achieve the functionality of parameterized views.

Consider this view:

CREATE VIEW vw_CustomerNamesInWA AS
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = 'WA'

You can create a more generalized version, vw_CustomerNamesInRegion, by replacing the WHERE Region = 'WA' with a WHERE Region = @RegionParameter and letting users specify the region they are interested in viewing. Views, however, do not support parameters in the search conditions specified in the WHERE clause.

Inline user-defined functions can be used to support parameters in the search conditions specified in the WHERE clause. This is an example of a function that allows users to specify the region in their select:

CREATE FUNCTION fn_CustomerNamesInRegion
( @RegionParameter nvarchar(30) )
RETURNS table
AS
RETURN (
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = @RegionParameter
)
GO

-- Example of calling the function for a specific region

SELECT *
FROM fn_CustomerNamesInRegion(N'WA')
GO

Inline user-defined functions follow these rules:

The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable because it is set by the format of the result set of the SELECT statement in the RETURN clause.

There is no function_body delimited by BEGIN and END.

The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.

<snip> section about using inline functions with indexed views </snip>

©1988-2000 Microsoft Corporation. All Rights Reserved.

Re:I recommend Mysql users to take a look at PG

[Elwood P Dowd]

As everyone else is pointing out, you can just include the criterion in the result set, and do your parameterization that way.

Sometimes this is not feasible. I've never had a purpose for inline functions, as whenever I've needed to parameterize a view, it's because I've also needed to do procedural calculation on those parameters. In such a case, a standard function (with BEGIN and END) is required rather than an inline function.

Re:I recommend Mysql users to take a look at PG

[Ragica]

"tsearch2" which openfts is based on is BSD licenced, and included in the PostgreSQL contrib package. It's pretty good, and all you need for creating full text searchable indexes.

The documentation describes the differences between tsearch2 and openfts like this:

OpenFTS is a middleware between application and database, so it uses tsearch2 as a storage, while database engine is used as a query executor (searching). Everything else (parsing of documents, query processing, linguistics) carry outs on client side. That's why OpenFTS has its own configuration table (fts_conf) and works with its own set of dictionaries. OpenFTS is more flexible, because it could be used in multi-server architecture with separated machines for repository of documents (documents could be stored in file system), database and query engine.

Re:I recommend Mysql users to take a look at PG

[rainman_bc]

Views do not cache execution plans like stored proccedures do, and views cannot accept parameters.

Stored Procs cache the execution plan and accept parameters. Huge difference.

IMHO Functions are closer to it than views.

AFAIK, PostgreSQL can't return recordsets from stored procedures. Really, you shouldn't have to IMHO. Stored procedures are really important for inserts and updates, and transaction locking and stuff. That's the critical use for stored procs. Returning recordset is a nice-to-have, but you can make due without.

Still better than any MySQL support for stored procs anyway.

Re:I recommend Mysql users to take a look at PG

[frostman]

I use lots of record-set-returning functions, quite complex ones, in Postgres at the moment.

There are simpler ways to go but for me the nicest is to create a TYPE which looks like a table when you're accessing it, and then make my function return a SETOF that TYPE. (Of course you don't need to make a TYPE if your result set is column-identical to a table.)

If you want to return a set you can't just select my_function(), you must select * from my_function() or select some_field from my_function(some_param) or whatever. You can nest, you can join, all kinds of goodies, all in pure SQL or pgPL/SQL according to your preference (or Perl or C or whatever if you're into that).

Dig through the documentation on that... I know there aren't as many nice books on PG as on ORA or MSSQL but once you grok it it's really very, very powerful.

Re:I recommend Mysql users to take a look at PG

[Sxooter]

It's obvious from your reply you aren't very familiar with PostgreSQL.

1: PostgreSQL never stores a query plan, they're always dynamically generated, as the underlying data may change, and why go to the bother of having a cost based query planner if you're not going to use it.

2: PostgreSQL has set returning functions.

3: PostgreSQL does not have stored procedures, it has user defined functions. These functions can be used in place of what other databases call stored procedures.

Just FYI.

Re:I recommend Mysql users to take a look at PG

[danharan]

If anything pissed you off with MySQL

Heh... you're kidding, right?

There are enough MySQL gotchas to drive anyone used to Oracle up the psych ward walls.

Every web developer I know keeps raving about the speed of MySQL... when I show them my database schema, it's usually the first time they've seen a 58 table database. It seems huge and unmanageable if you're used to 1-5 tables, and it most certainly isn't easy without triggers, stored procedures and foreign keys or any of the more complex functions and queries you need when dealing with normalized data.

MySQL really is a great little database for simple read-intensive applications. But it sure as hell feels like a toy to me.

Re:I recommend Mysql users to take a look at PG

[rainman_bc]

Yeah, you're right... My knowledge of PGSQL lacks behind my knowledge of SQL Server, Sybase, and MySQL... Friggin' hate MySQL though. Stupd non-ansi syntax.

The term function and stored procedure for Postgres is used synonymousouly. However I disagree with that and you're right about the execution plan. However in the world of enterprise RDBMS's, that's one of the key points about stored procs is the ability to cache the execution plan of a stored proc; Oracle, MSSQL, Sybase, etc.

Anyway a view a stored proc. That's all I was getting at. the rest of it was fluff :)

Re:I recommend Mysql users to take a look at PG

[Christopher B. Brown]

Hmm.

The fact that MySQL AB is a small company that entirely controls the source code base for their product strikes me as being a downright enormous business risk.

The changes away from friendliness of licensing towards users of free software that have taken place over the last year is a pretty clear demonstration of that business risk.

It would surprise me not at all if the venture capital folks decided that MySQL Version 5 would ultimately not be released in an "open source" form (once the alpha testing is done). After all, they have to get something back on their investment, and giving away licenses for free isn't a traditional VC-approved "business model."

If you think that's off, I'll amiably go along with the thought that elimination of the "open source" version might wait until they get enough functionality in that they can pitch it as an alternative backend to Oracle for SAP customers...

Re:I recommend Mysql users to take a look at PG

[Dukhat]

MySQL's text searching is still much easier to set up. You have to apply a patch in order to dump and restore a database with tsearch without adding several steps to your dump and restore process.

http://it.slashdot.org/comments.pl?sid=117456&th re shold=1&mode=flat&commentsort=0&op=Cha nge

BTW, when is postgres going to get binary dumps?

Re:I recommend Mysql users to take a look at PG

[Sxooter]

However in the world of enterprise RDBMS's, that's one of the key points about stored procs is the ability to cache the execution plan of a stored proc; Oracle, MSSQL, Sybase, etc.

And it's a right bad idea.

You create your stored proc today, and it has a selectivity of 95% of a 10000 row table. The query plan right uses a seq scan on a small table.

Next year, you have 10,000,000,000 rows in that table, and the selectivity is now 0.05%. Should we use the same query plan?

Re:I recommend Mysql users to take a look at PG

[Issue9mm]

If I recall correctly, MSSQL has an aging algorhithm that re-caches the execution plan when it becomes less effective.

Of course, being closed source, nobody knows how it works other than Microsoft, but I believe it doesn't stick with the same execution plan just to do so.

-9mm-

Re:I recommend Mysql users to take a look at PG

[rycamor]

PostgreSQL does cache query plans one a "per connection" basis. In other words, not over the long term, but if you call the same stored procedure twice in a row, the second call will be cached. Also, there are parameters about how you create a function/procedure that affects the amount of caching.

Another thing to remember about PostgreSQL is the built-in genetic query optimizer, which continually collects stats about queries and adjusts optimization.

Re:I recommend Mysql users to take a look at PG

[code_simian]

I use PGSQL and return reference cursors. The PostgreSQL JDBC driver is able to cast the return value into a standard ResultSet. Here is how I do it. The PGSQL function:

CREATE OR REPLACE FUNCTION
SelServiceTypes
() RETURNS REFCURSOR
AS
'DECLARE
ref REFCURSOR;
BEGIN
OPEN
ref
FOR
SELECT
ServiceType,
Description,
SortOrder
FROM
Service
ORDER BY
SortOrder;
RETURN ref;
END;'
LANGUAGE 'plpgsql';

The JDBC code, ignoring try - catch and assuming Connection con is already defined and holds an open connection, for simplicity:

Vector vector = new Vector();

CallableStatement proc = null;
ResultSet results = null;

// It is essential that we are inside a transaction, though in real
// code, you would either a) know you are not already in a transaction
// or b) check the status of con.getAutoCommit() first.
con.setAutoCommit(false);

// Creation the callable statment and set parameters
proc = con.prepareCall
("{ ? = call SelServiceTypes () }");
// Types.OTHER is important
proc.registerOutParameter(1, Types.OTHER);
// We could set up input parameters if we needed them

// Execute the procedure
proc.execute();
// Casting result to a standard jdbc.ResultSet
results = (ResultSet) proc.getObject(1);
while (results.next()) {
pst = new Service
(results.getInt(1), results.getString(2));
vector.add(pst);
}
results.close();
proc.close();

con.commit();
con.setAutoCommit(true); // Optional, see notes above

Re:I recommend Mysql users to take a look at PG

[Sunda666]

it has binary dumps for quite a while now...

pg_dump --format=c

peace

Re:I recommend Mysql users to take a look at PG

[jadavis]

You don't have to wait for 8.0 to do that... works fine in 7.4 (and 7.3 if I remember correctly).

Returning result sets was a solved problem in PostgreSQL a long time ago. They're also referred to as "table functions" in the docs because you can select from them like a table.

It makes me wonder why PostgreSQL doesn't provide some syntax for stored procedures, just to avoid confusion that postgresql doesn't have them. As far as I can tell, pg has all the same functionality in functions.

Re:I recommend Mysql users to take a look at PG

[Bradley]

You have to analyze to get that to change anyway, with postgres, and having analyze invalidate stored plans is probably sensible.

Postgres does have PREPARE, which speeds things up a lot, although DBI support for it isn't quite there last time I looked, plus it doesn't recalculate the plan when 'stuff' (schema, statistics, etc) change

Re:I recommend Mysql users to take a look at PG

[Unordained]

As everyone else is pointing out, you can just include the criterion in the result set, and do your parameterization that way.

Indeed: we use Firebird (the db server,) and for cases where you're doing param'ed views, it works just as well (speed-wise) to pull from the view with extra search criteria attached -- the optimizer sees the whole thing as one statement and optimizes it accordingly. It runs just as fast, and it doesn't restrict you to the parameters you thought of initially when creating the function. Firebird does support procedures returning datasets, and you can use them in an sql query, but they're a little odd (to me) in that they're expected to return results for a bit of everything, and you join against them for the more specific result you care about ... basically, you're expected to create (as I understand it) a dynamic view, join against it, and it'll figure out how to make just enough happen to answer your query. Did I mention I don't use that feature much at all?

It would be nice if all symbols (set-valued procedures, functions, views, tables, anything) could be used completely interchangeably, but current products tend to disallow that kind of thing (sadly).

Re:I recommend Mysql users to take a look at PG

[Sxooter]

PostgreSQL ONLY caches query plans for prepared queries, and only for that connection. It doesn't automagically cache query plans or result sets.

Die IT theme

[ScytheBlade1]

Die IT theme.

Re:Die IT theme

[Anonymous Coward]

Just in case... posting comments about the color scheme, while they make you feel better, don't affect the /. editors much. If you don't like the color scheme, the best is to email the editors and let them know. They pay attention to email much more than comments (which they may not even read).

Re:Die IT theme

[hatrisc]

I prefer the mac theme

Re:Die IT theme

[LnxAddct]

Actually, Rob reads his email very frequently and often responds quickly. I've had to email him once or twice about relativly stupid issues and have always gotten back a well thought out and thorough answer (as in it wasn't rushed).
Regards,
Steve

What the versions mean

[suso]

I remember reading a document somewhere for mysql that explains what each part of a version number "really meant". The major version number meant that the format of the databases will change (or could change). Is this also the case for postgres I wonder?

Re:What the versions mean

[SSpade]

In the case of postgres there are three digits in the version - a.b.c

If your upgrade increases either a or b then the on-disk structure of the database has changed, and as part of the upgrade you'll need to dump the database out to a backup file and restore it.

If the only change is in the final digit c then there's no on-disk change and you can upgrade just by upgrading the binaries. That tends to mean that final digit upgrades are bug fixes, and you should always do the upgrade.

So if you're running 7.4.1 you can easily, and definitely should, upgrade to 7.4.3. But upgrading to 8.0 is a marginally more time-consuming upgrade that you may not want to make on a production system unless you want the new features in 8.0

There's no 'technical' difference between a first digit change upgrade and a second digit upgrade. There's a difference in expectations though, and the version following 7.4.* has enough major new features to justify a major version jump to 8.0.0, with all the positives and negatives that jump implies. The upgrade path would have been identical had it been called 7.5.0 though.

Re:What the versions mean

[suso]

Thanks. Someone should mod you up to informative because that's some good info to know.

Re:What the versions mean

[Anonymous Coward]

So if you're running 7.4.1 you can easily, and definitely should, upgrade to 7.4.3. But upgrading to 8.0 is a marginally more time-consuming upgrade that you may not want to make on a production system unless you want the new features in 8.0

It is true in most cases that a point release upgrade can be done in place without re-initing the db. However the upgrade from 7.4.1 to 7.4.2 does require some extra steps. Please Read The Fine Manual if you're using 7.4.1 and upgrading to 7.4.2+

Upgrades which require an initdb can be performed within a limited maintenance window. The proceedure is relatively straight-forward:

1. Replicate the production database using either Slony or an older replication system. This can be done prior to the maintenance period. Unfortunately, Slony isn't quite as DBA friendly in the boot-strapping department as ERS. You can mitigate some of the pain by dropping non-critical indices durring the bootstrap phase and then adding them after the new subscriber has achieved synchronization.
2. Durring the maintenance peroid, stop input to the DB and allow the subscriber (new db) to catch up to the origin (production db).
3. Stop both the old production database and the new clone
4. Change the port to which the new clone binds and start it back up. It is now production.
5. Re-connect your client software and the maintenance period is completed.

The above requires more work, but when you have a database which takes about 10 hours to load and index and a maintenance window of no more than 2 hours / month...

Re:What the versions mean

[kill-1]

You're not completely right. If the b version number is increased, the on-disk format doesn't always change. Sometimes you can upgrade from a.b to a.(b+1) without a dump/restore.

Re:What the versions mean

[jadavis]

There's no 'technical' difference between a first digit change upgrade and a second digit upgrade.

As I understood from reading the lists, there is a technical distinction. a.(b+1).c is supposed to be more stable than a.b.d, even though it may have some additional features.

8.0 added so many powerful features that the developers did not want to imply that it would be more stable than 7.4. 7.4 is pretty much rock-solid, and 8.0 might not achieve that reputation until 8.1 or so.

Re:What the versions mean

[jadavis]

To which versions does that apply?

Re:What the versions mean

[TheLink]

That said you should always read the release notes.

See: <a href="http://developer.postgresql.org/docs/postgre s/release-7-4-2.html">http://developer.postgresql. org/docs/postgres/release-7-4-2.html</a>

In that particular case you either manually update the system catalogs, or you treat it like a major revision upgrade. I'd prefer to do the latter, looking at the steps required - coz I'm going to be dumping the DB to back it up anyway...

Can it Compete with Oracle or DB2?

[Greyfox]

I was interviewing with a company a while back that was planning to move from an Oracle based set-up to a Postgres based one. They were pushing enough data around daily that they were having to redesign their collector software to be more efficient, so I was somewhat concerned that Postgres wouldn't give them the performance they needed, but I haven't really seen enough of it to be able to comment.

On the other hand, I've seen both Oracle and DB2 corrupt indexes and database table data in various circumstances (Usually the failing of a DBA in some capacity or other.) I'd be curious to see how the various databases stack up against each other without the hype that most of the parties that publish such studies usually bring to the table.

Re:Can it Compete with Oracle or DB2?

[rtaylor]

PostgreSQL is a touch slower than Oracle in some special cases (single user on multi-cpu machine doing large or very complex queries) since Oracle can split the work up amongst CPUs.

With 50 to 100 users, both have similar performance for many workloads (you need to test your specific workload), but Pg may require a touch more tuning.

Pg is, however, quite a bit more reliable than Oracle as far as corruptions go (keeping in mind this is a .0 release) so long as the hardware is adequate (doesn't lie about writes hitting disk when they're really in cache).

There are ways of purposfully crashing PostgreSQL as an authenticated user (particularly if you have root access and can write functions in C), but in standard operation it's quite reliable.

I cannot speak for DB2, but based on their Docs I think Pg is nearly as feature complete in most areas, and much more complete in many others.

Re:Can it Compete with Oracle or DB2?

[ron_ivi]

Computerworld had an interesting article of the case when PostgreSQL beat Oracle(cool) to enable Afilias to power the .org domain when they took it away from Verisign (also cool).

Here's the ISOC's response to Oracle FUD.

We believe that the key point relating to databases for the .ORG
redelegation is not which database the operator is using, but,
rather, whether the database will support the .ORG registry in a
stable, scalable, and highly available manner.

Afilias has over a year of experience running a large scale gTLD
registry--the .INFO registry. This direct experience, the load &
stress tests conducted by Afilias (and listed in our proposal
<http://www.icann.org/tlds/org/applicati ons/isoc/section3.html#c17.10>),
and Afilias' compliance with ICANN's service level requirements clearly
demonstrate that the PostgreSQL database used by Afilias performs
at the level of reliability and availability required for the
mission critical operations of a global gTLD registry.

Further, the actual operating performance record of Afilias compares
very favorably to the records of the other .ORG applicants, many of
whom use commercial databases. A review of each bidder.s answers to
Question 14 <http://www.icann.org/tlds/org/questions-to-applic ants-14.htm>
illustrates the kind of hard data and real world registry experience
that ICANN is basing its decision on. Afilias has delivered this level
of performance based in part on its stable, reliable database, PostgreSQL.

The successful operation of a registry extends beyond simply which
database software is in use. It also requires a skilled operating staff
with the ability to design and implement reliable systems as well as
establish clearly defined resolution paths should problems occur.
Afilias' solid operating performance lends support to the claims in our
.ORG proposal regarding our ability to effectively manage the .ORG domain.

We do not take issue with the recitation of the many features and
benefits of Oracle's products. However, any suggestion that PostgreSQL
is unsuited for registry use is in direct contradiction to the facts.

Re:Can it Compete with Oracle or DB2?

[Stone316]

In my 8 years as an Oracle DBA I don't think i've ever seen a corrupt index. Saying that, I don't even know how you could force a index to get corrupted so I don't think were seeing all the info here.

There is alot of hype in the database market as is there with any other area such as OS preference. A good DBA, like a good software developer will pick the database that fits the needs. Saying that, usually DBA's (like anyone else) are under certain constraints from management but heres my take:

For:

Mission Critical Apps and large DB's: Oracle or DB2
Mid-Range apps/Mid-sized DB's: The above + SqlServer, PostgreSQL.
Small: SqlServer, PostgreSQL. (+ Oracle and DB2 if you like to toss around the $$).

I'd feel comfortable using PostgreSQL for upto and including mid-sized DB's. Currently I wouldn't use mySQL for anything until they fix their Gotcha's.

If you really and I mean really have faith in your developers and they have reviewed these gotcha's i'd consider MySQL but unfortunately, as every DBA knows this isn't always possible. It only takes one bad apple to mess everything up.

Of course there are many other factors to choosing a database but the core comes down to your employees and licensing. If all your DBA's have extensive training on Oracle then it doesn't make much sense to retrain them to use another DB just because it maybe a better fit. Also, if you have a server license for Oracle it doesn't make much sense to buy another so you can use DB2.

At our shop we have a mix of Oracle and SqlServer. But we do installs for DB2, Redbrick, informix, everything and anything under the sun.

Anyways, thats my 2 bits.

Re:Can it Compete with Oracle or DB2?

[ron_ivi]

Better, you can see all the competitors for .ORG answering ICANN's questions about databases here. It has the best summaries of Oracle vs PostgreSQL I've seen, because it comes from vendors competing for a big project arguing their pointts.

Oh, and a link to Oracle's FUD when they were trying to keep control of the .org comain: "PostgreSQL is used primarily in the embedded system market because it lacks the transactional features, high availability, security and manageability of any commercial enterprise database... Jenny Gelhausen Oracle Marketing" Seems Oracle didn't even do the homework to see which open source database she was talking about.

Re:Can it Compete with Oracle or DB2?

[JamesKPolk]

Oracle for "mission-critical"? I don't see how any one could feel comfortable putting the life of their company in the hands of another company like that.

Re:Can it Compete with Oracle or DB2?

[Stone316]

There's more to a database than feature set. While postgreSQL has enough features for 98% of the applications out there it isn't that simple.

In 8 years i've never lost data from an Oracle database from a software problem. I've seen data get lost, corrupted for a number of reasons including incompetent DBA's and their managers. (Off topic but once I was called into a major telephone company because they had some corrupted blocks from a hardware issue. I asked them how long did they keep their backups, 3 months they replied. Ok, how long have you known about this problem, 5 months. Hrmm... 3 - 5 = -2. You can fill in the rest of the story..)

I haven't used postgreSQL in a production environment long enough to know how stable it is. The reason companies choose commercial vendors for their DB's are two fold:

1) Track record. They know if they pick Oracle or DB2 they are getting a solid,proven database system. And there is an abundance (if not expensive) of knowledge out there. Their support is bar none and for the most part I have been extremely satisfied.

2) Managemnt loves to save money. Who wouldn't want to be able to say they saved the company millions of dollars by going to a 'free' or inexpensive (if you pay for support) rdbms like postgresql or mysql. One reason, blame. Opensource still has the stigma attached to it that no one is accountable for bugs. If a critical revenue generating db goes down because of a bug a manager needs to be able to point their finger. Until this stigma goes away we won't see alot of opensource adoption in the DB market. Whether this is right or wrong doesn't matter, people don't like change and opensource is a huge change for alot of managers.

Re:Can it Compete with Oracle or DB2?

[glwtta]

Out of curiosity - what would you consider a "Large DB"?

Re:Can it Compete with Oracle or DB2?

[Stone316]

Well, when I say large DB i'm referring to a combination of data, users and application. You could only have a couple of gigs of data but hundreds/thousands of simutaneous users, so that would be a large DB.

On the other hand you could have a relatively low number of users, say a data warehouse but have hundreds of gigs of data.

Application complexity plays a part as well. So if your asking me its hard to set ranges as to what is/isn't a large database.

Re:Can it Compete with Oracle or DB2?

[Speed Racer]

Oracle for "mission-critical"? I don't see how any one could feel comfortable putting the life of their company in the hands of another company like that.

So they should code their own RDBMS and put the life of their company in their own, likely non-RDBMS-expert hands? Maybe I missed your point but I don't see how your post make any sense?

Re:Can it Compete with Oracle or DB2?

[Speed Racer]

Small: SqlServer, PostgreSQL. (+ Oracle and DB2 if you like to toss around the $$).

Don't forget that Oracle Standard Edition One is less expensive than SQLServer.

Re:Can it Compete with Oracle or DB2?

[asdfghjklqwertyuiop]

Oracle for "mission-critical"? I don't see how any one could feel comfortable putting the life of their company in the hands of another company like that.

Welcome to the real world. We hope you enjoy your first day.

This happens quite frequently here.

Re:Can it Compete with Oracle or DB2?

[JamesKPolk]

If they use an RDBMS they have the code to, yes, they have the option of bringing an RDBMS on staff and doing custom improvements and fixes. However, they also have the option to go hire some other company to make the fixes and improvements they need.

The user of PostgreSQL has a whole market of developers to choose from. The user of Oracle has only one choice, and that company is known for taking a monopolist*'s rents from its customers.

Re:Can it Compete with Oracle or DB2?

[Stone316]

His post doesn't make sense because he doesn't understand management. In order to encourage the adoption of db's like mysql and postgresql you have to understand management. Its obvious by his comment he just doesn't understand them.

Every IT tech worker should try and at least understand management and their reasoning, how else could you expect to gain influence?

Re:Can it Compete with Oracle or DB2?

[JamesKPolk]

Oops.. I meant "bringing an RDBMS *expert* on staff"

And my footnote:

* I'm talking about economics here, not law or politics. The user of Oracle faces a monopoly of support for Oracle, not a competitive market of RDBMSes, because his data and code are tied to that database.

Re:Can it Compete with Oracle or DB2?

[JamesKPolk]

I *am* management. I don't actually want my competitors to stop wasting money on Oracle. It just amazes me that people do.

Re:Can it Compete with Oracle or DB2?

[Tailhook]

In my 8 years as an Oracle DBA I don't think i've ever seen a corrupt index. Saying that, I don't even know how you could force a index to get corrupted so I don't think were seeing all the info here.

I have. As the original poster said, there is usually a DBA involved.

You want to see Oracle corrupt something? Easy. Just install a half dozen oracle homes, from 8.0.6 through 9i, use any random version of sqlplus, sqldba that happens to be in the path to do things like create new tablespaces on whatever instance you happen to need to work on. Screw around with every init parameter there is, including undocumented parameters you read about on forums. Bounce the database a lot, and if it balks just kill the processes. Oh yeah, distributed transactions are fun too; wire up some materialized views over a international VPN connection and ignore hung transactions for a long time. Be sure to bounce the instance (using the aforementioned KILL -9) when Oracle tries to apply the transactions.

I guarantee you will witness a corrupt index in short order. If you're lucky that's all you'll see. It's a credit to Oracle that their product can withstand such ineptitude and still function.

Like you, I've never been a party to corrupting something in an Oracle instance. I have seen it, however, and I know the fools involved. That they only managed to corrupt a couple blocks and not eradicate entire tablespaces is only good fortune. Either way, 99% of "corrupt" whatever is DBA induced; Oracle is by non-amateurs, for non-amateurs and does not corrupt things willy nilly.

Re:Can it Compete with Oracle or DB2?

[tootlemonde]

Mission Critical Apps and large DB's: Oracle or DB2
Mid-Range apps/Mid-sized DB's: The above + SqlServer, PostgreSQL.

I've personally seen Postgres handle stock transaction databases of over 100 million rows with several selects per second. I'm sure others could point to even larger and more active examples.

I would suggest that the one big advantage of Oracle for large databases is that is can be optimized to much finer degree than Postgres. When there's a lot of revenue at issue then there's a good business case to be made in investing the time and resources in achieving this optimization.

In addition, Oracle is so loaded with features that if you need one and there's a business case, then cost is not a factor.

The only area where Postgres is superior to Oracle is in the ease of administration, where it is substantially easier. I don't know about the very largest applications, but for many typical database applications, the cost of administration is the largest single cost. For instance, Oracle installations are so complex that no one would set up an Oracle project where there is revenue on the line without a full-time DBA. In the Postgres world, it is quite common for a programmer to double as DBA.

Re:Can it Compete with Oracle or DB2?

[firewrought]

In my 8 years as an Oracle DBA I don't think i've ever seen a corrupt index. Saying that, I don't even know how you could force a index to get corrupted so I don't think were seeing all the info here.

FYI... try a destructive index rebuild on some tables that are being actively read/written to. (This is an Oracle 7.3 bug that bit us hard one time.)

Re:Can it Compete with Oracle or DB2?

[bestguruever]

One doesn't. What one does is try to find a company that they feel the can recoup enough money from in the event of a mission-critical failure to recover or at least bail out gracefully. This is what support contracts are all about. "Trust" in business usually boils down to liability contracts, bonding, or insurance. It all comes down to money

Re:Can it Compete with Oracle or DB2?

[JamesKPolk]

Good answer. Does anybody actually sell Oracle insurance, though?

Re:Can it Compete with Oracle or DB2?

[kpharmer]

Postgresql is cool, but it isn't ready to compete directly with Oracle or DB2 on large databases yet.

For example, lets say you've got a large reporting application...

1. DB2 can leverage all CPUs on a single CPU - and split the workload for a query across 2,4,8 or more CPUs.

2. you can partition data across multiple servers. This allows you to spread a single 400 gbyte table across a dozen fast 2-4 way servers. This allows you to leverage cheaper hardware (commodity servers vs sun e10ks), and with relatively low cost be able to throw 40 cpus at a query.

3. you can also use multi-dimensional clustering or union-all views (similar to Oracle partitioning). This allows you to avoid scanning data that isn't in the partition you're interested in - often dropping 95% of the workload.

Note that all three of the above can be used in conjunction - so you can create a database with 40+ CPUs that allows you to focus on just the 20 million rows of the 400 million in that table. Postgresql (or mysql) on the other hand will be slogging through all 400 million rows on a 4-way. No comparison whatsoever in performance at this level. They'll eventually get there, but they've got a long way to go.

Of course, most people don't have this kind of performance-challenge, but most of this capability is useful even at the 10 gbyte database size. In the meanwhile, postgresql is a great little database for quite a few applications: and it does a good job of supporting ansi sql, it is generally simple to port from postgresql to oracle, db2, etc.

Re:Can it Compete with Oracle or DB2?

[platypus]

And you know what? If people would license Oracle only for Mission Critical Apps and large DBs, Oracle would be bankrupt tomorrow.
At least if I extrapolate what I'm seeing in the enterprise, Oracle's total revenue would go down 80%. That's why they were already FUDing around in 2002 when the decision about the .org domains was made (hardly something which would justify paying $$$ to Oracle).

Re:Can it Compete with Oracle or DB2?

[Anonymous Coward]

This is the fakest answer in all of IT.

We had problems with MS IIS servers at a big financial services company - did they help us? No
Did we have the premium "MS fucked us so hard, we got a plaque for it" service/support contract? Yes.
Did we sue them? No fucking way.

Has any major software company ever been sued over the bugs or failures or problems caused by their software?

If it has happened, it is nowhere near the rate of problems that their software has caused.

I'd like to slap the shit out of the next idiot IT manager I hear say this kind of crap - we can't use that, we need to have someone we can sue...

Yeah, right except what really happens is that after their crappy software doesn't function the way it was promised, I get to work a holiday weekend to fix it while you golf with the assholes who sold it to us.
Same goes for EDS, Accent>ure, KPMG and the rest of those money holes. Gore (as in Gore-tex) was the only one who had the guts to say what every corporate IT slave knows - the emperors of the corporate systems world have no clothes

Re:Can it Compete with Oracle or DB2?

[bestguruever]

Yes, most times corruption is caused by a dba or sysadmin, but I have also seen it caused by Oracle itself. This was early in the 8.0 series and a patch resolved it. To Oracle's credit, they had a hot patch out the next day.

Re:Can it Compete with Oracle or DB2?

[Stone316]

Whether or not you could actually get money out of an rdbms vendor such as oracle in the event of a catastrophic failure doesn't matter. Its about coverying your ass. 99.9% of companies don't want to maintain rdbms software in order to use it, so they have to buy from someone. There's nothing really to argue here, its simple economics as you say.

I've read a few of your posts on this and I basically agree with your stance, the problem most people in management don't think along the same lines.

Re:Can it Compete with Oracle or DB2?

[JamesKPolk]

Sounds like Oracle makes money off of middle managers who get paid whether the company succeeds or fails then.

What an interesting world.

Re:Can it Compete with Oracle or DB2?

[johnnyb]

PostgreSQL supports union-all views.

For (1) and (2), I've never seen it. I worked w/ Travelocity and Cheaptickets.com and numerous other websites at EDS, and none of them used those features. Most of them were simply multiprocessor CPU boxes with lots of memory. I'm sure there are several cases where it's needed, but I don't think it's as ubiquitous as you indicate.

Re:Can it Compete with Oracle or DB2?

[johnnyb]

Depends on your usage of the term "mission-critical". The only thing that would keep me from using PostgreSQL 8 in a production house when it comes out is data set size, not the value of the data. In fact, I'd be more likely to use PostgreSQL over Oracle if the value of the data was the only factor. I wouldn't use PG7 w/ mission-critical data because of lack of PITR. You can also automate PITR recovery into a failover cluster pretty easily.

Re:Can it Compete with Oracle or DB2?

[kpharmer]

> PostgreSQL supports union-all views.

I didn't think postgresql allowed inserts to the view itself though. If it doesn't, then you'll have a much more complex application - since it always needs to know which table to insert to.

> For (1) and (2), I've never seen it. I worked w/ Travelocity and Cheaptickets.com and numerous
> other websites at EDS, and none of them used those features. Most of them were simply
> multiprocessor CPU boxes with lots of memory. I'm sure there are several cases where it's
> needed, but I don't think it's as ubiquitous as you indicate.

No, not ubiquitious. But it has been common in data warehousing now for almost ten years. Teradata then Informix became famous for their parallelism & partitioning capabilities based on it by mid-90s. Outside of data warehousing, these capabilities are commonly used in any large reporting systems, (r)olap, fraud detection, any scoring systems (especially balanced scorecard systems), clickstream analysis, etc.

The main reason to avoid the clustering form of parallelism (used in teradata, informix, and db2) is that the database licensing can get expensive. The main reason to avoid some of the parallelism capabilities within oracle is that the database AND hardware can get expensive. But that still leaves you with several options, take db2 for example:
- spend $50k (list) on a fast 4-way
- spend $30k (list) on db2 workgroup edition
- run parallel queries
- cluster your data with multi-dimensional clustering and/or union-all views.
A solution like this is about twice as expensive to purchase as postgresql but no extra cost over a plain commercial solution. And loads data at speeds like 50,000 rows / second - and leverages both parallelism and partitioning in query responses.

Neither Postgresql nor mysql can match the performance of that solution without relying upon multiple machines and manual partitioning. Both the labor and hardware costs of this alternative approach far outstrip the database licensing cost of using oracle or db2.

Basically, anyone who isn't leveraging these capabilities (and other similar ones) in large commercial databases these days either can't afford to change a legacy application, doesn't have the (relatively simple) database skills to pull it off, or really should be using postgresql...

And of course there are good reasons to use postgresql in this space - say you've got a start-up and can't afford the initial commercial licensing investment. That's fine, we just shouldn't kid anyone that postgresql at the high-end is very competitive (yet).

Re:Can it Compete with Oracle or DB2?

[johnnyb]

"I didn't think postgresql allowed inserts to the view itself though. If it doesn't, then you'll have a much more complex application - since it always needs to know which table to insert to."

You can insert into views, you just have to write rewrite rules to do it, which are pretty simple. It's actually pretty simple, and pretty darn powerful, especially since it doesn't depend on the DBMS knowing how to work with your specific view (i.e. - you can do inserts on views having advanced joins). Less automatic, more control.

http://www.postgresql.org/docs/7.4/static/rules. ht ml

As for parallel Oracle, it doesn't really live up to its hype. Even failover clustering on Oracle sucks (I'm speaking from 2 years ago, so it may have improved - however, people were using Oracle for mission-critical applications 2 years ago, so I think my argument still stands).

I agree that for VERY LARGE data sets, PostgreSQL still lacks, but most of the industry isn't working with VERY LARGE sets -- that's basically a niche market.

Re:Can it Compete with Oracle or DB2?

[jadavis]

PostgreSQL has a pretty darn good track record, and the only thing that can make it better is time and new installations, but your point is valid.

I would say that #2 is correct, except it has little or nothing to do with open source. It's just the fact the PostgreSQL is backed by small companies, and is unknown to many people. If you as an employee go with anyone other than the perceived market leader (in this case Oracle), than part of the responsibility for failure lies on you. So it has more to do with the market perception of a vendor. Anyone who chooses another product might be accused of "cheaping out" or something similar.

Consider Apache, the clear market leader. Nobody is accused of cheaping out when they download the source of apache for free.

Re:Can it Compete with Oracle or DB2?

[kpharmer]

> As for parallel Oracle, it doesn't really live up to its hype. Even failover clustering on Oracle
> sucks (I'm speaking from 2 years ago, so it may have improved - however, people were using Oracle
> for mission-critical applications 2 years ago, so I think my argument still stands).

Well, oracle ops/rac has always been an extremely complex product - one that most folks have avoided for good reasons IMHO. But that's not what I'm talking about here - I'm just talking plain/vanilla partitioning & parallelism. Well, the parallelism comes free with their standard & higher packages, you pay an extra $15k or so for partitioning. But it's very simple & easy to use, and most oracle applications these days take advantage of it. It's one of the chief reasons to use oracle these days, since that part of the product really sings.

> I agree that for VERY LARGE data sets, PostgreSQL still lacks, but most of the industry
> isn't working with VERY LARGE sets -- that's basically a niche market.

The only reason that most people aren't using very large volumes of data - is that they haven't yet figured out the value of analytics and how to preprocess data. For those that do understand this what data can do know that a small amount of transactional data will balloon in size once you set it up for analytical purposes. So, that 5 gbytes of data in your transactional database can easily consume 100+ gbytes in a warehouse or data mart.

Why so large? You've performed heavy transformations on the data to cleanse, integrate, summarize, and cleanse it. Then you kept every single version so that you have a complete historical recond. Once you've done this the data may be huge - but now you can finally start answering the basic business questions with it.

And maybe get into that profitable niche - where you're competing with companies that know exactly where they're making & loosing money...

Re:Can it Compete with Oracle or DB2?

[TheLink]

Hey if you're The Boss, that's a big difference. You should check if your underlings are tempted to.

It's pretty obvious actually. There are actually smart people who do that. They are not paying for consultancy, service, support or for the million or so features. They are spending Company Money so they can blame someone else later and save their own jobs. They are actually capable of doing everything themselves (probably better than the consultants), but why should they do it and risk their pension, jobs, bonuses etc?

There are many people who would gladly spend company/gov money to increase the chances that the company/gov keeps paying them, even if it is not best for the company overall. After all many companies (and bosses) don't give a damn about the employees anyway, so they're just returning the favour.

The challenge for The Boss is to create a culture that makes that behaviour rare.

Re:Can it Compete with Oracle or DB2?

[Pete]

You say "If a critical revenue generating db goes down because of a bug a manager needs to be able to point their finger." I thought Paul Graham put it perfectly:

This is the kind of possibility that the pointy-haired boss doesn't even want to think about. And so most of them don't. Because, you know, when it comes down to it, the pointy-haired boss doesn't mind if his company gets their ass kicked, so long as no one can prove it's his fault. The safest plan for him personally is to stick close to the center of the herd.

Within large organizations, the phrase used to describe this approach is "industry best practice." Its purpose is to shield the pointy-haired boss from responsibility: if he chooses something that is "industry best practice," and the company loses, he can't be blamed. He didn't choose, the industry did.

I believe this term was originally used to describe accounting methods and so on. What it means, roughly, is don't do anything weird. And in accounting that's probably a good idea. The terms "cutting-edge" and "accounting" do not sound good together. But when you import this criterion into decisions about technology, you start to get the wrong answers.

(under the section "A Recipe")

Mind you, that kind of criterion is probably still quite valid in decisions about database technology - Graham was mainly discussing programming language technology in the aforementioned article.

But still, it's an interesting (albeit depressing) perspective - that the highest priority for a boss (whether pointy-haired or not) is to avoid blame for failure; not necessarily to avoid failure itself *wry grin*.

(BTW: I have no dispute whatsoever with your first point :-))

Let the flames begin

[Gothmolly]

(MySQL|MSSQL|Oracle|DB2) is (cheaper|better|faster|ACID-compliant|'1337) and Postgres is (slower|buggier|missing features|has broken features|sucky)!!!

Re:Let the flames begin

[ckaminski]

I remember when you could map a drive from Windows to \\ftp.microsoft.com\data
</quote>

I remember that too... seems it stopped mid '96 or so. Wow. Thanks for the flashback.

Re:Here's the thing

[Anonymous Coward]

Yeah (what they said) and comparing dBase to
Postgres is like comparing a Toyota full of
muslims to an M1 Abrhams..

Database Questions

[digitaltraveller]

Is there a database (more heavyweight than SQLite) that allows you to specify where in the file system it keeps it's records?

Has any project ever built a IOSLAVE/VFS/LUFS filesystem bridge to a relational database?

Re:Database Questions

[LWATCDR]

Yes Postgres does.
Set $PGDATA to where you want the files stored.

Re:Database Questions

[alex_tibbles]

Berkely DB AKA libdbX.

Re:Database Questions

[ciroknight]

To the second question: Nobody in the open source world.

Microsoft's on the way to doing it, and there was a small project that existed for a few months that allowed GNOME to access a database as a file system (It was very nasty; involved a kernel patch (a CORBA orb) that nobody was too happy about, so the project never took off.)

I've thought about the problem a few times. It requires the kernel pass information back to user space, unless the database was actually incorporated into kernel space (and that won't blow over well for a number of reasons). Passing the data back to user space requires a messaging system. The problem is, there are very few messaging systems out there designed specifically for kernelspace-userspace communication. CORBA was one developers answer; my answer would to be to ground up a protocol (because I feel that a network messaging solution, i.e. TCP/IP, can't be secured well enough in the long run).

Lastly, a daemon needs to exist to listen to the calls from the kernel and interpret them into SQL. This could be built into the kernel itself, but once again you have to question the security of the kernel building an SQL query that would go directly to the database server. Also, one of the better parts about this daemon would be metadata extraction; since the daemon is virtually transparent to both the user and the database server, the metadata can be completely ripped from the data and stored in a seperate table to allow for much faster, more optimised searches. EXIF Tags can be copied from JPEGs, ID3 tags copied from MP3s, etc.

Ideally, the daemon would be pluggable, allowing for anyone's metadata extension to be added after compiliation, but I believe that it's important to have a functional system before having a featureful system.

If you'd like to talk more about it, I'm really open to the idea of finally having an SQL-based file system. A relational database file system is the future; if we get there before Windows, we can add yet another example of the speed of open source development.

Re:Database Questions

[eamacnaghten]

I would have thought that this is perfectly feasible and should not be too difficult with lufs (Web site here).

It should not be too difficult to create an appropriate lufs fss module to do the trick.

Re:Database Questions

[MattRog]

I agree that a RDBMS as a file system is the way to go -- but we'd have to invent a true RDBMS first. :)

I've been waiting for the antiquated concept of a 'file' to go away for a long time now. The 'file' concept exposes the underlying physical construct to the user for no particularly good reason. Nowadays this limitation is foisted upon users simply because "that's the way we've always done it" which is totally contrary to the way we think of logical constructs: documents, emails, contacts, etc. and there's no reason not to store those things in a RDBMS (unless you're a company that enjoys proprietary file format lock-in).

I think what would have to occur to really show the power would be an OS that offers a true RDBMS to all applications. When you really think about it, though, current operating systems are a form of DBMS except without some of the DBMS features that we all know and love -- constraints, domains, query language etc. (some of these are currently emulated via application programs).

What is exciting to geeks like me is that a true RDBMS would be an OS unto itself -- and you'd just have various applications hanging off of it to do the UI.

Taking it to the furthest level applications would no longer necessarily be these big monolithic, closed-source juggernauts but merely constraints and 'business logic' that plug into the user's DBMS and are interpreted via the UI. It's the penultimate "information wants to be free" system.

It is an incredibly powerful concept which would require re-thinking virtually everything that we do nowadays for application development: Count me "in" on possibly the greatest computing revolution since we invented PCs (well and probably the internet, too).

And as an aside, the above concepts are slowly being "reinvented" by the XML crowd (e.g. XUL, WinFS, etc.). It's a shame we've had the framework for a good 30-40 years but no one has really thought about it until now and instead choose the flavor-of-the-month instead of the sound foundation that the RDBMS provides. It's a damn shame, because the XML-based products will fail just like their ancestors (IBM IMS and other hierarchic/network products) did. This, of course, will destroy any products which do correctly implement it through guilt-by-association.

Of course, this idea is not without fault: such a system would be a non-trivial undertaking and probably would not even succeed in today's marketplace due to practitioner ignorance, corporate greed/power, and plain old end-user inertia. Still, it's one of those things that I wish I could do given unlimited money and plenty of talented software engineers.

Re:Database Questions

[ciroknight]

I will look into this. Thanks for the link!

Re:Database Questions

[mewphobia]

The problem is, there are very few messaging systems out there designed specifically for kernelspace-userspace communication.

How about making a dummy NFS server and serving the database that way? NFS is pretty portable too, which would avoid locking it into a particular OS.

my answer would to be to ground up a protocol (because I feel that a network messaging solution, i.e. TCP/IP, can't be secured well enough in the long run).

Just interested - what makes you say this? What is your reasoning? I just can't think of any reasons personally, so i'm assuming there is something i'm overlooking?

Re:Database Questions

[MikeBabcock]

To the second question, the namespace of SQL is much more complex than a filesystem hierarchy can handle. If you don't believe me, you haven't done enough SQL work.

If you want a filesystem that is really a database back-end and works as fast as one, try using ReiserFS and reading the docs, especially v4.

Re:Database Questions

[MikeBabcock]

You might want to look at this slashdot story about the subject, or as it mentions, this FAQ about the Newton OS (yes, the Apple Newton).

The Newton was very revolutionary in file storage; read and weep.

Native Win support - awesome

[Stone316]

I think this was a major stumbling block for postgreSQL's adoption. I'd love to use it here at work for some small projects but unfortunately were getting more and more windows servers. PITR recovery is a must for any production database these days. Maybe there are some 3rd party packages but I don't think mysql supports this yet. This is great news and I hope it spurs a new round of adoption for pgsql!

Re:Native Win support - awesome

[Stone316]

Yeah, your correct but unfortunately most saw that as a hack and you had to have half a clue to get it working. ie, its alot harder than double clicking on setup. :)

Re:Native Win support - awesome

[Anonymous Coward]

MySQL supports full PITR in version 4.1, which I believe will be Production end of Sept. Last I heard they were integrating PITR management into their GUI tool

Re:Native Win support - awesome

[GooberToo]

Ya but, even the developers do not consider cygwin to be of "production" quality. Rather, they consider the cygwin port to be something worth playing and developing with, under the assumption your productoin environment will NOT be a cygwin host.

That's a pretty dumb reason.

[Anonymous Coward]

You have a couple of full text indexing options with postgres, just go into the contrib directory and install one. Wow, that was tough.

Re:You can save a few clicks...and read it here.

[dave at hostwerks]

Apologies to Ziff-Davis...

From: "Marc G. Fournier"
To: pgsql-announce ( at ) postgresql ( dot ) org
Subject: PostgreSQL 8.0.0 Officially Goes Beta
Date: Mon, 9 Aug 2004 21:36:52 -0300 (ADT)

After almost 9 months of development, the PostgreSQL Global Development Group is proud to announce that development on PostgreSQL 8.0.0 has now finished, and is ready for some serious testing.

For those wondering about the 8.0.0 designation on this release, there have been several *very* large features included in this release that we felt warranted the jump. As with all of our releases, we aim to have this one as rock solid as possible, but *at least* one of the features added to this release involved such changes that may warrant a bit extra testing post-release before deploying it in production.

Although the list of new features in 8.0.0 is extensive, with both SMB (Win32 Native Support) and Enterprise (Nested Transactions and Point in Time Recory) features being added, there is one thing that hasn't been included as part of the core distribution, and that is a Windows Installer, which can be found at:
http://pgfoundry.org/projects/pginstaller

For a complete list of changes/improvements since 7.4.0 was released, please see:
http://developer.postgresql.org/beta-history.txt

That said, and without further ado, Beta 1 is currently available for download on all mirrors:
http://www.postgresql.org/mirrors-ftp.html

And, thanks to David Fetter, the Beta is also available via BitTorrent at:
http://bt.postgresql.org

As with all releases, the success of this release falls in the your hands ... to go from Beta -> Release, we need as many people out there to put it through her paces as possible, on as many platforms as possible. We urge anyone, and everyone, to download a copy and run her through her regression tests, and report any/all problems, and bugs, to
pgsql-bugs ( at ) postgresql ( dot ) org

The more bugs we can find, and eliminate, during Beta, the more successful the Release will be...

On behalf of all of the developers, Happy Bug Hunting ...

Windows support

[Anonymous Coward]

I think Windows support is the only reason MySQL is so popular. PostgreSQL has always been ahead of MySQL in terms of everything but speed. But everybody is familiar with MySQL because, when you want to pick something up, you pick the one that will work with your system, and most people are on Windows.

Up until this point, you have had to install hundreds of MB of cygwin to get PostgreSQL to work on Windows. I think it's a little late to usurp MySQL's market share, especially as MySQL is now entrenched in the cheap web hosting market, but at least PostgreSQL might get the respect it deserves.

Re:Windows support

[matchboy]

Cheap POSTGRESQL Web Hosting. ;-) http://www.planetargon.com/

Re:Windows support

[Khomar]

I think Windows support is the only reason MySQL is so popular...ahead of MySQL in terms of everything but speed. (emphasis added)

You cannot overlook the speed aspect as well. For many, many databases, the special features of foreign keys, stored procedures, etc. are not required. I have worked extensively with Oracle databases in the past, so I am well aware of the advantanges of these advanced features. However, in my current company, there has been no need. Most of our databases, while large, consist of a very small number of tables. The vast majority of our searches are performed on a single table, and these searches are completely optimized for speed. Stored procedures will not help in these scenarios, and foreign keys are not needed. I imagine there are a lot of web sites and applications that have the same characteristics.

I will not deny that the Windows support is huge, but it is not the only factor in MySQL's court. Speed is a huge issue, especially in the database world.

Re:Windows support

[dpb]

From my POV the MySQL -> PostgreSQL migration isn't so important as the Oracle -> PostgreSQL migration. Having been a part of the migration team porting the Red Hat CMS from Oracle to PostgreSQL I can say that even when we started with PG 7.2 it was more than capable as an oracle replacement. With improved optimizer performance in 7.3 and now even better Pl/SQL compatability, native Win32, savepoints, PITR and replication available, there is even less reason to use Oracle. I think PG will be able to take a large chunk out of Oracle in the Low & Mid-range markets.

Re:Windows support

[GooberToo]

PostgreSQL has always been ahead of MySQL in terms of everything but speed.

That blanket statement is simply not true. Most people think that a single user with a single query is a measure of speed. For most applications, it is not. And bluntly, this is exactly where MySQL's performance advantage starts and stops. MySQL simply does not scale nearly as well as just about any other RDBMS you'll find, including PostgreSQL.

Granted, there are still some corner cases where I'm sure MySQL is faster, especially when you're not using ACID compliant tables, but make no bones about it, PostgreSQL is much faster than most people realize and MySQL is much, much slower than is commonly believed.

Re:Windows support

[GooberToo]

I agree that speed can be a huge issue in the DB world. Just the same, when it comes to scalability, MySQL rarely stacks up well. Generally speaking, unless you only ever run a single query at any given time, then you'll be better served by another RDBMS. On average, MySQL really is a dog in "real world" environments.

Generally speaking, MySQL's performance is right up there is the great urban myths. Sure, it's wicked fast for a single user with a single query. Add multiple users, multiple queries, or worse, non-select only activity against the queried tables, and MySQL performs like a dog compared to most other RDBMS.

IMO, MySQL got it's performance myth because it was compared to PostgreSQL, back in the early and mid 90's, when PostgreSQL really was slow. These days, PostgreSQL is wicked fast and highly scalable. MySQL, on the other hand, is wicked fast for a single user running a single query whereby, the activity is select only. Attempt to make it scale and MySQL generally falls to the last position when comparing to Oracle, MS SQL, Informix, DB2, and PostgreSQL.

So you're right, speed can be a big thing, just make sure you understand where, why and how, you're getting that performance. Chances are, you're not getting anywhere near the performance you think you are with MySQL.

* Note that I have liberal use of "generally" above. This is because there is no single best RDBMS. PostgreSQL is not always the best tool for the job. Neither is Oracle or MSSQL for that matter. Just the same, use the best tool for the job. Chances are, if you're using MySQL, then odds are, it's not the best tool for the job. Period.

Re:Windows support

[JJahn]

MySQL is fast when you are using MyISAM tables, but when you use InnoDB or BDB tables to get transaction support, it is no faster and probably quite a bit slower than PostgreSQL.

Quite frankly, any application that does inserts or updates with more than one user at a time needs transactions. So MySQL is only faster in very limited applications. For the rest use PostgreSQL, or one of the commercial databases (Oracle, DB2, etc.) depending on your needs.

Re:Windows support

[aralin]

I will not deny that the Windows support is huge, but it is not the only factor in MySQL's court. Speed is a huge issue, especially in the database world.

I can agree with you, if you want to get a lousy job done fast, then MySQL is exactly that type of DBMS you pick. I mean, if you have few, mostly not conflicting, updates and mostly just need fast query, you will be fine.

On the other hand, if you want a full scale, acid compliant, transaction oriented DBMS, and your priority is many updates and fewer queries, and you have accent on quality of your data, you will pick PostgresSQL.

Many websites fall into the first category. But for example large scale email system would be much better off with postgress. Also, you can have a read-only query database on MySQL and read/write database on PostgresSQL if you want to get the best of both worlds.

Re:Windows support

[sqlgeek]

I regret to inform you that MySQL is actually not faster than Oracle.

MySQL cannot partition data and then either: 1. eliminate partitions and only perform i/o on the relevant one(s), 2. process against all partitions in parallel.

MySQL cannot maintain aggregated images of your data an re-write ad-hoc queries to use the aggregates.

MySQL cannot store metadata that will tell it that postal code implies county, and hence a query that wants data aggregated to the county can use the image of data at postal code granularity. (It is not true that postal code implies county, but you get my point).

MySQL cannot perform an efficient hash-join to save its life (even though a properly designed hash join is mathematically superior in a great many cases) -- and so it falls back on sort-merge regularly.

MySQL cannot re-write NOT IN or NOT EXISTS clauses as merge anti-joins. Neither can it re-write IN or EXISTS clauses as merge outer-joins.

I have to get back to work now, but surely you get the idea.

Cheers,
Scott

Re:Windows support

[Khomar]

I understand and agree with you completely, but my post had nothing to do with comparing MySQL with Oracle. The point was that there was more than just native Windows support that drove people to favor MySQL over PostgreSQL.

Actually, PostgreSQL is starting to look very inviting -- especially as the performance gap is narrowing. However, a switch will have to wait at least a year or two until the native Windows version is stable and supported (yes, we run a Windows shop... *sigh*).

Re:Windows support

[johnnyb]

If your main concern is speed you should not be using a relational database. If your main concern is data integrity, you should not be using MySQL.

Re:Windows support

[davegaramond]

For many, many databases, the special features of foreign keys, stored procedures, etc. are not required.

Foreign keys are _special features_? Not needed? Argh! The only kind of people who say something like this are: a) idiots; b) people who have been brainwashed by MySQL marketing. And to think that you said you have extensive experience with Oracle... (shudder).

Even with only two tables, most databases need foreign keys to maintain data integrity.

Re:Windows support

[Frank T. Lofaro Jr.]

And if speed is that important to you then you will be using top of the line hardware and/or using Oracle as your database.

full text search

[jbellis]

I guessed you missed OpenFTS, which has been out for a couple years now.

Re:full text search

[flyfishin]

The also missed tsearch2 http://www.sai.msu.su/~megera/postgres/gist/tsearc h/V2/

Yes

[jbellis]

... which you would know if you'd RTFA :P

Re:*cough* bullshit *cough*

[MarsDefenseMinister]

Doesn't even know about dBase III. That thing stopped development not 10 years ago, but 20 years ago. Maybe the guy was working so hard on dBase III that for the last 10 years of the project he didn't notice for a decade that all his fellow programmers weren't coming to work, his paychecks weren't arriving, Ashton-Tate was no longer in business, and a new company had moved into the office space that he was working in.

what about distributed transactions?

[RelliK]

As far as I'm concerned, the only truly missing feature is distributed transactions. Are there any plans to add them any time soon?

Re:what about distributed transactions?

[rtaylor]

There have been attempts at adding 2 phase commit which would work with Java applications -- these were deemed incomplete, unsafe or needing work.

It is likely work will continue on this feature, though don't expect a replication system to be based on this as there are a number of problems within the spec itself (failure modes that leave the system essentially out of order).

See the pgsql-hackers archives (Google Groups) for details.

Re:what about distributed transactions?

[GooberToo]

As far as I'm concerned, the only truly missing feature is distributed transactions. Are there any plans to add them any time soon?

Yes. IIRC, in the last release, the protocol has been expanded to allow for this concept. Nested transactions were also required. These are now in place. I'd guess that two phase commits and distributed transactions (sometime after 2pc) will follow sometime after the 8.0 series stablizes.

In other words, it's on their radar but I don't know what priority they are placing on it.

Re:what about distributed transactions?

[davegaramond]

The way I understand it, someone has been maintaining a 2PC patch (perhaps even since the 7.3.x days?) However, there seems to be little interest in the patch. It didn't get reviewed for long. And finally, when it's about to be integrated in the later phase of going to 8.0, the nested transaction patch got in. This conflicts heavily with 2PC. The 2PC patch maintainer didn't have time to adjust the patch, and so 2PC didn't make it to 8.0. Instead, nested transaction does.

2PC will probably be integrated in 8.1?

So if you really need 2PC, join the pgsql-hackers mailing list and test/comment on the 2PC patch. More requested features will get in first. That's the way it works.

Some nice performance enhancements

[grunt107]

The cross-datatype comparison indexing is very important (ex. '1' = 1), as well as index usage on OR clauses. Both of these before would cause full table scans, which is very costly on VLDBs (Very Large DataBases).
The improvement to the VACUUM I/O processor is important for Postgre to be used on a multi-app server. The 'play nice' feature will allow one server to house the DB AND web servers (albeit at a performance hit to the DB processes).
Overall, a nice improvement.

Re:Some nice performance enhancements

[johnnyb]

Just an FYI, casting can be used in previous versions to prevent this. For example:

'1' = integer_column

can be modified to use an index by doing

'1'::int = integer_column

The real confusing bit was coming where int8's and int4's were not using indexes, so

1 = int8_column

was not using an index, but

1::int8 = int8_column

was using the index. I previous just did copious casting, but now I don't have to. Yeah!

Synchronous Replication?

[Anonymous Coward]

The move to have a Win32 Native executable was to allow PostgreSQL to to compete with MySQL.

Synchronous replication is the only feature that will allow PostgreSQL to compete with Oracle. Mission critical backends need systems that stay entirely in-sync and if any one of the nodes fail, the system can fail-over to one of the other db's.

Master-slave just isn't sufficient.

Re:Synchronous Replication?

[rtaylor]

Master-Slave is a prerequisite to synchronous replication, as that is the method used to "catch-up" when you fall behind or there is a communication issue or to add a new slave to an already active cluster.

The second stage of Slony is to implement the above once they're satisfied that master-slave is fully functioning and bug-free.

Thank you Fujitsu And Afilias.

[ron_ivi]

I just wanted to say thanks to Fujitsu for helping pay for this

Fujitsu foots the bill for new PostgreSQL database features
Thursday July 01, 2004 (07:04 AM GMT)
...
Berkus described the new Fujitsu-formed features as follows:

* Tablespaces is a means of partitioning large amounts of data easily and efficiently on separate storage devices, a key requirement for maintaining PostgreSQL's performance on large databases in the hundreds of gigabytes, and terabyte range;
* Nested Transactions allows application developers a very granular level of control over database commits and rollbacks, which is particularly significant for maintaining data integrity and porting applications from other database platforms;
* Robust support for stored procedures in Java that exceeds the goals of the SQLJ specification in the ANSI SQL99 standard.

And thanks to Afilias (the guys who run the .org domain) - from the same article:

More recently, .org and .info domain registry company Afilias has sponsored developer Jan Wieck to work full time on developing a new, enterprise-class replication system for PostgreSQL called Slony-I, to be presented next month at OSCON in Portland, Ore.

Re:Thank you Fujitsu And Afilias.

[killjoe]

DOes anybody know if the new replication will support merge and multi-master replications. Even ring replication a-la mysql would be better then one way master-slave replication.

Re:Thank you Fujitsu And Afilias.

[ttfkam]

No more than Perl and Tcl are prerequisites. As with all programming language support in PostgreSQL, you compile what you want in and exclude what you don't want.

Calm down. Breathe.

Re:Thank you Fujitsu And Afilias.

[Sunspire]

...a new, enterprise-class replication system for PostgreSQL called Slony-I

PostgreSQL is good from a technical point of view, but they could use someone with some very basic PR and marketing skills on their team. A new website and better documentation wouldn't hurt either.

Re:Thank you Fujitsu And Afilias.

[Earlybird]

• More recently, .org and .info domain registry company Afilias has sponsored developer Jan Wieck to work full time on developing a new, enterprise-class replication system for PostgreSQL called Slony-I, to be presented next month at OSCON in Portland, Ore.

Except Slony-I is a single-master/multiple-slave replication system -- hardly "enterprise-class".

What's slightly noteworthy about Slony is that it has "switchover" support. If the master fails, one of the slaves become the master.

Of course, since Slony's replication is asynchronous, it means that at the point of switchover, slaves might not have received all pending changes, thus becoming out of sync with the master. Unfortunately, Slony doesn't really address this problem.

The design overview is useful reading, if rather confusing; the writer's first language is probably not English.

Re:Thank you Fujitsu And Afilias.

[boneshintai]

Strongly disagree.

The PostgreSQL website is easy to navigate and easy on the eyes. Links to anything most users will need -- downloads, docs, and search -- are right at the top of every page.

The MySQL website uses microfonts, and good luck finding documentation without resorting to google site:mysql.com; there's links to it on some pages and not others, and most notably not on the front page. The MySQL website is, apparently, trying to be oracle.com, only worse.

The difference is pretty simple: The postgresql developers are trying to write a database server. Selling it is not their concern, nor is marketing it to the world: they let others (mostly, their users) do that while they focus on development. MySQL, on the other hand, is a business: they need customers to survive, and sometimes the technology takes a back seat to the business side of things.

Re:Thank you Fujitsu And Afilias.

[jocmaff]

While I do agree the MySQL.com site could have better navigaion, it is still cake to find anything you want if you know the name...
example: just type into your browser "mysql.com/interval" and you are instantly transported to the proper documentation discussing SELECTING and using INTERVAL. Another great feature that I believe was implemented from the guys at PHP.net (learned this at the MySQL conference last year)

Re:Thank you Fujitsu And Afilias.

[goonerw]

The MySQL website uses microfonts, and good luck finding documentation without resorting to google site:mysql.com; there's links to it on some pages and not others, and most notably not on the front page. The MySQL website is, apparently, trying to be oracle.com, only worse.

Hell, not even Microsoft have a link to their documentation on their front page!! Let's whinge about them too.

Not everyone interested in MySQL is interested in its documentation, which is a) conveniently located under the Developer Zone tab and b) www.mysql.org redirects to dev.mysql.com anyway.

Shock, horror if a web developer wants to keep the site clean and organised.

Re:Thank you Fujitsu And Afilias.

[erlando]

Better documentation? What?!

Honestly, have you ever worked with PostgreSQL? I have for the past year, and the docs are excellent. There's a few odds and ends, but I have never seen any docs without them.

Re:Thank you Fujitsu And Afilias.

[leandrod]

> DOes anybody know if the new replication will support merge and multi-master replications

Not yet. Slony II is planned to.

Now what I really crave for are UPDATEABLE MATERIALIZED VIEWS.

Re:Thank you Fujitsu And Afilias.

[wieck]

Can you point to an asynchronous replication system for ANY database that does address that problem better that Slony-I does?

Sincerely, Jan

as TFA tells you,

[jbellis]

"Tablespaces allow administrators to select the file systems used for storage of databases, schemas, tables, or indexes."

Re:Two things

[MattRog]

"what does a wheel-barrow... have... to do with DBs??"

Nothing. It's even an incorrect category. A database is the data you collect. PostgreSQL is a DataBase Management System (DBMS). They should rename the category to reflect this.

Newbie Question - UI Tool

[kannibal_klown]

I'm big into Oracle, and use MySQL on my desktop for test databases. But the lack of views and such is driving me crazy.

What do you recommend for Windows software to handle PostgreSql?

I'm looking along the lines of something like SQL Navigator or Toad, but free.

Something to perform queries (with syntax coloring) and return results in nice tables, as well as other more administrative tasks.

Re:Newbie Question - UI Tool

[brunson]

TOra Rocks! Does just about everything TOAD does and more, plus it's free.

Oh, right, you're on Windows... Maybe you can compile it under Cygwin.

Re:Newbie Question - UI Tool

[donutz]

Probably not of interest to you, but certainly worth mentioning is phpPgAdmin, a web based PostgreSQL administration tool. Works well for me.

Re:Newbie Question - UI Tool

[rycamor]

Try PGAdmin III.

Re:Newbie Question - UI Tool

[woozlewuzzle]

Take a look at Aqua Data Studio (www.aquafold.com). It is written in java, runs on Windows, Linux and Mac OS X and has connectivity to many database systems. Not great as an admin tool yet but good for development.

Re:Newbie Question - UI Tool

[LemonFire]

I use DB Visualizer here at work, we run Oracle, mySQL, Postgres, and MS-SQL and it's nice to have a tool that will work the same way across these databases.

Its written in Java and works pretty well.
Its a little bit heavy on the memory side, but it will connect to just about any database over standard JDBC connections.

They do have a free version.

http://www.minq.se/products/dbvis/

Re:Newbie Question - UI Tool

[caulfield]

What do you recommend for Windows software to handle PostgreSql?

Myself, I use Eclipse to do all my coding, and I find the Quantum DB plugin to Eclipse to be awesome. Because we use Oracle, mysql, and Postgres, it's handy to have one piece of software handle all the DBs (so long as you have the right JDBC driver.

Re:Newbie Question - UI Tool

[counterplex]

If you don't mind Java GUIs, Db Visualizer is pretty slick.

Re:Newbie Question - UI Tool

[Alex Blume]

You could also check out my database tool DBInspect. No SQL syntax coloring (yet), but it's free, supports many databases out of the box, and has some nice features I've not seen in other tools.

Re:Newbie Question - UI Tool

[mabinogi]

The code's GPL, and even if new versions are closed the current (GPLd) version is still excellent.

And the grandparent might have been trying to be funny about Windows, but as TOra is built with QT, there is a native Windows version available. Though that version is only free (cost) for non comercial use. I believe the licenses are pretty cheap though.

MySQL faster than postgresql?

[jbellis]

Maybe if you only have one client querying the DB, and you're not using any features newer than SQL-89. Maybe.

Back in the 6.x days postgresql had a well-deserved reputation for being, well, slow. That was back in the '90s, though.

Re:MySQL faster than postgresql?

[Betcour]

AFAIK, version 7.0 came out in 2000. By that time, PHP and MySQL were already a successful couple.

Re:MySQL faster than postgresql?

[Anthony Boyd]

Maybe if you only have one client querying the DB, and you're not using any features newer than SQL-89.

You are on crack. Saying MySQL can't handle load is like saying PostgreSQL doesn't have better compliance with the standards.

Re:MySQL faster than postgresql?

[jocmaff]

how was nap time ?

Re:MySQL faster than postgresql?

[Sxooter]

Actually, they both handle load fairly well, it's how they handle overload that differs. PostgreSQL generally gets slower and slower until you think it's hung (it's not) and if you take the load off, it will finish up all the transactions etc...

MySQL, on the other hand, will tend to die, i.e. the mysqld will crash and stop running.

Now, that was a year or so ago, with v 3.x of MySQL and v 7.2 of PostgreSQL that I found the two to behave like that.

Why the red wheelbarrow ?

[BrookHarty]

Why the red wheelbarrow ?

Shouldn't it be an Elephant, nickname Slonik (Russian for small elephant)

Re:Why the red wheelbarrow ?

[landoltjp]

While the Oliphant may be the mascot of PostgreSQL, there is no DBMS-specific topic Icons. So, my guess is that any discussion regarding Oracle, Sybase, PostgreSQL, mySQL, etc, would all be here.

Just picture the Elephant in the wheelbarrow ;)

Re:Why the red wheelbarrow ?

[Anonymous Coward]

so much depends
upon

a red wheel
barrow

glazed with rain
water

beside the white
chickens.

-- William Carlos Williams

DROP COLUMN, ALTER COLUMN TYPE and SQL-99 Specs

[landoltjp]

Apologies in advance for upper-case, but seems fitting to type SQL commands in that manner.

Object Manipulation Changes
[...]

Add ALTER COLUMN TYPE to change column's type (Rod)
It is now possible to alter a column's datatype without dropping and re-adding the column.

Is altering a column type consistent with the SQL-99 spec? Although I can see how useful this might be, I'd be very concerned about modifying table columns on the fly like this. How are type mis-matches handled? If there is a type mis-match, with the command continue, or roll back?

Same with Drop Column. I recall "way back when", the effort to modify a column was much more involved.

Re:DROP COLUMN, ALTER COLUMN TYPE and SQL-99 Specs

[rtaylor]

You'll like this, actually...

If it can be cast directly (integer to numeric) it will do so implicitly.

So, integer to smallint will do part of the work -- but if it fails (comes across a number that won't fit in the smallint) then it will rollback -- nothing lost but a little time (most PostgreSQL commands are atomic and transactional).

However, you can use an expression to do the conversion if you have something stranger in mind -- it's essentially run as an UPDATE.

Excuse the crappy formatting.

BEGIN;

SAVEPOINT altertab;

ALTER TABLE tab
ALTER COLUMN text_col TYPE bool
EXPRESSION (CASE WHEN text_col = 'SOMETHING'
THEN TRUE
ELSE THEN FALSE
END);

ROLLBACK TO altertab;

ALTER TABLE tab
ALTER COLUMN text_col TYPE bool
EXPRESSION (CASE WHEN text_col IN ('SOMETHING', 'OR', 'ANOTHER')
THEN TRUE
ELSE THEN FALSE
END);

COMMIT;

Re:DROP COLUMN, ALTER COLUMN TYPE and SQL-99 Specs

[tarvin]

> Is altering a column type consistent with the SQL-99 spec?

As far as I can see, the SQL standards (looked at SQL:2003) don't specify a way to change a column's type. It does, however, specify how certain auxiliary column properties (such as its default value) can be changed.

> Same with Drop Column

The standard defines how to drop a column:

ALTER TABLE <tablename> DROP [COLUMN] <column name> <drop behaviour>

<drop behaviour> is RESTRICT by default, but may also be CASCADE (although the CASCADE behaviour is only an optional feature, as far as I can see). If set to RESTRICT, then the column drop will be denied if another database object is dependent on the column. If CASCADE is specified, then dependent objects are dropped, too.

Re:DROP COLUMN, ALTER COLUMN TYPE and SQL-99 Specs

[chriskl]

Hey Rob,

I think you mean the keyword 'USING' instead of 'EXPRESSION' :)

Chris

replication is not a failover solution

[RelliK]

PostgreSQL supports replication BUT replication is absolutely useless as a failover mechanism because it is asynchronous. That is, when you commit a transaction, you cannnot be sure if/when it gets propagated to the slaves. For true failover you need distributed transactions. Neither MySQL nor PostgreSQL support them, but curiously, Firebird does.

Re:replication is not a failover solution

[wieck]

Looking with a narrow view at situations that need 100% guaranteed zero transaction loss on failover, you are right. Many businesses however can live with a little (few seconds) lag and the risk of losing the last couple of transactions, given that there is a mechanism to later analyze the failed server (after recovery) and find out what had been lost, to solve these cases manually or inform users/customers.

The true failover functionality you are talking about will be the goal of my follow-up project Slony-II, which will implement synchronous multi master replication for PostgreSQL. The design phase will start in about 3-5 months.

Sincely, Jan

Re:replication is not a failover solution

[killjoe]

You may want to look at backplane.

PG in Webmin.

[caluml]

Although it's not a Postgres issue, I'd like to see better support for it in Webmin. I know phppgadmin exists, but I prefer doing the simple stuff in Webmin, and there are numerous times I've tutted, and clicked, because the Webmin module doesn't let you do it, whereas the MySQL module does.
(Can't think of any right now, but there are things...)

PG requires more tuning than Oracle?

[jbellis]

That's the exact opposite of most peoples' experience.

(From someone who used to wear Oracle DBA as one of his hats.)

Re:PG requires more tuning than Oracle?

[rtaylor]

Yes, that's most peoples experience -- but recently there have been a few migrators going from Oracle to PostgreSQL who found it difficult to get Pg to run the queries the way they wanted.

Of course, those situations often result in planner or optimizer patches and the next version doing better for everyone :)

I work for PG

[Anonymous Coward]

And Friday is Hawaiian Shirt Day. But ask yourself this, "Is this good for the company?"

Sure! Here's how to get it:

[NerveGas]

./configure;make

It's supported 64-bit for as long as I can remember.

steve

Re:not yet on par with MySQL

[rycamor]

Uhh... "apparently" what? Oh! I see... hehehe... and all this time, they had no idea they were operating with such a flawed SQL parser. No wonder they had to go to all the trouble to implement 6 procedural languages, as well as their extended RULE system, custom datatype capability, etc... Poor guys...

[Nice troll... ;-)]

Tablespace nice...but

[Twillerror]

Tablespaces are nice, but they really need to create database files that can contain objects. This is a major feature that most of the big boys have.

That is you allocate one file to a certain size. The file is a collection of database pages. Each page can be used by a table or index to store data.

This makes for much faster backups, and management. I can detach a database, copy the file somewhere, and reattach. There is a lot less file overhead since you lock on to a few files, versus a different file for each table/index. If your db has 100's or 1000's or tables this can really create a lot of overhead. If each table has a handful of indexs this can really mulitply.

Also you can attach different tables and indexs to different files and create groups of files. Then you can span these file groups across different drive subsystems to get really really good performance. This can even allow you to put non crucial data in another file that can be put on a super cheap drives, like debugging information or such. Tablespaces at least allows you to do this.

File per object is one of those things that really seperates postgresql from true enterprise level databases. Having a write ahead log used to be the other. You really need a db system that deal with pages, not files.

All in all though this is an important step for postgres, and maybe we will see single database files in the next version.

Re:Tablespace nice...but

[Lennie]

The problem is probably where to draw the line, because pgsql is an ObjectDBMS

Actually your right (I think it's a good thing) it (ODBMS) does seperate it from the rest.

Re:not yet on par with MySQL

[SSpade]

Postgresql supports classic BLOBs perfectly well. It _also_ supports bytea (binary string) fields. Has done for a long time.

Other databases...

[eluusive]

A couple (years?) ago there was an article about some SQL compatible database that was developed in academia which was much faster than even Oracle and was open source. But for the life of me I can't remember the name. Does anyone remember the artcle that was posted on slashdot about it?

Re:Other databases...

[icebattle]

you might be thinking of ingres... recently open-sourced by ca.

Re:not yet on par with MySQL

[EXTmilky]

Are you sure? Last time I checked it didn't know what "BLOB" means. If it finally found it's way into the Postgres code, then they should also put a note into their docs.

Re:not yet on par with MySQL

[Guillermito]

Appearantly their SQL parser is still very flawed and you cannot seriously store binary data with it (BLOBs), unless you can live by the ugly encoding you get your data back with. MySQL never had such problems (it also allows binary data in TEXT fields!),

Maybe it is because MySQL treats TEXT fields as binary since it has no Unicode support (at least, the current stable, non beta release).

But then again... a man's bug is another man's feature.

Re:not yet on par with MySQL

[EXTmilky]

Sure, they have a thousand "advanced features" and keep adding new ones day after day, but making the whole thing 8-bit clean would be nice too, eventually ;)

It's obvious that to them "advanced and custom datatype capability" sounds much cooler than support for "standard data types". It puzzles me why they so hardly fight the way everybody else does it.

[grumble, grumble, grumble]

[mark-t]

There's _still_ no WITH RECURSIVE clause support.

How many versions of postgresql have gone by with that on the TODO list?

Is postgresql with the WITH RECURSIVE clause support pipedreamware or something?

Re:[grumble, grumble, grumble]

[Sxooter]

Stop grumbling and go here:

http://developer.postgresql.org/readtext.php?src /F AQ/FAQ_DEV.html+Developers-FAQ

and implement it. This ain't no commercial app, it's open source.

Re:[grumble, grumble, grumble]

[davegaramond]

There's ltree in contrib/. It's pretty much the same thing, but with different syntax.

Re:not yet on par with MySQL

[cmowire]

There's two ways to do binary types.

One way, of course, is to use a binary type, which can be tricky, depending on your client API. It mostly sucks if you are using an actual text-based insert query and response, whereas most of the time, there's *also* a way to use a more binary-oriented interface -- using the binary COPY interface and the relevant C API.

The other way is to use the large object support.

P.I.T.R

[blooba]

Point-In-Time Recovery is extremely important, and was the one major factor that prevented me from seriously considering PostgreSQL in a production environment. However, please bear in mind that such a feature requires extensive testing, i.e., crashing your db in every conceivable way, and then recovering it quickly AND consistently (in consistent mode.) So I will still wait and see about this feature.

Tablespaces are also a key feature. The nicest thing about tablespaces is: each schema can have its own tablespace. This makes maintenance much, much easier, allowing you to isolate the data for each of multiple applications or developers. You can also use it to isolate mission-critical data within the same schema, which in many cases can keep your app running, even if you lose a non-critical portion of your database.

Savepoints are nice, but I've never had to use them. And altering column data types is nifty, but not really useful in the real world.

Btw, does PostgreSQL have row-level locking yet?

Re:P.I.T.R

[GooberToo]

Btw, does PostgreSQL have row-level locking yet?

Static docs provide your answers.

Remember, one of the points of using MVCC is to avoid row locking whenever possible. But, I think you'll be hard pressed to come up with a situation where your desired locking facilities are not provided for with PostgreSQL.

To quote the online documentation:
"In addition to table-level locks, there are row-level locks. A row-level lock on a specific row is automatically acquired when the row is updated (or deleted or marked for update). The lock is held until the transaction commits or rolls back. Row-level locks do not affect data querying; they block writers to the same row only."

Re:P.I.T.R

[Sxooter]

Btw, does PostgreSQL have row-level locking yet?

You mean:

select * for update from table ...

That's been there for some time now.

Re:P.I.T.R

[blooba]

Dang. That's nice. Thanks for the info. At this point, I really should R.T.F.M.

While we're at it, how about materialized views? A lot of my clients, particularly those with data warehouse db's, really need materialized views to solve complex performance issues.

Re:P.I.T.R

[johnnyb]

PITR is actually pretty well-tested, as it is not really any different from the WAL they added many years ago. It's just organized so that it's easier actually do PITR with it. The recovery technology has been there for years, it's the administration that is new.

Re:P.I.T.R

[TheRealFoxFire]

Materialized views are provided in the PostgreSQL contributed package. Its implemented using database triggers, and works (I use it in production) but its not as mature as the official features.

Re:P.I.T.R

[blooba]

Looks like I have fewer and fewer excuses not to try an open source database.

The only problem I can think of with using triggers to refresh materialized views, is that you cannot defer the refresh. It's rare, but some people do not want their M.V.'s refreshed in near-real-time. They prefer a daily full refresh.

Also, how easy is it to manage this approach? Does the dba have to setup custom triggers on each of the underlying tables, or does pgSQL automatically generate and enable the triggers?

Re:P.I.T.R

[chriskl]

Erm. PostgreSQL has had row-level locking for 10 years...

Re:P.I.T.R

[abulafia]

The only problem I can think of with using triggers to refresh materialized views, is that you cannot defer the refresh. It's rare, but some people do not want their M.V.'s refreshed in near-real-time. They prefer a daily full refresh.

I've done this with triggers, two ways: (1) via a "changes" relation. The trigger informs the change relation of the PK of the tuple that changed, and a batch job (or however you want to trigger it) runs the trigger that updates. (2) In some circumstances, you can add an attribute to indicate that a tuple changed - a date, or a hash, depending on circumstance. Crawl that nightly (or whenever) that of the changes relation.

Not as "pretty" as Oracle, in that you don't have the CREATE MATERIALIZED VIEW... syntax, but nothing all that different is going on, functionally speaking.

You Win

[Stone316]

I've seen some bad DBA's but that one takes the cake. :)

Re:not yet on par with MySQL

[EXTmilky]

True, in this case it is MySQLs lack of standards compliance and features that allow it to store binary files into TEXT fields (I'm sure this in violation of the SQL standard) - since you cannot have any 8-bit value in UTF-8 strings.

And after all nobody should expect to get free string matching and regex features for BLOB fields anyhow (they're meant to be blackboxes). But what counts at the end of the day is, that MySQL is far more user-friendly and people would have a hard time to migrate to Postgres, despite the fact that it provides so much more possibilities everywhere else.

Re: Your sig

[chill]

It is also important to remember that there was no provision for a standing army in the Constitution, as well.

Correct, and this was done on purpose. Jefferson, for one, wanted a provision to PROHIBIT a standing army except in times of war. He was unable to convince the majority of others to go along with this, though.

However, it is an irrelevant point. An army is there for the Federal Gov't, whereas the militias were for the States. The rights of the PEOPLE to bear arms was given so that the people and States could have an adequate defence against the Federal Gov't as well as foreign enemies. Trust of central gov'ts was not high considering the recent history of Revolution.

This is why all the arguments about protecting yourselves from criminals; guns for hunting; etc. are irrelevant. The 2nd Amendment is there exclusively to give the people the power to protect themselves from the Gov't, if necessary.

Really cool stuff in PostgreSQL

[cmowire]

• user defined types, including the ability to create customized indicies for them. So there's a type specifically to handle encrypted authentication passwords, for example, so you don't need to mess with crypt() all of the time.
• Inheritance for tables, so you can agregate different types of data without needing to explicitly set all kinds of primary keys.
• Full ACID support, like a real database
• MVCC instead of row or table locking.
• BSD license

Re:Really cool stuff in PostgreSQL

[Sxooter]

Inheritance for tables, so you can agregate different types of data without needing to explicitly set all kinds of primary keys.

Unfortunately, the inheritance doesn't currently handle things like pks and unique indexes across the inherited tables. sigh.

Re:Really cool stuff in PostgreSQL

[T-Ranger]

As for passwords, what would be _realy_ cool is if they stored them in plantext, and allowed chalange/response authentication, or better yet, SASL.

Re:Really cool stuff in PostgreSQL

[cmowire]

Indeed.

See, the PostgreSQL authentication (i.e. what your clients use to connect to the database) can use all kinds of different methods, including challenge/response.

What I'm talking about is when you are making some sort of web application where you want to allow users to specify passwords, but you want those passwords to be reasonably secure themselves and stored in a database table.

So the way the contrib module for PostgreSQL works is that they are stored encrypted. However, if you try to compare them against the plaintext password, it will encrypt the supplied plaintext and compare it against the encrypted form in exactly the same way that the unix password system works.

Having said that, it's entirely possible (just nobody's done it yet) to have a similar "shared secret" that allows for the challenge/response authentication systems and, at the same time, implement it as a type such that all of the logic behind it is nicely modularized.

DB2 - solid and proven

[Anonymous Coward]

Heh. The funny thing is, I work at a Big Blue shop (they even buy IBM desktops). I've made multiple versions of DB2 shit themselves doing relatively benign things (a C udf, running fenced, for example, caused a 14 partition 8.1 DB2 warehouse on AIX 5.2 to spontaneously croak, all because I declared a return variable as varchar for bit data and treated it like a standard varchar; still no idea why, they never closed the PMR).

Once I had a db2 database here that would crash when you ran a backup (7.0 unpatched; it was a little neglected).

It's a great db, and a workhorse, but it's not rock solid. We find issues here all the time; even flakiness in the vaunted db2 query optimizer.

Re:not yet on par with MySQL

[EXTmilky]

[...] - actually reading the documentation helps quite a bit

Only sad, that you didn't. That excerpt exactly told it: PG does not support the "BLOB" type, but instead introduces "BYTEA", which is very annoying to use (decoding on the client side is really backwards if you actually queried a database for your data). What I'm saying is, that you're often better off simply encoding anything into base64 before putting it into PG databases - in the end it comes down to that. It was already mentioned (not to say recommended) that way on their mailing lists, btw.

More adoption of SQL92 than of MySQL's QL

[brlewis]

PostgreSQL adheres to the standards more than MySQL, so you're using a language with broader industry adoption if you use PostgreSQL. Especially from someone who says, "If X then we'll use Oracle", you ought to know that PostgreSQL would make migration to/from Oracle easier.

Re:not yet on par with MySQL

[cmowire]

Except that if you code it in base64, you'll blow up the size of the binary data, whereas if you'd use BYTEA or large-object support, you won't.

Not the only reason

[wonkavader]

The reason I used mysql instead of postgres, last time I made that choice (6+ years ago) was because of the speed. Windows support didn't matter at all, only the blinding speed of MySql compared to old Postgres (http://mysql.matrix.com.br/information/benchmarks .html). My understanding is the Postgres has gotten much faster: Are there more recent benchmarks around?

Re:not yet on par with MySQL

[Guillermito]

But what counts at the end of the day is, that MySQL is far more user-friendly and people would have a hard time to migrate to Postgres

It depends on which user you are talking about.

A database system has many users. Among them, database administrators and programmers. Programmers' goal is to hack applications as easily and quickly as possible. Database administrators' goal is to make sure the database keeps its integrity (i.e. the data stored there is correct)

I would say that PostgreSQL is more friendly towards database administrators and Mysql is more friendly towards programmers. And, no, you can't have a DBMS which makes both happy: there are opposing roles.

IMO what counts at the end of the day (I mean, from a business point of view) is that the organization as a whole can trust its data, and not if a hacker wrote 50 or 100 fewer lines of code.

Of course, if you are talking about a situation in wich the same person is the database administrator *and* the only programmer, well... I think MySQL is OK for your league.

MySQL only fast for single user?

[wieck]

You really need to update your "known facts".

Using a TPC-W style benchmark suite implemented with Apache, PHP4 and either MySQL 4.1.1 or PostgreSQL 7.4.2, I get more or less the same performance. Because of the transactional requirements and the update concurrency, all tables are InnoDB, of course. Based on that I cannot but contradict your claims about MySQL's scalability (and I am a PostgreSQL CORE developer). It keeps well up and is stable even under heavy load. Where the test uses a stored procedure in PostgreSQL, it must use a bunch of PHP code and separate query calls in the MySQL case, but that is exactly what developers do today and since the Apache server is part of the benchmarked system, this is as fair as possible.

That said, Apache+PHP+DB is the environment most people are talking about when they speak about simple to medium complex Web applications. With the scalability and performance being head to head, why would someone voluntarily miss stored procedures, views, triggers and all the other yet to be done for MySQL features? And while the (new in 4.1) subselect support makes it possible to get all of the TPC-W functionality implemented at all, to get it running fast enough in MySQL one has to rewrite some queries in a manner that I would call unmaintainable code. These complex features are not something where you can say "Transactions, checkmark". You have to look at how complete the implementation is and how well the query optimizer can deal with queries that use that feature.

So looking at the two right now, with the performance advantage gone, and the Win32 support knocking at the door, replication available and tons of well settled features in the HISTORY that are still on MySQL's ROADMAP, PostgreSQL is not just the better choice in some cases. It is ahead ... except for MySQL's outstanding marketing.

Sincerely, Jan

Re:not yet on par with MySQL

[EXTmilky]

I've lately read that one thing where PG shines is, that it actually would compress TEXT and *CHAR fields on-the-fly. And since base64 compresses quite well this would also accomodate any redundancy in binary files, so files may occasionally even become slower when stored this way (instead of in BYTEA fields).

tsearch2 licensing

[frostman]

tsearch2 (full-text search) is included in the contrib directory of the official distribution.

Documentation is a bit slim but it's very nice and only takes a couple hours to figure it out the first time, and a few minutes to set up in future installs.

Here's a link.

From that site:

License:
Stable version, included into PostgreSQL distribution, is released under BSD license. Development version, available from this site, is released under the GNU General Public License, version 2 (June 1991).

Re:not yet on par with MySQL

[rycamor]

"8 bit clean"? Oh, now you've convinced me... So the fact that I have the inconvenience of running my data through escape_bytea() before storing it as a binary string is THE major reason why PostgreSQL shouldn't be used? That discounts anything else about what a DBMS should do?

Yeah, PostgreSQL doesn't support 'standard' data types, and MySQL does? I really hope someone isn't reading this thinking you are serious. (You are not serious, are you?). MySQL's support for real datatype constraints is so bad that it should be considered typeless.

The whole point of a serious DBMS like PostgreSQL is not to allow your data to do things it shouldn't. If you don't want constraints or real data integrity, then fine; Use MySQL. Just be aware that your application still has to reimplement half the things that the DBMS normally handles.

Re:not yet on par with MySQL

[Bromrrrrr]

..Mysql is more friendly towards programmers.

Are you kidding me? If you like to do everything in your app that _should_ have been handled by the DBMS then yeah, sure :)....MySQL is more friendly to the programmer who hasn't got a clue about databases maybe, if you know what a real DBMS can do it becomes a pain in the ass to work with.

you can't have a DBMS which makes both happy: there are opposing roles.

Well, I don't think I'd agree with that, but if anything I'd say MySQL is the easier one to administer. Postgres needs a lot more thought with regards to tuning and user management.

Especially user management in Postgres is, in my view, horrible and a lot better in MySQL. Well, can't have it all I guess :) I'd still take Postges over MySQL any day.

Does the on-disk format still change at every rel?

[perbu]

This makes maintaing a _large_ Postgres-installation a real pain. Dumping a 200GB database to SQL and piping the SQL back into Postgres can take days. How hard can it be to decide on one format and to stick to this?

Mysql is still able to read and write ancient databases (the ISAM format was defined in 1986 and can still be read).

But does it have finer grained locking

[Dukhat]

The biggest problem that I face with Postgres 7.4 is that its referential integrity's locks block INSERTS and UPDATES that should go through fine.

For example, set up these two tables:
CREATE TABLE car_type (
id serial primary key,
name varchar(20)
);

CREATE TABLE car (
id serial primary key,
car_type_id integer references car_type
);

Now, try having two different connections insert with the same foreign key value (this field does not have a unique constraint):

Connection1:
BEGIN; INSERT INTO car (car_type_id) values(1);

Connection2:
BEGIN; INSERT INTO car (car_type_id) values(1);

You will see that the second transaction is waiting for the first transaction to commit. That is just rediculous and is one reason that Postgres is still small time.

Re:But does it have finer grained locking

[Cajal]

It's rather odd that you're seeing this behavior, since MVCC is designed specifically to prevent it. What is your transaction isolation level set to? Also, why aren't you using a SEQUENCE for car_type_id?

Re:But does it have finer grained locking

[Anonymous Coward]

don't use a serial datatype for the id, and everything will be fine.

And kids, serial datatypes are nice for some things; very few things, though. more often than not, they wind up being a huge pain in the ass.

Re:But does it have finer grained locking

[GooberToo]

Well, sequences exist outside the transaction, so I can't say that it should effect it in the least.

I'm running 7.4.2 here and I get this:
Session 1:
test=# insert into car_type values ( 1 ) ;
INSERT 33665 1
test=# BEGIN; INSERT INTO car (car_type_id) values(1);
BEGIN
INSERT 33666 1

Session 2:
test=# begin; insert into car( car_type_id) values ( 1 ) ;
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block

Sounds like he's fudding, using a different isolation level, or using an old version of PostgreSQL.

Prehaps he can restate exactly what his problem is and exactly what his test case is. As he presented it, it would simply fail because of a forign key constraint. As you can see, I inserted a row to allow it...but, it sounds like he's confused or only telling part of the story.

Re:But does it have finer grained locking

[johnnyb]

You can lower the isolation level if you want. That behavior is _required_ for proper full-transaction-isolation handling. If you don't like it, I think there is a "set isolation level" or something that does what you want it to.

Re:But does it have finer grained locking

[[Rob]]

I've spent 4 moderation points on this thread but now thats all gone to waste. I want to reply to this.

I beleive you have made a mistake. The error you are recieving is due to a failed statement in your transaction. I have tested his example in 7.4.3 and found it to be correct. I even removed the sequences from the example and the block still happened. I don't know why the block is occuring. Maybe because the reference checks in both transactions are trying to read the same row? That doesn't right so I don't know.

As a previous post said, changing the isolation level will probably fix the problem. Here is the documentation.

Re:But does it have finer grained locking

[GooberToo]

Opps. You're right. I did have an error there and simply ignored it without thought. I'm checking into the situation to see what's going on here.

Thanks.

Re:not yet on par with MySQL

[cmowire]

Ummm....

So you cause you string to take up more space, then count on the compression being good enough to undo that, sucking up CPU all of the time, because you don't *like* the format that the standard text format of the binary is. You do know that most of the APIs provide functions to translate these sorts of things for you already.

Doesn't make sense to me.

Do you *know* what compression format it uses for text and the paramaters thereof? Just because it says that it uses compression doesn't mean that it will do a good job of compressing base64. Have you run tests to see if you are, in fact, correct about this?

oops

[sootman]

shoulda been .msi, not .mis.

funny

[jbellis]

My experience has been that saying MySQL can't handle load is like saying it gets hot in Texas in the summer. :)

Re:funny

[Anthony Boyd]

That says more about your skills than it does about MySQL.

Re:Does the on-disk format still change at every r

[Christopher B. Brown]

They change the format because it it is necessary in order to implement the new features that they are adding in.

One of the points of Slony-I is to provide an answer to this very problem. Slony-I supports versions 7.3, 7.4, and 8.0, and may be used to support a short-outage upgrade path.

Suppose you have a 7.3 database, and want an 8.0 one. You set up replication between the 7.3 database and the new 8.0 one. It may take a couple of days for the new one to get up to date, but you don't have to shut the 7.3 one down.

Once the databases are more or less in sync, you do a MOVE SET to change the "master" to be the 8.0 database. Since they are nearly in sync, this should only take a few seconds. Presto! The 8.0 database is the "master," and you can switch over to it with whatever brief outage is needed to get your application to point to a new server.

Re:Does the on-disk format still change at every r

[Sxooter]

Actually, it's not the format of the data store so much as changes to the system catalog, which are put in place during initdb.

There has been a project for upgrading in place, but it just hasn't had enough man power thrown at it to be a viable solution.

Maybe it's time for you to volunteer?

Re:not yet on par with MySQL

[EXTmilky]

Right, but tha APIs BYTEA unencoding functions typically use regular expressions to do the trick. En/Decoding everything to base64 is faster than to/from the format the PG-guys have choosen. Both is a pain in the ass regarding CPU cycles; but what really makes this whole story annoying to me, is that you're forced to do it _client-side_ anyway. That's where PG is behind others.

And no: I didn't have run any tests on this - I just take it for granted if the docs tell me. And wether they use gzip or straight deflate doesn't change that much. Unless they use RLE, larger files _will_ get smaller this way on disk, unless they're not any further compressable things like PNG or JPEG images.

Re:not yet on par with MySQL

[Guillermito]

you can't have a DBMS which makes both happy: there are opposing roles.

Well, I don't think I'd agree with that, but if anything I'd say MySQL is the easier one to administer. Postgres needs a lot more thought with regards to tuning and user management.

When I said PostgreSQL was more friendly towards the database administrator I was talking about administering the data, not the DBMS (these are not quite the same)

There are tasks that are impossible to acomplish in MySQL because of the lack of features. For instance, How do you implement an audit trail within the DB in MySQL without triggers? Answer: you can't. You have to do it in your app (which can be easily bypassed) Now... I am willing to trade some point and click easy to administer interface or syntax any day if in return I have more control over which data enters the database.

Windows Native Support

[adolfojp]

I believe that native windows support for PostgreSQL is essential, not necesarily to deploy apps in that enviroment but to test and develop them. When I started using MySQL on my windows box, I had also looked into PostgreSQL. The lack of windows binaries for PostgreSQL made MySQL the default choice for me. On features alone PostgreSQL wins hand down. Also, in my experience, the faster performance of MySQL over PostgreSQL dissapears when I use InnoDB tables for transactional data processing. The doors to PostgreSQL have been open to many developers stuck in the windows world. Perhaps I will try PostgreSQL for my ASP.NET apps in addition to my trusty MySQL.

Cheers
Adolfo

Re:not yet on par with MySQL

[EXTmilky]

I didn't talk about the inconvinience to run some escaping function before I throw data into the database, I refered to the fact that I'm forced to _unescape_ it everytime AFTER it's gone into the database. The database server can keep it's stuff in whatever format it wants, it just shouldn't expose ugly internals to the clients (which like in this case had to do post-processing on behalf of the DBMS).

I didn't say that data storage is the most important thing to do with a real DBMS (then everybody would still be using BerkelyDB), but storing DATA is an important task for DATAbases - all the search and calculation features are nice extensions, nothing more.

Re:not yet on par with MySQL

[wieck]

Come on guys, the troll has a point. The encoding issues with bytea need to be added to the PostgreSQL-gotchas page ;-)

Re:not yet on par with MySQL

[EXTmilky]

Yes, ok, someone has worked on it. But as you already said, it is not a thing one would want to / could do via SQL in Postgres. The API functions you pointed to clearly resemble BerkelyDB features; just extended by network access. If you can write custom applications and use the C API everything is fine.

Actually I'm favouring the text-based query+insert database interface, as you call it; and so it looks not all too intuitive to use a specialized API for anything but text and numerics. But then again, this is perfectly fine for custom applications and it is of course a lot safer, faster and senseful to do it this way, the favourable way to say the least - but still: PG here failed on its transition to becoming a real SQL database I'd say. Not meant to be rude, just my point of view.

Views

[ttfkam]

If you are competent enough to set up a database, you're competent enough to use escaping/unescaping functions.

That said, to make it transparent, just set a trigger and a view. Create a trigger on insert or update to the table in question to run the escape prior to saving to the table. After you've done this once, insert and update as normal from now on. Create a view that selects on the unescaped value. After you've done this once, select from the view as normal from now on.

It's nice that PostgreSQL allows folks to work around these "issues." I don't blame you for not seeing the possibility that people wouldn't want or care about escaped binary data in their database. With MySQL, failure to see possibilities is commonplace. Like for example the fact that triggers and views are probably unknown to you because MySQL doesn't have them. But even though they easily and elegantly address your "problem," I guess they're just "nice extensions."

And for the record, "data" is a piece of information. "Database" is the collection of information -- the collection of data. Storing data is an important task for "database management systems." All the search and calculation features are the raison d'etre.

Re:Go postgreSql, go!

[bestguruever]

If you're going to use it expensively, then why not just go with Oracle?

Re:How about case insensitive string search

[davegaramond]

Postgres will probably support it in the long run (2-3 years?) after they rework their internationalization support. This will allow you to define a collation ordering for your columns. But on the other hand, perhaps you should not rely on case insensitivity, as it will limit you to only MySQL and SQL Server. Many other DB's act case sensitively (Oracle, PostgreSQL, DB2, ...)

Yes, not on par, but better!

[davegaramond]

Not only you can store binary data easily in Postgres, you can even *index* it (even if it's thousands of bytes long).

Can you do that with MySQL???

Re:not yet on par with MySQL

[Bromrrrrr]

When I said PostgreSQL was more friendly towards the database administrator I was talking about administering the data, not the DBMS (these are not quite the same)

Well, just goes to show how much I know about being a DB admin :)

I just meant that I don't agree with your idea that MySQL is better for programmers, it is not! And admins and programmers do not have opposing roles in this.

Re:MySQL vs PG

[davegaramond]

The features are getting in there... You make me laugh. Where are the *basic* things like triggers, stored procedures (only alpha), check constraints, etc? People have been waiting for *years*. MySQL development is getting slower and slower...

Problem with PG is that it is an old design that has been worked with for quite a while. Dude, MySQL design is even *older* (ISAM, for one). Postgres has relatively newer features like object relational.

Re:not yet on par with MySQL

[johnnyb]

If you're using bytea fields and Perl DBI

$bstring; #binary string
$id; #identifying attribute

$sth = $dbh->prepare("update x set y = ? where z = ?");
$sth->bind_param(1, $bstring);
$sth->bind_param(2, $id);
$sth->execute;

You may have to modify bind_param in the first case to $sth->bind_param(1, $bstring, DBI::SQL_BINARY), but I can't remember exactly how it goes.

In php, you have to be more explicit, with pg_escape_bytea and stripcslashes.

Re: Your sig

[elucubra]

Interesting that so many americans believe that a hand gun is needed. Long guns, non automatic are all that should be allowed. If every citizen had a long gun, freedom would be preserved, and a hell of a lot of lives would not be wasted by impulse shootings.

Re:not yet on par with MySQL

[davegaramond]

What BYTEA encoding issues? You store *raw bytes* in BYTEA. As for quoting, you can use '\\000' to '\\377' to represent byte 0 - 255. You can store *anything* in BYTEA. So again, what encoding issues?

Re:in other news:

[davegaramond]

This is not even a good troll, not by far. Beta news is important, especially for a software that's used by many. It will encourage people to try it, and thus find more bugs. Postgres 8.0 in particular is important, as it introduces a new platform (win32). Slashdot also carries news about XP2 being delayed (and then re-delayed, and then again). That's even less interesting than Postgres 8.0beta news, in which new software are actually delivered.

Not fair!

[davegaramond]

Dude, with "kill -9" it will not be long until you corrupt PostgreSQL, MySQL, etc. too.

Re:Not fair!

[Sxooter]

Actually, you can kill -9 postgresql, or pull the power plug in the middle 1,000 write transactions, and if you have a journaling file system, it will come right back up at boot up.

This isn't a theory, I've tested it. (note either you have to turn off the lying cache in IDE drives or use SCSI for this kind of power off reliability.)

Passing parameters? easy!

[YetAnotherLogin]

Try this one:

CREATE FUNCTION name(int) RETURNS SETOF test AS '
SELECT * FROM test WHERE id=$1;
' LANGUAGE SQL;

Then try using:

select * from name(1);

Should work pretty well.

Re:not yet on par with MySQL

[Guillermito]

I agree with you in general.

What I meant was this:

Many times I have heard programmers call, for instance, referential constraints "useless" (I know MySQL now has foreign key support), and think of them (and other integrity restrictions) just as a nuisance that they had to cope with.

These programmers think the DBMS has to accept any data they throw at it, in the way they throw at it

I was thinking on that when I said MySQL was easier for the programmer. Of course, you can say the same about PostgreSQL. Just don't use any integrity constraint.

The fact is that in many cases you don't have that option in MySQL (lack of features). On the other hand if you (DBA) have a DBMS which implements proper integrity constraints (PostgreSQL or other) it is unlikely that you choose not to use them.

So in the end, one of these programmers (not the good programmers, indeed) would feel that MySQL is "easier" because he won't have to code workarounds to recover from database "errors". MySQL would just happyly accept any SQL sentence provided it is syntactically correct, whereas other databases could refuse to execute the same sentence if it violates some constraint.

Why?

[cr0sh]

I believe that native windows support for PostgreSQL is essential, not necesarily to deploy apps in that enviroment but to test and develop them.

Every place I have worked has had at least one or two (if not more) old PCs lying around doing nothing (and when I mean "old", I mean a couple of gens old - not dirt old). Simply waiting in the wings acting as spares. Furthermore, at all of my jobs there have been at least one *nix box.

So, turn that unused hardware into something useful - drop BSD or Linux on it (or some other *nix, or scrounge on Ebay for an old Sun box or something else cheap), and drop PostgreSQL on it - ODBC drivers on your Windows boxen and there you go: an easy, cheap (maybe even free) PostgreSQL development environment.

Just because you develop under Windows doesn't mean all of your development tools have to be under Windows...

Re: Your sig

[jadavis]

You don't even need to go into that to show the problem with his sig.

Here is the text of the 2nd:
A well regulated Militia, being necessary to the security of a free State, the right of the people to keep and bear Arms, shall not be infringed.

Now, allow me to change just a few words:
A well educated electorate, being necessary to the security of a free State, the right of the people to keep and read books, shall not be infringed.

Does the above mean that only the well-educated should be allowed to keep and read books? Of course not. The second clause stands on it's own. If the founding fathers had intended for only the militia to reserve their right to bear arms, they would have said "the right of the militia" not "the right of the people".

I can respect arguments that gun control laws are a good idea. I cannot respect arguments that gun control is constitutional.

You're exactly right, of course.

[Futurepower(R)]

You're exactly right, of course. Those who say otherwise have no professional communication experience.

Maybe because MySQL has fewer gotchas?

[jdoeii]

PgSQL had a very steep lerning curve even for people with experience in other databases. Various unusual stuff just popped up in places which could not be expected from previous experience elsewhere.

How about COUNT(*) and other aggregates? Is is still as slow in 8.0 as it used to be in 6.* and 7.*?

I guess 8.0 fixed a long standing problem of select ... where small_int_column=1 not using indexes. That was a serious problem for those just starting with PgSQL because it was totally unexpected.

MySQL is nearly maintanence-free. It just runs and runs. PgSQL needs VACUUM and friends. Once we tried to move a database (from MSSQL) with market data to PgSQL (I think it was 7.4). A lot of stuff was inserted, and a lot of selects performed. Running vaccum/vacuum analyze once in 24 hours was not enough! By the end of the day performance of selects was abysmal. Every time we ran VACUUM ANALYZE, it took longer, and longer, and longer.

What about the problem with memory management and cache? I see it's being addressed in 8.0. I have to test 8.0, but with 7.4 it was impossible to run PgSQL efficiently as a dedicated server - grab all available memory and cache everything aggressively. It was very frugal about memory even when it was not necessary.

And, of course, the query optimizer. But that's understandably difficult. I have to see if improvements in 8.0 are significant

Re:Maybe because MySQL has fewer gotchas?

[GooberToo]

PgSQL had a very steep lerning curve even for people with experience in other databases. Various unusual stuff just popped up in places which could notbe expected from previous experience elsewhere.

IMO, it has a "very steep" learning curve because so many people have to unlearn all the bad habbits from MySQL. If you're coming to PostgreSQL, and can be bothered to read and follow directions for 30-minutes to an hour, IMO, you'll be on the same footing. In my opinion, the there is a curve, but it's not steep at all, assuming you're not coming from MySQL, where you've been taught to do things counter to just about every other product out there.

How about COUNT(*) and other aggregates?

That's a great question! Sadly, select count(*) is still slow and don't imagine that changing any time soon, at all. Many other aggregates are now faster. These are incremental changes that have been creaping in over the last four of fives releases. Aside from select count(*), PostgreSQL offers a very speedy solution. The general run is, if you really need a select count(*), keep a derived count somewhere, which is probably going to be faster anyways. Ya, no ideal, but a perfectly valid solution for most.

MySQL is nearly maintanence-free. It just runs and runs.

Perfectly valid comment. However, for the last several releases, there has been an autovacuum daemon available, which takes care of the vast majority of these situations. More below.

PgSQL needs VACUUM and friends.

This is true, however, with the autovacuum daemon, it pretty much takes care of it self. One of the things you'll see in 8.0 is that the daemon is now integrated into the postmaster. This will make it even easier to use and setup.

Once we tried to move a database (from MSSQL) with market data to PgSQL (I think it was 7.4). A lot of stuff was inserted, and a lot of selects performed. Running vaccum/vacuum analyze once in 24 hours was not enough!

That's not all that surprising. It's also possible that you needed some additional tuning on your database, to properly reuse all of your pages, when they were vacuumed.

By the end of the day performance of selects was abysmal. Every time we ran VACUUM ANALYZE, it took longer, and longer, and longer.

This is one of those situations where it pays to be proactive. Assuming you had it configured properly, to correctly reuse all of your pages, you may still needed to run vacuum more than once per day. But, that's not nearly as bad as it sounds. Had you running it, say 20 (wild number here) times a day, via cron, chances are, the whole system would of stayed fast and happy. Once you fall behind the curve, then you'll always be playing catchup. Worse, if you're not reusing all of the pages (tuning issue), performance will continue to get worse and worse, proportionaly to the amount that your not reusing, compounded by not vacuuming often enough.

Here is a great PostgreSQL tuning referencea

What about the problem with memory management and cache? I see it's being addressed in 8.0. I have to test 8.0, but with 7.4 it was impossible to run PgSQL efficiently as a dedicated server - grab all available memory and cache everything aggressively. It was very frugal about memory even when it was not necessary.

I assume you mean as a non-dedicated server. Also, assuming you're running on Linux, and using a current kernel (2.6.x), you can tuning the "swappiness" of the kernel, which greatly helps both dedicated and non-dedicated installations.

And, of course, the query optimizer. But that's understandably difficult. I have to see if improvements in 8.0 are significant

Well, you are right that the optimizer is a tough nut. This is why so many give up and allow hints and junk. Just the same, the optimizer generally works pretty well. It's really just a matter of

Re:Maybe because MySQL has fewer gotchas?

[jdoeii]

IMO, it has a "very steep" learning curve because so many people have to unlearn all the bad habbits from MySQL

Read your next statement Sadly, select count(*) is still slow and don't imagine that changing any time soon. Is using count(*) a bad MySQL habbit? PgSQL is difficult because it has unusual problems, not expected from experience with Oracle/MSSQL/MySQQL.

As for aggregates, I know PgSQL functions can't use indexes by design. But addressing the most common cases of COUNT/MIN/MAX/SUM and maybe AVG might be possible. For example, MIN/MAX queries can be rewritten with ORDER/LIMIT 1. It's really unexpected for someone new to PgSQL (but with a lot of experince with other databases) that COUNT(*) on large tables is unuseable AND there is no work around.

Assuming you had it configured properly, to correctly reuse all of your pages, you may still needed to run vacuum more than once per day.

One quite capable DB admin worked on it full time for about two weeks, another one consulted. They tried everything imaginable. Still, running vacuum slowed selects by 5-10 times. That was not acceptable during the day, thus we could run it only at night. And that was not often enough.

Two experienced people spent two weeks and failed to configure it for sufficient performance. Yes, you can say they were not experienced enough. I can also say that the configuration was overly difficult.

I assume you mean as a non-dedicated server. Also, assuming you're running on Linux

I mean a dedicated server, as in nothing but PgSQL 7.4 on a FreeBSD 4.9. Say, the DB is 1GB, server has 2GB RAM. PgSQL should just suck everything in RAM and run read queries directly from there. Why can't it do it? Or maybe it can, but how to configure it in such a way is really non-obvious.

Re:Maybe because MySQL has fewer gotchas?

[GooberToo]

Is using count(*) a bad MySQL habbit?

Absoluetely not. There is nothing wrong with it. It's just the developers don't want to have to hard code an optimal path for that aggregate. The issue exists because PostgreSQL supports user extensible aggregate functions, whereas most DB's don't.

PgSQL is difficult because it has unusual problems, not expected from experience with Oracle/MSSQL/MySQQL

Well, that I'm not willing to swallow. Simple fact is, each and every RDBMS has their own distinct sets of issues, problems, caveats. If something as simple as "select count(*)" is a serious problem, then chances are, you shouldn't be using a database to begin with. While clearly more cumbersome, triggers are easily implemented which allow you to easily and rapidly store derived values, such as counts. This is a very, very minor work around compared what things that MySQL forces people to do. The primary difference is that most people don't realize they they are doing things they hardway with MySQL until they get experience on other systems.

As for aggregates, I know PgSQL functions can't use indexes by design.

I'm not sure we're talking about the same thing here, but PostgreSQL has allowed functional indexes for some time now. But I don't think it would make sense to think that you can create a functional index on an aggregate. I think they were introduced in 7.1 or 7.2. Here's a reference to the 7.2 docs which talk about it.

One quite capable DB admin worked on it full time for about two weeks, another one consulted. They tried everything imaginable. Still, running vacuum slowed selects by 5-10 times. That was not acceptable during the day, thus we could run it only at night. And that was not often enough.

I certainly don't mean to knock your DBAs. My point is, did they attempt to get support from the mailing lists? Did they use the available documentation to it's fullest? Thinking that a Sybase DBA is going make an Oracle database sing, without Oracle DBAs helping, isn't very realistic. That's my point. Far too often, I see people state that they had problems, usually very, very basic problems, but never once bothered to get help from the people that are most qualified to help. Whos failing is that? PostgreSQL's or the DBA's? Obviously, I can't comment on the specifics here, but I think you can understand where I'm coming from.

Yes, you can say they were not experienced enough. I can also say that the configuration was overly difficult.

Well, I certainly do believe that there is a learning curve. You'll find that any non-trivial RDBMS has such a curve. IMO, PostgreSQL is no greater than any other. In fact, I would argue that it's certainly far less steep than Oracle or MSSQL. Having said that, just because there is a curve, is not an excuse (making an assumption here) to not obtain assistance from the wonderful support mechanisms which are readily available to any PostgreSQL user.

I mean a dedicated server, as in nothing but PgSQL 7.4 on a FreeBSD 4.9. Say, the DB is 1GB, server has 2GB RAM. PgSQL should just suck everything in RAM and run read queries directly from there. Why can't it do it? Or maybe it can, but how to configure it in such a way is really non-obvious.

Well, unlike most DB's, PostgreSQL assumes that the OS will do a fair share of caching. This means, you want to leave a fair amount free for system cache. Having said that, for the internal buffers that PostgreSQL does keep, version 8.0 does add several optimizations which greatly enhances many types of workloads (seq scan no longer flushes buffers of commonly used rows, etc).

Many more specifics can easily be gained from reading the documentation, reading the easy to follow tuning guide, which I previously pointed people at, or heck, either doing simple searches of the archives or simply posting a question to a ma

Re:Maybe because MySQL has fewer gotchas?

[jdoeii]

The issue exists because PostgreSQL supports user extensible aggregate functions, whereas most DB's don't.

Yes, I know. I think it was Tom Lane back in the days of 6.5 who gave me the same exactly reasons for not addressing the issue of slow aggregates. I understand your reasoning, I just disagree with it. How many users write their own aggregates compare to the number of users who just want to use COUNT/MIN/MAX? Your admittedly technologically cool solution is addressing needs of maybe 5% of users leaving the majority out in the cold.

Simple fact is, each and every RDBMS has their own distinct sets of issues, problems, caveats.

Yes. When I write a complex query I expect to tweak it or tweak the indexes for optimal performance. But when I write SELECT COUNT(*) FROM tbl I expect it to be snappy no matter how big the table is. I never expect to rewrite basic stuff like SELECT id, SUM(a) FROM tbl GROUP BY id because it's too slow. I don't exppect to run maintanence jobs (VACUUM) every hour during peak load times becuase otherwise the database does not perform.

If something as simple as "select count(*)" is a serious problem, then chances are, you shouldn't be using a database to begin with. While clearly more cumbersome, triggers are easily implemented which allow you to easily and rapidly store derived values, such as counts.

Re-read your words. What you are describing is exactly the steep learning curve: a simple thing requiring a complex solution. Is it not? What about porting issues? Just think about how much effort is wasted by reimplementing every simple query with triggers.

In fact, I would argue that it's certainly far less steep than Oracle or MSSQL

Less than Oracle (not "far" less) - yes. Less than MSSQL - no. But that could be just our shop since we have the people with the right experience. The query optimizer in MSSQL 7.0/2K is significantly better than the one in PgSQL 7.4 though. The PgSQL 7.4 optimizer is on par with MSSQL 6.5. Writing queries to MSSQL is easier than to PgSQL.

I would be interested to hear the details of your RDBMS, which seemingly could not be handled by PostgreSQL

The show stopper was a table with the market data. It had the bars with Open/High/Low/Close. It had about 20 columns, about 5-10 million records for a total 1-1.5GB. Not really that big. The new data was continuosly inserted, a lot of selects were issued. The majority of selects were issued for the recently inserted data, with values obviously not accounted for in the index statistics. Some selectes (about 10%) were issued for historical data.

The whole table was never kept in RAM, although the RAM was sufficient. The relatively rare queries for historic data trashed the cache, so selects for recent data had to refill it from disk. The continuous stream of inserts screwed index statistics slowing selects. Running vacuum analyze was taking a few minutes. During this time it slowed selects to an unacceptable level.

I don't claim I understand PgSQL internals well. That's just my take on the problem.

Just the same, version 8.0 does represent many improvements. Hopefully you and your guys will be willing to check it out again.

We will. As soon as we are sure that doing so won't get us in trouble with the accounting types :-). The last try of two weeks of paid time wasted was not pretty.

Re:Maybe because MySQL has fewer gotchas?

[GooberToo]

Your admittedly technologically cool solution is addressing needs of maybe 5% of users leaving the majority out in the cold.

Well, I'm not sure it's right to call it "mine". ;) Tom is a purist and frankly, it's annoying form time to time. There are times when, as we both see, purity gets in the way of good business sense. But (you knew it was coming), many features which get added (by developres, do make use of these facilities, so it's not really fair to to call Tom out either. He's a smart guy and often sees the whole picture bigger and better then most. Then there are those times when you just want to smack him up side his head. ;) But that doesn't happen very often.

I don't exppect to run maintanence jobs (VACUUM) every hour during peak load times becuase otherwise the database does not perform.

In fairness, you haven't had to for a while now, thanks to the AVD. Also worth mentioning is that 8.0 brings new optimizations which will further reduce disk spikes during WAL flush as well as reduce disk spike and CPU hits from vacuumes. Both of which, seemingly are targeted to make YOU happy. ;)

Re-read your words. What you are describing is exactly the steep learning curve: a simple thing requiring a complex solution. Is it not? What about porting issues? Just think about how much effort is wasted by reimplementing every simple query with triggers.

Ya, I thought you might say this. Put it into perspective. Look at the odd contortions that people have to go through with MySQL to do some things, which are trivial on most every other RDBMS. Yet, people still consider MySQL to very simple. And, let's not lose perspective here. It's not like you have to "reimplement every simple query with triggers." That's simply not a fair or honest statement. For smaller tables, use select count(*). For large targets and larger, compute the derived value via a trigger. Which, suddenly makes your select run as fast or faster than most other systems. It's not like select count(*) has to be ruled out completely. But, I clearly see your point and agree somewhere in the middle, somewhat.

Less than MSSQL - no. But that could be just our shop since we have the people with the right experience.

I'd place my money on having the experience. Look at how many versions and how incrementally MSSQL knowledge has been gained. You can't dismiss it and it's certainly not fair to compair years of experience and expertise with zero on a new RDBMS and say one complex and the other is not. Realistically, MSSQL has continued to add many complex and powerful features. It's no longer the trivially easy DB that it was 6 years ago. Likewise, you've learned what and how to do things and what things to avoid. You know how to tune it and it's part of your "trivial" knowledge now. As such, it's not fair to compare the two using such a metric. As I said, each system has their own quirks and caveats, it's just that you have mastered them on MSSQL.

The show stopper was a table with the market data. It had the bars with Open/High/Low/Close. It had about 20 columns, about 5-10 million records for a total 1-1.5GB.

Were all 20 columns commonly queried? If not, paritioning your data may make sense. With tablespaces (versus the old voodoo method), you will be able to more readily spread your disk activity out, which will also greatly help your performance (e.g. index on one disk, WAL on another, table on yet another). Without knowing the details, I'm betting there's a number of optimizations which are available, even with the current stable release (7.4.2, with 7.4.3 or 7.4.4 due out shortly).

The whole table was never kept in RAM, although the RAM was sufficient. The relatively rare queries for historic data trashed the cache, so selects for recent data had to refill it from disk.

Sounds like the need for additional tuning. On a dedicated server, the OS sho

Re: Your sig

[elucubra]

How long have you been a professional armed person?

I have one year's experience in a terrorist zone under my belt. It doesn't make me part of the elite, but I think I can form a reasonably informed opinion.

Handguns in the hands of the non profesional are the number one cause of inocent deaths caused *by inocent* ( 'till then) shooters screwing up.

A sniper is orders of magnitude more efficient against armed forces than person with a handgun. Now try to imagine trying a coup d'etat in a country with a couple of hundred million snipers.

Hand guns are only slightly more effective than knives close range, and pretty useless long range.

Re:not yet on par with MySQL

[Anonymous Coward]

This may be rude.. but I think you need to do more reading.

I have now implemented two huge applications (>100,000 LOBS)in postgresql using both LOB's and BYTEA, using Windows ODBC *and* the C API. The C API requires more work and is ofcourse rather custom/non-portable - so is MySQL's interface. The ODBC app code is EXACTLY like Oracle, MySQL, SQL Server, even MS Access. The first of these apps was written 3 years ago using LOB's and ODBC. I'm afraid to say that you may have to update you're view of PG - your opinions may have been valid at one stage, but certainly not for the last *3* years and I can provide proof of that..

I'm sorry if this seems harsh its just that I'm getting very tired of MySQL pundits who have no clue comparing MySQL to enterprise class DB's like Oracle/SQL Server/PostgreSQL (not that I'm saying u have no clue!!).

When your DB supports triggers,rules,stored procedures,views,extensible types,extensible operators,check constraints,functional indexes,better than row-level locking, full sub-selects etc.. then feel free to compare MySQL and PG again - until then keep playing with your toys.

(Sorry if this sounds harsh)

Re:Does the on-disk format still change at every r

[ttfkam]

You mean that same ISAM format that doesn't support transactions, foreign keys, etc.? That ISAM table format?

In other news, if you can't make a dump of your database, how are you doing backups? You are backing up your database, right? Right? If it's a space issue, how about spending $130 for 200GB on a separate box for temporary storage? And days? C'mon! What are you using? A 233Mhz Pentium with a single IDE drive also doubling as a fileserver?

And finally, don't dump to SQL.

pg_dump --format=c

Faster; Smaller; More efficient.

Re:Does the on-disk format still change at every r

[perbu]

In other news, if you can't make a dump of your database, how are you doing backups?

You missed the point completely. Some systems just can't stop for a dump/restore. EVER. And you cannot use PostgreSQL for such an operation because the developers can't descide how the data are supposed to be stored.

If my system manages to dump 20 000 rows per second and restore at a rate of 10 000 rows per second that means a 100M row database would need at least 4 hours for an upgrade. Try selling such a system to a large company. They will laugh. And 100M rows is not really that much - but it is to much for PostgreSQL if you want 24/7 operation. :(

Re:Does the on-disk format still change at every r

[ttfkam]

I didn't say it had to stop. Granted pg_dump slows a system down, but the whole point was to allow hot backups.

That said, I get what you are saying, but in that case, how do you upgrade *anything* (and why)? What happens when a drive fails in your RAID? Do you wave your hands and wail because the speed has dropped during the array rebuild? How do you add drive space, memory, CPU power?

Really what you seem to be lacking, as other posters have mentioned, is redundancy in your database. Granted you will be back to a couple of days for the complete transition, but you are asserting a requirement that 90% of us do not need. Nevertheless, the synchronization option is relatively pain free and allows for the 24/7 uptime you require.

For the rest of us, it'll just be a few (ungodly at 3am) hours at most.