Re: Contention preventing locking

them are larger for xlock optimization. But, you can notice that xlock optimization significantly reduce degradation speed although doesn't completely eliminate this negative trend. Thanks, Michail. чт, 15 февр. 2018 г. в 19:00, Konstantin Knizhnik mailto:k.knizh...@postgresp

Re: Contention preventing locking

On 20.02.2018 14:26, Simon Riggs wrote: On 15 February 2018 at 16:00, Konstantin Knizhnik wrote: So in heap_acquire_tuplock all competing transactions are waiting for TID of the updated version. When transaction which changed this tuple is committed, one of the competitors will grant this

Re: Contention preventing locking

On 20.02.2018 16:42, Simon Riggs wrote: On 20 February 2018 at 13:19, Konstantin Knizhnik wrote: On 20.02.2018 14:26, Simon Riggs wrote: On 15 February 2018 at 16:00, Konstantin Knizhnik wrote: So in heap_acquire_tuplock all competing transactions are waiting for TID of the updated

Re: Contention preventing locking

it to be too expensive operation. Scanning the whole page on tuple update seems to be not an acceptable solution. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

Re: Contention preventing locking

On 20.02.2018 19:39, Simon Riggs wrote: On 20 February 2018 at 16:07, Konstantin Knizhnik wrote: On 20.02.2018 14:26, Simon Riggs wrote: Try locking the root tid rather than the TID, that is at least unique per page for a chain of tuples, just harder to locate. As far as I understand, it

Re: Contention preventing locking

On 26.02.2018 17:00, Amit Kapila wrote: On Thu, Feb 15, 2018 at 9:30 PM, Konstantin Knizhnik wrote: Hi, PostgreSQL performance degrades signficantly in case of high contention. You can look at the attached YCSB results (ycsb-zipf-pool.png) to estimate the level of this degradation

Re: Contention preventing locking

On 26.02.2018 17:20, Amit Kapila wrote: On Tue, Feb 20, 2018 at 10:34 PM, Konstantin Knizhnik wrote: On 20.02.2018 19:39, Simon Riggs wrote: On 20 February 2018 at 16:07, Konstantin Knizhnik wrote: On 20.02.2018 14:26, Simon Riggs wrote: Try locking the root tid rather than the TID

Re: Contention preventing locking

On 28.02.2018 16:32, Amit Kapila wrote: On Mon, Feb 26, 2018 at 8:26 PM, Konstantin Knizhnik wrote: On 26.02.2018 17:20, Amit Kapila wrote: Can you please explain, how it can be done easily without extra tuple locks? I have tried to read your patch but due to lack of comments, it is not

Re: [HACKERS] Surjective functional indexes

should be prohibited for such index at all. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

Re: Cached/global query plans, autopreparation

On Mar 2, 2018, at 11:29 PM, Bruce Momjian wrote: > On Thu, Feb 15, 2018 at 03:00:17PM +0100, Shay Rojansky wrote: >> Just wanted to say that I've seen more than 10% improvement in some >> real-world >> application when preparation was done properly. Also, I'm assuming that >> implementing this

Re: Contention preventing locking

On 03.03.2018 16:44, Amit Kapila wrote: On Thu, Mar 1, 2018 at 1:22 PM, Konstantin Knizhnik wrote: On 28.02.2018 16:32, Amit Kapila wrote: On Mon, Feb 26, 2018 at 8:26 PM, Konstantin Knizhnik wrote: Yes, but two notices: 1. Tuple lock is used inside heap_* functions. But not in

Re: All Taxi Services need Index Clustered Heap Append

ns. In Vertica you can create arbitrary number of "projections" where data is sorted in different way. In Postgres is can be achieved using indexes or external storages. I hope that extensible heap API will simplify development and integration of such alternative storages. But even ri

WaitLatchOrSocket optimization

ks like it is negligible comparing with overhead of close (if I comment this branch, then pgbench performance is almost the same - 227k TPS). But if there are some other arguments against using cache in WaitLatchOrSocket, we have a patch particularly for postgres_fdw. -- Konstantin Knizhni

