Anna Rankin stops by to discuss the choice to go with PostgreSQL views in order to automate processes and clean up application code.
Views are pseudo-tables. That is, they are not real tables; nevertheless appear as ordinary tables to SELECT. A view can represent a subset of a real table, selecting certain columns or certain rows from an ordinary table. A view can even represent joined tables. Because views are assigned separate permissions, you can use them to restrict table access so that the users see only specific rows or columns of a table.
Top comments (8)
Great video! I like how you mentioned that it "feels right". That's really a huge part of application design - The best design patterns often just feel like the right thing to do.
The other thing that you didn't mention is that when using SQL views, the database engine can do some pre-optimization of the query, as it knows the exact query that's going to be ran. This can result in a small performance boost for very common queries. With a regular query, it needs to parse, prepare, and optimize that query every single time you run it.
Oh, cool - I actually didn't know that! Thanks :D
That's partially true. For instance SQL Server cashes parameterized queries and saves their execution plans.
At least to my knowledge 🙂
awesome stuff. What happens as the time sensitivity of your data increases? perhaps GA enrollment data isn't the best example, but for something like finance or trading, does it make sense to update a view multiple times per second or at that point is it no better than just querying the original DB every time?
Thanks, Walker! :D If you need up-to-the-second results, I'd stay away from a materialized view and go with a regular ol' view and generate results on the fly. In my experience, materialized views are best for things that don't change often.
That "feels right" feeling (1:40) is what I always strive for. When a code or process is clean, it welcomes future changes and keeps errors to the minimum. This is some great stuff!!
Materialized views (discussed starting at 1:49) seem like a really clean way to get big performance gains without gumming up application logic.
I like how she articulated the problem at 1:03, and also the solution later.
Also, big yes to code feeling "just right." Such a good feeling!