Is there any way to divide one table into several small ones and keep the querys?

Question

I have a problem on my server, my table artigos has 5.5 million lines and a normal select takes about 8 seconds to run. I didn’t have a brilliant idea, I will divide my table into 10 tables, and the articles will be distributed as follows:

id % 10 -> tabela_destino

When my system looks for my article, it will search in a table of 550,000 instead of 5.5 million, and the consultation time will be much shorter, about half a second. However the system that is consulting is Django, so there is no way to do the search in hand ( OR at least I believe that).

So I decided to keep the normal table with 5.5 million, which will have only the id, and together the other 10 tables. What I want to know is if there is a way to automate postgres to search a field in another table:

I will illustrate:

My initial table has the following fields:

Artigos:
id: integer (pk)
text: string
data: timestamp

Then I will divide into others:

Artigos0
id: integer (pk)
text: string
data: timestamp

Artigos1
id: integer (pk)
text: string
data: timestamp

....

And the initial table will only have the id:

Artigos:
id: integer (pk)

But when you have a select of the type select texto from Artigos where id = 1, I want the postgres already know that have to look in the table Artigo1 without changing the query. Is there any way to do this automatically?

Answer 1

I don’t know if this type of automation is possible in postgres, but if you choose to actually split the tables, you can use Raw Queries do Django para buscar direto na tabela que Voce deseja (do a busca na mao, as Voce said).

It would look something like this:

id = 1
resultado = Artigos1.objects.raw("SELECT texto FROM artigos1 WHERE id=%d", id)

Reference: https://docs.djangoproject.com/en/2.1/topics/db/sql/#Performing-raw-queries