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?
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.