DEV Community


Explain Postgresql Views Like I'm Five

sloan profile image Sloan ・1 min read

I simply cannot fully grasp this concept no matter how hard I try. I'd love a few explanations!


Editor guide
vnbrs profile image
Vinicius Brasil

There are two ways to make a pancakes: with eggs, flour, baking powder and milk or with a pancake mix.

Think of a database view as pancake mix. The ingredients were already prepared before. You just have to mix and heat it.

A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query. []

4lch4 profile image
Devin W. Leaman

If I'm not mistaken then, they sound like assigning a method name to an SQL function that was built previously.

Am I close?

vnbrs profile image
Vinicius Brasil

Yes, you're getting close. I'll try to write some pseudo code so you can understand better:

// creating a view
paid_orders_view = SELECT * FROM orders WHERE status = 'PAID';

// using this view
SELECT SUM(subtotal) FROM paid_orders_view;

Not assigning a function, but a SQL statement. In most cases a query.

Thread Thread
ben profile image
Ben Halpern

Why not have this logic stored in the program itself, even as a simple string variable? What am I gaining from using this functionality?

Not challenging, 100% curious.

Thread Thread
isaacdlyman profile image
Isaac Lyman

One nice thing about a function stored in SQL is that SQL can create an "execution plan" and store it as well, so it doesn't have to reparse and plan everything each time you submit the query. It speeds things up, especially with queries you use frequently.

Thread Thread
erikpischel profile image
Erik Pischel

For me, views often are kind of abstractions or interfaces. Especially when the view contains joins. It hides the details of the data model. Database programmers can even change those details while maintaining the SELECT clause of the view so for the application programmer the interface has not changed.

Thread Thread
vnbrs profile image
Vinicius Brasil

Hey Ben,

SQL views aren't just stored queries, it's a virtual table. You can make joins and select specific columns, granting permissions to different database users to it, instead of granting permission to every table. Views are also indexed, when talking about materialized views.

So by using SQL views you've got: security, performance and simplicity. Also, not every system is backed by a modern application layer.

kirillian profile image
John Epperson

These answers are partly true. Everything said so far is pretty correct, especially considering the open source community used MySQL for so long which only has this type of view. However, in postgres, you have the option of creating a materialized view which is everything said above but then the database runs the query and stores the data to disk so that running queries against it doesn't spend the time running again. 9 times out of 10 in postgres, you will want to use materialized views.

The benefits of basic views are pretty much barely anything at all. You can get the benefits of a pre-planned and stored query by using prepared statements (which, you probably already do automatically if you use a modern framework as this is a standard defense against SQL injection).

At the end of the day, all features, views included are tradeoffs. I rarely find basic views to be worth it (though others on this page clearly have found reason for it). I DO find that materialized views give me enough value for the added complexity to justify their use when needed. Note that materialized views must be refreshed as they are a kind of caching of your data. But, then again, I really find that views make the most sense on the reporting side of things where caching is a respected and legitimate pattern.

shoupn profile image
Nick Shoup

I use views, and create views frequently. Not necessarily in postgres. I've used views mostly to do joins from various tables and do basic calculations across joined fields. Usually then called and stored in another table that others on my team have permissions on. They don't need permissions on the hundreds of tables on the various schemas. As was said think of views as a saved sql statement.

moopet profile image
Ben Sinclair

One thing I used to use views for was on an unknown schema to join everything I could find together into one enormous pile of data, so I could get an overview of what was going on quicker. I wouldn't use these monsters for anything apart from that though!