Postgres Views

Summary

Postgres views serve as query shortcuts that execute underlying SQL statements upon retrieval without generating new tables or persisting duplicate data. By encapsulating complex multi-table joins, standard views provide query consistency across applications, simplify repetitive calls, improve logical schema organization, and enhance security by restricting sensitive columns. In contrast, materialized views physically store query results on disk, dramatically reducing read latency for heavy queries spanning millions of rows. Because materialized views introduce the trade-off of stale data, administrators must periodically run the refresh command based on workload tolerances for use cases like analytics and internal dashboards. Materialized views should not substitute query optimization, as underlying query efficiency remains essential.

Context

Complex, repetitive queries across multiple joined tables increase maintenance overhead, introduce risks of inconsistency across applications, and can suffer from slow execution times or sensitive data exposure.

Approach / What changed

Encapsulating queries using standard Postgres views without storing data, or utilizing Postgres materialized views to physically persist query results alongside scheduled refresh operations.

Takeaways

• Postgres views act as named query shortcuts that do not duplicate data, behaving like tables that can be joined or layered into new views while restricting access to sensitive columns.
• Materialized views physically store query results to accelerate read latency on large queries, but require executing REFRESH MATERIALIZED VIEW to manage data staleness.
• Materialized views suit workloads tolerant of outdated data, such as internal dashboards and analytics, but they do not replace the need to optimize slow underlying queries.

Related reading

• Supabase · Aug 2, 2020

  Continuous PostgreSQL Backups using WAL-G

  Continuous PostgreSQL backups require coordinating physical base snapshots with ongoing write-ahead log archiving to support point-in-time recovery. The open-source WAL-G utility simplifies this workflow by managing backup pushes and fetches to cloud storage services such as Amazon S3. In a typical setup on Ubuntu with PostgreSQL 12, envdir supplies AWS credentials and storage prefixes to WAL-G commands embedded directly in the PostgreSQL archive configuration. Regular base backups can be automated via cron jobs, which minimizes the volume of WAL archives that must be replayed during a restore. When recovering to a new instance, administrators fetch the latest base backup, define a restore command with targeted recovery timestamps in the configuration, and initiate replay using a recovery signal file.

  Angelico de los Reyes

• Supabase · Jul 7, 2020

  Physical vs Logical Backups in PostgreSQL