DEV Community

Play Button Pause Button
dev.to staff
dev.to staff

Posted on

The What and Why of PostgreSQL Views

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.

More info on PostgreSQL views

Top comments (8)

Collapse
 
daniel15 profile image
Daniel Lo Nigro

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.

Collapse
 
annarankin profile image
Anna Rankin

Oh, cool - I actually didn't know that! Thanks :D

Collapse
 
buinauskas profile image
Evaldas Buinauskas

That's partially true. For instance SQL Server cashes parameterized queries and saves their execution plans.

At least to my knowledge 🙂

Collapse
 
walker profile image
Walker Harrison

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?

Collapse
 
annarankin profile image
Anna Rankin

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.

Collapse
 
maestromac profile image
Mac Siri

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!!

Collapse
 
ben profile image
Ben Halpern

Materialized views (discussed starting at 1:49) seem like a really clean way to get big performance gains without gumming up application logic.

Collapse
 
andy profile image
Andy Zhao (he/him)

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!