[SOLAR_FIELDS]

One thing author did not touch on: since the tables were denormalized-ish in the original MySQL DB, did his application lose significant performance by having to perform the joins for every single query in the renormalized PostGres instance? Or were the DB's small enough and indexed properly enough so it didn't really matter?

Might have been worthy of testing this to see if it was worth it to materialize certain views and refresh them every 15-30 minutes or so.

[koolba]

Alternatively did the application speed up after it was normalized? A normalized data set can be substantially smaller which may allow the entire data set to fit in memory.

Also, it's officially "PostgreSQL" or informally "Postgres". Not "PostGres".

[Amezarak]

At least in my experience, joins in 100s of GB databases are not a performance problem. I've always been somewhat baffled by the claims I see pretty regularly that joins are a major performance concern. Was this a big deal in the past?

[gaius]

Was this a big deal in the past?

In MySQL, yes. It lacked the concept of foreign keys.

[kondro]

Lack of foreign keys does not effect performance.

In fact, having foreign keys is a net-negative performance-wise because it means that each foreign key must be checked for every insert/update/delete.

[branko_d]

Foreign keys can improve performance, in some cases:

https://stackoverflow.com/a/8154375/533120

[gaius]

Errm, we're talking about performance of joins

[ComputerGuru]

Joins will use the regular index matching it against the join condition just like a where clause would.

[kondro]

They don't help with that either.

[Sacho]

Perhaps parent is conflating indexes with constraints? I believe InnoDB creates indexes automatically with foreign keys.