RE: [LUG] furthering the revolution: ECDL

["Henshall, Stuart - WCP" <SHenshall@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx>]

> From: Aaron Trevena [mailto:betty@xxxxxxxxxxxx]
>
>
> On Sat, 9 Feb 2002, kevin bailey wrote:
> > > > we see postgres having a good future because it has had 
> ACID compliance
> > > > built-in from the begining.
>
> > > My understanding was that MySQL now provides sensible behaviour
> > > via an alternative table type, but I do wonder how many people
> > > understand the relevant differences in quality between
> > > apparently competing database products.
>  
> > i'll have to check this out further but i believe MYSQL was 
> file based 
> > initially - and has now had record locking and transactions 
> added on - 
> > which doesn't feel too good.
>
> if you use certain table types - the it can kind of implement 
> transactions, but it is a hack.
>  
> > postgres is probably better for DB's where the size is in 
> the order of 
> > gigabytes - whereas MYSQL is suited to small jobs and being the DB 
> > behind small websites.  if the web-site is running 
> e-commerce though 
> > postgres would again be better,
>
> I'd disagree :) MySQL scales very well on doing rapid 
> selections and joins 
> (i.e. generating content, etc) it scales less well for 
> inserts ( accepting 
> data, etc) and doesn't really do transactions properly.
>
> Postgres and MySQL can combine well by having all the transactions 
> (updates, etc) handled by Postgres and then Have MySQL update 
> itself from 
> Postgres when its not too busy and otherwise concentrating on 
> pushing out 
> results as quick as it can.
>
> It should be quicker to update N records in 1 go than to 
> update N records 
> in N queries as there will be less locking and no contention 
> / blocking.

While it is normally quicker to do the updates at once if possible,
if you are going to be updateing a _lot_ of records it can be worth 
splitting them into batches of, for instance, 100,000. This is due to
a) Update locks being held during the whole transaction. Therefore 
increasing the chance of contention with another transaction (in 
PostgreSQL only update contention - writers & readers don't block each 
other). If you're going to basically put a table out of use though, 
you can just grab an appropriate table lock.
b) In PostgreSQL the WAL will need to hold the data necessary to redo 
the transaction. This can lead to a growth of WAL files, which could 
end up being created in critical paths (as well as eating up more disk 
space). I assume most other transactional ACID db's have similar 
problems.

> > i have to set up a postgres DB on a server soon and will be 
> running some 
> > tests - i'll try to find time to set up MYSQL as well and 
> run the same 
> > tests - i'll post any results/conclusions,
>
> You'll find that MySQL isn't case sensitive and Postgres is - if your 
> dirty has varying or poor capitalisation it can be a pig to 
> clean it all. 
> Also Postgres uses ' instead of " for quoting values in 
> queries. Finally 

I believe this is according to SQL-92 spec (it uses " for quoting
names).
Also PostgreSQL isn't case sensitive for table names unles quoted
like "NaMe", only when comparing data in text, varchar, etc.....
This you can solve by lower(field_name)=lower('text').
You can also index on lower(field_name). Hmmm... Guess you could do
create a case insensitive type and then use that....

> Postgres doesn't allow you to drop a column from a table - 
> you have to 
> create a new table without teh column with data inserted by a 
> great big 
> select - this can be a pig.

You're right there :(

> regards,
> A.

Cheers,
- Stuart

--
The Mailing List for the Devon & Cornwall LUG
Mail majordomo@xxxxxxxxxxxx with "unsubscribe list" in the
message body to unsubscribe.