TeXnician already said when it's useful, I would like to add when it is detrimental.
Never use tabularx without an X column!
Sometimes I saw questions with this wrong usage here, never do that!
\documentclass{article}
\usepackage{caption}
\usepackage{array}
\usepackage{booktabs}
\usepackage{tabularx}
\begin{document}
\begin{table}\centering
\caption{Here it is useful}
\begin{tabularx}{\linewidth}{Xcc}
\toprule
Column A & Column B & Column C \\
\midrule
\texttt{tabularx} is useful when there is one (or more) columns with long text which goes on more than one row & lions & ducks \\
\bottomrule
\end{tabularx}
\end{table}
\begin{table}\centering
\caption{\label{tab:awful}Here it is useless and a bit awful (you only have short text in columns, there is too much blank space left)}
\begin{tabularx}{\linewidth}{*3{>{\centering\arraybackslash}X}}
\toprule
Column A & Column B & Column C \\
\midrule
marmots & lions & ducks \\
\bottomrule
\end{tabularx}
\end{table}
\begin{table}\centering
\caption{\label{tab:wrong}Here it is detrimental and horrible (never use \texttt{tabularx} without an X column)}
\begin{tabularx}{\linewidth}{ccc}
\toprule
Column A & Column B & Column C \\
\midrule
marmots & lions & ducks \\
\bottomrule
\end{tabularx}
\end{table}
\begin{table}\centering
\caption{Instead of Table \ref{tab:awful} or Table \ref{tab:wrong}, this one is much better}
\begin{tabular}{ccc}
\toprule
Column A & Column B & Column C \\
\midrule
marmots & lions & ducks \\
\bottomrule
\end{tabular}
\end{table}
\end{document}
