Really solid list! I went through all the Active Record, query design, and index design tips for PostgreSQL, and can +1 them all. Nice work.<p>For readers who want all of these and more in book form, with a sample Rails app and big data to test with (generated), please consider my book:<p>High Performance PostgreSQL for Rails
<a href="https://news.ycombinator.com/item?id=38407585">https://news.ycombinator.com/item?id=38407585</a><p>The book helps readers build database skills with the overall purpose of improved performance and scalability.<p>Again, great, concise article. I’ll be recommending it to others and it will help a lot of developers!<p>Thanks!
> We can’t think of any good reason to do without [CDNn]<p>CDNs are another way to track everybody. So privacy is an excellent reason for not using a CDN.
> Enable keep-alive connections. Keep-alive connections are reusable. They prevent having to re-establish a connection, as well as SSL negotiation. They reduce latency time for all pages made up of several resources.<p>Pretty sure this only applies to HTTP/1 and you'll get better performance with HTTP/2:<p>"Connection-specific header fields such as Connection and Keep-Alive are prohibited in HTTP/2 and HTTP/3"<p><a href="https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Keep-Alive" rel="nofollow noreferrer">https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Ke...</a><p>"HTTP persistent connection, also called HTTP keep-alive, or HTTP connection reuse, is the idea of using a single TCP connection to send and receive multiple HTTP requests/responses, as opposed to opening a new connection for every single request/response pair. The newer HTTP/2 protocol uses the same idea and takes it further to allow multiple concurrent requests/responses to be multiplexed over a single connection."<p><a href="https://en.wikipedia.org/wiki/HTTP_persistent_connection" rel="nofollow noreferrer">https://en.wikipedia.org/wiki/HTTP_persistent_connection</a>
> We can’t think of any good reason to do without them except for an application running solely on a private network.<p>Ever read those articles that explain half the internet is unavailable because of some random e.g. cloudflare outage? That.
Some good advice here, but the “don’t index boolean columns” needs an “it depends” caveat, since Postgres will sometime use multiple boolean indexes to perform a bitmap index scan, which can be advantageous.
Good stuff but the `size`, `count`, `length` section just intensifies my dislike for ORMs. ORMs bury all of the SQL, just for devs to dig it back up when they realize it's important for performance. Now you have to be a SQL expert <i>and</i> an ActiveRecord expert.
These performance rules applies for all backend development. Use compression and caching, index foreign keys in your database and tune your sql queries.
Great list, but one caveat I'd add is this: While "SQL will always be faster than your code" is true, in the context of a sufficiently large app with many parallel requests the solution might still be to do some processing in the app because it can scale horizontally and (most) databases can only scale vertically and are thus more limited.
The hint about using .pluck to only grab what you need from an ActiveRecord query is a pretty good one. I hand't realized you could do that.<p>I assume this is telling us it doesn't actually make an ActiveRecord instance out of each row when you do that. And instantiating big bunches of ActiveRecord model instances just to grab a few fields from a result set with a lot of rows can be sooo slow.