Re: Oracle vs SQL Server vs MySQL vs Postgres

I started working with Postgres recently and Jeremy Schneider kindly shared some resources. One of them was "The Internals of PostgreSQL" http://interdb.jp/pg/index.html which answered a lot of my "how does it actually works" questions.

Gwen

On Wed, Mar 01, 2023 at 2:03 PM, kyle Hailey <kylelf_at_gmail.com> wrote:

> Yeah, I was thinking about writing it but I only know the high level and
> not the details.
> Reading Jonathan Lewis' Oracle 8i intro blew me away. It was such clear
> powerful description of Oracle.
> I'd love to see that for the 4 main databases.
> When you do a select what happens? WHen you do a transaction ? what
> happens and in what order? Of course I knew at one time and remember the
> basics but not the details.
> like a transaction, you get an ITL , you write some stuff to the rollback,
> everything including rollback gets written to the redo etc etc delayed
> block clean out etc etc
> What happens on Postgres? I don't know. You stuff your new data in tothe
> block, leave the olds stuff, the vacuum process comes along and clean out
> all the bloat as long as there is no one anywhere in the database including
> completely unrelated databases and unrelated schemas with a transaction
> open etc. etc
> What happens on SQL server? what get gets written to rollback ? there is
> only rollback written when there is row level lockiing?
> What happens on MySQL? what is history length list? How does MySQL track
> and rebuild versions of blocks? Does it just leave the old data in the
> blocks until some later date? Who cleans it out and how?
>
>
>
> On Wed, Mar 1, 2023 at 1:44 PM John Thomas <jt2354_at_gmail.com> wrote:
>
> Kyle, sounds like you have a first draft :-) We'll look forward to the
> published version!
>
> Oh, and MySQL's equivalent of a shared pool mechanism was pretty worthless
> some years ago.
>
> Regards,
>
> John Thomas
>
>
> On Wed, 1 Mar 2023 at 20:55, kyle Hailey <kylelf_at_gmail.com> wrote:
>
>
> Anyone know of good doc on the comparisons across the major RDMS ,
> like the high level performance OMGs?
>
> Examples
>
>
> - SQL Server by default is missing row level locking (OMG! of course
> you can turn it on at a cost)
> - Postgres , any open transaction, be it "create table foo(id int);
> select * from foo; " tanks performance for everyone on the DB doing
> transactions, everyone.
> - Postgres - you wrap your transaction IDs and your database goes
> down a day or two
> - MySQL has similar issue with "create table foo(id int); select *
> from foo; " where the history length list grows.
> - MySQL wait events are worthless. the main wait, like 80% of all
> waits is a CPU, IO and LOCK all rolled into one, yes indeed, worthless OMG
>
>
> Kyle
>
>

--
http://www.freelists.org/webpage/oracle-l