andatki

Happy to discuss any details from the post. Thanks for taking a look.

https://andyatkinson.com

If merging or moving data between environments is a regular occurrence, I agree it would be best to have non-colliding primary keys. I have done an environment move (new DB in different AWS region) with integers and sequences for maybe a 100 table DB and it’s do-able but a high cost task. At that company we also had the demo/customer preview environment concept where we needed to keep the data but move it.

Great!

Good addition!

This was written based on working on several Postgres databases at different companies of “medium” size as a consultant, that had excessive IO and latency and used UUID v4 PKs/FKs. They’re definitely out there. We could transform the schema for some key tables as a demonstration with big int equivalents and show the IO latency reduction. With that said, the real world PK data type migration is costly but becomes a business decision of whether to do or not.

Appreciate it!

Note that if you’re using UUID v4 now, switching to v7 does not require a schema migration. You’d get the benefits when working with new records, for example reduced insert latency. The uuid data type supports both.

Hi there. Thanks for the feedback. I updated that section to hopefully convey the intent more. The type of ordering we care about for this topic is really B-Tree index traversal when inserting new entries and finding existing entries (single and multiple values i.e. an IN clause, updates, deletes etc). There's a compelling example I re-created from Cybertec showing the pages needed and accessed for equivalent user-facing results, comparing storing PKs as big integers vs. UUID v4s, and how many more pages were needed for v4 UUIDs. I found that to be helpful to support my real world experience as a consultant on various "medium sized" Postgres databases (e.g. single to 10s of millions of records) where clients were experiencing excessive latency for queries, and the UUID v4 PK/FKs selection made for reasons earlier was one of the main culprits. The indexes wouldn’t fit into memory resulting in a lot of sequential scans. I’d confirm this by showing an alternative schema design and set of queries where everything was the same except integer PKs/FKs were used. Smaller indexes (fit in memory), reliable index scans, less latency, faster execution time.

Good point that the post should be made clear it’s referring only to my experience with Postgres.

I’ve used Marp a lot and it’s great. Column layouts and code highlighting are two features Presenterm offers that I don’t think are available in Marp.

What sorts of skills are you looking to build? Since this post has a lot of comments about query optimization, I’d suggest query optimization is more in the realm of developers and not DBAs. Schema, query, and index design. DBAs might be more focused on replication/HA, security, fleet management, backups, DR.

Happy to help with more targeted recommendations!

Nice post covering many of the main ways to improve efficiency.

Here’s a related post I wrote for AppSignal:

What's Coming in Ruby on Rails 7.2: Database Features in Active Record https://blog.appsignal.com/2024/07/24/whats-coming-in-ruby-o...

For folks interested in additional depth on optimizing Postgres for use with Active Record/Rails, please check out my book:

High Performance PostgreSQL for Rails https://andyatkinson.com/pgrailsbook

Thanks!

What a name! - A dot Atkinson

Rails and Postgres (and AWS) was the pre-acquisition stack, and development continued with that stack during this time period (2020-2021). https://en.wikipedia.org/wiki/Flip_(software)

Microsoft acquired companies with web and mobile platforms with varied backgrounds at a high rate. I got the sense that the tech stack—at least when it was based on open source—was evaluated for ongoing maintenance and evolution on a case by case basis. There was a cloud migration to Azure and encouragement to adopt Surface laptops and VS Code, but the leadership advocated for continuing development in the stack as feature development was ongoing, and the team was small.

Besides hosted commercial versions, I was happy to see Microsoft supporting community/open source PostgreSQL so much and they continue to do so.

https://en.wikipedia.org/wiki/List_of_mergers_and_acquisitio...

https://techcommunity.microsoft.com/t5/azure-database-for-po...

Hi there! That's funny! This interview and those gem updates were unrelated. However both are part of the sweet spot for me of education, advocacy, and technical solutions for PostgreSQL and Ruby on Rails apps.

I hope you’re able to check out the podcast episode and enjoy it. Thanks for weighing in within the gem comments, and for commenting here on this connection. :)

