[scottshamus]

There’s lot of good solutions out there but if you really like using Postgres, you could move your logs to a secondary Postgres DB or Postgres server so it doesn’t affect your application DB’s performance. I’ve scaled this to 10s of millions of log messages without much issue.

[OldHand2018]

I like this approach and have successfully used both table partitioning [1] and (if necessary) foreign data wrappers [2] to keep my main database small and fast. There is a pretty old blog post that clued me into it [3]. It works really nicely, allowing for such things as old data stored on spinning disks and newer data on NVMe drives. You can query the main table directly with query parameters that limit which partitions you hit, or you can query a partition table if you know that's where all the data resides. It's pretty awesome!

[1] https://www.postgresql.org/docs/current/ddl-partitioning.htm...

[2] https://www.postgresql.org/docs/current/ddl-foreign-data.htm...

[3] https://pgdash.io/blog/postgres-11-sharding.html