I have experienced similar issues, where data that is perfectly fine according to PostGIS and QGIS (tested with ST_MakeValid in PostGIS, and by loading the data in QGIS and running QGIS's geometry validity checker), still couldn't be loaded reliable in ArcGIS.
Note that the data in my case was stored in WGS 1984 (SRID 4326) "geometry" storage (so not "geography").
I finally solved it by using two strategies:
Run ST_Buffer in PostGIS with a tolerance of 0, which is a quite common practice according to what I read to solve some difficult to resolve geometry errors.
If that still fails to return a valid data set for ArcGIS (and it wasn't enough in my case...), I recommend deleting any Polygon geometry record having an area less than 1 square meter.
To do this, I calculated the geodesic area for all Polygons in PostGIS, and subsequently deleted the ones with area < 1 m2.
The latter action solved my issues, and allowed ArcGIS to finally accept the data as 100% valid and load it without issues as an ArcGIS Query Layer.
If you want to calculate the geodesic area as well, you can use:
If your data is in SRID 4326:
However, depending on your data, I don't know if this latter action is acceptable, although arguably, Polygons with an area < 1 m2 are likely erroneous in most GIS datasets (sliver data etc.). There is not much use for such tiny geometries in the real world of GIS daily practice and databases.
By the way, I have no idea why ArcGIS isn't capable of using these tiny shapes, while QGIS happily ingests them. Looking at the WKT representation of problematic features in DBeaver, apparently showed a valid representation. Maybe it is something to do with maximum coordinate precisions in ArcGIS, and conversions (transparently executed - not visible to user) needed to get the data in ArcGIS ready format for display, and / or the combination with storage of coordinates in lat/long SRID 4326 instead of a more regular planar projection like Web Mercator, but this is all speculation...