Re: WaitLatchOrSocket optimization

Hi, On 15.03.2018 20:25, Andres Freund wrote: Hi, On 2018-03-15 19:01:40 +0300, Konstantin Knizhnik wrote: Right now function WaitLatchOrSocket is implemented in very inefficient way: for each invocation it creates epoll instance, registers events and then closes this instance. Right

Question about WalSndWriteData

included in the copy data packet which is already copied to libpq connection buffer. And next WalSndPrepareWrite call will reset ctx->out buffer. So I wonder what is the reason of writing timestamp in ctx->out buffer after processing it by pq_putmessage_noblock? -- Konstantin Kni

Lack of T_TargetEntry in exprType function

Is there any reason for not handling T_TargetEntry node kind in exprType() function in nodeFuncs.c? Is it considered as non-valid expression tag? But why in this case it is handled in the exprLocation function? -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian

Re: Lack of T_TargetEntry in exprType function

On 20.03.2018 17:00, Tom Lane wrote: Konstantin Knizhnik writes: Is there any reason for not handling T_TargetEntry node kind in exprType() function in nodeFuncs.c? It's intentional because that's not considered an executable expression. I tried to apply this function to the a

Re: Question about WalSndWriteData

On 21.03.2018 04:50, Peter Eisentraut wrote: On 3/16/18 12:08, Konstantin Knizhnik wrote: pq_putmessage_noblock copies data from ctx->out buffer to libpq buffers. After it we write timestamp to ctx->out buffer. And comments says that we should do it "as late as possible". Bu

Re: [HACKERS] Secondary index access optimizations

essage-id/flat/8eed9c23-19ba-5404-7a9e-0584b836b3f3%40postgrespro.ru#8eed9c23-19ba-5404-7a9e-0584b836b...@postgrespro.ru -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company repeat-query.sh Description: application/shellscript

Re: [HACKERS] Secondary index access optimizations

On 21.03.2018 20:30, Konstantin Knizhnik wrote: On 01.03.2018 23:15, Andres Freund wrote: Hi, This patch seems like quite a good improvement. One thing I've not really looked at but am slightly concerned in passing: Are there cases where we now would do a lot of predicate pruning

Re: Custom explain options

On 10/01/2024 8:46 am, Michael Paquier wrote: On Wed, Jan 10, 2024 at 01:29:30PM +0700, Andrei Lepikhov wrote: What do you think about this really useful feature? Do you wish to develop it further? I am biased here. This seems like a lot of code for something we've been delegating to the exp

Re: Custom explain options

On 10/01/2024 8:29 am, Andrei Lepikhov wrote: On 30/11/2023 22:40, Konstantin Knizhnik wrote: In all this cases we are using array of `Instrumentation` and if it contains varying part, then it is not clear where to place it. Yes, there is also code which serialize and sends instrumentations

Re: Custom explain options

On 09/01/2024 10:33 am, vignesh C wrote: On Sat, 21 Oct 2023 at 18:34, Konstantin Knizhnik wrote: Hi hackers, EXPLAIN statement has a list of options (i.e. ANALYZE, BUFFERS, COST,...) which help to provide useful details of query execution. In Neon we have added PREFETCH option which shows

Re: Custom explain options

On 12/01/2024 7:03 pm, Tomas Vondra wrote: On 10/21/23 14:16, Konstantin Knizhnik wrote: Hi hackers, EXPLAIN statement has a list of options (i.e. ANALYZE, BUFFERS, COST,...) which help to provide useful details of query execution. In Neon we have added PREFETCH option which shows

Re: Custom explain options

On 13/01/2024 4:51 pm, Tomas Vondra wrote: On 1/12/24 20:30, Konstantin Knizhnik wrote: On 12/01/2024 7:03 pm, Tomas Vondra wrote: On 10/21/23 14:16, Konstantin Knizhnik wrote: Hi hackers, EXPLAIN statement has a list of options (i.e. ANALYZE, BUFFERS, COST,...) which help to provide

Re: Custom explain options

