shlomi-noach

There is always a schema, and PlanetScale, in particular, promotes the use of schemas. That is, behind the scenes we operate Vitess, which is an infrastructure and sharding framework on top of MySQL. You will create schemas in PlanetScale just as you would in MySQL. Managing the schema, though: operating schema changes in code and in production, mitigating schema conflicts, scheduling migrations, running online migrations, experimenting with schema changes, etc., are something relational databases don't provide the right (or any) tooling for developers, and this is one of the things PlanetScale aims to bring to developers.

In my past experience as a production engineer, and in our many discussions within the community, we've seen many approaches developers take just to avoid the hassle of running yet another ALTER TABLE; whether because it requires going through a different team, or because this poses an availability risk, etc. PlanetScale offers to bring ownership back in the developer's hands.

Hope this clarifies. We'll publish more soon, and I'll be able to provide more links.

(I'm an engineer at PlanetScale)

(Edit for grammar/typos)

Per https://news.ycombinator.com/item?id=27203568, there is a a normal MySQL schema. This is about how PlanetScale solves the schema management friction, so as to enable developers working with schemas: safe branching, online schema changes, deployment queues, protected branches, conflict resolution. (I'm an engineer at PlanetScale)

Deepthi, tech lead for Vitess, just published this RFC a few days ago: https://github.com/vitessio/vitess/issues/7084

Vitess plans to support PostgreSQL, but there is no work on that at this time.

The origin of the name 'freno' is discussed in the README: https://github.com/github/freno#whats-in-a-name

    What's in a name?
    "Freno" is Spanish for "brake", as in car brake. 
    Basically we just wanted to call it "throttler" or 
    "throttled" but both these names are in use by multiple 
    other repositories and we went looking for something else. 
    When we looked up the word "freno" in a dictionary, we 
    found the following sentence:

    Echa el freno, magdaleno!

    This reminded us of the 80's and that was it.

Asterix: "You make me sick talking about boars"

Obelix: "And you bore me talking about sickles"

(inexact quote from memory, Asterix and the Golden Sickle)

Absolutely priceless translation!

They can be a hassle when you want to shard your data, having outgrown your single-instance capacity. You will either shard functionally (extracting complete tables to other database servers), in which case FKs will completely break, or horizontally (split rows across database servers), in which case you may or may not be able to still use FKs.

They're also a performance impact on large tables since inserts/deletes must make multiple trips to the tables/indexes. That's a growing operational hassle as tables grow larger.

No, on the database itself. I used "child side" for lack of better terminology. Every foreign key has two edges: the "parent" table and the "child" table. Rows in the "child" table depend on existence of matching rows in the "parent" table.

It should be feasible to run `gh-ost` to ALTER a table that only has "child"-side constraints. It will be impossible to run `gh-ost` to ALTER a table that has "parent" side constraints.

Hope this clarifies.

Author of gh-ost here. Actually, it should be possible to support child-side foreign keys. They would have to be named differently (the foreign key constraint has a unique name in a schema) -- but it should work. See discussion in https://github.com/github/gh-ost/issues/507

It is true that it is not on our roadmap to implement FK support for gh-ost (see https://github.com/github/gh-ost/issues/331), but if anyone wishes to contribute support for FK we're grateful. We've had more complex contributions coming from the community and we're grateful for those.

Author of gh-ost here. Here are my thoughts on migrating to a different RDBMS: http://code.openark.org/blog/mysql/mysql-vs-postgresql-gh-os...

Since you already use HAProxy you should get that almost for free. Your app would talk to the databased by communicating through HAProxy, which would direct traffic to the single-writer; HAProxy's health tests would detect who the single-writer is, and would adapt in the event of failover.

> In my experience, real-world concurrency issues (that you are talking about) are always hard to find in manual/automated testing

I agree, and I don't have a good, generic, strategy for that. I didn't give the best example.

Agreed, and having some experience with both MySQL and SQLite, running SQLite in testing cannot cover problems you'd find with MySQL (e.g. SQLite is so limited with concurrency that you cannot use it to confirm concurrency issues will be dealt with in your production MySQL). I would assume the same holds for SQLite<->PostgreSQL.

I'm curious whether pretix attempted using MariaDB/Galera with single-write mode, such that the three servers still run synchronous replication, but only one of them gets the writes.

I suspect (disclaimer: not running Galera myself) "WSREP has not yet prepared node for application use" and deadlocks would be mitigated, and am curious to learn if that were indeed the case.

Here's how to see all your PRs:

https://github.com/search?q=is:pr+author:YOURNAME&type=Issue...

Here's how to see all your open PRs:

https://github.com/search?q=is:pr+is:open+author:YOURNAME&ty...

Replace YOURNAME with your GitHub handle, or pick your colleagues names.

> I'd be willing to bet over 99.999% of all deployed production web applications serving requests today -- has a bottleneck at the database and the network that dwarfs any overhead from language performance

A language is not just about overhead, it's also about capability. For example, your database is slow, and your web page needs to render by executing 20 queries. Will your programming language allow you to parallelize those queries? While your database/network may have high latency, they can still have good throughput.

This particular example is very real, and is incidentally relevant to the original post, since Python and Go implementations have different capabilities.

Just curious about the MySQL problems you hit.

MySQL replicas self-recover via MASTER_CONNECT_RETRY and MASTER_RETRY_COUNT. e.g. set to 1, 8640000 respectively to make a replica retry every one second for up to 100 days before finally giving up.

At least this is true for network errors. If replication breaks due to data inconsistency, it should stay broken and a human should look into it, IMHO.

Corrupted tables and repairs suspiciously sound like MyISAM, and a realize this ancient engine still lurks at some places. I haven't done a "repair" for over 14 years -- the last time I used MyISAM in production. Using MyISAM today is analogous to using the ext2 file system today.

In any case this just clarifies a couple comments on MySQL and has nothing to say on MySQL vs. PostgreSQL.

> MariaDB 10 was a game changer for the mysql landscape

Can you please elaborate in what respect?

> I've been looking for something similar for Postgres and MySQL/MariaDB without any luck, and it really surprises me there's not more interest in doing migrations this way.

skeema (suggested above by evanelias) and square/shift are two management tools for MySQL/MariaDB.

On the lower level, I'm authoring GitHub's gh-ost, an online schema migration tool, and we have Ruby on Rails + scripts automation around that. Described in https://www.percona.com/live/17/sessions/automating-schema-c... .

I suspect no matter what solution you'd use, you will always need to break migrations into parts that are not fully automated. e.g. when dropping a column you'd first deploy code that ignores said column, only then run the migration to actually drop the column.

@jivid the logical backups are done per-table, not per-server. Per-table logical backups are useful to the engineers owning the data. It makes it easy for them to restore data from a single table.

When an engineer loads logical backup data, it loads into a non-production private zone where the engineer has access to the data, and can then make informed decisions on whether there is need to re-apply data changes (due to bug, due to need to review historical data, etc.).

This of course has the advantage of quicker restores (only need a single table), and this happens to cover the vast majority of cases. This doesn't cover the case where we need to restore consistent data for two or more different tables.

Enough with the FUD please. These claims are so 80s. MySQL & InnoDB is fully ACID compliant.