Is a relational database with a dynamic number of tables a good design? I have a use case where I wish to create a table for each entity to which the underlying application that owns this entity will publish records.
This table has a fixed structure, so if there are 5 such entities in my system, there will be 5 different tables with the same schema.
The schema is generic with one of the columns in the schema as JSON for flexibility. I do not expect queries based on the fields in the JSON. I expect the following queries on each entity:

On the auto-increment id primary key column with LIMIT and OFFSET where I need to read X rows from the record with id Y.
On the creation date column with LIMIT X.

I expect thousands of such entities to be created on the fly so in turn there will be thousands of tables in the database.
In future when one of these entities have fulfilled their purpose, the table would be simply deleted.
I expect most of these tables to have not more than 100 rows while there will be a few with at least 1M rows as time goes by.
This design makes data easy to query as my application can determine the table name from the entity name.
Is this a bad design?
Is there a limit to the number of tables in a database in RDBMS (the above design is with Postgresql 11 in mind) keeping performance in mind?
Should I use any different datastore to achieve this other than RDBMS? Any suggestions?
Is this a bad design?

Yes.  See comment by LowlyDBA.

Is there a limit to the number of tables in a database in RDBMS ...

Each RDBMS has a different maximum number of tables.  There is a Q&amp;A for this is on Stack Overflow.

Should I use any different datastore to achieve this other than RDBMS?

No. RDBMS should be fine if you design your schema correctly.

Any suggestions?

From your description, a single table with Entity_Name as part of the Primary Key should work. A table with 100M rows is not a problem for today's databases.
create table data_for_entity (
  entity_name     varchar(50),
  entity_id       int,
  entity_value    text, -- or whatever PostgreSQL uses for JSON
  constraint data_for_entity_pk Primary Key (entity_name, entity_id)
);