Quantcast

postgresql vs mysql

classic Classic list List threaded Threaded
147 messages Options
1234 ... 10
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

postgresql vs mysql

Gusti-2
  Hello

 I  have a friend that ask me why postgresql is better than mysql.
 I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?

  Thank you,
   Gustavo


--
                           ||\             // \
                           | \\           //   |  
I'm thinking.               \  \\  l\\l_ //    |
    _              _         |  \\/ `/  `.|    |
  /~\\   \        //~\       | Y |   |   ||  Y |
  |  \\   \      //  |       |  \|   |   |\ /  |
  [   ||        ||   ]       \   |  o|o  | >  /
 ] Y  ||        ||  Y [       \___\_--_ /_/__/
 |  \_|l,------.l|_/  |       /.-\(____) /--.\
 |   >'          `<   |       `--(______)----'
 \  (/~`--____--'~\)  /           U// U / \
  `-_>-__________-<_-'            / \  / /|
      /(_#(__)#_)\               ( .) / / ]
      \___/__\___/                `.`' /   [
       /__`--'__\                  |`-'    |
    /\(__,>-~~ __)                 |       |__
 /\//\\(  `--~~ )                 _l       |--:.
 '\/  <^\      /^>               |  `   (  <   \\
      _\ >-__-< /_             ,-\  ,-~~->. \   `:.___,/
     (___\    /___)           (____/    (____)    `---'


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

Jaime Casanova
On 2/20/07, gustavo halperin <[hidden email]> wrote:
>   Hello
>
>  I  have a friend that ask me why postgresql is better than mysql.
>  I personally prefer posgresql, but she need to give in her work 3 or 4
> strong reasons for that. I mean not to much technical reasons. Can you
> give help me please ?
>

in MySQL if you have tables MyISAM they will ignore all rollback
commands you exexute, so you will have inconsistencies in databases...

worst, if you have tables MyISAM and tables InnoDB the first will
ignore all rollback commands and the laters won't...

of course the legendary speed in mysql can be obtained if you use
tables MyISAM :(

some other issues (some of them had been resolved in 5.x i don't know wich ones)

http://sql-info.de/mysql/gotchas.html


one last thing mysql team doesn't afraid to change behaviours between
minor releases, look at this thread

http://archives.postgresql.org/pgsql-general/2005-12/msg00487.php

http://dev.mysql.com/doc/refman/5.1/en/join.html
(Join Processing Changes in MySQL 5.0.12)



--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
                                       Richard Cook

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

snorkel

> one last thing mysql team doesn't afraid to change behaviours between
> minor releases, look at this thread
>
That is so true, all the differences between minor versions made creating
Lightning Admin for MySQL a pain in the rear...

After I did the port I really appreciated how clean PostgreSQL is.

--
Tony Caduto
AM Software Design
Home of Lightning Admin for PostgreSQL and MySQL
http://www.amsoftwaredesign.com


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

Ron Johnson
In reply to this post by Gusti-2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/20/07 15:25, gustavo halperin wrote:
>  Hello
>
> I  have a friend that ask me why postgresql is better than mysql.
> I personally prefer posgresql, but she need to give in her work 3 or 4
> strong reasons for that. I mean not to much technical reasons. Can you
> give help me please ?

The only reason I'd need is that MySQL (even InnoDB) lets you
accidentally insert intrinsically bad data.  According to the
official v5 docs, it's the app programmer's fault if s/he tries to
insert 35-Feb-2007 into the database.  MySQL will purposefully
convert it to '0000-00-00'.

http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF29nmS9HxQb37XmcRAsD9AJ4gGKaCz5gTQD879DBvsay6nHU8+wCfSj3J
98mWmmEqtFKGaDX4ZvU87J4=
=EPxL
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

Adam Rich

I'm not apologizing for their past mistakes.. But the issue
you cite is no longer true:

"As of 5.0.2, the server requires that month and day values
be legal, and not merely in the range 1 to 12 and 1 to 31,
respectively."


mysql> use test
Database changed
mysql> create table test ( td DATE );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values ('35-Feb-2007');
ERROR 1292 (22007): Incorrect date value: '35-Feb-2007' for column 'td'
at row 1
mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.27-standard |
+-----------------+
1 row in set (0.00 sec)



-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Ron Johnson
Sent: Tuesday, February 20, 2007 11:35 PM
To: [hidden email]
Subject: Re: [GENERAL] postgresql vs mysql


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/20/07 15:25, gustavo halperin wrote:
>  Hello
>
> I  have a friend that ask me why postgresql is better than mysql.
> I personally prefer posgresql, but she need to give in her work 3 or 4
> strong reasons for that. I mean not to much technical reasons. Can you
> give help me please ?

The only reason I'd need is that MySQL (even InnoDB) lets you
accidentally insert intrinsically bad data.  According to the
official v5 docs, it's the app programmer's fault if s/he tries to
insert 35-Feb-2007 into the database.  MySQL will purposefully
convert it to '0000-00-00'.

http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF29nmS9HxQb37XmcRAsD9AJ4gGKaCz5gTQD879DBvsay6nHU8+wCfSj3J
98mWmmEqtFKGaDX4ZvU87J4=
=EPxL
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

Tom Lane-2
"Adam Rich" <[hidden email]> writes:
> I'm not apologizing for their past mistakes.. But the issue
> you cite is no longer true:
> "As of 5.0.2, the server requires that month and day values
> be legal, and not merely in the range 1 to 12 and 1 to 31,
> respectively."

Really?

[tgl@rh2 ~]$ mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.32 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test ( td DATE );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values ('35-Feb-2007');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+------------+
| td         |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

mysql>

Note that this case is *not* testing whether mysql knows that
February has less than 31 days.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

Ron Johnson
In reply to this post by Adam Rich
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/20/07 23:59, Adam Rich wrote:
> I'm not apologizing for their past mistakes.. But the issue
> you cite is no longer true:
>
> "As of 5.0.2, the server requires that month and day values
> be legal, and not merely in the range 1 to 12 and 1 to 31,
> respectively."

Only if you set sql modes STRICT_TRANS_TABLES and STRICT_ALL_TABLES
(which *still* allow bogus dates like 2007-02-00!!) or TRADITIONAL.

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

It's (finally) a step in the right direction, but is really only a
"pretty please", since SQL modes are session-time changeable.

>
> mysql> use test
> Database changed
> mysql> create table test ( td DATE );
> Query OK, 0 rows affected (0.01 sec)
> mysql> insert into test values ('35-Feb-2007');
> ERROR 1292 (22007): Incorrect date value: '35-Feb-2007' for column 'td'
> at row 1
> mysql> select version();
> +-----------------+
> | version()       |
> +-----------------+
> | 5.0.27-standard |
> +-----------------+
> 1 row in set (0.00 sec)
>
>
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On Behalf Of Ron Johnson
> Sent: Tuesday, February 20, 2007 11:35 PM
> To: [hidden email]
> Subject: Re: [GENERAL] postgresql vs mysql
>
>
> On 02/20/07 15:25, gustavo halperin wrote:
>>  Hello
>
>> I  have a friend that ask me why postgresql is better than mysql.
>> I personally prefer posgresql, but she need to give in her work 3 or 4
>> strong reasons for that. I mean not to much technical reasons. Can you
>> give help me please ?
>
> The only reason I'd need is that MySQL (even InnoDB) lets you
> accidentally insert intrinsically bad data.  According to the
> official v5 docs, it's the app programmer's fault if s/he tries to
> insert 35-Feb-2007 into the database.  MySQL will purposefully
> convert it to '0000-00-00'.
>
> http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html
>

- ---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


- ---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF2+SQS9HxQb37XmcRAqh4AJwJz41yaTzIkqcAIr1wi7gK7J1QPACgvl07
fVNXVeoJo4vWhbIeGWM5MWs=
=Px12
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

Chad Wagner-3
In reply to this post by Gusti-2
On 2/20/07, gustavo halperin <[hidden email]> wrote:
I  have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?

How about the fact that MySQL accepts the following query as legal:

SELECT foo, bar, COUNT(*)
FROM baz
GROUP BY foo

And produces, naturally, an unexpected result instead of an error.  Totally annoying, I don't know if it was ever fixed.  It seems that MySQL's parser is generally weak at syntax validation in it's default configuration.


--
Chad
http://www.postgresqlforums.com/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

pickscrape
This can (I discovered yesterday) be fixed by adding ONLY_FULL_GROUP_BY
to the sql_mode setting.

As Ron mentioned though that can be happily overridden on a per-session
basis so it's not as 'strict' as it makes out...

Chad Wagner wrote:

> On 2/20/07, *gustavo halperin* <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     I  have a friend that ask me why postgresql is better than mysql.
>     I personally prefer posgresql, but she need to give in her work 3 or 4
>     strong reasons for that. I mean not to much technical reasons. Can you
>     give help me please ?
>
>
> How about the fact that MySQL accepts the following query as legal:
>
> SELECT foo, bar, COUNT(*)
> FROM baz
> GROUP BY foo
>
> And produces, naturally, an unexpected result instead of an error.  
> Totally annoying, I don't know if it was ever fixed.  It seems that
> MySQL's parser is generally weak at syntax validation in it's default
> configuration.
>
>
> --
> Chad
> http://www.postgresqlforums.com/


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

Jan de Visser
In reply to this post by Tom Lane-2
On Wednesday 21 February 2007 1:10:41 am Tom Lane wrote:

> "Adam Rich" <[hidden email]> writes:
> > I'm not apologizing for their past mistakes.. But the issue
> > you cite is no longer true:
> > "As of 5.0.2, the server requires that month and day values
> > be legal, and not merely in the range 1 to 12 and 1 to 31,
> > respectively."
>
> Really?
>
> [tgl@rh2 ~]$ mysql test
... snip ...

It gets better: The problem is not just feb 35, it's also that it doesn't warn
you that it didn't like the input format:

[head sep-head 08:49]$ mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.33 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test ( td DATE );
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test values ('35-Feb-2007');
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> select * from test;
+------------+
| td         |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

mysql> insert into test values ('17-Feb-2007');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+------------+
| td         |
+------------+
| 0000-00-00 |
| 0000-00-00 |
+------------+
2 rows in set (0.01 sec)

mysql> insert into test values ('2007-02-19');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------------+
| td         |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 2007-02-19 |
+------------+
3 rows in set (0.00 sec)

mysql> insert into test values ('2007-02-35');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+------------+
| td         |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 2007-02-19 |
| 0000-00-00 |
+------------+
4 rows in set (0.00 sec)

mysql>                                                    



--
--------------------------------------------------------------
Jan de Visser                     [hidden email]

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

Michael Fuhr
On Wed, Feb 21, 2007 at 08:54:30AM -0500, Jan de Visser wrote:
> It gets better: The problem is not just feb 35, it's also that it doesn't warn
> you that it didn't like the input format:

Actually it did, sort of.

> mysql> insert into test values ('35-Feb-2007');
> Query OK, 1 row affected, 1 warning (0.07 sec)
                            ^^^^^^^^^
mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'td' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

Not as good as "ERROR: hey bonehead, there ain't no such date" but
at least it's something :-)

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

Scott Marlowe
In reply to this post by Gusti-2
On Tue, 2007-02-20 at 15:25, gustavo halperin wrote:
>   Hello
>
>  I  have a friend that ask me why postgresql is better than mysql.
>  I personally prefer posgresql, but she need to give in her work 3 or 4
> strong reasons for that. I mean not to much technical reasons. Can you
> give help me please ?

My personal peeves:

It's got a query parser that's dumb as a brick.  Basically it seems to
work like this:  Got an index?  Cool, use it.  Complex queries quickly
bog down on large data sets in MySQL.  Just read the database forums at
phpbuilder.com or anywhere else that people use mysql a lot and you'll
see request after request to fix up a query performance-wise that
PostgreSQL would run with decent speed.  Further, the output of Explain
is damned near useless.

You can't change a table in any way without rewriting the whole thing,
resulting in a very long wait and a complete table lock on any alter
table action on big tables.  Don't forget that if you've got a really
big table, you need that much space free on the drive to alter the table
for the rewrite that's going to take place.

It swallows column level foreign key contraints and does nothing with
them, no errors nothing, even if you're defining innodb tables.  I.e.
this produces not errors:

mysql> create table a (id int primary key) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
 
mysql> create table b (a_id int references a(id)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
 
mysql> insert into a values (1);
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into b values (1);
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into b values (2);
Query OK, 1 row affected (0.03 sec)
 
That last statement should fail.  Or the creation of table b should
throw a warning.  Or something.

This is with 5.0.19.

So, innodb tables pay for the sins of the fathers (i.e. myisam tables)
and by extension, so do you.

My main gripe about MySQL is that it teaches you bad habits.  It plays
loose and fast with your data, and teaches you to do that too.

If it was a lot faster than PostgreSQL (like it was back in the days of
7.1 or 7.2) it might be worth the effort to overcome its shortcomings,
but it's not.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

Jack Orenstein-2
Scott Marlowe wrote:
> You can't change a table in any way without rewriting the whole thing,
> resulting in a very long wait and a complete table lock on any alter
> table action on big tables.  Don't forget that if you've got a really
> big table, you need that much space free on the drive to alter the table
> for the rewrite that's going to take place.

Forgive a dumb question: What does postgresql do with ALTER TABLE?
What sort of modifications do not require time proportional to the
number of rows in the table?

Jack Orenstein

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

Lincoln Yeoh
In reply to this post by Chad Wagner-3
At 07:31 PM 2/21/2007, Chad Wagner wrote:

>On 2/20/07, gustavo halperin
><<mailto:[hidden email]>[hidden email]> wrote:
>I  have a friend that ask me why postgresql is better than mysql.
>I personally prefer posgresql, but she need to give in her work 3 or 4
>strong reasons for that. I mean not to much technical reasons. Can you
>give help me please ?
>
>
>How about the fact that MySQL accepts the following query as legal:
>
>SELECT foo, bar, COUNT(*)
>FROM baz
>GROUP BY foo
>
>And produces, naturally, an unexpected result instead of an
>error.  Totally annoying, I don't know if it was ever fixed.  It
>seems that MySQL's parser is generally weak at syntax validation in
>it's default configuration.

** syntax/misc gotchas

Too many. See other emails. Or search for MySQL gotchas.

** Feature gotchas
At first look MySQL seems to have all sorts of nice features and
great performance. BUT, when you start to get to the details, too
often you'd find that some features aren't so compatible with others
or take a bit (lot?) more effort to get working properly.

Want transactions? Use innoDB. Want to restore a multi-gigabyte
database fast from backups, sure use MyISAM (too many people seem to
have probs doing that with innoDB).

Want foreign keys to work? Use innoDB. MyISAM tables allow you to
specify foreign keys but ignores AND forgets them.

You can mix MyISAM tables with innoDB tables in the same database.
That's a minus.

Want to back up a consistent snapshot of the database AND still have
users using the database live? Use only InnoDB tables. Because to
ensure consistency when dumping MyISAM tables you should lock all the
tables involved. You still want a live consistent backup of a
database with some MyISAM tables? Here's a method I suggested: use
multiple MySQL servers with replication - do the backup snapshot off
a slave, while users are using the master (or other slaves). If
anyone has better ideas do let me know :).

Do not use innoDB on a filesystem that does not support files > 2GB
in size. Though MySQL +innoDB supports a configurable like
"autoextend:max:1000M", this only works if you using a single shared
tablespace, doesn't work if you are using one
"innodb_file_per_table". BUT if you are using a single shared
tablespace be aware that you can't easily shrink such tablespaces and
reclaim unused space.

Too many IFs, BUTs, ONLYs, etc.

** D'oh level release gotchas
Example: Before MySQL 5.0.13, GREATEST(x,NULL) and
LEAST(x,NULL) return x when x is a non-NULL value. As of 5.0.13,
both functions return NULL if any argument is NULL, the same as
Oracle.  This change can cause problems for applications that rely
on the old behavior.

Or release 5.0.19: The InnoDB storage engine no longer ignores
trailing spaces when comparing BINARY or VARBINARY column values.
This means that (for example) the binary values 'a' and 'a ' are now
regarded as unequal any time they are compared, as they are in MyISAM
tables. (Bug#14189)

** Commercial/strategic gotchas
Oracle owns the companies that make the transactional backends for
MySQL (innoDB, sleepycat).

** Conclusion
In my opinion, if you don't have anything that specifically requires
MySQL, but where MySQL is suggested, it's better to use Postgresql.

Not saying Postgresql is perfect - rather that MySQL makes Postgresql
look really good.

Unfortunately, I have to deal with MySQL at work. *sigh*.

Regards,
Link.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql vs mysql

Scott Marlowe
In reply to this post by Jack Orenstein-2
On Wed, 2007-02-21 at 10:12, Jack Orenstein wrote:

> Scott Marlowe wrote:
> > You can't change a table in any way without rewriting the whole thing,
> > resulting in a very long wait and a complete table lock on any alter
> > table action on big tables.  Don't forget that if you've got a really
> > big table, you need that much space free on the drive to alter the table
> > for the rewrite that's going to take place.
>
> Forgive a dumb question: What does postgresql do with ALTER TABLE?
> What sort of modifications do not require time proportional to the
> number of rows in the table?

It's an interesting subject, and it's not a dumb question.  In
PostgreSQL, indexes live in another file than the table.  In MySQL they
are part of the main table file with myisam tables.  I don't know what
innodb does in this regard.

The only thing I can think of that rewrites a whole postgresql table
would be reindexing it, or an update without a where clause (or a where
clause that includes every row).  Normal operations, like create index,
add column, drop column, etc do not need to rewrite the table and happen
almost instantly.

For instance, on a table with about 30 columns and 100,000 rows, I can
add a column this fast:

alter table brs add column a int;
ALTER TABLE
Time: 57.052 ms

alter table brs rename column b to c;
ALTER TABLE
Time: 33.281 ms

alter table brs drop column c;
ALTER TABLE
Time: 31.065 ms

Of course, mvcc (which both postgresql and innodb use) have other
issues, like doubling the table size if you update every row until the
dead tuples can be reclaimed.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

1234 ... 10
Loading...