--- title: "Some SQL optimizations" publication: "Postgres Weekly" issue: 543 published_at: "2024-02-21" canonical_url: "https://postgresweekly.com/issues/543" --- Plus a successful EC2 to RDS migration story. | #​543 — February 21, 2024 [Web Version](https://postgresweekly.com/issues/543) Together with [![Hasura\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,e_trim,w_285,h_84/e_make_transparent/co_white,e_outline:5/rqccs6gvtpeifpf64bb4.png)](https://hasura.io/graphql/database/postgresql?utm_source=postgres-weekly&utm_medium=newsletter) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/zcbeoxdnt3q3lcaylod7.jpg)](https://aiven.io/developer/sql-query-optimization-guide) [SQL Query Optimization: A Comprehensive Developer's Guide](https://aiven.io/developer/sql-query-optimization-guide) — A bumper packed post digging into the optimization of `SELECT`, `INSERT`, and `DELETE` queries, covering areas like using indexes, paginating results, avoiding joins, and how window functions can help (or not). It’s not Postgres-only but clearly written with Postgres in mind. Francesco Tisiot / Aiven [What's Faster? `COUNT(*)` or `COUNT(*)` with `LIMIT`](https://blog.jooq.org/an-efficient-way-to-check-for-existence-of-multiple-values-in-sql/) — SQL guru Lukas Eder looks at the execution plans and runs benchmarks to see if using a `LIMIT` clause is useful when checking for existence of N values in SQL. _Spoiler: Yes._ Lukas Eder [![\](https://copm.s3.amazonaws.com/fe916569.png)](https://hasura.io/graphql/database/postgresql?utm_source=postgres-weekly&utm_medium=newsletter) [Instant Realtime GraphQL APIs on PostgreSQL with Hasura](https://hasura.io/graphql/database/postgresql?utm_source=postgres-weekly&utm_medium=newsletter) — Connect Hasura to your PostgreSQL database and get a secure and highly performant GraphQL API. Query tables and views with filtering, sorting, pagination, aggregations and more. Instantly pull any real-time changes to your data with subscriptions. Hasura sponsor [A Story of a Spectacular Success in Migrating to AWS RDS](https://www.smily.com/engineering/a-story-of-a-spectacular-success-intro-to-aws-rds-database-migration-series) — Being based on EC2 already, this isn’t your typical on-prem to cloud migration story, but it’s interesting to see what moving five clusters covering 54 applications with terabytes of live data over to RDS entailed. Karol Galanciak (BookingSync SAS) _IN BRIEF:_ - 📅 Microsoft's _POSETTE_ Postgres event is [looking for speakers](https://www.citusdata.com/posette/2024/cfp/) – you have until April 7 to submit a proposal. - If you're running Postgres 11 on Amazon RDS, note that your database will be [automatically enrolled into Amazon RDS Extended Support](https://aws.amazon.com/blogs/aws/your-mysql-5-7-and-postgresql-11-databases-will-be-automatically-enrolled-into-amazon-rds-extended-support/) at the end of this month. As this may attract extra charges, look into upgrading ASAP. - A reminder of [how to use variables in `psql`.](https://www.depesz.com/2023/05/28/variables-in-psql-how-to-use-them/) - Jimmy Angelakos shares his talk on [how Postgres can help you enforce best practices](https://vyruss.org/blog/fosscomm-2023-how-postgresql-helps-you-enforce-best-pratices.html) including the video and[slidedeck.](https://vyruss.org/computing/slides/fosscomm2023_how_postgresql_can_help_you_enforce_best_practices.pdf) [Creating 'Last Updated' Columns in Postgres](https://www.morling.dev/blog/last-updated-columns-with-postgres/) — If you want an automatically updated `updated_at` timestamp column in your table, you don’t _have_ to use a trigger (though you probably should..) Gunnar Morling [Deciphering Postgres Encryption: A Beginner's Guide](https://www.highgo.ca/2024/02/13/deciphering-postgresql-encryption-a-beginners-guide/) — A (very) casual look at six levels of encryption that can be used with Postgres, from basic things like passwords being hashed through to disk-level encryption and encrypting the data within the database itself. Tristen Raab [Fan-Out from Postgres with Change Data Capture using Debezium and Upstash Redis](https://neon.tech/blog/fan-out-postgres-changes-using-debezium-and-upstash-redis) Evan Shortiss (Neon) [Amazon Aurora vs. RDS: Understanding the Difference](https://www.timescale.com/blog/amazon-aurora-vs-rds-understanding-the-difference/) Carlo Mencarelli (Timescale) 📰 Classifieds 📢 Free Distributed Postgres DB with 3 node cluster and 1-click provisioning across 3 regions from pgEdge Cloud. [Sign up and get a cool t-shirt](https://www.pgedge.com/get-started/cloud?utm_campaign=Cloud%20Developer%20Launch&utm_source=Postgres%20Weekly%20Sponsorship&utm_medium=Postgres%20Weekly&utm_term=Postgres%20Weekly&utm_content=Postgres%20Weekly). --- ⚡Learn how to use [prepared statements on PgBouncer 1.22.0](https://neon.tech/blog/pgbouncer-the-one-with-prepared-statements?utm_source=cooperpress&utm_medium=newsletter&utm_campaign=postgresweekly) to increase throughput and optimize your queries in Postgres using Neon. --- 📢 Using Redis as a Postgres cache? [Readyset](https://readyset.io/?utm_campaign=cloud&utm_medium=newsletter&utm_source=postgres-weekly) Cloud makes query caching easier without query optimization or app rewrites. [Try for free today](https://readyset.io/?utm_campaign=cloud&utm_medium=newsletter&utm_source=postgres-weekly). 🛠 Code and Tools [PostgreSQL Anonymizer: Data Masking for Postgres](https://postgresql-anonymizer.readthedocs.io/en/stable/) — Rather than simply anonymizing dumps, say, this extension takes anonymization directly to your database by having you declare [masking rules](https://postgresql-anonymizer.readthedocs.io/en/stable/declare_masking_rules/) directly against your schema. ([Git repo.](https://gitlab.com/dalibo/postgresql_anonymizer)) Dalibo [libpqxx 7.9: The Official C++ Client API for Postgres](https://github.com/jtv/libpqxx/releases/tag/7.9.0) — It’s a _pretty big release_, says the maintainer, and the last one to support C++17 with libpqxx 8.0 set to demand C++20. [Main repo.](https://github.com/jtv/libpqxx) Jeroen Vermeulen [Schemalint 1.1: A Linter for Postgres Schemas](https://github.com/kristiandupont/schemalint) — A linter that lets you raise errors on issues like casing or column types (so think more like ESLint than a basic formatter). Kristian Dupont [pgexporter 0.5: Prometheus Exporter for Postgres](https://pgexporter.github.io/) — As of [v0.5](https://pgexporter.github.io/releases/pgexporter_0_5_0.html), it’s targeting Postgres 12 and higher and now supports client certificates for TLS. Red Hat - [pgmoneta 0.9](https://pgmoneta.github.io/release/announcement/2024/02/14/pgmoneta-0.9.0.html) – The backup/restore tool gets an update to improve TLS support, as with pgexporter above. - [ruby-pg 1.5.5](https://github.com/ged/ruby-pg) – The Postgres client library for Ruby. **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. _Thanks!_ --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*).