Re: How should we design our tables and indexes

There is a lot to unpack here. I'm going to take a quick pass, but you
ought to consider getting some custom expert help.

On Sat, Feb 10, 2024 at 2:39 PM veem v <veema0000(at)gmail(dot)com> wrote:

> ... These will be pagination queries. These read queries will be on big
> transaction tables (will have ~500+ attributes approx will have approx.
> rows size of ~1KB) having a continuous stream of inserts
>

Pagination is already a hard problem, and does not even make sense when
combined with "a continuous stream of inserts". What should the user see
when they click on page 2?

Also, 500 attributes?! What data types are those? If boolean, you may want
to look at using bitfields.

1)Is there any way in postgres to influence the optimizer for the
> "first_row" optimization, so that it won't go for evaluating all the rows
> from such UI search queries. As because these will be pagination queries
> and the user will be interested in seeing top 100 transactions in the first
> page asap?
>

Using LIMIT does allow for this, with certain caveats. The best answer for
a lot of these questions is "try it and see" with a simplified version of
your queries against some dummy data.

> one Index on table1(MID) , one index Table1(CID), one index on
> table2(ACN_NBR)?
>

This. One index for each important column. Especially if they vary between
queries, as you alluded to later.

select count(*) over() as total_record, * [large query omitted]
>

Queries starting with select count(*) are also a red flag, but it's hard to
tell from this. Probably best to explain what you think the query is doing
using regular words.

3)One of the use cases is that the customer should be able to search on
> certain attributes and should be able to see the transactions in "desc by
> processing_date" i.e. latest transactions on the first page on the UI. And
> in such scenario, if the search attribute is less unique and the customer
> puts a date range of a month i.e. over 30 partitions , it may results in
> scanning and sorting billions of rows to get the top/recent ~100
> transactions and most likely not going to respond back in <1 sec, even goes
> for the index. So how should we handle or design indexes for catering such
> queries? For e.g. if we have the only filter on column "TABLE1.CID" in the
> above query, which is very less unique then how to handle it?
>

Well, there is only so much a database can do if you are pulling from 500
different attributes. But the whole purpose of an indexed column is to
prevent having to scan *all* the rows. If it's a common value, then
hopefully there is something else in the where clause more specific that is
also indexed. As mentioned above, a LIMIT and ORDER BY, with the
appropriate indexes, can return early.

For e.g. if we set "max_parallel_workers_per_gather"=4 to speed up the
> queries, then we will be able to serve only 32/4=8 concurrent user requests
> at any point in time. If we are targeting to serve ~100 concurrent users ,
> will it be advisable to change or we should test the system with default
> i.e. not setting this parallel parameter?
>

Again, this is a TIAS (try it and see), but in general it's not the number
of concurrent users, but the number of concurrent queries, which is a
factor of how fast your queries are. Feed Postgres as many parallel workers
as you can.

tl;dr Less than 1 second response time is *probably* reachable given your
parameters, but parts of it are too vague to state for certain.

Cheers,
Greg