Slashdot Mirror
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."
Except ready to go full text searching, the ONLY thing keeping me on mysql.
http://www.rustyrazorblade.com
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.
I'm trying to teach myself to set people on fire with my mind... Is it hot in here?
Is database replication and fail over working yet?
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!
"Thanks to the remote control I have the attention span of a gerbil."
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.
As far as I'm concerned, the only truly missing feature is distributed transactions. Are there any plans to add them any time soon?
___
If you think big enough, you'll never have to do it.
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.
I just wanted to say thanks to Fujitsu for helping pay for this
And thanks to Afilias (the guys who run theStored 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:
The only way that I could find to do that in postgres was:
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...
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.
___
If you think big enough, you'll never have to do it.
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.
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.
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?
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.
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 is more friendly towards programmers.
:)....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.
:) I'd still take Postges over MySQL any day.
Are you kidding me? If you like to do everything in your app that _should_ have been handled by the DBMS then yeah, sure
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
What a rotten party, have we run out of beer or something?
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.
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.
Information: "I want to be anthropomorphized"
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
There's ltree in contrib/. It's pretty much the same thing, but with different syntax.
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.