Hacker News Reader

Comment by munk-a | Hacker News Reader

I carried out a (quite ill-advised in retrospect) migration from MySQL to Postgres on a mature codebase with absolutely no automated test coverage and it went pretty smoothly. We had some bumpy performance directly after the production migration but we were able to reach parity after about two months of observing and adding targeted indices.

I absolutely adore Postgres's tweaks to the SQL language, that dialect is amazing and has served us extremely well. And, since we passed pg10 a while back, the performance tuning you can do on it is pretty amazing.

View on HN

> I absolutely adore Postgres's tweaks to the SQL language

Do you mean additions to the SQL standard, or bits of the standard that MySQL doesn't implement, or something else?

The reason i ask is that i also enjoy writing SQL for PostgreSQL, but as far as i know, i am sticking to standard SQL. Perhaps there are things i'm missing, or things i like which i haven't realised are nonstandard!

MySQL does not conform to the standard in a number of ways. On the one hand, thankfully you can turn on ANSI MODE. On the other hand if you're using MySQL you've probably got a bunch of devs used to their non-ANSI SQL and turning on ANSI MODE may break stuff they do.

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...

I assume they mean non-standard additions in PostgreSQL. A lot of the very handy pg functions are non-standard, as well as the rich text matching capabilities, many data types, and even the RETURNING clause, I believe.

There is definitely stuff that is non-standard, like the casting with :: in addition to CAST AS.

But it's true that PSQL sticks very closely to the standard or only adds on top of it, while MySQL is very uncomformant.

Funnily enough, I actually first used Postgres over MySQL a long time ago when I moved from PHP to NodeJS, and was annoyed that MySQL uses backticks for identifiers while PSQL uses SQL standard double quotes, because in JS multi-line strings (for SQL queries) are also use backticks and I was annoyed by escaping. Sometimes it's the little things...

It is practically essential for any RDBMS to provide non-standard statements and features. For example, even CREATE INDEX isn't in the SQL standard, since the SQL standard deliberately avoids talking about what it considers to be implementation details. This is probably a good goal in many ways. But what counts as an implementation detail is open to interpretation.

You could say the same thing about the isolation levels. They are supposed to be platonic ideals, free from any implementation baggage. But the reality is that you can tell that the people that originally defined how they work were mostly (perhaps entirely) thinking about old school two-phase locking.

I'm not a critic of the standard -- it's imperfections (which are arguably contradictions) reflect real world differences that are hard or impossible to resolve. Just pointing out that this is how it is.

Oh, it's totally fair to criticize the standard. Those clowns lock their deliberation and standard definitions behind huge paywalls.

The best way to learn the SQL standard is to read the Postgres docs since they clearly call out non-compliances - there is no other easy to access and clear explanation of the SQL standard.

Any “platonic ideals” mention outside its natural scope deserves attention.

You don't think the backticks lend a nice old school vibe?

If you use any of the really amazing JSON or Array stuff, you're stepping outside of the standard.. but it's worth it!

I absolutely love Postgres' JSON and array support. Its saved me from needing a document database in a few cases, which drastically simplified the overall deployment (one DB instead of two, or trying to represent relational stuff in a document DB). I really like JSON for stuff that is user defined, that I don't need to query into, but postgres' support means I still can should I ever need to. Which has happened once or twice for debugging.

100%. I've dealt with many small-to-medium sized projects where the usual approach would have been some form of EAV mess.

Just using a BSON column for such attributes hits the Pareto 80/20 sweet spot, _and_ I can query against them much more easily than in an EAV schema (far fewer joins).

They're great for when the schema is mostly normalized, but there's just a little bit of denormalized stuff that doesn't warrant completely architecting the schema around it.

> but postgres' support means I still can should I ever need to.

And since postgres supports indices on arbitrary expressions you can use CREATE INDEX for the json-queries in your WHERE:

CREATE INDEX ON publishers((info->>'name'));

Yes, Postgres' jsonb and array support lets you not only query into these fields, but also, like you say, index into them too. Its really the best of both worlds: you get a great solid relational database, but if you need document database features, you don't really lose out on anything by using json, except that the queries are a bit more ugly. I'm super happy with postgres these days, its rock solid and full of fantastic features.

The word "any" there is mildly misleading about the JSON things, since there is now SQL/JSON which PG supports: https://www.postgresql.org/docs/13/functions-json.html#FUNCT...

