Search Postgresql Archives

Re: select count() out of memory

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




Tom Lane wrote:

You are making a common beginner error, which is to suppose that N
little tables are better than one big one. They are not.

Well that depends on how you define better. For my purposes, it is better.

What you're
effectively doing is replacing the upper levels of a big table's indexes
with lookups in the system catalogs, which in point of fact is a
terrible tradeoff from a performance standpoint.

Only if you assume I use all data in all tables all the time. But as I have explained in other replies recently, most of the times only data from the newest child table is used.

I did the performance tests before deciding on the design and having it all in one large table would not perform at all within requirements, The reason was that the indexes for the ever growing table would take longer and longer to update at each insert.

When I use partitions, or child tables, I can use COPY to insert the data into the new chilkd table and then add the indexes to the single table only. That was, by far, the fastets solution.

From a database-theory standpoint, if all this data is alike then you
should have it all in one big table.

Then, what is the point with partitions, if you can not use it to somehow separate logically similar data into different paritions because one has a need to do so? Of course I could have put it in a single table, had it not been for the performance. I could have used a discrete timestamp to separate the data, but why? partitions is more practical.

There are certain practical cases
where it's worth partitioning, but not at the level of granularity that
you are proposing.

If its practical to use partitions, granularity does not come into the equation.

regards

thomas



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux