https://dba.stackexchange.com/questions/195104/postgres-grou... has some more info on this
That all said, until that happens, I'd strongly suggest avoiding that functionality since it can lay down some real landmines.
I am trying to work around this but so far my only solution is to parse the SQL of the view definition and extract the information from there. I have it somewhat working but it's a bit complex for my liking..
SELECT DISTINCT ON (vt.id)
row_to_json(vt.*) FROM vt
JOIN vy ON vt.id = vy.tid
WHERE vt.id = 1
AND vy.amount > 1;
SELECT posts.*, MAX(COALESCE(comments.created_at, posts.created_at)) AS latest_timestamp FROM posts LEFT OUTER JOINS comments ON posts.id = comments.post_id GROUP BY posts.id ORDER BY latest_timestamp desc
In short, this is sorting posts by the most recent comment, with a fallback to the post date if the post has no comments on it. Hard to get rid of the grouping here and get the same data back.
1. Apply: the new schema is created and any new data is added to the backing table.
2. Cleanup: the schema is deleted and any data that is no longer needed is removed from the backing table.
For example, to perform a column deletion:
1. Apply: create new schema without the column which is being deleted. Don't change anything in the backing table.
2. Cleanup: delete the schema and the column from the backing table.
I've always liked the idea of using views as an API. But, I'd never considered using Postgres' schemas as a way of versioning that API. This approach wouldn't work for every kind of migration but definitely seems correct and useful in some cases.
As others have pointed out you still of course have to deal with the technical debt of clients using the "old" schemas/views... but that is a problem that has to be addressed anyway... so I don't consider that to be a "flaw" of this approach; it's something close to orthogonal to this approach.
The interesting part is when you actually get to pay the debt back. Yes, this is not free lunch. You move fast, but you have to eventually pay the cost of the underlying storage containing all the historical schemas combined at once.
This is the difference between carefully planned schema migrations and abstraction-based migrations. The former takes more time planning and executing, but the end state is the reference schema. The latter keeps on extending the underlying schema and building projections over it. Over time, the system accumulates tech debt in the form of ever-expanding base schema that contains all the historical columns.
The point is not to let old clients work indefinitely.
In other versions of this idea, all views/procedures are prefixed with some revision-number but that looks messy.
That purging is what has a lot of operational complexity. And renaming a column. All the rest is zero-downtime in PG already.
We rarely drop columns due to this, but haven't found it to be a huge issue. If we need to, we'll wait until the last supported version that has the column in its schema is retired. Then we'll add an explicit "drop column" to our schema file for the next version.
In the simplest form, when you add a column to some schema, it should be materialized in the base schema and exposed via the migration view.
The problems start when you add and remove 20 columns because even though they are no longer visible in migration schemas, they take up space in the base schema
All I'm saying is that I don't think we need a full Turing-complete cannonball to hit the (relatively) small fly of no-downtime migrations.
Is it a silver-bullet? It's Turing-complete so high chances yes. But for me it has a high risk of causing a silver-poisoning.
Personally, I would stick with simpler solutions.
Add Xn+1: create new views without column.
When no Xn clients remain: drop column from tables and drop schema Xn.
The point as I see it is to not break live client connections which expect the column to exist.
You can use views to make migrations that were previously tricky zero-downtime.
If that's not the case, then I mist've read the article wrong!
Edit: although when I think of it - if you want to eventually materialize old migration schemas into the base schema, you need to do the rename, too. Which is not zero-downtime because of new migration schemas that do the renaming automatically. Meaning changing views' definition, meaning lots of locking.
So, you still need maintenance windows to merge all the changes. Just not on every change. Otherwise the base schema will then eventually be completely out of sync and contain tons of old, unused columns.
Now, when you remove a column, you just create a new view without it being available. But the column in the base schema must stay because: you support the old schema, and because you want to avoid the operational complexity of physically removing it, right?
Now, you can obviously split a table into a list of tables each storing the pk and some column value. But you have just invented columnar storage in a row-based rdbms :D
A different approach is to have scheduled maintenance window, for example quarterly or every 6 month when you materialize all the changes at once. The amortized operational cost is way lower :)
Until you have no current clients left, you can't delete the old view or remove underlying columns that that old view requires. Once they have, you can execute that phase.
 https://www.postgresql.org/docs/12/sql-createview.html (See "Updatable Views")
