Hacker News Reader

Comment by doctor_eval | Hacker News Reader

I think they are right that many people who swear by MySQL have never used another SQL database.

When MySQL hit the scene at the start of the whole LAMP thing, PG was much slower in some use cases than MySQL, and proprietary databases were super expensive. There weren’t too many options. PG was still fairly rough around the edges.

Of course I also needed transactions, and they didn’t come with MySQL - that would hurt performance apparently - so when I was forced to leave Solid due to HP’s acquisition and subsequent pricing hike, PG was the only option for me.

But most developers didn’t need (or didn’t know they needed) transactions, and SQL was the tech de jour, so off they went.

I tried to like MySQL, but the weird not-quite-SQL syntax, lack of transactions and this _weird feeling I got_ put me off.

View on HN

Over my career I've used Sybase, SQL Server, Oracle, MySQL, Postgres, even a bit of DB2 way back in the day. I wouldn't use MySQL if I had another option, but that said I've managed some web sites (Drupal and other CMSs) that used MySQL and it was ... fine. It worked, and never needed any attention.

MySQL is a simple database that's easy to set up and run. Don't make the mistake of thinking there's nothing better, but for certain use cases, it gets the job done.

At that point your options were either a dog slow database (PG), a database that randomly lost data (MySQL), or big money for Oracle. The first thing everyone did when they got their first VC check was spring for Oracle licenses and Sun boxes to run it.

I'm curious - when what true? In the 2000s? Before?

I joined my first startup on 2010 and we had a managed PG database that was good enough for us I reckon.

I’m thinking of the first dotcom bubble—-late 90s into early 2000s.

Around that date there was akso MSSql server 2000. I remember it was good enough and not as expensive as Oracle.

It was essentially Sybase 4.9.2 at that time.

My recollection is that running Windows’ servers was generally and all or nothing thing. Either you were a Windows’ shop that hired all Windows’ people and had a hefty annual contract with MSFT or you’d never consider MSSql.

For some reason I remember Postgresql 8.3 as being the pivotal release that let us adopt it, and that came out in 2008 according to the internet. So yeah we are talking ancient history now.

Looking back at the release notes I can't believe how many of those features are critical to how I use PG today. Really some visionary work there.

https://www.postgresql.org/docs/8.3/release-8-3.html

> With significant new functionality and performance enhancements, this release represents a major leap forward for PostgreSQL. This was made possible by a growing community that has dramatically accelerated the pace of development. This release adds the following major features...

definitely true in the late 90s.

We found SOLID back in the 90s, which was an awesome, full featured Finnish (I think?) SQL database server. It was proprietary but super cheap. Then HP bought it and jacked the price 10x and we were in trouble cos our product pricing at the time depended on the low cost SQL server (this is before cloud of course).

Before that I ran something called Unify which was ... interesting.

> so when I was forced to leave Solid due to HP’s acquisition and subsequent pricing hike

We used the Solid database at my previous company in 1998-2005+. It was a great piece of software. IBM bought it, rebranded it as an in-memory database, marketed it to telecom companies, and now it seems to be completely focused on that: https://en.wikipedia.org/wiki/SolidDB

It's a shame because it was so simple to manage and "solid" as a rock. Worked really well for us and we never had performance problems with it.

As an aside, I was certain that Solid was bought by HP and looking at the wiki page I think we were already using PG by 2007. So it maybe changed hands a few times.

Regardless, it was an awesome database, ahead of it’s time in many ways. And amazing support.

Transactions are rarely useful for a LAMP style setup where you have a stateless web server doing request-response - it's not like you can serve a form to the user and hold a transaction open until they commit it.

Funnily enough I'd say the feeling was what really put me off PostgreSQL. Everything just felt slightly more cumbersome to do; I had to constantly look up all these backslash commands whereas things like SHOW CREATE TABLE might be slower if you spend all day doing database admin, but were a lot easier to remember as a developer who only used them occasionally.

that's the exact argument that I've heard in the 90's... nothing has changed :)

One http request usually does multiple database operations, so why would transactions not be useful?

At the point where you're handling a request the user has already submitted their form, so if you refuse to save what they submitted then that ends up as a pretty terrible user experience (unlike with a rich client application where it might be ok to reject a commit to the server because the user still has a local copy). And if you can't roll back then having a transaction is pretty meaningless.

Transactions are super useful in many contexts, and they certainly aren't about "refusing to save what they submitted". They are about behaving properly when something goes wrong, or when you try to enter data into a database that's invalid.

If you have a form submission that updates multiple tables - say, adds you as a customer, saves your order, and adds it to a queue - then a transaction ensures that either ALL of the tables are updated or NONE of them are. In this way you don't end up with half-orders, or data that you can't use later.

It's far better user experience to fail, than to lie about having succeeded and throw the user's data away.

Transactions are one of the most useful tools in the data toolbox... you can get work done without them, sure, but most of the time you just end up creating something that looks a lot like a transaction, but slower...

1. If the transaction fails due to a conflict it can usually be transparently re-tried (doesn't work if you have external side-effects). So the remaining failures are either due to user error (passing invalid data) or bugs and not related to the use of transactions.

2. Error handling becomes much easier if you can raise an error at any point during request processing and it simply rolls back the whole transaction, since you don't have to duplicate all validation to also run before you first write to the database. One important case is if there is a bug that triggers an assertion failure in the middle of the request processing.

> If the transaction fails due to a conflict it can usually be transparently re-tried

True, but if your transaction is retriable in that way then you might as well write the initial request and then do the processing async afterwards (i.e. event sourcing style).

I mean synchronous retry during the same request on conflicts with concurrent operations. Async processing is usually not an option, because then you can't return the result in the current request. Also retries can fail, which needs to communicated to the user.

Reply on news.ycombinator.com