Search Postgresql Archives

RE: [EXTERNAL] Re: performance expectations for table(s) with 2B recs

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



 

On 2021-12-08 14:44:47 -0500, David Gauthier wrote:
> So far, the tables I have in my DB have relatively low numbers of records (most
> are < 10K, all are < 10M).  Things have been running great in terms of
> performance.  But a project is being brainstormed which may require some tables
> to contain a couple billion records.
[...]
> What else should I be worried about ?
>
> I suspect that part of why things are running really well so far is that the
> relatively small amounts of data in these tables ends up in the DB cache and
> disk I/O is kept at a minimum.  Will that no longer be the case once queries
> start running on these big tables ?

>

>Depends a lot on how good the locality of your queries is. If most read

>only the same parts of the same indexes, those will still be in the

>cache. If they are all over the place or if you have queries which need

>to read large parts of your tables, cache misses will make your

>performance a lot less predictable, yes. That stuff is also hard to

>test, because when you are testing a query twice in a row, the second

>time it will likely hit the cache and be quite fast.

> 

>But in my experience the biggest problem with large tables are unstable

>execution plans - for most of the parameters the optimizer will choose

>to use an index, but for some it will erroneously think that a full

>table scan is faster. That can lead to a situation where a query

>normally takes less than a second, but sometimes (seemingly at random)

>it takes several minutes - users will understandably be upset about such

>behaviour. It is in any case a good idea to monitor execution times to

>find such problems (ideally before users complain), but each needs to be

>treated on an individual basis, and sometimes there seems to be no good

>solution.

 

To the OP, that’s is a tall order to answer – basically that’s wjhy DBA’s still have

Jobs…

 

For Peter I have a question. What exactly causes ‘unstable execution plans’ ??

 

Besides not using bind variables, bad statistics, would you elaborate in what would

contribute to that instability?





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux