> For the complete documentation index, see [llms.txt](https://notes.tatusl.dev/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://notes.tatusl.dev/databases/relational_databases.md). # Relational databases * Tackling performance problems with more hardware usually just delays problems * Instead of just provisioning bigger database instance, one should try to optimize database schema and queries * normalizing data is usually the first thing to do and check * normal forms * in schema design, one should avoid data duplication * Primary keys can be also short strings instead of integers * with this, some information can be retained * PostgreSQL has auto compress feature (TOAST) ## Using EXPLAIN * shows the query plan * using `EXPLAIN ANALYZE` actually executes the statement and run time statistics are added to display ## Optimizing performance * Sequential scans are usually bad for performance ### Indices * To simplify, indices in tables turn sequential scans to index scans * Most of the regularly used queries should have index * however the come with a cost * especially when writing to a table * What if I have sequential scans even after adding an index * small table (not lots of rows) * on multi field indices, fields can be omitted only from the end * using function in where clause * however function can be used in index itself * Postgres specific reasons why indices won't work * VACUUM hasn't been run, so table statistics are not updated ## Object-relational mappings (ORM) * Usually it's a good thing to write simple code with abstractions * however, abstractions might have a performance cost * Using ORM is usually recommend, but when performance is critical it could be better to write pure SQL ## Sharing responsibilities between application code and database * Let the database do the heavy lifting * when database works, app sleeps * the is overhead going back and forth between application and database * Prefer small number of big queries instead of big number of small queries ## Resources * [PostgreSQL 14 Internals ebook](https://postgrespro.com/community/books/internals) * [Postgres Weekly newsletter](https://postgresweekly.com/) ## References * My own notes and interpretations on my colleagues excellent presentation with title "Your Customer Might Not Need a Bigger Database Instances" (17.01.2025) --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://notes.tatusl.dev/databases/relational_databases.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.