Hacker News Reader

Comment by chris_wot | Hacker News Reader

The main advantage Oracle has over SQL Server in my opinion is that Oracle has tablespaces and multi-version concurrency control baked in, whereas SQL Server has ONE TempDB and snapshot isolation relying on that one TempDB.

View on HN

On the nose. SQL Server is a great product, but their MVCC is basically hacked in. They're also lacking in the features Oracle has around their Undo tablespace, and auditing.

As a PostgreSQL user I dread the idea of running a database without MVCC, even MySQL and SQLite have decent MVCC support.

Then you really don't want to read about escalation locking...but if you want to be horrified visit the following:

https://technet.microsoft.com/en-us/library/ms184286(v=sql.1...

Postgres MVCC is anything but decent. It will take over control of the main heap. Good luck tackling that.

What exactly does that mean? MVCC is definitely different in Oracle and Postgres, but that doesn't mean it "takes over the heap" it just means it stores a new version of the row in the same heap. Later on an auto-vacuum process clears out the old heap page, or sometimes on demand when a select, update or delete accesses the row.

Oracle stores the old version of the row in a rollback segment.

According to the following article, "only changed values are written to undo whereas PostgreSQL/SQL Server creates a complete new tuple for modified row. This avoids bloat in the main heap segment." [1]

1. http://www.enterprisedb.com/postgres-plus-edb-blog/amit-kapi...

The very next sentence:

Both Oracle and SQL Server has some way to restrict the growth of version information whereas PostgreSQL/PPAS doesn't have any way.

True, this can be an issue. But the auto-vacuum and auto-cleanups should resolve this issue. Have you experienced this as an issue? Genuinely curious.

(Don't speak to me about SQL Server. Of all the brain dead ideas, implementing storage in the TempDB for snapshot isolation!)

Reply on news.ycombinator.com