Le 23 avril 2017 à 12:48, Ertan Küçükoğlu écrivait :
> Hello All,
>
> Using PostgreSQL 9.6.2 on a Windows 64bit platform.
>
> I am about to start a new software development dealing with warehouse
> operations. Software should handle multi-company structure. There will be
> single company starting to use the software at the beginning.
>
> I need to decide between below scenarios before I develop any database
> structure.
>
> Biggest table I am assuming will be inventory movements table. I am
> expecting about 90% of the queries will run on that table. I do not see that
> table to have more than 100.000 rows in a year for a single company. I am
> not planning to have a new database for each year. Data will pile up in
> time.
>
> My current scenarios are:
> 1) Separate company records by adding a field in each table and keeping all
> records in a single database and schema. (Queries will be easier to build
> and run)
> 2) Separate company records by adding a new schema for each company there
> may be keeping all records in a single database. (Queries will need UNION
> across schemas for corporate reports)
> 3) Separate company records by creating a new database for each company.
> (This option, I do not want to do. Will think about corporate reports if I
> have to select this one).
Hi,
I would explore that scenario too:
4) Separate company records by adding a field in each table keeping all
records in a single database and schema. Partitionnate table based on
that field for table with more than 100M rows. (see
https://www.postgresql.org/docs/current/static/ddl-partitioning.html)
The main advantage versus 1) for partitionning is when your user queries will go
for a sequential scan plan. This will lead to scan only the relevant
partitioned tables, and will speed up that processes.
The second advantage versus 2) and 3) is the simplification of queries,
no need for union. Just modifying the predicate (WHERE id_company IN (1, 2, 3) )
>
> I appreciate any suggestion on above possible options. New suggestions,
> experience sharing are always welcome.
>
> Thanks & regards,
> Ertan Küçükoğlu
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general