Hacker News Reader

Comment by tucnak | Hacker News Reader

You can express it in SQL; you would need to create a table with "the contents of file Y" from S3, or use something like PostgreSQL foreign data wrappers to that effect: we're using pg_analytics[1] to query CSV/Parquet data, and the same applies to most databases these days.

The limitation you're implying—is not that of SQL, but your data model.

[1] https://github.com/paradedb/pg_analytics

View on HN

Both you and sgarland are missing the point here: the example I gave is literally just that: an example. There could be many more complex cases, some relying on other business logic provided by your application, some more isolated. Some of those cases could be handled using some random PG extension, but for many that won't be sufficient.

In contrast, a migration written in an actual programming language can handle all such cases. Depending on the amount of abstractions applied, it can also look close enough to a declarative language (in other words, it doesn't have to be verbose).

So yes, the limitation I'm implying very much is a limitation of SQL (or really any declarative query language for that matter). It has nothing to do with the data model as it applies equally well to using e.g. MongoDB, Redis, or really anything else that stores a pile of data you may want to transform in complex ways.

What can I say, use a shitty schema—win shitty prizes. This is why we never allow coders to touch the schema: you _will_ screw it up given the tiniest chance. Suddenly, my data model has to be aware of "all such cases." Thank you please no good bye.

Unfortunately what you wind up with, IME, is devs “owning” their DB (sometimes it’s a schema on a shared instance, sometimes it’s its own instance), until such time that the poor decisions catch up. Then it’s an incident, at which point the DBRE / SRE team asks WTF, nurse the thing back to life, and then are summarily ignored for future advice. Rinse and repeat.

I’m hoping my current job breaks this trend.

True. Although to be fair, I think you're a bit naive to assume that this is a trend, if anything. IMO there's no escaping Ownership issues: if your culture is to "move fast break things", it would be inevitable. The ownership philosophy is such that some gaps in ownership are inevitable.

I think you’re missing the point that what you’re describing is not a migration as a DB{A,RE} would call it. That is what I’m pushing back on.

If you’re shifting or otherwise manipulating tuples, then yes, you probably want to handle that logic in a more general-purpose language (though it isn’t _required_, annoying though it might be otherwise).

But for DDL? No, I don’t want anything in the way. The state should be stored in VCS, of course, but I don’t want some abstraction trying to be clever about ALTER TABLE foo. Just run it how I specified.

Reply on news.ycombinator.com