On Sun, Nov 2, 2008 at 4:11 PM, Simon Windsor <simon.windsor@xxxxxxxxxxxxxxx> wrote: > Hi > > Generally, I have avoided using VIEWS within application code and only used > them for client interfaces, the sole reason being the performance of views > against tables. Have you confirmed this belief with tests? Or just following some kind of general knowledge that's filtered down from other dbas? > Changes to database over the past few years appear to have improved the > performance of views, but I am still not comfortable with using VIEWS within > application code. The main reasons I have are This is true for some other dbms'. It's generally not true for PostgreSQL. PostgreSQL views have for a very long history of running as queries the same as if you ran them by hand. This is due to the use of an advanced rules system that allows for the same performance of views as for the original queries on the table. > * Data within a view is not necessary sequential, unlike a table Data in a pgsql view is EXACTLY the same as if you ran the query by hand. > * Higher overhead mapping to original tables and indexes The overhead in pgsql is measured in sub millisecond time. > * Danger of linking views and tables and not utilising utilising > underlying tables properly. not sure what you mean there at all. > > Am I right to avoid to VIEWS within application code? Yes. Absolutely. If you're running MySQL. OTOH, if you're in pgsql there's no real reason to avoid them. unless you'll also be supporting mysql. then mysql plays the lowest common denominator trump card it so often does and forces you to dumb down your db layer to make it happy. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general