Re: Please suggest me on my table design (indexes!) - Mailing list pgsql-general

Yes, surely bigserial of overdimensioned.

Anyway, i need to keep the postgresql cpu and ram usage as lower as
possible, same for the disk usage.
I'm starded an application that would be similar to shopify.com, but
free and opensource (it will be opensource just when i'll finish at
least the first beta), so everyone can register, everyone will have it
own database, with the same structure for all.. and even the 'engine',
php and python scripts, will be the same for everyone.
And all that must run on one server, at the moment..
Till now i tested with 10 beta-tester (the old version of the project,
using mysql database) and everythings works fine, but i'll need to
have at least 1000 users using it 24/7, keeping the performance
acceptable.

On 23 Giu, 17:35, rfu...@gmail.com ("Chris Spotts") wrote:
> IMHO running queries on 23k'ish worth of rows isn't liable to stress any
> reasonably modern server, likely several times over that shouldn't either
> for simple "LIKE" searches.
> What kind of growth are you expecting?
>
>
>
> > -----Original Message-----
> > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> > ow...@postgresql.org] On Behalf Of DaNieL..!
> > Sent: Tuesday, June 23, 2009 9:44 AM
> > To: pgsql-gene...@postgresql.org
> > Subject: Re: [GENERAL] Please suggest me on my table design (indexes!)
>
> > Yes, after my post i've tryed the versione with 2 separate table (a
> > copy of the contact table) with inside just the employees, and, with
> > my surprise, the query planner looks identical, both with 1 big table
> > and with 2 splitted table.
> > This sound a bit strange for me, becose in my test the 'employees
> > rows' are about 13K, the company about 3k, the private around 7K.
> > So i thougt that moving the employees to another table would decrese
> > much the indexes weight on the original table, and so raise the speed
> > for the remaining original contact table (with inside just private and
> > company users)
>
> > On 23 Giu, 16:29, gryz...@gmail.com (Grzegorz Ja¶kiewicz) wrote:
> > > 2009/6/23 DaNieL..! <daniele.pigned...@gmail.com>:
>
> > > > The `problem` is that i dont know if having so many indexes will
> > raise
> > > > problems as the data dimension grown.
> > > > That seem to be not very efficient:http://explain.depesz.com/s/Q0m
>
> > > Well, this is slow, because for some reason postgres decided to use
> > > seq scan on contact e.
> > > As for speed with many indices. Btree for varchar is going to be
> > > slower, than - say for integer, or any other fixed length type.
> > > This is due to nature of index. Having said that, if you expect a lot
> > > of repetition, split it/normalize it.
> > > Index performance also hurts, when you get a lot of variants of data
> > > (worse case, all varchar rows are different, and don't share too many
> > > leafs on index).
> > > So it is always beneficial to have separate table, if data is
> > > redundant - especially when it is text/varchar/bytea.
>
> > > Also, index size grows pretty badly when you modify table's content a
> > > lot in postgresql.
> > > Rule of thumb, bigger the index in size, comparable to data size -
> > the worse.
>
> > > --
> > > GJ
>
> > > --
> > > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> > > To make changes to your
> > subscription:http://www.postgresql.org/mailpref/pgsql-general
>
> > --
> > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> > To make changes to your subscription:
> >http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general