--- title: "Medium-hard SQL questions to think about" publication: "Postgres Weekly" issue: 354 published_at: "2020-05-06" canonical_url: "https://postgresweekly.com/issues/354" --- This week's Postgres news, views, and tools. #354 — May 6, 2020 [Read on the Web](https://postgresweekly.com/issues/354) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/lxwnydmo4bpoa6o9dqhl.jpg)](https://pgmodeler.io/) [pgModeler: A Postgres Database Modeler](https://pgmodeler.io/) — An easy way to create and edit database models in a visual way. It’s packaged up as a paid product but is also open source so you can build your own. Raphael Araújo e Silva [The Best Medium-Hard Data Analyst SQL Interview Questions](https://quip.com/2gwZArKuWk7W) — This article begins with a quote: _“The first 70% of SQL is pretty straightforward but the remaining 30% can be pretty tricky.”_ True! This article focuses on the tricky ‘medium-hard’ area that few tutorials venture into. Zachary Thomas [![\](https://copm.s3.amazonaws.com/2d4c553e.png)](https://www.datadoghq.com/dg/monitor/postgresql-benefits/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyBenefitsP) [Monitor Custom Postgres Metrics in Real-Time with Datadog](https://www.datadoghq.com/dg/monitor/postgresql-benefits/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyBenefitsP) — Monitor and visualize Postgres performance in context end-to-end alongside the rest of your stack. Create custom, drag-and-drop dashboards to quickly view analytics on any Postgres metric. Try Datadog free. Datadog sponsor [My Favorite PostgreSQL Extensions: Part Two](https://severalnines.com/database-blog/my-favorite-postgresql-extensions-part-two) — The second part of a series we linked to last week. This time, Nawaz takes a look at pgAudit, pg_repack, and HypoPG. Nawaz Ahmed [Backup Manifests and `pg_verifybackup` in Postgres 13](https://www.2ndquadrant.com/en/blog/backup-manifests-and-pg_verifybackup-in-postgresql-13/) — Postgres 13 will introduce two features to enhance the automated validation of physical backups: backup manifests and a new tool called `pg_verifybackup`. Gabriele Bartolini [arm64 Packages Now on apt.postgresql.org](http://www.df7cb.de/blog/2020/arm64-on-apt.postgresql.org.html) — If you’re running ARM64 hardware and Debian or Ubuntu, you can now install Postgres via apt. Christoph Berg [Speeding Up `count(*)`: Why Not Use `max(id) - min(id)`?](https://www.cybertec-postgresql.com/en/speeding-up-count-why-not-use-maxid-minid/) — A warning tale in case you decide to take this shortcut. While you might be able to estimate or fudge a number that’s close, you can’t guarantee sequences will give you an exact, correct answer here. Hans-Jürgen Schönig [Using Postgres for JSON Storage](https://info.crunchydata.com/blog/using-postgresql-for-json-storage) — With JSON and JSONB types and associated advanced ways to query such columns, using Postgres as a store for JSON data is pretty simple. This is the briefest of overviews but leads into [an interactive online tutorial.](https://learn.crunchydata.com/postgresql-devel/courses/beyond-basics/qjsonintro/) Steve Pousty [How to Migrate From Inheritance-Based Partitioning to Declarative Partitioning](https://www.2ndquadrant.com/en/blog/how-to-migrate-from-inheritance-based-partitioning-to-declarative-partitioning-in-postgresql/) — Partitioning was introduced in Postgres 10 and Postgres 11 improved the declarative partitioning support. This article demonstrates a move from inheritance based partitioning to declarative partitioning using the native features found in Postgres 11+. Caterina Magini [Free eBook: How to Get a 3x Performance Improvement on Your Postgres Database](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklySecondary05062020) — Learn our best practices for optimizing Postgres query performance for customers like Atlassian and how to reduce data loaded from disk by 500x. pganalyze sponsor [How to Backup Multiple Tablespaces with `pg_basebackup`](https://www.highgo.ca/2020/04/30/how-to-backup-multiple-tablespaces-with-pg_basebackup/?utm_source=rss&utm_medium=rss&utm_campaign=how-to-backup-multiple-tablespaces-with-pg_basebackup) Ahsan Hadi ▶[Security and Compliance with Postgres](https://www.youtube.com/watch?v=cAbfHT5ed9g) — A recorded webinar that 2ndQuadrant ran recently. Boriss Mejías [Oracle to PostgreSQL: ANSI Outer Join Syntax in Postgres](https://www.2ndquadrant.com/en/blog/oracle-to-postgresql-ansi-outer-join-syntax-in-postgresql/) — The latest in a series of blog posts about migrating to Postgres from Oracle which looks at what Postgres offers in place of Oracle’s join operators. Kirk Roybal [An Interview with 2ndQuadrant's Jimmy Angelakos](https://postgresql.life/post/jimmy_angelakos/) — The latest ‘PostgreSQL Person of the Week’ to face questions about his experiences with Postgres. Andreas Scherbaum [dadbod.vim: A Modern Database Interface for Vim](https://github.com/tpope/vim-dadbod) — A Vim plugin for interacting with numerous databases, including Postgres. Tim Pope 🗓**Upcoming Online Events** - [Postgres Pulse](https://www.enterprisedb.com/postgres-pulse) - weekly at 11am ET each Monday. Weekly Zoom-based sessions with folks like Bruce Momjian, Vibhor Kumar, and other people at EnterpriseDB. - 🔐 [Postgres Vision 2020](https://www.postgresvision.com/) on June 23-24. A full attempt at an online Postgres conference across multiple days with multiple tracks. 🔐 – requires e-mail address or registration 💵 – costs money to participate 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