index update

On 07/18/2012 12:02 PM, Pawel Veselov wrote:

Hi.

If I have a lot (10k) tables, and each table has a btree index, and
all the tables are being constantly inserted into, would all the
indexes have to be in memory, and would effectively start fighting for
space?

Quite likely, yes.

You could make it a bit more efficient by grouping inserts up and doing
batches for each table - if that's possible in your application.

10k tables is a heck of a lot. That sort of thing often implies app
design issues. Why 10k tables? What do they do?

--
Craig Ringer

On Tue, Jul 17, 2012 at 9:18 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:

On 07/18/2012 12:02 PM, Pawel Veselov wrote:

Hi.

If I have a lot (10k) tables, and each table has a btree index, and all
the tables are being constantly inserted into, would all the indexes have
to be in memory, and would effectively start fighting for space?

Quite likely, yes.

You could make it a bit more efficient by grouping inserts up and doing
batches for each table - if that's possible in your application.

Not with the current design :)

10k tables is a heck of a lot. That sort of thing often implies app design
issues.

That may be. However, attempting to put all the data into the same table
created problems with looking it up, and the fact that both write and read
traffic hits the same table.

Why 10k tables? What do they do?

Realistically, that's a way to partition data. The tables have the same
structure, but apply to different "partitions". If I am to use Postgres
partitioning, would there be any difference? Considering that I would have
to do almost exactly the same, with the inheritance and all?

Thank you!

Pawel Veselov <pawel.veselov@gmail.com> wrote: