--- title: "A look at Postgres's window frames" publication: "Postgres Weekly" issue: 324 published_at: "2019-09-25" canonical_url: "https://postgresweekly.com/issues/324" --- The latest Postgres news, articles, tools, and projects. #324 — September 25, 2019 [Read on the Web](https://postgresweekly.com/issues/324) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/zuyhvrqygnckzoxroci6.jpg)](https://mjk.space/advances-sql-window-frames/) [Advanced SQL: A Look at 'Window Frames'](https://mjk.space/advances-sql-window-frames/) — A deep dive into advanced uses of window functions ([an earlier article covers those](https://mjk.space/advanced-sql-window-functions/), if you need it). Window _frames_, an SQL feature in which Postgres is leading in support, let you take window functions to a much deeper level to perform complex aggregations. Some great examples in here and a must read. Michal Konarski [![\](https://copm.s3.amazonaws.com/66d08498.png)](https://www.sqlprostudio.com/ads/pgweekly.php) [SQLPro for Postgres - macOS and iOS Database Client](https://www.sqlprostudio.com/ads/pgweekly.php) — A powerful, native iOS and macOS Postgres database client. Easily browse and edit records, either visually or using a truly great query editor. If you work with Postgres make sure to check out SQLPro. Get one year free today. Hankinsoft Development, Inc sponsor [Yugabyte DB 2.0 GA: A 'Jepsen Tested', High-Performance Distributed SQL DBMS](https://blog.yugabyte.com/announcing-yugabyte-db-2-0-ga-jepsen-tested-high-performance-distributed-sql/) — Yugabyte DB is a Google Spanner-inspired, cloud-native distributed SQL database that’s 100% open source and [aims to be Postgres compatible](https://blog.yugabyte.com/postgresql-compatibility-in-yugabyte-db-2-0/) in most cases. Kannan Muthukkaruppan [A Few Special-Case Performance Enhancements in Postgres 12](https://www.2ndquadrant.com/en/blog/pg12-a-few-special-case-performance-enhancements/) — With every new release of PostgreSQL, there are a range of performance enhancements, some general, some very niche. Here’s a (very) brief look at three very specific, niche improvements. John Naylor [Azure Data Factory Can Now Use Azure Database for PostgreSQL As a Sink](https://techcommunity.microsoft.com/t5/Azure-Database-for-PostgreSQL/Bring-your-data-to-Azure-Database-for-PostgreSQL-Hyperscale/ba-p/857975) — One for Azure users only, specifically users (or potential users) of Azure Data Factory, Microsoft’s data integration service. Parikshit Savjani [A Practical Example of Using Row Level Security](https://www.cybertec-postgresql.com/en/using-row-level-security-to-make-large-companies-more-secure/) — An example of giving different permissions to different types of user for working upon the same table. Hans-Jürgen Schönig ▶[Getting PostgreSQL 12 Beta 4 Running in Less Than Six Minutes](https://fluca1978.github.io/2019/09/19/PG12Beta4.html) — A demonstration of how [pgenv](https://github.com/theory/pgenv) can make Postgres easier to install. Luca Ferrari [Manage Linux Control Groups in Postgres with `pg_cgroups`](https://www.cybertec-postgresql.com/en/pg_cgroups-linux-control-groups-in-postgresql/) — The [pg_cgroups extension](https://github.com/cybertec-postgresql/pg_cgroups) makes it easier for you to manage resource limits for Postgres clusters on Linux by using control groups (a.k.a. [cgroups](https://en.wikipedia.org/wiki/Cgroup)). Laurenz Albe [📕 20 Patterns to Watch for in Engineering Teams](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29) — Actionable insights to help you debug your development process with data. [Get your copy](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29). GitPrime sponsor [What’s Faster? `COUNT(*)` or `COUNT(1)`?](https://blog.jooq.org/2019/09/19/whats-faster-count-or-count1/) — Spoiler: It doesn’t matter, _unless_ you’re using Postgres! Lukas Eder [Comparing Distributed SQL Performance: Yugabyte vs. Aurora PostgreSQL vs. CockroachDB](https://blog.yugabyte.com/comparing-distributed-sql-performance-yugabyte-db-vs-amazon-aurora-postgresql-vs-cockroachdb/) — You should, by now, know to look at benchmarks with a skeptical and trained eye, of course 😄 though in this case, it’s nice to see a competing service actually being recommended for many use cases. YugaByte [SQLTools: Database Tools for VS Code Users](https://marketplace.visualstudio.com/items?itemName=mtxr.sqltools#sqltools) — Supports various database types (Postgres, of course, but Cassandra support has also just been added) and offers a connection explorer, query runner, IntelliSense, bookmarks, query history, etc. Visual Studio Marketplace supported by [![Datadog\](https://res.cloudinary.com/cpress/image/upload/v1566815662/buz6rhyghoxpslxmnfni.png)](https://www.datadoghq.com/postgres-monitoring/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyTip) 💡**Tip of the Week** Using `CASE` in `ORDER BY` — the basics [CASE](https://www.postgresql.org/docs/9.5/functions-conditional.html) expression is essentially the 'if/then' of the SQL world. Given one value, `CASE` can return another of your choice – but did you know you're able to use it in `ORDER BY` clauses to define custom orders based upon other values? Here's a simple use case. Let's say you have a table listing employees and their job titles: id | name. | title ------------------- 1 | Oscar | Cleaner 2 | Carol | CEO 3 | Jimbo | CFO 4 | Bobby | Assistant If you wanted to have some sort of order based upon job titles, you could use `CASE` like so: SELECT * FROM employees ORDER BY CASE WHEN title = 'CEO' THEN 1 WHEN title = 'CFO' THEN 2 WHEN title = 'CTO' THEN 2 ELSE 3 END; This is only a basic example to show off the idea. We'll dig into some more advanced examples in coming weeks. This week's tip is sponsored by [Datadog.](https://www.datadoghq.com/postgres-monitoring/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyTip) Monitor PostgreSQL performance [with Datadog.](https://www.datadoghq.com/postgres-monitoring/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyTip) 🗓**Upcoming Events** - [PostgresConf South Africa 2019](https://postgresconf.org/conferences/SouthAfrica2019) (October 8-9 in Johannesburg) — An opportunity for the database administration and developer community who use Postgres to get to know each other. - [PostgreSQL Conference Europe 2019](https://2019.pgconf.eu/) (October 15-18 in Milan, Italy) - [pgDay Santiago](https://www.pgday.cl/2019/) (October 29 in Santiago, Chile) - [PG Down Under 2019](https://pgdu.org/) (November 15 in Sydney, Australia) — The second outing for this annual, Australian Postgres conference. The CFP remains open till next week. Curated by [Cooperpress](https://cooperpress.com/), [Peter Cooper](https://twitter.com/peterc), and [Craig Kerstiens](https://twitter.com/craigkerstiens). [Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) © Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom