3

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.

Improve this question
4
  • 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, 2019 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, 2019 at 6:31
  • @user2189731 Clarifucations should be posted as edits to your Question rather than as a Comment.
    – Basil Bourque
    Apr 10, 2019 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.
    – Channa
    May 3, 2020 at 15:15

1 Answer 1

Reset to default
2

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
2
  • 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, 2019 at 6:08
  • More than a couple of hundred partitions is probably too many.
    – Laurenz Albe
    Apr 10, 2019 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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