To answer your second question first: yes you should partition. Oracle's query optimizer has a feature called partition elimination, which will check the predicate for the partition and only execute the SQL on the appropriate partitions.
Partitioning also leaves all the data in one space. Conceptually, think of it as many tables of identical structure, with an implicit UNION ALL between them if you were to do a SELECT from the entire table. Except "under the hood" Oracle sorts the actual rows into the right "table" based on the criteria you specify. Any rows that come in that don't match any of the criteria, go into what's known as the "default" partition.
For what you want to do, a "range partition" might be a good approach (so you can add more tenants later), e.g.:
create table transaction (id, tenant_id, a, b, c, d)
partition by range(tenant_id)
partition p_tenant1 values less than (2) tablespace ts_tenant1
partition p_tenant2 values less than (3) tablespace ts_tenant2
partition p_tenant3 values less than (4) tablespace ts_tenant3
partition p_tenantd values less than (MAXVALUE) tablespace ts_default;
Then later
alter table transaction
add partition p_tenant4 values less than (5) tablespace ts_tenant4;
This will create something that looks and behaves just like a normal table, but actually rows where tenant_id=1 will be in a partition in tablespace ts_tenant1, and queries will ignore all other partitions. Queries across the entire table can run in parallel on each partition. If tenant_id=4 in this scenario, the row will live in ts_default unless you add the new partition as shown, but the INSERT won't be rejected because there's no partition for it!
FWIW At my site we use partitioned tables in our 40Tb DW, you don't need to worry about this approach scaling or performing, if you choose your partitioning strategy well (e.g. you could partition on tenant_id then subpartition on month perhaps), create the right indexes, and so on.
My question is, do you think we should look into upgrading to SQL
Server 2014 Enterprise so that we can partition our time sheet tables?
No. Absolutely not. On a pocket change server of 8 cores you would be spending ~$50k and be unlikely to see any benefit.
I would suggest trying, in this order:
- Increase the memory allocation to SQL Server. You said the server is dedicated but have only half the RAM allocated. 32GB server, allocate 28GB.
- Upgrade the server memory to [size of database] + 4GB.
- Post the problem queries on dba.se.
- Engage an expert.
Best Answer
No, there is no real row number threshold. If you only have queries that select rows by primary key, the size of the table doesn't really matter.
Partitioning is also primarily a management tool to quickly remove no longer needed rows, not so much a performance tool.
It can be used to improve performance, but only if you have queries that only need a (small) subset of all the rows. If all queries (or at least all performance critical one) do contain the partitioning key, then partitioning can help with performance.
You also need to choose the partitioning key based on then number of partitions this results in. With Postgres 12 or later "thousands" of partitions are feasible (I have heard of users using ~20000 partitions successfully, but I think that's already a stretch). An excessive amount of partitions will most probably be not practical as it will make the planning of the queries a lot slower.
You should also take the fact into account, that a partitioned tables is limited in what the primary key can be - it has to include the partition key. So if you have foreign keys referencing the partitioned table, this might get complicated.