~postgresweekly | Issue 543 - Some SQL optimizations (10)

pgexporter 0.5: Prometheus Exporter for Postgres — As of v0.5, it’s targeting Postgres 12 and higher and now supports client certificates for TLS.

SQL Query Optimization: A Comprehensive Developer's 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.

What's Faster? COUNT(*) or COUNT(*) with LIMIT — 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.

A Story of a Spectacular Success in Migrating to AWS RDS — 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.

Creating 'Last Updated' Columns in 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..)

Deciphering Postgres Encryption: A Beginner's 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.

PostgreSQL Anonymizer: Data Masking for Postgres — Rather than simply anonymizing dumps, say, this extension takes anonymization directly to your database by having you declare masking rules directly against your schema. (Git repo.)

libpqxx 7.9: The Official C++ Client API for Postgres — 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.

Schemalint 1.1: A Linter for Postgres Schemas — A linter that lets you raise errors on issues like casing or column types (so think more like ESLint than a basic formatter).

Instant Realtime GraphQL APIs on PostgreSQL with Hasura — 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.