selecting node type & number of nodes of redshift cluster only from a performance perspective

Many Low-Spec Nodes:

Pros:

Better for handling large amounts of data and lots of users at the same time.
Good for complex queries that need to run quickly.
Provides redundancy, so if one node fails, the others can keep working.

Cons:

More complex to manage.
Can have higher network overhead, which might slow down some queries.
Requires careful storage management to avoid bottlenecks.

Single High-Spec Node:

Pros:

Easier to manage.
Can have lower network overhead, leading to faster queries in some cases.
Can be more cost-effective for smaller workloads.

Cons:

Not as scalable as many nodes, so it might not handle large workloads as well.
If the node fails, the whole system goes down until it's fixed.
Resource contention can be an issue if lots of queries are running at once.

In simple terms, many low-spec nodes are good for big, busy databases, while a single high-spec node might be better for smaller databases or simpler workloads.

Choosing a larger node type is almost always better for performance compared to relatively smaller node types. For example, instead of 8-node ra3.4xlarge cluster, choosing 2-node ra3.16xlarge will be better for most use cases.

We try to apply the table design best practices and queries design best practices as much as we can and most critical queries will execute the optimal way. However, there will always be some or other query that needs to create temporary tables during query execution. Having more nodes could lead to the system having to create more copies of such temporary tables, and in such cases having less nodes can end up making a noticeable difference.

Having smaller nodes does allow you to bump up your cluster in relatively smaller increments compared to larger nodes. But if performance is the focus then I recommend with going for the largest node type possible.