Postgres multi tables vs single table performance I am building a db using PostgreSQL for a survey application. The legacy system uses separate tables for each survey which has around 1 million rows each(maximum). Since the columns are exactly the same for all the tables, I was wondering if having separate table is more efficient than having a single table with an Foreign key column pointing to the survey id instead of having separate table for every survey id.

What is the trade-off and which approach is better scalable?
Do you find yourself writing lots of queries in which you combine rows from many of those tables together with UNION or UNION ALL?  If not, then the current setup is probably more efficient than the single table.

Once you have tens of thousands of tables, you can have scalability problems when doing things like backup, dump, and restore, and upgrades.  The nature of those problems would depend on which PostgreSQL version you are using, which tools you are using to do those things, and the details of your database objects.