Ask HN: How is your experience with Oracle, Postgres and MySQL?

https://news.ycombinator.com/item?id=9705135

by tuyguntn • 11 years ago

5 24 11 years ago

news.ycombinator.com

This is a follow up to https://news.ycombinator.com/item?id=9702372. How was your experience with these databases, PG, Oracle, MSSQL, MySQL. If you describe with one sentence how do you describe them in one sentence.

breakingcups3• 11 years ago

PG: Great in theory, virtually no GUI tooling that's up to the task. MSSQL: Excellent, wish it ran on Linux.

tuyguntn2• 11 years ago

Could you elaborate, why PG is great on theory? I have mostly worked with only PG and have little experience with MSSQL, but latest versions of PG is really great.

breakingcups3• 11 years ago

Well, reading about all the features PG has makes me very excited. Purely querying from PG also makes me happy thus far. What I'm uneasy about is administration (backups, etc.).

I guess part if it is also that I'm very used to the MS tooling, which is truly superb. This makes some of the administration steps for PG seem a bit outdated and clunky, which could be a very unfair bias from my side.

Maybe another way of putting it would be that the core is great, but the edges a bit rough for my spoiled, delicate hands.

samspenc• 11 years ago

Have you tried Aqua Data Studio? Its not just for PG - can connect to quite a few different DB types and works really nicely with all of them.

Aqua is commercial software, but pretty inexpensive, and we use it at my company to connect to the different databases we work with.

breakingcups3• 11 years ago

I haven't yet, I might look into that.

subrat_rout• 11 years ago

What about pgAdmin3. I have been using is for last two years and it gets the job done.

fiedzia• 11 years ago

MySQL: half-backed, inconsistent, full of quirks and traps PG: My db of choice.

stonemetal2• 11 years ago

In my experience MSSQL and Oracle are more or less interchangeable. I have never run across PG running at the same sort of scale as the other two, but it seems to be a contender. Though I would have to agree with breakingcups, PG lacks tooling compared to the other two.

tuyguntn2• 11 years ago

Can you say little more about what kind of scale are you talking?

stonemetal2• 11 years ago

Not very big, 10s of millions of rows. The biggest Postgres install I have gotten to work with was in the tens of thousands of rows. I am sure Postgres could handle more I just haven't seen it in the wild.

needusername3• 11 years ago

Oracle: Doesn't scale down. Lacks a lot of convenience features (BOOLEAN, DATE, IF (NOT) EXISTS, …). You need a team of DBAs to manage it and have a good relationship them (send birthday cards for their kids, I'm not making this up). You need to have people on your team with a good understanding of Oracle. Then the speed and information you can get out of it is amazing — given your corporate structure gives you access to a good disk subsystem.

Support is a waste of time (we have a priority 1 bug open for more than half a year).

