I have tens of thousands of geofences on the AWS Redshift table A.
And also logging of tens of thousands of AIS signals every 1 hour on table B.
However, I want to check and log that which AIS is incoming or outgoing for whole geofences.
How can I achieve that?
From your description of the problem, I would guess that a geofence needs to be transformed into a polygon using ST_Polygon().  Once you have your polygons (table A), you will need to take the AIS signals and transform them into geolocation points (table B) using ST_Point().
When you have all your data transformed into compatible geolocation format - you will use the ST_Contains().
I will add that geolocation data might take sometime in Redshift and you will most likely have to do a cartesian product of all your polygons vs all your geolocation points.
I hope I understood your problem properly.
How to check an AIS signal is outgoing from a geofence with spatial database?