3 ms·

That is a well-thought out list, and you’re clearly aware of and take advantage of the DB’s capabilities. Seriously, congrats. Especially RETURNING – it’s alway

That is a well-thought out list, and you’re clearly aware of and take advantage of the DB’s capabilities. Seriously, congrats. Especially RETURNING – it’s always baffling to me why more people don’t use it (or its sad cousin in MySQL that lets you get the last inserted rowid if using an auto-increment). Most devs I’ve worked with don’t know about aggregations beyond COUNT and GROUP BY, and do everything in the app. I’ve pointed these out before, and am always told, “we don’t want to have additional logic in the DB.” So you want a dumb bit box then, got it – why are you using an RDBMS? > Transactional DDL I know this is a thing, and I’ve used it in Postgres, but I’ve also never found myself in MySQL being upset that I didn’t have it. Everything should be thoroughly tested in staging before prod. > RLS Yes, amazing feature if you use it correctly. I will give MySQL a couple of nods in its favor: well, three. 1. Clustering index. If you design your schema around this fact, range queries can be WAY faster. Dropbox does this, IIRC. 2. Generated Virtual Columns. Not sure why Postgres still doesn’t support this. 3. ON UPDATE CURRENT_TIMESTAMP. It’s so nice to have the DB automatically track update times for you, IMO.

ttfkam 2y ago
I use transactional DDL all the time, even during development. It's nice to not have to fully reset your schema every time you test a migration file locally. With transactional DDL, you run the whole list, and if any fails, it rolls back to where you started. You look at the error, edit your migration, and try again. It really is a time saver. There is a peace of mind always knowing your schema is in a consistent state not unlike the peace that comes from a good set of unit tests. 1. Yep, I definitely miss clustering indexes in Postgres sometimes. I can sometimes fake it with covering indexes when all I want are an extra column or two along with the primary key or similar without seeking to the main table, but you're right about that MySQL/MariaDB win here. 2. The dynamic computed column is an easy workaround with immutable functions that take the record as a param. CREATE TABLE foo ( a int, b int, c int ); CREATE FUNCTION d(entry foo) RETURNS int LANGUAGE sql IMMUTABLE AS $$ SELECT foo.a + foo.b + foo.c; $$; SELECT a, b, c, d(foo) FROM foo; It's not part of the table schema when doing a SELECT *, but it is just as efficient as a computed column in MySQL/MariaDB and only slightly more verbose. 3. ON UPDATE CURRENT_TIMESTAMP works in Postgres with a trigger function, which you can reuse if all your tables use the same name for your "last_modified" column (probably a good idea anyway). Not as convenient as the declarative syntax, but it's a fairly trivial workaround. CREATE OR REPLACE FUNCTION update_last_modified() RETURNS TRIGGER AS $$ BEGIN NEW.last_modified = now(); RETURN NEW; END; $$ language 'plpgsql'; CREATE TRIGGER foo_last_modified BEFORE UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE update_last_modified(); CREATE TRIGGER bar_last_modified BEFORE UPDATE ON bar FOR EACH ROW EXECUTE PROCEDURE update_last_modified(); One function, many triggers. You also get to choose between "when transaction started" (now() or CURRENT_TIMESTAMP), "when statement started" (statement_timestamp()), or "right now" (clock_timestamp()). I don't mind workarounds so much as functionality that simply cannot be replicated. For example I miss real temporal table support in Postgres like what you can find in MariaDB or MS SQL Server. The painful kludges for missing PIVOT support like in MS SQL Server is another one. You never know how much you need deferred foreign key constraints until you don't have them anymore. Or a materialized view.
- evanelias 2y ago
  > It's nice to not have to fully reset your schema every time you test a migration file locally In terms of dev flow, this is only a problem with imperative migration systems. Declarative schema management tools solve it by being able to transition any live database state into the desired state, which is expressed by a repo of CREATE statements. If something fails, you fix the bad CREATE and run the tool again, and it effectively picks up where it left off. And well-designed declarative tools catch many problems pre-flight anyway by running a suite of linters, running the emitted SQL statements in a sandbox first, etc. If the tool's diff returns clean, you know your schema is in the right state on the DB. Ironically, lack of transactional DDL actually makes declarative schema management more straightforward in MySQL/MariaDB: you can't mix DDL and DML there anyway, so it's more natural to handle schema changes vs data migrations using different tools/pipelines.

ttfkam 2y ago

I use transactional DDL all the time, even during development. It's nice to not have to fully reset your schema every time you test a migration file locally. With transactional DDL, you run the whole list, and if any fails, it rolls back to where you started. You look at the error, edit your migration, and try again. It really is a time saver. There is a peace of mind always knowing your schema is in a consistent state not unlike the peace that comes from a good set of unit tests. 1. Yep, I definitely miss clustering indexes in Postgres sometimes. I can sometimes fake it with covering indexes when all I want are an extra column or two along with the primary key or similar without seeking to the main table, but you're right about that MySQL/MariaDB win here. 2. The dynamic computed column is an easy workaround with immutable functions that take the record as a param. CREATE TABLE foo ( a int, b int, c int ); CREATE FUNCTION d(entry foo) RETURNS int LANGUAGE sql IMMUTABLE AS $$ SELECT foo.a + foo.b + foo.c; $$; SELECT a, b, c, d(foo) FROM foo; It's not part of the table schema when doing a SELECT *, but it is just as efficient as a computed column in MySQL/MariaDB and only slightly more verbose. 3. ON UPDATE CURRENT_TIMESTAMP works in Postgres with a trigger function, which you can reuse if all your tables use the same name for your "last_modified" column (probably a good idea anyway). Not as convenient as the declarative syntax, but it's a fairly trivial workaround. CREATE OR REPLACE FUNCTION update_last_modified() RETURNS TRIGGER AS $$ BEGIN NEW.last_modified = now(); RETURN NEW; END; $$ language 'plpgsql'; CREATE TRIGGER foo_last_modified BEFORE UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE update_last_modified(); CREATE TRIGGER bar_last_modified BEFORE UPDATE ON bar FOR EACH ROW EXECUTE PROCEDURE update_last_modified(); One function, many triggers. You also get to choose between "when transaction started" (now() or CURRENT_TIMESTAMP), "when statement started" (statement_timestamp()), or "right now" (clock_timestamp()). I don't mind workarounds so much as functionality that simply cannot be replicated. For example I miss real temporal table support in Postgres like what you can find in MariaDB or MS SQL Server. The painful kludges for missing PIVOT support like in MS SQL Server is another one. You never know how much you need deferred foreign key constraints until you don't have them anymore. Or a materialized view.

evanelias 2y ago
> It's nice to not have to fully reset your schema every time you test a migration file locally In terms of dev flow, this is only a problem with imperative migration systems. Declarative schema management tools solve it by being able to transition any live database state into the desired state, which is expressed by a repo of CREATE statements. If something fails, you fix the bad CREATE and run the tool again, and it effectively picks up where it left off. And well-designed declarative tools catch many problems pre-flight anyway by running a suite of linters, running the emitted SQL statements in a sandbox first, etc. If the tool's diff returns clean, you know your schema is in the right state on the DB. Ironically, lack of transactional DDL actually makes declarative schema management more straightforward in MySQL/MariaDB: you can't mix DDL and DML there anyway, so it's more natural to handle schema changes vs data migrations using different tools/pipelines.