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.

  • Thanks. That one additional information "partitioning is index plus more" helped. I can arrange my data/table for partitioning. The question is, since partitioned tables are stored separately, why can't we just use multiple tables? Is there any specific benefit using 1 logical table? And since PGSQL10 has limitation on partitioning while I'm using 10, should I use partitioning in PgSQL10 or should I upgrade to PgSQL11 and use it? – user2189731 Apr 10 '19 at 5:51
  • 1
    Partitioining only helps performance if you know that all or most of your queries have a where condition on the partitioning key so that partitions can be eliminated and don't need to be looked at. If you are sure you need partitioning, the you should definitely upgrade to Postgres 11. How many rows does your "one big table" contain? You can get a long way in Postgres without partitioning. – a_horse_with_no_name Apr 10 '19 at 6:31
  • @user2189731 Clarifucations should be posted as edits to your Question rather than as a Comment. – Basil Bourque Apr 10 '19 at 12:08
  • We go with table partitioning only when we need performance. I had been into situation when , we had to go with table partitioning. However, 10k isn't a good number of records where you can see benefit of table partitioning. We were getting 100000 records per day, so created partitions of 40000000 records. I'm using partial indexes already for each partitions to take advantage. In your case, I recommend to go with index/partial indexes of postgres. – Shiwangini Shishulkar May 3 at 15:15

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.

| improve this answer | |
  • Thanks. I understand that core operation could be more efficient. But I do have scripts to messing around views. And I can get some benefit when query is about certain tables. I'll learn more about "you shouldn't have too many partitions, else query planning will become too slow". The old question again, "how many is too many"? Which I asked myself when I created those 10k tables... – user2189731 Apr 10 '19 at 6:08
  • More than a couple of hundred partitions is probably too many. – Laurenz Albe Apr 10 '19 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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