Postgresql performance with one large table?

Question

I have a basic question regarding database design. We have one postgresql database, one table of which is much larger than other tables. This is an audit trail that logs all changes to our software. Isn't that smart for that? Would it be better for me to store this in another data store (redis) or another database? Or Postgresql handles this so that a large table will not affect read performance in other tables. How postgres know what data will be cached in memory, etc.

Sorry for such a simple and vague question, I'm not sure how else to ask about it. I just don’t want to go down the road, after a year, and we have serious problems with the database performance, because one of our tables is 50 GB, and the rest of the database is 1 GB.

Thanks.

Answer 1

We use table partitioning (using PostgreSQL inheritance ), partitions are ~ 150 GB in one month of data. The size of the table is not so important, this applies to your queries and the indexes that are used for these queries.

When working with performance, you should always use real data, real queries, EXPLAIN and EXPLAIN ANALYZE. Without this, it will be a successful shot.