Postgresql – What kind of database should I use

This seems a little outside the scope of a StackExchange question. However.....

NoSQL databases are, typically, build to resolve specific issues with the relational model. The most common issue addressed is scalability. However, because they're all designed to address different aspects of certain problems that some applications have with the relational model, there really isn't something you can say about all of them as a whole. Maybe you need to handle terabytes of data? Or maybe you need to handle a data schema that is very dynamic across the board and an EAV type schema would just kill performance? Maybe you want a data store that cares about availability more than consistency (see CAP theorem)? Each one is good at something that could be wildly different from what another is good at, while an RDBMS is a much more generalized database.

The answer I hear from people I trust on this topic is that if you don't already know why you need to abandon a traditional RDBMS for NoSQL, then you almost certainly should stick to an RDBMS. There's a reason it took 30 years before people began to seriously consider data store models beyond the RDBMS. If your queries are requiring features not available in MySQL/MariaDB, you may wish to consider another RDBMS that has a more robust feature set. That could be PostgreSQL, or Oracle, or MS SQL Server.

Your proposed schema here already suits the relational model fairly well. You may need a few junction tables for many-to-many relationships, but I don't think you necessarily will see many of those. If you properly size the DB, properly index your tables, and analyze your common queries -- things you have to do with NoSQL, too -- I don't see that you'd have a huge problem. You may even be able to get away with views for the most common queries.

You need to decide if your categories are hierarchical (one parent) or graphical (multiple parents). For example, are "cell phones" in the category "electronics" as well as "communications"?

What kinds of fields might your categories have? Use Table Inheritance to allow categories that are similar but not identical.

Let's say you go with hierarchical categories. It should look like this:

-- hierarchical relationships usually have a single table with a field with a self-referencing foreign key:
create table category (
  id bigserial primary key,
  name text not null unique,
  parent_id bigint null references category(id) --if I'm null then I am a top-level category
);

create table ad (
  id bigserial primary key,
  subject text not null
  ...
);

-- this pattern is called a junction table:
create table ad_category (
  ad_id bigint references ad(id),
  category_id bigint references category(id),
  primary key (ad_id, category_id)
);

I would suggest Postgres over MySql, since it supports WITH queries, which make hierarchical querying easier.

Regarding images, consider storing them in Amazon S3 / Cloudfront. You would associated them with the ad via the HTML in the ad. For example, in your UI, the user would upload an image, get the URL back, and this would get plugged in to an <img> tag.

Though you could also create a junction table similar to the ad_category table, and remove orphan images from S3 on a schedule:

create table ad_picture (
  ad_id bigint not null references ad(id),
  picture_url text not null,
  unique (ad_id, picture_url)
);