Dealing with really big db tables, any advice?

Have you checked that all indices are set for the tables your using?

It's basically just a table looks that like this:

CREATE TABLE products (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    barcode TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT DEFAULT NULL,
    manufacturer TEXT DEFAULT NULL,
    not_vegan_reason TEXT DEFAULT NULL,
    warning TEXT DEFAULT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    image_url TEXT DEFAULT '' NOT NULL,
    vegan_status veg_status DEFAULT 'not_sure' NOT NULL,
    ingredients TEXT
);

Could an index benefit this table alone somehow?

It fetches comment count and product change count, but I removed these and still slow.

And I have a search query that looks like this:

        let searchName = query @Product |> filterWhereILike (#name, "%" <> searchTerm <> "%")
        let searchManufacturer = query @Product |> filterWhereILike (#manufacturer, Just ("%" <> searchTerm <> "%"))
        let queryProducts = queryUnion searchName searchManufacturer
        (productsQ, pagination) <-
            queryProducts
                |> ( \p -> case veganStatusParam of
                        Nothing -> p
                        Just vegStatusParam ->
                            p |> filterWhere (#veganStatus, vegStatusParam)
                   )
                |> ( \p -> case orderType of
                        Ascending -> orderByAsc #updatedAt p
                        Descending -> orderByDesc #updatedAt p
                   )
                |> paginateWithOptions
                    ( defaultPaginationOptions
                        |> set #windowSize 1
                        |> set #maxItems 10
                    )
        products <- productsQ |> fetch

I guess maybe the search terms could be a performance bottleneck?

Try adding an index on the name and manufacturer.

Amazing! So much faster now! Thanks!

You can create a one index per table, and Postgres will optimize it:

CREATE INDEX products_index ON products (id, name, manufacturer);

Really nice, thanks! Only bottleneck I have now seems to be ORDER BY. Looking into how I can do some optimization on timestamps.

Index will also speed up sorting, adding one on the updatedAt.

Another option is to increase the memory limits for Postgres, so it sorts in memory, not on disk.

Didn't get noticable improvement on adding date to regular index, but adding a BTREE index on the date really did the trick :D

CREATE INDEX products_updated_at_index ON products USING BTREE(updated_at);

Interesting, the default index type is btree, so that's strange.

It became instantly faster after running that migration, but can't say for 100% sure if the previous index was processing or something 🤷