Hacker News Reader

Comment by bjpirt | Hacker News Reader

I'm a happy Postgres user and recently did some work with a government agency using Oracle - the thing that shocked me most about Oracle was the lack of transactional DDL operations which was something I'd just taken for granted in the Postgres world.

View on HN

Coming from MySQL to Postgres a few years back the transactional DDL statements were a joy to work with - I've had to claw a legacy into the modern era and utilizing them has allowed me to execute live migrations from legacy into shims and then from shims into modern.

I also really appreciate the transactional TRUNCATE - I pretty much never use it but at least in Postgres I never have to worry about someone else trying to run one and wiping state unexpectedly.

> I also really appreciate the transactional TRUNCATE - I pretty much never use it but at least in Postgres I never have to worry about someone else trying to run one and wiping state unexpectedly.

as long as auto commit is not enabled.

These goodies are possible, because of PostgreSQL's MVCC which requires running vacuum. Nothing is for free unfortunately.

Interestingly even though MSSQL server uses an extremely different implementation of MVCC, it internally has a vacuum equivalent. (Which is required even when all MVCC support is disabled! It is used to enable efficient implementation of deletes, without having to use absurdly coarse locks).

MSSQL just handles doing that cleanup silently in the background while exposing basically no no configuration except a trace flag that can turn it off.

I don't think undo vs. heap based MVCC choice has any big impact with regards to transactional truncate, nor transactional DDL in general. You are likely to see proof of that within a couple of years.

To be fair I think only Postgres has that feature.

Also SQLite3.

And MSSQL.

With quite a few exceptions you have to be aware of to avoid losing data during migrations.

Reply on news.ycombinator.com