PostgresSQL 10 and 11 introduced partitioned tables. Quoted from the official site:
"Partitioning refers to splitting what is logically one large table
into smaller physical pieces"
And the benefit are mainly performance gains in querying/deleting. But if we create many tables based on the partitioning criteria, wouldn't the benefit be the same?
I had a DB with 10K tables to store financial data, which worked fine for me, except for slow loading in pgAdmin and later a foreign key issue that made me struggle since it's a huge pain to update a foreign key. So I decided to rebuild the DB with one or two big tables. Then this new feature gave me hope again. I'm not sure if it's so good. What's the major difference between multiple physical or logical tables?
EDIT:
I studied Pg11 partition for a while. It seems it's a good solution for my DB need. But, the question is even more annoying to me.
As I undersatand, partitioned tables are stored separately, I suppose there would be some internal "index/planning" in paritioned tables. So it would be slightly faster than multiple(<10k) physical tables. Then while partitioned tables could have partitions as well, it's even more interesting and that's what I wanted actually.
Then I found this post "https://stackoverflow.com/questions/6104774/how-many-table-partitions-is-too-many-in-postgres" saying several hundreds partitions are proper. I will assume he's talking about earlier version and I believe PG11 should be better which I'm not sure. And I saw from another post that more than 1000 partitions would slow query down due to planning which I think should more or less improved in PG11 too.
Anyway, for now, I'm sure partition is what I'm looking for but I'm going to try a 10k paritions(with sub-partitions). Hopefully I'll be lucky enough to get a good result.
BTW, pgAdmin4 slow down somehow is a common problem for earlier version and latest 4.4 more or less fixed this, based on my search in stackoverflow. So probably 10k physical tables are not that bad, except for foreigh key update, which I decided not to use anymore.
Best Answer
The key difference between having many smaller tables and having one partitioned table is that the partitioned table can appear as a single table in SQL statements. You don't have to mess around with views and triggers (the latter are probably also less efficient), it is all handled in core.
However, you shouldn't have too many partitions, else query planning will become too slow, and you will suffer from all the other problems that probably made you abandon a design with tens of thousands of tables.
Maybe you can use partitioning to your advantage if you manage to split your table into fewer partitions.
Partitioning is a comparatively new feature in PostgreSQL, and v11 has many notable improvements in this area. So if you want partitioning, use v11 by all means.