11.1g was so buggy that your DBAs would forbid us to go to 12.1c (BETWEEN AND wasn't working correctly).

I just wish they would open source their JDBC driver and accept pull requests.

Scale: about 10 TB, tables with between 500M and 1.5B rows, slow disk subsystem (SAN with about 150k IOP/s)

pjungwir• 11 years ago

The last time I used Oracle was 8i, but it certainly supported IF (NOT) EXISTS.

I mostly agree with you about scaling down, and culturally Oracle DBAs are big at gatekeeping, but as a fairly junior database developer I was still able to figure out the basics of installing and configuring it, so maybe it's not that bad.

needusername3• 11 years ago

I meant IF (NOT) EXISTS for DDL. It exists for DML of course.

iracic• 11 years ago

Handling requests can be tricky. Support site is very useful (althrough not a best example of user interface). Usually, you do not want to put your important stuff on first version of some release. Also new features, as somewhere said, are as some good wine - you need to give them some time to have a good use of them.

You need a team of DBA That surprises me any time I hear. Do you think you would need less people using some other RDBMS ?

JDBC driver is actually very good. How would you improve it?

needusername3• 11 years ago

Support site is very useful

No it's not. They do everything in their power to stall and put everything on you until you give up. First they ask you to upload dozens of pointless trace files that have nothing to do with the issue. Once that's done they'll ask you to "describe your architecture in detail". Then then it gets bogged down for months "in development" with no updates at all from their side.

you need to give them some time to have a good use of them

Yeah right, because it's not like we're paying them multiple millions a year and can expect them to ship features that are tested and work. I mean it's Open Source, they're doing it in their free time and they accept patches. Right?

Do you think you would need less people using some other RDBMS ?

Yes

JDBC driver:

- basically fix everything that shows up during our traces:

-- don't use String#substring() for parsing SQL (hint to Oracle, you made it slow in 1.7.0_06, maybe you should have sent the memo to the rest of your organization)

-- don't use regexes to do a case insensitive string compare

-- there are more but these are just the ones I can remember on top of my head

- fix buggy features:

-- fix accessing generated keys

-- fix accessing statement metadata (that was once working for the 12c driver but would throw an ArrayIndexOutOfBoundsException when you had a comment in the query and a NULL bind parameter, their "fix" was to always throw a SQLFeatureNotSupportedException)

-- fix ResultSet#getDate to be JDBC Spec compliant (is was spec compliant in 11g but they changed it in 12c to violate the spec because "we know better than the JDBC specification" and "a major version allows us to do breaking changes")

-- there are more but these are just the ones I can remember on top of my head

- implement Java 8 support

- basically apply every patch they have rolled out since the last driver release but they couldn't be bothered to make a new driver release because they do driver releases only with database releases because that makes a lot of sense

- 12c has acceptable memory management, 11g had horrible memory management

- there are more but these are just the ones I can remember on top of my head

cmollis• 11 years ago

oracle is great, has lots of great tools, and seems to be pretty solid. It is also ridiculously expensive. I have found Postgres to be essentially equal to it in terms of programmability (triggers, stored procedures) and.. it's free. It keeps getting better.. lots of larger scale startups use it (like instagram), and is essentially eating Oracle.. particularly where Oracle plays best (at the fortune 100 corporate level). Most of my clients have plans to migrate to it, or are actively doing that. I've used Mysql for smaller stuff and it's ok..why use it when you have postgres. The license is better anyway.

Haven't used MSSQL in years, but it's very good (on Windows).

thorin• 11 years ago

Oracle XE is good, free and still pretty good but you are limited to small(ish) amounts of data. You can do most of your testing and dev with it though. There are a number of options such as Standard One which offer pretty cheap licenses for most of the functionality you'd need to compete with Postgres and MSSql.

That said, I've had great experiences with Postres and MSSql too, it's just that I learnt all my core db stuff from Oracle 7 and moved on from there.

I haven't bothered with mysql for some time. I think I'd always go for Postgres in preference.

MSSQL and Oracle have many advantages when working in coporate/enterprise environments and you probably won't have to worry about licensing costs so much then!

thornygreb• 11 years ago

Longtime Oracle dev/dba. I have been using PG now for over 6 years, and it is my #1 choice for new projects if possible. Oracle beats it in instrumentation and tooling and to me the Oracle RDBMS documentation is more thorough. The implementation of MVCC is radically different and I'm not too keen on vacuuming but it is a necessary evil tradeoff for the benefits in PG's implementation. And above all I can put PG on a box with 40 cores and have dozens of hot standby's for $0 in licensing fees.

dalacv• 11 years ago

I love Oracle XE (Express Edition - Free) and use it regularly for any side projects. Oracle APEX is not bad either.

bsg75• 11 years ago

PostgreSQL has great language features and has been a stable OLAP platform. Coming from MSSQL, the only feature I miss is query parallelism.

The lack of equivalent feature in MySQL makes it difficult to work with once accustomed to others (OLAP context, not OLTP).

ibejoeb• 11 years ago

edit: Sorry, I missed the "1 sentence" guideline.

I have a lot of Oracle commentary in my post history, but I would still like to chime in again with a specific case that may be interesting to some people here. I'm totally unaffiliated with Oracle, so if it sounds like a pitch, it's just because I've had a long, positive experience with their technology.

There is absolutely nothing on the market, commercial or OSS, that can touch Oracle Database. If you really take advantage of it, it'll be clear.

I wrote a couple of big financial software applications, and reporting is a huge component. One of the key differentiators in my product vs the competition is that all of the reporting is ad-hoc, whereas theirs is batch. This is important to my customers, because they typically have planning and modeling work to do, and waiting around for batches just won't fly.

There are a few ways to get this kind of ad-hoc reporting experience, but the easiest is by directly issuing queries against the data. For this to be viable, though, you need two things: An expressive data manipulation language and the ability to exercise the hardware.

Oracle is the only database that delivers. Oracle's support for modern SQL and its proprietary extensions (e.g., [1]) are incredibly powerful, but they're expensive CPU-wise. However, Oracle will parallelize these, effectively running a single query over n cores.

Without those features, I'd have been building change monitors, data diffing algorithms, solvers, and all of that stuff to support background partial recomputation. Job runners, queuing, cache invalidation... man, just thinking about all that stuff again makes me realize just how nearly impossible it would have been to get this company off the ground.

Also, a note on pricing: it's expensive if you need these features, and licensing truly is arcane, but their are some pretty good options now. Several others have pointed to Oracle's free and cheap offerings, like XE and SE1, but Oracle is available on RDS with licensing included. You can quite literally amortize the licensing cost by the hour and quit anytime, and it's nothing more than an option when you provision your instance.

All that said, if you really need it, buying a $40k license is an awful lot cheaper than buying a couple $150k engineers to make it work on some other platform.

This has really informed my philosophy, especially when doing things on a startup budget. Get the best tools you can and use the hell out of them for all they're worth. There are thousands of engineer-years behind Oracle, and they've tackled a lot of hard problems for you. It's always a shame when I look at an operation and see people doing suboptimal reimplementations (you did you own materialized view?) because nobody read the docs...

[1] http://docs.oracle.com/cd/B19306_01/server.102/b14223/sqlmod...

drifter89• 11 years ago

DBA & PL-SQL/UNIX Developer here. Primary work with Oracle and SQL Server. Also support PG, and MySQL.

SQL Server hands down has the best GUI tools, none of the other DB's can even match this. Lots of information/documentation online. Makes life a lot easier. With Flashback being added to SQL16, it's going to be really hard to beat. Setting up, and maintaining HA options like Mirroring, Clustering, Replication is significantly easier that all the other DB's I've used.

Oracle is great, and also a pain in the ass. Works on any OS...but in my experience works best on Solaris. They really need to improve their tools. If you want to be effective you pretty much have to use the command line, and sql plus. There is nothing wrong with that, and in fact I prefer this method for most things. However, once you get used to SQL Server, it's crazy how much time you save, by having simple tools. Data Grid sucks ass, and is just a pain. They try to push this now, instead of the command line tools, pretty big mistake in my eyes. Oracle also has a lot of new features that are truly awesome. Like Audit Vault, and data masking.

PG is pretty awesome, but most enterprise level applications do not support it. Same issue with MySQL. The learning curve is also steeper for these. I don't believe MySQL, or PG can come close to SQL Server, or Oracle.

cafard• 11 years ago

Oracle: use it a lot, find PL/SQL an excellent language, wish that it were less expensive.

MSSQL: Use it some, wish that T-SQL had more of the features of PL/SQL, don't mind it.

PostreSQL: Limited experience, like it OK. May yet port some Oracle stuff there.

MySQL: Don't mind it, but don't use it much.

Ask HN: How is your experience with Oracle, Postgres and MySQL?

Related Stories