Hacker News Reader

Comment by Justsignedup | Hacker News Reader

As a developer:

- No license fees is huge. My company saves tens of thousands on license fees when we can't really afford it.

- The ability to set up environments super quickly is amazing.

- Rollback of DDLs is huge for testing migrations

- The backup features aren't broken by default. I've spent hours researching how to back up an oracle db and transfer it to another. And at that point versions matter. With postures I think I spent 30 minutes total figuring it out over the years.

Overall I am a huge fan of Postgres having used it for about half of my career and oracle for the other half. I genuinely hope I never use oracle again.

View on HN

We have a database server at work that costs us about $10k a year. It’s bare metal so that gets a nice spec. We’re probably a little over provisioned, but not crazy so.

The sticker price of the Oracle licence for this would be about $400k a year.

It’s just a complete non-starter for most companies.

Obviously you don't have experience with Oracle or you would have written hundreds of thousands of dollars in license fees... /s

Obviously you don't have experience with Oracle or you would have written millions of dollars in license fees... /s

Funny story: I worked at a place which spent millions annually with Oracle. They had a couple hours downtime every day because they couldn’t afford the online backup product.

This was an Ivy League university with an endowment in the tens of billions.

This is not true. There is no separate licensing for archivelog mode (the single thing that is needed for online backups).

That may be the case now but it was either different back then or neither their Oracle-trained DBAs nor their Oracle consultants was aware of it.

I was working with an even worse Oracle product (Hyperion) which had a convoluted homegrown script restarting all of the services on a number of servers to work around their inability to reliably handle database connection failures lasting more than a short interval. Before do that, they’d tried to get rid of the backup window but were told that wasn’t possible unless they could come up with a large amount of money for the license.

> Rollback of DDLs

Oracle can't roll back these (create table/drop index etc)? That really surprises me. Pretty certain MSSQL can, because I've used it. Amazed, actually, that oracle can't.

Very few databases support transactional DDL, meaning that you can do something like:

  BEGIN;
  DROP TABLE foo;
  CREATE TABLE bar (t int);
  ROLLBACK;

...and it will just work as you expect. Oracle doesn't, nor does MSSQL.

As far as I know, other than Postgres, only Sybase ASE, Informix, DB2 UDB, and Firebird support transaction DDL.

> Very few databases support transactional DDL

Postgres, SQL Server, Firebird, DB2, SQLite, Ingres, Informix, Teradata, Vertica all support transactional DDL

SQL Server partially supports it - indexes for example are not versioned which causes snapshot transactions to fail if someone rebuilds an index - even if it’s not in scope of the snapshot.

That doesn't sound right by my experience, Could you give a link please? I'm concerned I've not heard of this. Probably because it's MVCC/snapshot isolation which I'm not familiar with.

DDL is transactional in SQLite.

Well, https://www.mssqltips.com/sqlservertip/4591/ddl-commands-in-...

MSSQL can roll back DDL. But the DDL changes are not fully transactional, as other trans sacri one can see new columns and tables before the DDL transacrion commits.

Postgres gets it right. DDL is contained in the transacrion until commit.

That sounds like a transaction isolation level being set too low. Really sounds like it.

Having DDL is nice until you are trying to add indexes to a production system and can’t have it locking the entire table. Then you need to add the index concurrently and Postgres does not support concurrent index creation inside of a DDL transaction.

> No license fees is huge. My company saves tens of thousands on license fees when we can't really afford it.

It isn't always about affording it, it's about risk reduction, or the perception thereof:

Every single time the company I work for has to renew a license, it has to once again initiate the long, painful process of finding its ass with both hands. "Why are we paying for this? Who exactly is using it? Why are they using it? How much will this cost? Can we make it cost less? Given that it's impossible for us to negotiate this cost, how can we make the company we're licensing it from not charge us as much money as it's inevitably going to? When is this license due? Why hasn't IT, which has no purchasing authority, fixed this yet?"

With Postgres and CentOS and other OSS, none of that happens. The C-levels never know, so they never get a chance to do their headless-chicken dance routine. Much more relaxing.

Some points for you, if I may:

- Why are you worried about license fees as a developer? :-) Seriously speaking, start with Oracle SE2. It is so cheap, I would use it everywhere. And if you need those "partitioning", "parallel execution" etc - your business is Enterprise, and you can afford such price tags. (I know, there will be a sh1tst0rm now, but please think about what IO said from business POV)

- Oracle Public Cloud gives you database extremely fast - and you will never have issues with license audit

- The name of "Rollback of DDLs" is "no developer access to production" and "UAT" :-)

- hmmm... $> rman rman> connect target rman> backup database plus archivelog delete input; rman> exit

What can be easier?

Are you a huge fan of Vacuuming, XID Freezing, WAL optimisations etc. etc.?

The MVCC in Postgress, in my view (!) is joke from academia, where they had not a single DBA :-)

Why is MVCC in Postgres a joke in academia?

Reply on news.ycombinator.com