Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

we am at a point where I have more than a thousand Tables in our PostgreSQL database server. I remember reading that there was a way to speed up the database once it reached more than a thousand Tables, but I cannot seem to find any mention of this on the Web with Google. Any help would be nice.

Thanks

share|improve this question

3 Answers 3

up vote 1 down vote accepted

I don't recall anything specific to a table count that is greater than 1000, but you may have been thinking of using tablespaces to spread db objects/tables over different volumes to enhance performance? (i.e. tables that more write heavy on a different volume to tables that are read heavy, or splitting different sets of heavy use tables over volumes on different channels)

tablespaces are covered here in the postgres docs.

having said this, unless there are specific performance issues you need to address, I wouldn't make the changes arbitrarily...

share|improve this answer

PostgreSQL creates a file in the filesystem for every table; depending on your underlying filesystem, you'll eventually run into performance problems when any process (PostgreSQL included) reads the directory metadata. To alleviate this, you'll want to create a number of different tablespaces and place different tables in different tablespaces in order to prevent having thousands of files in the same directory.

It might help to read a bit about the CREATE TABLESPACE and ALTER TABLE commands to move tables into a given tablespace.

share|improve this answer

You may be referring specifically to the advice around tuning max_fsm_relations when you have lots of tables. But that requirement was removed in 8.4, and it's now automatically tuned for you.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.