How to handle analytics workloads in Postgres without slowing down transactions

Analytics queries can significantly impact transaction performance in databases like Postgres by consuming shared resources, such as buffer cache pages and connections, which transactional queries also require. The solution isn't merely to speed up analytics queries but to separate their workloads from transactional operations. Strategies for addressing this issue range from creating read replicas to offload analytics queries, using materialized views for frequently run queries, and implementing partial indexes or time-based partitioning to minimize the data touched by analytics queries. Additionally, setting a statement timeout for analytics roles can prevent long-running queries from holding connections indefinitely. However, these methods only mitigate the problem temporarily, as Postgres, being a row-store transactional database, is inherently unsuited for analytics workloads. A more permanent solution is to move analytics workloads to a columnar OLAP database like Tinybird, which is optimized for such tasks, thus allowing Postgres to focus on transactional operations while Tinybird handles analytics efficiently.