I have a system that I am needing to convert from FoxPro files being accessed with DAO to PostgreSQL. This system serves 1,000 clients and will be expanding to 2,000 within the next 18 months. The current system has a directory with files that contain information of a global nature such as the list of clients, list of users and which client they belong to, etc. Then each client has files within their own directory to keep the size of the tables manageable. Each client has 165 tables. These tables are all the same definition across the different groups. I have been trying to determine the best way to define this system within PostgreSQL. I have considered partitioning tables, but if I am correct that would result in 330,000 files and I am not certain if that will cause an issue with degraded file system performance. Is it possible to create a tablespace for each group and then create partitioned tables for the groups within the group's tablespace to limit the number of files in a directory? I plan on utilizing the built-in streaming replication, so I assume if I went the tablespace route I would need to create directories for all future groups from the outset since creating them individually with code on the backup systems would be difficult. Another option would be placing an extra field in each table identifying the group it belongs to and combining all of the separate tables of the same definition into one table. This would result in some tables having 300 million entries currently and that would climb over the next 18 months. The final option I can see is creating a schema for each of the different clients. I am not certain if this is a better option than partitioned tables. I haven't been able to determine if schema objects are stored in a sub directory or if they are in the same directory as all of the other tables. If they are in the same directory then the same issue arises as the partitioned tables. Of course, I am certain there are a number of other possibilities that I am overlooking. I am just trying to determine the best way to move this over and get things into a more modern system. -- View this message in context: http://postgresql.nabble.com/Migrating-a-FoxPro-system-and-would-like-input-on-the-best-way-to-achieve-optimal-performance-tp5837211.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
