[Question] How exactly do indexes differ?

[francoism90]

First of all many thanks for creating this library. I'm planning to build material views, and some other features like unlogged to learn more about PostgreSQL in general, and also to speed up my applications. :)

I'm a bit confused about the indexes. In the README it's stated indexes created by Laravel itself aren't really indexes (they are constraints).

I recently switched to PostgreSQL on Laravel 11, they are listed as indexes by DBeaver and also seem to behave like this. Is this something that has been improved since Laravel 11, or should I see something different when listing the indexes of a table?

My last question is about material views. I assume you need to define them in your migrations folder, and fill them later with data? I would like to fill them with an user_id column, so this view become pretty large. Or do you recommend creating a material view for each user?

If you have any best practice tips, please let me know as well. But so far it looks exactly what I need. :)

[tpetry]

I'm a bit confused about the indexes. In the README it's stated indexes created by Laravel itself aren't really indexes (they are constraints).

I recently switched to PostgreSQL on Laravel 11, they are listed as indexes by DBeaver and also seem to behave like this. Is this something that has been improved since Laravel 11, or should I see something different when listing the indexes of a table?

Laravel creates a constraint to ensure uniqueness and PG created internally an index to do this. So there's nothing to do and you can use Laravel's unique method if your only goal was to ensure a value exists only once. But you have to use the unique index method I created if you want to use any of the additional index options I've built because they are not available with constraints used by Laravel.

My last question is about material views. I assume you need to define them in your migrations folder, and fill them later with data? I would like to fill them with an user_id column, so this view become pretty large. Or do you recommend creating a material view for each user?

You can define material views in your migrations or anywhere else in your code - its up to you. But you don't fill them with data yourself. You define a query whose results are the data of the table. By default the materialized view is filled automatically at creation time. But I've also provided options to create a materialized view without any filled data and fill it later (refresh the data).

Or do you recommend creating a material view for each user?

Thats up to you. What is best depends on too many factors. But a materialized view for every user sounds very intense when you e.g. have tens of thousands of users.

[francoism90]

@tpetry Thanks for your answer, and explanation how it differs with Laravel's approach. :)

I'm still learning about PostgreSQL, this does include features that make it unique compared to other solutions. It indeed seems every materialized view for an user is very intense and inflexible. I'll checkout how it works and what would be a better solution.

It's great you can basically call most things outside migrations as well.