It would be horribly painful to stick to _just_ SQL/JSON for manipulations, and I have absolutely zero idea what the coverage is in other platforms, but portability isn't completely off the table like it used to be

Personally, back before JSON became a standard thing - I made heavy use of HSTORE to clean up some insanely cludgey string packing BS we'd been doing - leveraging recursive CTEs using the UNION definition allowed us to move a fixed tier system into a self-referential one that saved us a lot of headaches as well.

Other folks have mentioned :: instead of CAST AS (which is really nice when you need to write it a bunch of times) but it's mostly the diverse built-in function set and constraint support that keeps me an enthusiastic Postgres supporter.

> as far as i know, i am sticking to standard SQL

PostgreSQL documentation is very good about comparing each feature to the standard.

For example, the SQL standard specifies that triggers fire in the historical order they are added to the table.

PostgreSQL chose to fire them alphabetically.

Technically nonconformant, but in practical terms, far better.

Also the c++ bindings are very mature.

This was surprising for my old tech lead who was sheepish on moving away from MySQL.

He would later refer to the MySQL bindings as “brain dead”

Why would alphabetically be better? Wouldn't you want first in first out?

It means that the order they fire is imperative and determined by how you run your migrations.

The problem is that it doesn't leave you a great way to adjust order. It's just kinda weird.

(To be fair, table columns share this characteristic.)

> To be fair, table columns share this characteristic.

The table columns order is meaningless, but the triggers order is very relevant.

Table column order is not meaningless.

``` INSERT INTO foo SELECT * FROM bar ```

The harder problem however, is order in composite types.

Yeah, I do really recommend people not to do that sort of thing when they ask me.

Alphabetical makes it so two databases with the same triggers have the same behavior. FIFO breaks that, because now it depends on trigger construction order (eg when dump/restoring) and is totally invisible to the user.

Additionally, modifying a trigger by drop/creating it would change behavior, unless you also drop/create every trigger (in the original order)

Thank you most important thing I learned today.

What is an example where the order of execution is required, not contrived or bad practise. Looking for a good use case

Easiest I can imagine is a user-facing id that gets incremented by trigger (e.g. select max(col)+1 — autoincrement/serial doesn’t guarantee contiguity, or even reasonable increments…).

And then any second trigger that relies on that value

Oh, I think it's probably bad practice to knowingly depend on that.

But I also think that it's unnecessarily problematic to make the order non-deterministic.

Why was this down-voted? It is a good question. I thought exactly the same myself. And the reply from @setr is helpful. (Hat tip) I never thought about the impact of dump/restore!

Not only what @setr answered but consider also that by having a consistent ruling you can actually design or alter the order of triggers just by naming them according to a specific scheme.

It just makes easier to understand what happens when triggers are invoked, without having to actually force specific cases by direct experimentation.

I did not even know that the standard prescribed a FIFO order, looks like a pretty bad idea to me, actually.

We also did a MySQL 5.7 to PG 9.6 migration (thanks to https://pgloader.io/) and we got some pretty immediate speed improvements (2-5x).

We shot ourselves in the foot a few weeks later with some poorly written RLS policies, but it was overall a smooth experience.

pgloader is great.

Sadly for me when I tried to make a PoC of Postgres for our eCommerce platform I found that we had violated our constraints and lost data.

That was the day I went from MySQL agnostic to MySQL hating.

It largely doesn’t matter that the defaults could be configured to be more strict. We live in a world where people are trying to avoid hiring sysadmin and that database was set up by a developer. It should not have been a default.

> It largely doesn’t matter that the defaults could be configured to be more strict. We live in a world where people are trying to avoid hiring sysadmin and that database was set up by a developer. It should not have been a default.

Everyone agrees with this. That's why they changed the default nearly 6 years ago.

Yes, the old default was terrible. But there's no way to change the past, so why complain about it for years and years?

All versions with this bad default have hit end-of-life, what more can be done?

I am very much an example of one of those previously MySQL devs that now uses Postgres for new projects, though I've never done a migration between the two.

I had an app with a fairly complex and large set of tables and data and migrated it from mySQL to pg with very little trouble. It was a few years back, but from what I recall a re-wrote a few queries in my data layer and that’s about it. Much easier than it was to setup nginx to take over some Apache duties.

Reply on news.ycombinator.com