On 14/01/2024 11:47 pm, Tomas Vondra wrote: The thing that was not clear to me is who decides what to prefetch, which code issues the prefetch requests etc. In the github links you shared I see it happens in the index AM code (in nbtsearch.c). It is up to the particular plan node (seqscan, in

Re: Custom explain options

On 15/01/2024 5:08 pm, Tomas Vondra wrote: My patch does not care about prefetching internal index pages. Yes, it's a limitation, but my assumption is the internal pages are maybe 0.1% of the index, and typically very hot / cached. Yes, if the index is not used very often, this may be untrue. B

Re: index prefetching

Hi, On 12/01/2024 6:42 pm, Tomas Vondra wrote: Hi, Here's an improved version of this patch, finishing a lot of the stuff that I alluded to earlier - moving the code from indexam.c, renaming a bunch of stuff, etc. I've also squashed it into a single patch, to make it easier to review. I am th

Re: Custom explain options

On 16/01/2024 5:38 pm, Tomas Vondra wrote: By "broken" you mean that you prefetch items only from a single leaf page, so immediately after reading the next one nothing is prefetched. Correct? Yes, exactly. It means that reading first heap page from next leaf page will be done without prefetc

Re: index prefetching

On 16/01/2024 6:25 pm, Tomas Vondra wrote: On 1/16/24 09:13, Konstantin Knizhnik wrote: Hi, On 12/01/2024 6:42 pm, Tomas Vondra wrote: Hi, Here's an improved version of this patch, finishing a lot of the stuff that I alluded to earlier - moving the code from indexam.c, renaming a bun

Re: index prefetching

On 16/01/2024 11:58 pm, Jim Nasby wrote: On 1/16/24 2:10 PM, Konstantin Knizhnik wrote: Amazon RDS is just vanilla Postgres with file system mounted on EBS (Amazon  distributed file system). EBS provides good throughput but larger latencies comparing with local SSDs. I am not sure if read

Re: index prefetching

On 16/01/2024 11:58 pm, Jim Nasby wrote: On 1/16/24 2:10 PM, Konstantin Knizhnik wrote: Amazon RDS is just vanilla Postgres with file system mounted on EBS (Amazon  distributed file system). EBS provides good throughput but larger latencies comparing with local SSDs. I am not sure if read

Re: index prefetching

I have integrated your prefetch patch in Neon and it actually works! Moreover, I combined it with prefetch of leaf pages for IOS and it also seems to work. Just small notice: you are reporting `blks_prefetch_rounds` in explain, but it is not incremented anywhere. Moreover, I do not precisely u

Re: index prefetching

On 18/01/2024 6:00 pm, Tomas Vondra wrote: On 1/17/24 09:45, Konstantin Knizhnik wrote: I have integrated your prefetch patch in Neon and it actually works! Moreover, I combined it with prefetch of leaf pages for IOS and it also seems to work. Cool! And do you think this is the right design

Re: index prefetching

On 18/01/2024 5:57 pm, Tomas Vondra wrote: On 1/16/24 21:10, Konstantin Knizhnik wrote: ... 4. I think that performing prefetch at executor level is really great idea and so prefetch can be used by all indexes, including custom indexes. But prefetch will be efficient only if index can

Re: index prefetching

On 20/01/2024 12:14 am, Tomas Vondra wrote: Looks like I was not true, even if it is not index-only scan but index condition involves only index attributes, then heap is not accessed until we find tuple satisfying search condition. Inclusive index case described above (https://commitfest.postgr

Re: index prefetching

On 19/01/2024 2:35 pm, Tomas Vondra wrote: On 1/19/24 09:34, Konstantin Knizhnik wrote: On 18/01/2024 6:00 pm, Tomas Vondra wrote: On 1/17/24 09:45, Konstantin Knizhnik wrote: I have integrated your prefetch patch in Neon and it actually works! Moreover, I combined it with prefetch of leaf

Re: index prefetching

On 22/01/2024 1:47 am, Tomas Vondra wrote: h, right. Well, you're right in this case we perhaps could set just one of those flags, but the "purpose" of the two places is quite different. The "prefetch" flag is fully controlled by the prefetcher, and it's up to it to change it (e.g. I can easily

Re: index prefetching

On 22/01/2024 1:39 am, Tomas Vondra wrote: Why we can prefer covering index  to compound index? I see only two good reasons: 1. Extra columns type do not  have comparison function need for AM. 2. The extra columns are never used in query predicate. Or maybe you don't want to include the column

Speed-up shared buffers prewarming

Hi hackers, It is well known fact that queries using sequential scan can not be used to prewarm cache, because them are using ring buffer even if shared buffers are almost empty. I have searched hackers archive but failed to find any discussion about it. What are the drawbacks of using free buf

Re: PGC_SIGHUP shared_buffers?

On 16/02/2024 10:37 pm, Thomas Munro wrote: On Fri, Feb 16, 2024 at 5:29 PM Robert Haas wrote: 3. Reserve lots of address space and then only use some of it. I hear rumors that some forks of PG have implemented something like this. The idea is that you convince the OS to give you a whole bunch

Parallel plan cost

Hi hackers, I wonder why while calculating cost of parallel scan we divide by parallel_divisor only CPU run cost, but not storage access cost? So we do not take in account that reading pages is also performed in parallel. Actually I observed strange behavior when increasing work_mem disables p

OOM in hash join

Hi hackers, Too small value of work_mem cause memory overflow in parallel hash join because of too much number batches. There is the plan: explain SELECT * FROM solixschema.MIG_50GB_APR04_G1_H a join solixschema.MIG_50GB_APR04_G2_H b on a.seq_pk = b.seq_pk join solixschema.MIG_50GB_APR04_G3_

Re: Memory leak from ExecutorState context?

On 11.04.2023 8:14 PM, Jehan-Guillaume de Rorthais wrote: On Sat, 8 Apr 2023 02:01:19 +0200 Jehan-Guillaume de Rorthais wrote: On Fri, 31 Mar 2023 14:06:11 +0200 Jehan-Guillaume de Rorthais wrote: [...] After rebasing Tomas' memory balancing patch, I did some memory measures to answer

Re: Memory leak from ExecutorState context?

On 21.04.2023 1:51 AM, Melanie Plageman wrote: On Thu, Apr 20, 2023 at 12:42 PM Konstantin Knizhnik wrote: On 11.04.2023 8:14 PM, Jehan-Guillaume de Rorthais wrote: On Sat, 8 Apr 2023 02:01:19 +0200 Jehan-Guillaume de Rorthais wrote: On Fri, 31 Mar 2023 14:06:11 +0200 Jehan-Guillaume de

Can concurrent create index concurrently block each other?

One our customer complains that he spawned two `create index concurrently` for two different tables and both stuck in"waiting for old snapshots". I wonder if two CIC can really block each other in `WaitForOlderSnapshots`? I found the similar question in hacker archive: https://www.postgresql.or

Re: Can concurrent create index concurrently block each other?

On 15/10/2023 10:59 pm, Tom Lane wrote: Konstantin Knizhnik writes: One our customer complains that he spawned two `create index concurrently` for two different tables and both stuck in"waiting for old snapshots". I wonder if two CIC can really block each other in `WaitForOlde

Custom explain options

Hi hackers, EXPLAIN statement has a list of options (i.e. ANALYZE, BUFFERS, COST,...) which help to provide useful details of query execution. In Neon we have added PREFETCH option which shows information about page prefetching during query execution (prefetching is more critical for Neon archi

Re: Custom explain options

On 30/11/2023 5:59 am, Andrei Lepikhov wrote: On 21/10/2023 19:16, Konstantin Knizhnik wrote: EXPLAIN statement has a list of options (i.e. ANALYZE, BUFFERS, COST,...) which help to provide useful details of query execution. In Neon we have added PREFETCH option which shows information about

Re: Index range search optimization

On 04/10/2023 3:00 am, Alexander Korotkov wrote: On Wed, Oct 4, 2023 at 12:59 AM Pavel Borisov wrote: I've looked through the patch v8. I think it's good enough to be pushed if Peter has no objections. Thank you, Pavel. I'll push this if there are no objections. -- Regards, Alexander Ko

Re: [Proposal] Global temporary tables

n and postgres_fdw establish its own session where content of the table is empty. But if you insert some data in f_gtt1, then you will be able to select this data from it because of connection cache in postgres_fdw. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

Re: Built-in connection pooler

Hi David, On 24.03.2020 16:26, David Steele wrote: Hi Konstantin, On 11/14/19 2:06 AM, Konstantin Knizhnik wrote: Attached please find rebased patch with this bug fixed. This patch no longer applies: http://cfbot.cputube.org/patch_27_2067.log CF entry has been updated to Waiting on Author

Small computeRegionDelta optimization.

vs. 1881 seconds. Looks like it was mostly limited by time of writing data to the disk. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company diff --git a/src/backend/access/transam/generic_xlog.c b/src/backend/access/transam/generic_xlog.c index

Re: Columns correlation and adaptive query optimization

On 24.03.2020 20:12, David Steele wrote: On 12/24/19 3:15 AM, Konstantin Knizhnik wrote: New version of patch implicitly adding multicolumn statistic in auto_explain extension and using it in optimizer for more precise estimation of join selectivity. This patch fixes race condition while

Re: Columns correlation and adaptive query optimization

On 25.03.2020 16:00, David Steele wrote: On 3/25/20 6:57 AM, Konstantin Knizhnik wrote: On 24.03.2020 20:12, David Steele wrote: On 12/24/19 3:15 AM, Konstantin Knizhnik wrote: New version of patch implicitly adding multicolumn statistic in auto_explain extension and using it in optimizer

Re: weird hash plan cost, starting with pg10

idn't check versions below 8.4 though. Thanks Richard I can propose the following patch for the problem. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index

Re: Columns correlation and adaptive query optimization

y limit on the number of columns for which this will work, or should there be any such limit...? Right now there is limit for maximal number of columns used in extended statistic: 8 columns. But in practice I rarely see join predicates involving more than 3 columns. -- Konstantin Knizhnik Postgr

Re: Columns correlation and adaptive query optimization

On 25.03.2020 20:04, Rafia Sabih wrote: Also, there is no description about any of the functions here, wouldn’t hurt having some more comments there. Attached please find new version of the patch with more comments and descriptions added. -- Konstantin Knizhnik Postgres Professional

Re: zombie connections

On 03.04.2020 15:29, Robert Haas wrote: Hi, Suppose that the server is executing a lengthy query, and the client breaks the connection. The operating system will be aware that the connection is no more, but PostgreSQL doesn't notice, because it's not try to read from or write to the socket. I

Race condition in TransactionIdIsInProgress

Hi hackers, Postgres first records state transaction in CLOG, then removes transaction from procarray and finally releases locks. But it can happen that transaction is marked as committed in CLOG, XMAX_COMMITTED bit is set in modified tuple but TransactionIdIsInProgress still returns true. As a

Re: Lack of PageSetLSN in heap_xlog_visible

On 11.11.2022 03:20, Jeff Davis wrote: On Thu, 2022-10-13 at 12:49 -0700, Jeff Davis wrote: It may violate our torn page protections for checksums, as well... I could not reproduce a problem here, but I believe one exists when checksums are enabled, because it bypasses the protections of Updat

Re: Add ZSON extension to /contrib/

On 25.05.2021 13:55, Aleksander Alekseev wrote: Hi hackers, Back in 2016 while being at PostgresPro I developed the ZSON extension [1]. The extension introduces the new ZSON type, which is 100% compatible with JSONB but uses a shared dictionary of strings most frequently used in given JSON

Index-only scan and random_page_cost

Hi hackers, Right now cost of index-only scan is using `random_page_cost`. Certainly for point selects we really have random access pattern, but queries like "select count(*) from hits"  access pattern is more or less sequential: we are iterating through subsequent leaf B-Tree pages.  As far as

Lack of PageSetLSN in heap_xlog_visible

Hi hackers! heap_xlog_visible is not bumping heap page LSN when setting all-visible flag in it. There is long comment explaining it:    /*     * We don't bump the LSN of the heap page when setting the visibility     * map bit (unless checksums or wal_hint_bits is enabled, in w

Re: SLRUs in the main buffer pool, redux

On 28.05.2022 04:13, Thomas Munro wrote: On Fri, May 27, 2022 at 11:24 PM Thomas Munro wrote: Rebased, debugged and fleshed out a tiny bit more, but still with plenty of TODO notes and questions. I will talk about this idea at PGCon, so I figured it'd help to have a patch that actually appli

Re: Flush pgstats file during checkpoints

On 18/06/2024 9:01 am, Michael Paquier wrote: Hi all, On HEAD, xlog.c has the following comment, which has been on my own TODO list for a couple of weeks now: * TODO: With a bit of extra work we could just start with a pgstat file * associated with the checkpoint redo location we'

Re: 回复: An implementation of multi-key sort

On 04/07/2024 3:45 pm, Yao Wang wrote: Generally, the benefit of mksort is mainly from duplicated values and sort keys: the more duplicated values and sort keys are, the bigger benefit it gets. ... 1. Use distinct stats info of table to enable mksort It's kind of heuristics: in optimizer, ch

One more problem with JIT

Hi hackers, I am using pg_embedding extension for Postgres which implements HNSW index (some kind of ANN search). Search query looks something like this:     SELECT _id FROM documents ORDER BY openai <=> ARRAY[0.024466066, -0.00042, -0.0012917554,... , -0.008700027] LIMIT 1; I do not p

Sync scan & regression tests

Hi hackers, Is it is ok, that regression tests do not pass with small value of shared buffers (for example 1Mb)? Two tests are failed because of sync scan - this tests cluster.sql and portals.sql perform seqscan without explicit order by and expect that data will be returned in particular or

Re: Support prepared statement invalidation when result types change

On 25.08.2023 8:57 PM, Jelte Fennema wrote: The cached plan for a prepared statements can get invalidated when DDL changes the tables used in the query, or when search_path changes. When this happens the prepared statement can still be executed, but it will be replanned in the new context. Thi

Re: Let's make PostgreSQL multi-threaded

On 06.06.2023 12:07 AM, Jonah H. Harris wrote: On Mon, Jun 5, 2023 at 8:18 AM Tom Lane wrote: For the record, I think this will be a disaster.  There is far too much code that will get broken, largely silently, and much of it is not under our control. While I've long been in

Re: Let's make PostgreSQL multi-threaded

On 06.06.2023 5:13 PM, Robert Haas wrote: On Tue, Jun 6, 2023 at 9:40 AM Robert Haas wrote: I'm not sure that there's a strong consensus, but I do think it's a good idea. Let me elaborate on this a bit. Not all databases have this problem, and PostgreSQL isn't going to be able to stop ha

Re: Named Prepared statement problems and possible solutions

On 07.06.2023 10:48 PM, Dave Cramer wrote: Greetings, At pgcon last week I was speaking to some people about the problem we have with connection pools and named prepared statements. For context pgjdbc (and others) use un-named statements and then switch to named statements after using the

Re: Named Prepared statement problems and possible solutions

On 08.06.2023 3:43 PM, Jan Wieck wrote: On 6/8/23 02:15, Konstantin Knizhnik wrote: There is a PR with support of prepared statement support to pgbouncer: https://github.com/pgbouncer/pgbouncer/pull/845 any feedback, reviews and suggestions are welcome. I was about to say that the support

Re: Let's make PostgreSQL multi-threaded

On 07.06.2023 3:53 PM, Robert Haas wrote: I think I remember a previous conversation with Andres where he opined that thread-local variables are "really expensive" (and I apologize in advance if I'm mis-remembering this). Now, Andres is not a man who accepts a tax on performance of any size wi

Re: Named Prepared statement problems and possible solutions

On 08.06.2023 6:18 PM, Dave Cramer wrote: On Thu, 8 Jun 2023 at 11:15, Jan Wieck wrote: On 6/8/23 10:56, Dave Cramer wrote: > > > > > On Thu, 8 Jun 2023 at 10:31, Jan Wieck > wrote: > >     On 6/8/23 09:53, Jan Wieck wrote: >     

Re: Let's make PostgreSQL multi-threaded

On 12.06.2023 3:23 PM, Pavel Borisov wrote: Is the following true or not? 1. If we switch processes to threads but leave the amount of session local variables unchanged, there would be hardly any performance gain. 2. If we move some backend's local variables into shared memory then the perfor

Re: Let's make PostgreSQL multi-threaded

On 13.06.2023 10:55 AM, Kyotaro Horiguchi wrote: At Tue, 13 Jun 2023 09:55:36 +0300, Konstantin Knizhnik wrote in Postgres backend is "thick" not because of large number of local variables. It is because of local caches: catalog cache, relation cache, prepared statements cache,.

Re: Let's make PostgreSQL multi-threaded

On 13.06.2023 11:46 AM, Kyotaro Horiguchi wrote: So we can assume that catalog  and relation cache should always fit in memory memory (otherwise significant rewriting of all Postgtres code working with relations will be needed). I'm not sure that is ture.. But likely to be? Sorry, looks li

Re: Let's make PostgreSQL multi-threaded

On 15.06.2023 1:23 AM, James Addison wrote: On Tue, 13 Jun 2023 at 07:55, Konstantin Knizhnik wrote: On 12.06.2023 3:23 PM, Pavel Borisov wrote: Is the following true or not? 1. If we switch processes to threads but leave the amount of session local variables unchanged, there would be

Re: Bypassing shared_buffers

On 15.06.2023 4:37 AM, Vladimir Churyukin wrote: Ok, got it, thanks. Is there any alternative approach to measuring the performance as if the cache was empty? The goal is basically to calculate the max possible I/O time for a query, to get a range between min and max timing. It's ok if it's

Re: Let's make PostgreSQL multi-threaded

On 15.06.2023 11:41 AM, James Addison wrote: On Thu, 15 Jun 2023 at 08:12, Konstantin Knizhnik wrote: On 15.06.2023 1:23 AM, James Addison wrote: On Tue, 13 Jun 2023 at 07:55, Konstantin Knizhnik wrote: On 12.06.2023 3:23 PM, Pavel Borisov wrote: Is the following true or not? 1. If

Re: Let's make PostgreSQL multi-threaded

On 15.06.2023 12:04 PM, Hannu Krosing wrote: So a fair bit of work but also a clearly defined benefits of 1) reduced memory usage 2) no need to rebuild caches for each new connection 3) no need to track PREPARE statements inside connection poolers. Shared plan cache (not only prepared statem

Index range search optimization

Hi hackers. _bt_readpage performs key check for each item on the page trying to locate upper boundary. While comparison of simple integer keys are very fast, comparison of long strings can be quite expensive. We can first make check for the largest key on the page and if it is not larger than

asynchronous commit&synchronous replication

Hi hackers, Logical replication apply worker by default switches off asynchronous commit. Cite from documentation of subscription parameters: ``` |synchronous_commit|(|enum|)

Re: Threading in BGWorkers (!)

On 23.06.2020 06:38, Tom Lane wrote: James Sewell writes: I was talking about PostgreSQL and threading on IRC the other day - which I know is a frowned upon topic - and just wanted to frame the same questions here and hopefully get a discussion going. I think the short answer about threadin

Re: Threading in BGWorkers (!)

On 23.06.2020 10:15, James Sewell wrote: Using multithreading in bgworker is possible if you do not use any Postgres runtime inside thread procedures or do it in exclusive critical section. It is not so convenient but possible. The most difficult thing from my point

Re: Built-in connection pooler

On 01.07.2020 12:30, Daniel Gustafsson wrote: On 24 Mar 2020, at 17:24, Konstantin Knizhnik wrote: Rebased version of the patch is attached. And this patch also fails to apply now, can you please submit a new version? Marking the entry as Waiting on Author in the meantime. cheers ./daniel

Re: Built-in connection pooler

On 02.07.2020 17:44, Daniel Gustafsson wrote: On 2 Jul 2020, at 13:33, Konstantin Knizhnik wrote: On 01.07.2020 12:30, Daniel Gustafsson wrote: On 24 Mar 2020, at 17:24, Konstantin Knizhnik wrote: Rebased version of the patch is attached. And this patch also fails to apply now, can you

Re: Persist MVCC forever - retain history

On 02.07.2020 21:55, Mitar wrote: Hi! (Sorry if this was already discussed, it looks pretty obvious, but I could not find anything.) I was thinking and reading about how to design the schema to keep records of all changes which happen to the table, at row granularity, when I realized that al

Re: Built-in connection pooler

Thank your for your help. On 05.07.2020 07:17, Jaime Casanova wrote: You should also allow cursors without the WITH HOLD option or there is something i'm missing? Yes, good point. a few questions about tainted backends: - why the use of check_primary_key() and check_foreign_key() in contrib/

Re: Persist MVCC forever - retain history

On 05.07.2020 08:48, Mitar wrote: Hi! On Fri, Jul 3, 2020 at 12:29 AM Konstantin Knizhnik wrote: Did you read this thread: https://www.postgresql.org/message-id/flat/78aadf6b-86d4-21b9-9c2a-51f1efb8a499%40postgrespro.ru I have proposed a patch for supporting time travel (AS OF) queries

Postgres is not able to handle more than 4k tables!?

I want to explain one bad situation we have encountered with one of our customers. There are ~5000 tables in their database. And what is worse - most of them are actively used. Then several flaws of Postgres make their system almost stuck. Autovacuum is periodically processing all this 5k relat

Re: Postgres is not able to handle more than 4k tables!?

On 09.07.2020 03:49, tsunakawa.ta...@fujitsu.com wrote: From: Konstantin Knizhnik Autovacuum is periodically processing all this 5k relations (because them are actively updated). And as far as most of this tables are small enough autovacuum complete processing of them almost in the same

Re: Postgres is not able to handle more than 4k tables!?

On 09.07.2020 00:35, Tom Lane wrote: Konstantin Knizhnik writes: There are several thousand clients, most of which are executing complex queries. So, that's really the core of your problem. We don't promise that you can run several thousand backends at once. Usually it's r

Re: Postgres is not able to handle more than 4k tables!?

Hi Stephen, Thank you for supporting an opinion that it is the problems not only of client system design (I agree it is not so good idea to have thousands tables and thousands active backends) but also of Postgres. We have made more investigation and found out one more problem in Postgres ca

Re: Postgres is not able to handle more than 4k tables!?

On 09.07.2020 18:14, Tom Lane wrote: As I understood the report, it was not "things completely fall over", it was "performance gets bad". But let's get real. Unless the OP has a machine with thousands of CPUs, trying to run this way is counterproductive. Sorry, that I was not clear. It is a

Re: Postgres is not able to handle more than 4k tables!?

On 09.07.2020 19:19, Nikolay Samokhvalov wrote: Hi Konstantin, a silly question: do you consider the workload you have as well-optimized? Can it be optimized further? Reading this thread I have a strong feeling that a very basic set of regular optimization actions is missing here (or not exp

Re: Postgres is not able to handle more than 4k tables!?

On 09.07.2020 22:16, Grigory Smolkin wrote: On 7/8/20 11:41 PM, Konstantin Knizhnik wrote: So looks like NUM_LOCK_PARTITIONS and MAXNUMMESSAGES  constants have to be replaced with GUCs. To avoid division, we can specify log2 of this values, so shift can be used instead. And

Re: Postgres is not able to handle more than 4k tables!?

On 15.07.2020 02:17, Alvaro Herrera wrote: On 2020-Jul-10, Konstantin Knizhnik wrote: @@ -3076,6 +3080,10 @@ relation_needs_vacanalyze(Oid relid, instuples = tabentry->inserts_since_vacuum; anltuples = tabentry->changes_since_analyze;

<    1   2   3   4   5   6   7   8   >