--- title: "Postgres mistakes to avoid" publication: "Postgres Weekly" issue: 503 published_at: "2023-04-26" canonical_url: "https://postgresweekly.com/issues/503" --- Plus the 42 ways things you can CREATE in Postgres. | #​503 — April 26, 2023 [Web Version](https://postgresweekly.com/issues/503) Together with [![pgAnalyze\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,e_trim,w_350,h_100/e_make_transparent/co_white,e_outline:7/lhwcvgc1asd4fzaahty4.png)](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklyPrimary04262023) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/le2sjyft6o9futvibr7n.jpg)](https://philbooth.me/blog/nine-ways-to-shoot-yourself-in-the-foot-with-postgresql) [Nine Ways to Slip Up with Postgres](https://philbooth.me/blog/nine-ways-to-shoot-yourself-in-the-foot-with-postgresql) — A quick tour of some common mistakes or pitfalls that you can encounter when using our favorite database. It resonated with a lot of people and stoked extensive discussions on both Reddit and [Hacker News.](https://news.ycombinator.com/item?id=35684220) Points include forgetting to add indexes for foreign keys, not tweaking `work_mem`, and leaning too heavily on CTEs. Phil Booth ⚠️ If you like this sort of thing, the classic Postgres wiki ['Don't Do This' entry](https://wiki.postgresql.org/wiki/Don't_Do_This) will also be up your street. [![\](https://copm.s3.amazonaws.com/bfefc344.png)](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklyPrimary04262023) [Best Practices for Optimizing Postgres Query Performance (eBook)](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklyPrimary04262023) — Learn how to get a 3x performance improvement on your Postgres database and 500x reduced data loaded from disk. We share our learnings from helping companies like Atlassian, CounterPath, and others speed up their queries. pganalyze sponsor [Build Postgres Extensions with Rust with PGRX](https://github.com/tcdi/pgrx) — For ‘various reasons’ the popular PGX framework [has renamed](https://github.com/tcdi/pgrx/issues/1106) to PGRX but remains a great way to build Postgres extensions in Rust. The reason isn’t explained, but I do note a company owns PGX as a trademark for database purposes, so probably a good move. Technology Concepts & Design, Inc. **IN BRIEF:** - Peter Eisentraut looks at [the different variants of the `CREATE` command](http://peter.eisentraut.org/blog/2023/04/25/create-commands-in-postgresql-releases) that have turned up in Postgres over the years. We now have 42 of them! - Ryan Booz has written a [getting started with `psql`](https://www.red-gate.com/simple-talk/databases/postgresql/postgresql-basics-getting-started-with-psql/) tutorial few experienced readers are likely to need _but_ it has a neat 'history of `psql`' section going back to Ingres's monitor tool. - This week's _PostgreSQL Person of the Week_ is [Loxodata's Sébastien Lardière.](https://postgresql.life/post/sebastien_lardiere/) [Solving N+1 Postgres Queries for Ruby on Rails Apps](https://www.crunchydata.com/blog/postgresql-for-solving-n+1-queries-in-ruby-on-rails) — Tips for working with Ruby on Rails’s ActiveRecord and using better SQL to improve performance and avoid N+1 queries. Christopher Winslett [Migrating Postgres from Heroku: A Customer Study on Crunchy Bridge](https://www.crunchydata.com/case-studies/carerev?utm_source=cp-brf-0426) Crunchy Bridge sponsor [How Postgres Triggers Can Simplify Your Backend Development](https://themythicalengineer.com/how-postgres-triggers-can-simplify-your-backend-development.html) — Triggers let you automatically execute a function in response to a specific database event which provides a route to offloading certain logic to the database itself. The Mythical Engineer The role of triggers was recently discussed within the community [in the latest PGSQL Phriday](https://mydbanotebook.org/post/triggers2/) with mixed opinions on their use. [A Few Ways Indexes Could Hurt You](https://www.percona.com/blog/postgresql-indexes-can-hurt-you-negative-effects-and-the-costs-involved/) — Indexes come with a cost in terms of performance and resource consumption. Jobin Augustine (Percona) [Key Considerations While Migrating Bulk Operations from Oracle to Postgres](https://aws.amazon.com/blogs/database/key-considerations-while-migrating-bulk-operations-from-oracle-to-postgresql/) — Using Amazon Database Migration Accelerator (Amazon DMA) specifically. Karimi and Gopalappa (AWS) [Migrate Data from Partitioned Tables in Postgres using AWS DMS](https://aws.amazon.com/blogs/database/migrate-data-from-partitioned-tables-in-postgresql-using-aws-dms/) Ayyakkannu, Joy, and Hegde (AWS) Code and Tools [pgddl: DDL EXtractor Functions (ddlx)](https://github.com/lacanoid/pgddl) — SQL functions to convert PostgreSQL system catalogs to nicely formatted snippets of SQL DDL, such as `CREATE TABLE`. One [interesting use case](https://stackoverflow.com/questions/23488228/how-to-get-sql-text-from-postgres-event-trigger/76057610#76057610) for this is to get the actual DDL SQL executed that resulted in an event trigger being called. Alexi Theodore - [pg_graphql 1.2](https://supabase.com/blog/whats-new-in-pg-graphql-v1-2) - [pgslice 0.6](https://github.com/ankane/pgslice) ↳ Postgres partitioning as easy as pie - [ruby-pg 1.5.2](https://github.com/ged/ruby-pg) ↳ Postgres client library for Ruby. - [pq 1.10.9](https://github.com/lib/pq) ↳ Pure Go Postgres driver for `database/sql` - [Citus 11.1.6](https://github.com/citusdata/citus) - [TimescaleDB 2.10.2](https://github.com/timescale/timescaledb/releases/tag/2.10.2) 🗣 If you fancy a debate.. [Ask HN: Is Postgres Better than MySQL?](https://news.ycombinator.com/item?id=35599118) — Hacker News tackles a classic, perennial point of debate. How do two of the most popular open source databases stack up against each other? Hacker News **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|*)