3 ms·

Schemas have saved my ass a million times too, but schema migrations are a pain. I'd love a database that's loose with types by default, but allows you to add

by m0th87 14y ago

Schemas have saved my ass a million times too, but schema migrations are a pain. I'd love a database that's loose with types by default, but allows you to add declarative specifications for types. Documents are checked at insert/update time, but otherwise the specifications play no role in how the data is stored. That way you get schemas, but only when you need them. And migrations don't require heavy collection-wide locking; instead, you just change the constraints you enforce and lazily update old documents. Postgres does to an extent with CHECK constraints, but you still have to do an ALTER TABLE if you change the underlying type of a column.

petergeoghegan 14y ago
Yes, you still have to do an ALTER TABLE to change the type of a column within Postgres, because, well, that's just how it's done. This does not necessarily mean that the table will be rewritten, which I assume is what you're concerned about. See this item from the Postgres 9.2 release notes: Reduce need to rebuild tables and indexes for certain ALTER TABLE ... ALTER COLUMN TYPE operations (Noah Misch) Increasing the length limit for a varchar or varbit column, or removing the limit altogether, no longer requires a table rewrite. Similarly, increasing the allowable precision of a numeric column, or changing a column from constrained numeric to unconstrained numeric, no longer requires a table rewrite. Table rewrites are also avoided in similar cases involving the interval, timestamp, and timestamptz types.
lwat 14y ago
> ...you still have to do an ALTER TABLE if you change the underlying type of a column. I don't see why this is a problem? How often do you update really big (hundreds of millions of rows) columns' data types in practice? Most of the time you'd change type early in development when you don't have that much data in the table yet which makes it pretty much instantaneous. If you really need to update the data type for a supersized table you could always make a new column instead and migrate lazily to that in the background. But all of this happens so rarely that in my view it's a silly excuse to dump all the great benefits you get from having an enforced database schema.
- saurik 14y ago
  > If you really need to update the data type for a supersized table you could always make a new column instead and migrate lazily to that in the background. I do this "irritatingly often". That said, the fact that while I'm doing it I have the advantages of schemes to make certain I do it correctly is amazing ;P. Even in a world where I have to do this every day, I still wouldn't feel "ok, let's not have a schema at all" would be a better fix to that problem. (To be clear: I am not "disagreeing" with you: I am agreeing with a point that is even stronger than the one you made, as I think you ceded too much ground ;P.)
lucian1900 14y ago
Migrations are the main reason I like schemas! Trivial migrations (like adding columns) are trivially managed by a good library (SQLAlchemy + Alembic or Django + South) and more complex migrations are possible. Without a explicit schema, it's very hard and sometimes impossible to change the shape (schema) of your data.

by m0th87 14y ago

petergeoghegan 14y ago

Yes, you still have to do an ALTER TABLE to change the type of a column within Postgres, because, well, that's just how it's done. This does not necessarily mean that the table will be rewritten, which I assume is what you're concerned about. See this item from the Postgres 9.2 release notes: Reduce need to rebuild tables and indexes for certain ALTER TABLE ... ALTER COLUMN TYPE operations (Noah Misch) Increasing the length limit for a varchar or varbit column, or removing the limit altogether, no longer requires a table rewrite. Similarly, increasing the allowable precision of a numeric column, or changing a column from constrained numeric to unconstrained numeric, no longer requires a table rewrite. Table rewrites are also avoided in similar cases involving the interval, timestamp, and timestamptz types.

lwat 14y ago

> ...you still have to do an ALTER TABLE if you change the underlying type of a column. I don't see why this is a problem? How often do you update really big (hundreds of millions of rows) columns' data types in practice? Most of the time you'd change type early in development when you don't have that much data in the table yet which makes it pretty much instantaneous. If you really need to update the data type for a supersized table you could always make a new column instead and migrate lazily to that in the background. But all of this happens so rarely that in my view it's a silly excuse to dump all the great benefits you get from having an enforced database schema.

saurik 14y ago
> If you really need to update the data type for a supersized table you could always make a new column instead and migrate lazily to that in the background. I do this "irritatingly often". That said, the fact that while I'm doing it I have the advantages of schemes to make certain I do it correctly is amazing ;P. Even in a world where I have to do this every day, I still wouldn't feel "ok, let's not have a schema at all" would be a better fix to that problem. (To be clear: I am not "disagreeing" with you: I am agreeing with a point that is even stronger than the one you made, as I think you ceded too much ground ;P.)

lucian1900 14y ago

Migrations are the main reason I like schemas! Trivial migrations (like adding columns) are trivially managed by a good library (SQLAlchemy + Alembic or Django + South) and more complex migrations are possible. Without a explicit schema, it's very hard and sometimes impossible to change the shape (schema) of your data.