Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows

От: Andy Colson
Тема: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows
Дата: ,
Msg-id: 4CFE8E59.2010505@squeakycode.net
(см: обсуждение, исходный текст)
Ответ на: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Tom Polak)
Ответы: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Richard Broersma)
Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Gary Doades)
Список: pgsql-performance

Скрыть дерево обсуждения

Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Tom Polak, )
 Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  ("Kevin Grittner", )
 Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Andy Colson, )
 Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Andres Freund, )
 Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Craig James, )
 Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Andy Colson, )
  Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Richard Broersma, )
   Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Kenneth Marshall, )
    Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Andy Colson, )
     Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Tom Polak, )
      Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Craig James, )
      Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Gary Doades, )
      Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  ("Kevin Grittner", )
      Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  ("Pierre C", )
       Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Tom Polak, )
        Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Craig James, )
         Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Scott Marlowe, )
          Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Gael Le Mignot, )
           Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Scott Marlowe, )
           Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  ("Pierre C", )
        Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Richard Broersma, )
         Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Justin Pitts, )
        Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Rob Wultsch, )
        Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Robert Haas, )
         Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Andy Colson, )
         Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Tom Polak, )
          Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Robert Haas, )
        Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Andy Colson, )
  Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Gary Doades, )
 Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Mladen Gogala, )

On 12/7/2010 1:22 PM, Justin Pitts wrote:
>>
>> Also, as a fair warning: mssql doesn't really care about transactions, but
>> PG really does.  Make sure all your code is properly starting and commiting
>> transactions.
>>
>> -Andy
>
> I do not understand that statement. Can you explain it a bit better?

In mssql you can write code that connects to the db, fire off updates
and inserts, and then disconnects.  I believe mssql will keep all your
changes, and the transaction stuff is done for you.

In PG the first statement you fire off (like an "insert into" for
example) will start a transaction.  If you dont commit before you
disconnect that transaction will be rolled back.  Even worse, if your
program does not commit, but keeps the connection to the db open, the
transaction will stay open too.

There are differences in the way mssql and pg do transactions.  mssql
uses a transaction log and keeps current data in the table.  In mssql if
you open a transaction and write a bunch of stuff, the table contains
that new stuff.  Everyone can see it.  (I think default transaction
isolation level is read commited).  But if you set your isolation level
to something with repeatable read, then your program will block and have
to wait on every little change to the table.  (or, probably page.. I
think mssql has page level locking?)

anyway, in PG, multiple versions of the same row are kept, and when you
open, and keep open a transaction, PG has to keep a version of the row
for every change that other people make.  So a long lasting transaction
could create hundreds of versions of one row.  Then when somebody goes
to select against that table, it has to scan not only the rows, but
every version of every row!

So my point is, in PG, use transactions as they were meant to be used,
as single atomic operations.  Start, do some work, commit.

mssql made it easy to ignore transactions by doing it for you.  Ignoring
transaction in PG will hurt you.

you can google MVCC and "postgres idle in transaction" for more.

-Andy


В списке pgsql-performance по дате сообщения:

От: Ivan Voras
Дата:
Сообщение: Re: Performance under contention
От: Robert Haas
Дата:
Сообщение: Re: Performance under contention