How to prevent seek pagination from doing sequential scan over entire table
There may be more potential for improvement. But the obvious big issue is this:

SELECT
   r.resource_id,
   title,
   COALESCE(likes, 0) AS likes
FROM
   resources r 
   LEFT JOIN
      resource_votes_aggregate a 
      ON r.resource_id = a.resource_id 
ORDER BY
   likes DESC,               -- refers to output column!
   resource_id DESC
LIMIT 5;

The manual:


  If an ORDER BY expression is a simple name that matches both an output
  column name and an input column name, ORDER BY will interpret it as
  the output column name. This is the opposite of the choice that GROUP BY
  will make in the same situation. This inconsistency is made to be
  compatible with the SQL standard.


So even if there is an applicable index including resource_votes_aggregate.likes, it cannot be used as the column is hidden behind the expression COALESCE(likes, 0) AS likes - reflected in the query plan as:


  Sort Key: (COALESCE(a.likes, '0'::bigint)) DESC


Fix with:

SELECT
   r.resource_id,
   r.title,
   COALESCE(a.likes, 0) AS likes
FROM
   resources r 
   LEFT JOIN
      resource_votes_aggregate a 
      ON r.resource_id = a.resource_id 
ORDER BY
   a.likes DESC NULLS LAST,  -- refers to input column!
   r.resource_id DESC
LIMIT 5;

This refers to the input column now and qualifies for index usage. It's substantially cheaper to evaluate in any case, even without index, so you can't go wrong with this.

Since likes obviously can be NULL, be sure to add NULLS LAST. See:


Sort NULL values to the end of a table


Table-qualification for all input columns (like I added) is a good way to avoid confusion - not only for the problem at hand.

MATERIALIZED VIEW

The MV in your fiddle would be more efficient like this:

CREATE MATERIALIZED VIEW resource_votes_aggregate AS
SELECT resource_id
     ,(count(*) FILTER (WHERE vote))::int AS likes  -- faster
FROM   resource_votes
GROUP  BY resource_id;


The aggregate FILTER is typically faster.
count() returns bigint (8 bytes). Cast to integer (4 bytes).

This is the all-important index (missing in your fiddle):

CREATE INDEX ON resource_votes_aggregate (likes DESC, resource_id DESC);


With two integer columns, index tuples fit the minimum size of 8 bytes. (With bigint for likes, it would be 16 bytes (12 + 4 padding). See:


Is a composite index also good for queries on the first field?
Configuring PostgreSQL for read performance


Obviously, you also need an index on resources(resources_id). If that's the PK, then it's there.

If you use this query a lot, and preconditions for index-only scans are given, a multicolumn index on (resources_id) INCLUDE (title) may pay. See:


Can Postgres use an index-only scan for this query with joined tables?


Optimized query

If Postgres is not smart enough to give you the best query plan, this equivalent query will:

(
SELECT r.resource_id, r.title, a.likes -- COALESCE not needed
FROM   resource_votes_aggregate a
JOIN   resources r USING (resource_id)
ORDER  BY a.likes DESC                 -- NULLS LAST not needed
        , a.resource_id DESC           -- perfect for above index
LIMIT  5  -- logically redundant, but may help with best plan
)
UNION ALL
(
SELECT resource_id, title, 0 AS likes 
FROM   resources r
WHERE  NOT EXISTS (  -- only rows without likes
   SELECT FROM resource_votes_aggregate a
   WHERE  a.resource_id = r.resource_id
   )
ORDER  BY r.resource_id DESC
LIMIT  5  -- logically redundant, but may help with best plan
)
LIMIT 5


The second SELECT will typically be never executed, if there are at least 5 resources with likes. See:


Way to try multiple SELECTs till a result is available?
Select first record if none match


Aside

You mentioned pagination. Be sure to avoid LIMIT / OFFSET for big tables. See:


What is the recommended way to join junction tables for efficient ordering/pagination?