Postgresql performance with one LARGE table?

Question

I have a basic question regarding database design. We have a single postgresql database, with one table that is much larger than the other tables. It's an audit trail that logs any and all changes in our software. Is it not smart to do this? Would I be better off storing this in another data store (redis) or another database? Or does Postgresql handle this in a way that a large table won't effect read performance on other tables. How does postgres know which data to cache in memory etc.

Sorry for such a basic and vague question, I'm not sure how else to ask this. I just don't want to get down the road, a year from now, and we're having serious database performance issues because one of our tables is 50gb, while the rest of the database is < 1gb.

Thanks.

Could you get out of this problem ? I am also facing the same issue at my end. Would like to have your suggestion please... — pramod
– pramod, Commented Mar 28, 2016 at 4:02

Frank Heikens · Accepted Answer · 2013-01-27 19:44:16Z

4

We use table partitioning (using PostgreSQL inheritance), the partitions are ~150GB for one month of data. The size of the table isn't that important, it's about your queries and the indexes that are used for these queries.

When working on performance, you should always use real data, real queries, EXPLAIN and EXPLAIN ANALYZE. Without this, it's gonna be a lucky shot.

answered Jan 27, 2013 at 19:44

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql performance with one LARGE table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related