Great list of Postgres features called out that highlight the extensive feature set.

Most of these are covered in my book, for anyone that’s interested in learning them. The book uses a Ruby on Rails app with Postgres instances for examples and exercises. Hope the plug is ok here as some folks may be looking for learning resources for Postgres. https://andyatkinson.com/pgrailsbook

Newer versions of Postgres also support dropping indexes concurrently. I recommend using the concurrently option when dropping unused or unneeded indexes on any table with active writes and reads. https://www.postgresql.org/docs/current/sql-dropindex.html

Makes sense. Check out the implementation of automatic lock timeout retries in Strong Migrations.

Lock Timeout Retries [experimental] https://github.com/ankane/strong_migrations

Do you do any alerting for INVALID indexes? For example, by default PgHero will display them prominently and I believe PgAnalyze does as well. My thought is to put the energy into making INVALID indexes highly visible. Perhaps combined with a process step. 1. Any CREATE INDEX CONCURRENTLY migrations go out in their own deployment. 2. Any queries that depends on that index being present means a PR has a process step asking the author to verify that the index exists and is valid. That way you wouldn't have to lose if_not_exists.

That all said, still would be cool to make this the default in Active Record. Nice idea!

Hey Shayon. IF NOT EXISTS or the Active Record version "if_not_exists: true" can be pretty handy though when there's a valid index in production, to help drive schema definition consistency in all environments (prod, dev, CI, etc.). As you pointed out though, it only makes sense when the indexes being checked are valid.

In my experience on large tables that are “busy”, sometimes indexes need to be added manually first from a utility session, perhaps inside tmux/screen that's detached from while they are created. This could take hours for large tables. Then once done, and the index is valid, an Active Record migration can be sent out using “if_not_exists: true” to make sure it’s applied everywhere.

Your point that it could be misused unintentionally due to not knowing an index is INVALID is a good one, and I feel it should be part of how it works by default in Active Record. Had you considered trying to propose that to rails/rails? I would certainly support that PR (may be able to collaborate) and could add more examples and validation.

Author here! If you use PostgreSQL with web apps, what sorts of things surprised you as functionality that was useful, that you didn't initially know about?

Or maybe, what functionality was more complex than you expected? Thanks!

Congrats on the launch Gwen!

Michael and Nikolay are joined by Andrew Atkinson, author of High Performance PostgreSQL for Rails, to discuss how Rails and Postgres work together — where the limits are, how people use the ORM, things that are improving, and some things we can do as a Postgres community to make it even better.

I was able to adopt fish shell and get many features from bash that were provided from plugins. I prefer the more “stock” shell configuration for easier maintenance across computers and over time, and a less complex dotfiles project. https://github.com/andyatkinson/dotfiles

On a team I limit my fish shell use to my own machine though, and would use bash for scripts to share with a shebang line. I also switch to a bash shell when needed.

That's great! Hopefully the authors of Rails Guides and the Getting Started tutorial see this. I'll share it with a core member and ask them to reshare it. I'm sure they'd appreciate seeing their hard work get recognized, and would welcome for your feedback.

Correct, when the query conditions match the index conditions, and they both select a low proportion of the rows. For PostgreSQL and those unfamiliar with partial indexes, worth a read: https://www.postgresql.org/docs/current/indexes-partial.html

Thank you! Beta book period is nearly wrappped up!

Great! I wasn’t sure whether to plug it but it seemed very relevant to the post. :)

I tend to agree as a SQL enthusiast. However I have yet to see a Rails team that doesn’t use Active Record or writes much SQL directly, or by default, across 100s of apps. I’m sure it happens but in my experience it’s rare.

This is a place where I think tools like Rubocop help. They can be configured to point out method swaps like this (size over count) automatically which is a relatively low effort task to change the code.

With those rules/linting in place, you aren’t throwing out the benefits of AR (ORM), and hopefully leveraging their useful methods like these that help avoid unnecessary queries.