Reading about the history of topology was enormously clarifying for me here; I can warmly recommend checking out James' History of Topology and/or Dieudonné's History of Algebraic and Differential Topology.
People were trying to do topology long before the modern definition of a topological space; the ideas go back at least as far as Riemann and Poincaré and probably further. When people were trying to clarify exactly what spaces we ought to allow as topological spaces it was not clear exactly how permissive or restrictive we should be (e.g. it was not clear whether we should require spaces to be Hausdorff). The definition of a topological space needs to satisfy several criteria to be useful, some of which trade off against each other:
- It should include the examples people actually want to study, e.g. manifolds, topological vector spaces, topological groups. It should be reasonably easy to verify that these are examples.
- It should straightforwardly allow new spaces to be constructed from old spaces in natural ways, e.g. taking products and quotients.
- It should allow proofs of important general theorems (e.g. the basic results on compactness and connectedness) which are reasonably straightforward.
- It should hopefully not constrain future mathematicians too much if they want to study stranger spaces than we currently do.
Broadly speaking the tradeoff is that if you require more axioms (e.g. the Hausdorff axiom) then you can prove stronger theorems but those theorems apply to fewer spaces. For example if you really dislike the topologist's sine curve then you might decide you want to restrict your attention to topological spaces which are locally path-connected or something like that. This includes all manifolds and CW complexes but it does not include some very important examples such as infinite Galois groups, which are totally disconnected. For another example, you might decide you want to require topological spaces to be Hausdorff because you think non-Hausdorff spaces are weird and poorly behaved, but again this means you exclude very important examples like the Zariski topology, which is almost never Hausdorff.
So, overall the modern definition of a topological space is a reasonable historically contingent compromise between multiple competing considerations, which was settled on from experience trying to use it to do stuff; in particular I want to emphasize that this was a choice we made to use this definition to study topology, and if history had gone another way we could have made a different choice. I personally do not consider topological spaces to be "God-given" the same way I consider, say, the integers to be. And in fact for some applications people have found that topological spaces are not the right tool and they use different tools, for example:
- In some contexts (e.g. constructively) it turns out to be better to isolate the behavior of the lattice of open sets away from the set of points of a topological space, and treat it as an abstract lattice which does not necessarily need to embed into a lattice of subsets. This leads to the study of locales, which loosely speaking are "topological spaces which may not have enough points."
- Topological spaces are not cartesian closed, meaning given two topological spaces $X, Y$ we cannot generally assign a meaningful topology to the set of continuous functions $X \to Y$ that satisfies various nice properties we might want. This is an impediment to certain constructions in algebraic topology, so algebraic topologists often use slightly different convenient categories of topological spaces, which are cartesian closed.
- Topological abelian groups do not form an abelian category, which makes it difficult to do homological algebra with them, and in some applications people really want to do this. Recently some replacements for topological spaces have appeared, namely condensed sets and pyknotic sets, which fix this issue by, loosely speaking, allowing some stranger "even more non-Hausdorff quotients" than are possible in topological spaces.
However, it's worth noting that none of these examples are motivated by the desire to throw out pathological-looking spaces. Actually some of them allow "even more pathological-looking" spaces! Grothendieck taught us that it is better to have a nice category with nasty-looking objects than to have nice objects but living in a nasty category, and the category of topological spaces is nice but not that nice; the examples above are trying to rectify this, as opposed to trying to remove individual pathological-looking spaces. The individual pathological-looking spaces just turn out not be too much of an impediment, and some of them even end up being surprisingly important.
On the other hand, sometimes people have asked for a simpler kind of topology exactly as you are asking for, which allows e.g. nice spaces like manifolds and not pathological examples like space-filling curves. This goes under the broad heading of "tame topology," and I know nothing about it but you can use that search term to find more. It is not a mainstream subject, and my impression is that it mostly has not seemed worth the effort since topological spaces are mostly adequate for foundations.
For example if you ultimately only care about manifolds (say your interest is in differential geometry) then the way the foundations of manifold theory get set up is that we define manifolds as being certain special topological spaces, namely those that are locally Euclidean and Hausdorff (plus a size axiom). This works fine. Manifolds are very nice, e.g. locally path-connected. So in manifold topology you can mostly ignore stuff like the topologist's sine curve and space-filling curves. I don't know that using a theory of tame topology as the foundations here would meaningfully simplify anything, and it might be annoyingly restrictive.
But also, topology has many more applications than this and in some of those the spaces that occur are strange-looking at first glance (e.g. the Galois groups mentioned above, which are Stone spaces).