Generally I've found these useful in the short term for letting application logic continue to work blissfully ignorant to the fact that the "table" it is writing to has been moved, merged or refactored into a set of tables that the trigger will bridge logic over to. While in the long term I've found that the automatic logic covers the most common case, a view that's filtering out some rows for some reason (permissions - soft deletion) before presenting it to the application logic.
- Dropping a column doesn't work (assuming the point of dropping it is actual deletion, rather than just hiding it)
- Adding a column doesn't work either
- Changing a column's nullability, default value, or data type doesn't work
- Doesn't help with adding or changing constraints or indexes
So when dropping a column it would go like this:
1. Migration is applied. Columns is removed from the new view but remains in the backing table.
2. Migration is no longer needed. Column is removed from backing table.
This design should be extendable to cover changing data type (introduce a new column and moves values over + sync values using trigger), changing default or changing the contents of a full column.
Constraints or indices are a bit trickier, but it might work to create a new column duplicated from the old one and add constraints to that instead.
Not sure about default value change, but I would also say its zero-downtime as adding a column with default is zero-downtime. Haven't checked, though.
Dropping a column and changing the data type are not zero-downtime.
Renaming a column is not zero-downtime as you might have multiple readers/writes using both.
Making a previously nullable column NOT NULL is not zero downtime. Neither are adding constraints -- except in some cases with NOT VALID, which isn't quite the same thing.
> Dropping a column and changing the data type are not zero-downtime.
Dropping a column is zero downtime.
In this particular case, rather than making everything a view all the time, you could just use views during a migration window to get the same effect. Replace the table with a view which has all the columns plus the new name as an alias for the old one, migrate all the clients, replace the view with the table with the column renamed. No special permanent crap necessary.
View performance can be a thing at larger scale for OLTP workloads, also, the solution you propose also adds complexity since you have two schemas now instead of one, and as you rightly point out, complexity with views themselves. The question becomes when is this added complexity worth it?
So views don't add complexity relative to giving the query explicitly. It might even help since the incoming queries can be smaller, thus less parsing/processing.
Creating a separate "data owner" and "api owner" to own the api and data schema+tables/views respectively alleviates this problem. Your API schema owner != your table owner unless you `ALTER TABLE FORCE ROW LEVEL SECURITY` in addition to just enabling it.
What issues are you running into with RLS?
We can test this.
create role data;
create role api;
create role bob;
create role alice;
create schema api_v1 authorization api;
create schema data authorization data;
set role data;
create table data.user_profile (
id int generated always as identity primary key,
grant usage on schema data to api;
grant select on data.user_profile to api;
insert into data.user_profile (username, value) values
('bob', 'Bob''s First Value'),
('bob', 'Bob''s Second Value'),
('alice', 'Alice''s First Value');
create policy "Users can only see their own data"
for select using (current_user = username);
alter table data.user_profile enable row level security;
select * from data.user_profile; -- shows 3 record, both bob and alice's
set role api;
create view api_v1.user_profile as
select * from data.user_profile;
grant usage on schema api_v1 to bob;
grant usage on schema api_v1 to alice;
grant select on api_v1.user_profile to bob;
grant select on api_v1.user_profile to alice;
select current_user; -- currently api
select * from api_v1.user_profile; -- shows 0 records
set role bob;
select * from api_v1.user_profile; -- shows 2 records, both bob's
set role alice;
select * from api_v1.user_profile; -- shows 1 record, alice's.
-- both alice and bob cannot access the data schema or it's contents. RLS is respected for the api_v1 schema.
CREATE OR REPLACE FUNCTION my_view() RETURNS TABLE (...columns) AS $$
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_view();