Re: log_statement vs log_min_duration_statement

On Tue, Sep 26, 2023 at 5:33 PM Atul Kumar wrote: > Hi, > > I have a query about parameters and log_statement > > my postgres version is 12 and running on centos 7 > > my log_statement is set to "DDL". > > and log_min_duration_statement is set to "1ms" > > so technically it should log "ONLY

Re: Question regarding specifics of GIN and pg_trgm performance and potential use of show_trgm to improve it

On Wed, May 24, 2023 at 4:35 PM Pavel Horal wrote: I didn't see your email when first sent, and stumbled upon it while searching for something else. But it still might be worthwhile commenting even after all of this time. > > *Is my understanding correct that this happens only because pg_trgm

Re: Helping planner to chose sequential scan when it improves performance

On Sun, Jun 25, 2023 at 3:48 PM David Rowley wrote: > On Wed, 14 Jun 2023 at 07:28, Patrick O'Toole > wrote: > > Maybe we are barking up the wrong tree with the previous questions. Are > there other configuration parameters we should consider first to improve > performance in situations like

Re: Helping planner to chose sequential scan when it improves performance

On Tue, Jun 13, 2023 at 3:28 PM Patrick O'Toole wrote: > run the query twice first, then... Is that a realistic way to run the test? Often forcing all the data needed for the query into memory is going to make things less realistic, not more realistic. Assuming the system has more stuff to

Re: [EXT] Re: Why using a partial index is doing slightly more logical I/O than a normal index

On Wed, May 3, 2023 at 2:00 PM Dirschel, Steve < steve.dirsc...@thomsonreuters.com> wrote: > Thanks for the reply Jeff. Yes- more of an academic question. Regarding > this part: > > > >Index Cond: ((workflow_id = 1070) AND ((status)::text = ANY >

Re: Why using a partial index is doing slightly more logical I/O than a normal index

Because both the actual times and the expected costs are so similar to each other, I am assuming you are asking this as more of an academic question than a practical one. If it is actually a practical matter, you should find a better example to present to us. On Wed, May 3, 2023 at 9:17 AM

Re: unknown postgres ssl error "could not accept SSL connection: Success" and timeout

On Wed, May 3, 2023 at 9:54 AM Sergey Cherevko wrote: > Ubuntu 18.04.6 LTS (GNU/Linux 4.15.0-167-generic x86_64) > > OpenSSL 1.1.1 11 Sep 2018 > > Sometimes i see this in postgres logs > So, your system is working normally most of the time? Or is it working normally all of the time, and you

Re: Can't connect to server

On Sat, Mar 18, 2023 at 2:11 AM Wu, Abigaile wrote: > I am writing to seek your assistance with a problem I am facing while > using pgAdmin4. After downloading the PostgreSQL 15, I downloaded the > latest version of Pgadmin 4 and then I click the server and choose > PostgreSQL 15. Whenever I try

Re: Tuning a query with ORDER BY and LIMIT

On Wed, Jun 22, 2022 at 6:19 PM Peter J. Holzer wrote: > > >That's just how btree indexes work and Oracle will have the same > >limitation. What would be possible is to use an index only scan > >(returning 2,634,718 matching results), sort that to find the 50 newest > >entries and retrieve only

Re: Tuning a query with ORDER BY and LIMIT

On Wed, Jun 22, 2022 at 3:39 PM Dirschel, Steve < steve.dirsc...@thomsonreuters.com> wrote: > explain (analyze, verbose, costs, buffers, timing, summary, hashes) > 'hashes', what's that? Are you using community PostgreSQL, or someones fork? > With Oracle for a query like this since the index

Re: Index creation

On Mon, Jun 20, 2022 at 1:17 AM Дмитрий Иванов wrote: > Your statement seems obvious to me. But what I see doesn't seem like a > conscious choice. It turns out that it is better to have a lighter > general-purpose index than to strive to create a target covering index for > a certain kind of

Re: Why password authentication failed for user "postgres"?

On Fri, Jun 3, 2022 at 6:32 PM BeginnerC wrote: > Hello everyone, > I am a newbie to the postgres,when I use the psql to connect to the > postgres,a error message printed: > These command list like this: > > psql -U postgres > Password for user postgres:postgres > *postgreSQL: password

Re: unoptimized nested loops

On Thu, Jun 2, 2022 at 12:32 AM Tom Lane wrote: > Jeff Janes writes: > > On Tue, May 31, 2022 at 4:04 PM Tim Kelly > wrote: > >> I do not see evidence that the nested loop is trying to reduce overhead > >> by using the smaller set. It seems to want to scan

Re: unoptimized nested loops

On Tue, May 31, 2022 at 4:04 PM Tim Kelly wrote: > > c_db=>select count(id) from metadata where author like '%Kelly%'; > > count > --- >3558 > (1 row) > It would be interesting to see the explain of this. We know how many rows it found, but not how many it thinks it will find. > I

Re: autovacuum on primary blocking queries on replica?

On Fri, May 27, 2022 at 3:01 PM Don Seiler wrote: I've been reading tales of autovacuum taking an AccessExclusiveLock when > truncating empty pages at the end of a table. I'm imagining that updating > every row of a table and then rolling back would leave all of those rows > empty at the end and

Re: JSONB index not in use, but is TOAST the real cause of slow query?

On Sat, May 28, 2022 at 1:54 PM Shaheed Haque wrote: > And how can I understand the dreadful amount of > time (of course, this is just on my dev machine, but still...)? Is > there a way to see/tweak what TOAST costs or indeed to confirm if it > is even in use? > Turn on track_io_timing, and

Re: pg_upgrade from Postgresql-12 to Postgresql-13 fails with "Creating dump of database schemas postgres *failure*"

On Fri, Feb 18, 2022 at 8:35 AM Yoong S. Chow wrote: > > Here are the logs: pg_upgrade_server.log , > pg_upgrade_dump_13427.log and > tail_postgresql-12-2022-02-16_190344.log . > >From that last log

Re: Autovacuum not functioning for large tables but it is working for few other small tables.

On Wed, Dec 16, 2020 at 6:55 AM M Tarkeshwar Rao < m.tarkeshwar@ericsson.com> wrote: > ... > > > All the threshold level requirements for autovacuum was meet and there are > about Million’s of dead tuples but autovacuum was unable to clear them, > which cause performance issue on production

Re: Performance of "distinct with limit"

On Fri, Aug 28, 2020 at 8:34 AM Klaudie Willis < klaudie.wil...@protonmail.com> wrote: > No index on n, no. Index might solve it yes, but it seems to me such a > trivial optimization even without. Obviously it is not. > > QUERY > PLAN > | > >

Re: When to use PARTITION BY HASH?

On Fri, Jun 5, 2020 at 6:12 AM Oleksandr Shulgin < oleksandr.shul...@zalando.de> wrote: > On Thu, Jun 4, 2020 at 4:32 PM Jeff Janes wrote: > >> On Wed, Jun 3, 2020 at 7:55 AM Oleksandr Shulgin < >> oleksandr.shul...@zalando.de> wrote: >> >> With hash par

Re: When to use PARTITION BY HASH?

On Wed, Jun 3, 2020 at 7:55 AM Oleksandr Shulgin < oleksandr.shul...@zalando.de> wrote: With hash partitioning you are not expected, in general, to end up with a > small number of partitions being accessed more heavily than the rest. So > your indexes will also not fit into memory. > > I have

Re: How can I set a timeout for a locked table in Function ?

On Fri, Jan 3, 2020 at 1:05 PM Thomas Kellerer wrote: > Michael Lewis schrieb am 03.01.2020 um 18:00: > > > Why take an exclusive lock on an entire table to update a single row? > > That's what I was asking myself as well. > Note that the code takes "row exclusive", not "exclusive". It is

Re: Changing default ../data/ directory

On Sat, Jan 4, 2020 at 10:20 AM Rich Shepard wrote: > > The PGDATA environment variable used to be used for this (but I never did > get it satisfactorily working). PGDATA should work fine if you always start the server directly. But if you sudo or su to another user, they likely won't inherit

Re: Are my autovacuum settings too aggressive for this table?

> > > > Live tuples = 19,766,480 > Analyze scale factor = 0.001 > Analyze thresh = 5000 > Thresh + live_tuples * factor = 24,766 > > So an autovacuum analyze should trigger around 24K tuples modified, is > this to little or too much? This seems too much to me. Was there a specific problem

Re: pg_basebackup

On Mon, Dec 23, 2019 at 4:13 AM Kyotaro Horiguchi wrote: > Hello. > > At Mon, 23 Dec 2019 03:38:12 +, Daulat Ram > wrote in > > thanks Adrian, what about the > > postmaster.opts file, this file was also skipped in backup. > > The file is overwritten at startup so there's no point in having

Re: slow insert speeds with bytea

On Mon, Dec 2, 2019 at 4:42 AM Alex O'Ree wrote: > Is there anything I can to increase insert speeds for bytea? Currently > running postgres 9.6.15 > > I have a few tables without a bytea and a few with bytea. There is a large > performance difference with inserts between the two. I'm inserting

Re: Trouble incrementing a column

On Sat, Nov 23, 2019 at 4:47 PM Tom Lane wrote: > Note that you pay a fairly substantial performance penalty for deferring > the check, which is why it isn't the default, even though the SQL spec > says it ought to be. > Do you know what the worst case scenario is for the performance of

Re: How to drop all tokens that a snowball dictionary cannot stem?

On Sat, Nov 23, 2019 at 10:42 AM Christoph Gößmann wrote: > Hi Jeff, > > You're right about that point. Let me redefine. I would like to drop all > tokens which neither are the stemmed or unstemmed version of a known word. > Would there be the possibility of putting a wordlist as a filter ahead

Re: How to drop all tokens that a snowball dictionary cannot stem?

On Fri, Nov 22, 2019 at 8:02 AM Christoph Gößmann wrote: > Hi everybody, > > I am trying to get all the lexemes for a text using to_tsvector(). But I > want only words that english_stem -- the integrated snowball dictionary -- > is able to handle to show up in the final tsvector. Since snowball

Re: Weird ranking results with ts_rank

On Fri, Nov 15, 2019 at 1:31 AM Javier Ayres wrote: > Hi everybody. > > I'm implementing a solution that uses PostgreSQL's full text search > capabilities and I have come across a particular set of results for ts_rank > that don't seem to make sense according to the documentation. > While the

Re: PostGreSQL Replication and question on maintenance

On Thu, Nov 14, 2019 at 12:23 PM github kran wrote: > >> >> *Problem what we have right now. * >> >> When the migration activity runs(weekly) from past 2 times , we saw the >> cluster read replica instance has restarted as it fallen behind the >> master(writer instance). >> > I can't figure out

Re: Recovering disk space

On Thu, Oct 10, 2019 at 3:57 AM stan wrote: > However, my 50G disk is still 96% full. How can I recover the disk space I > seem to have used u doing this? > The bloated storage is likely not under PostgreSQL's control. Use the tools provided by your OS to figure out what is using the space.

Re: Regarding db dump with Fc taking very long time to completion

On Fri, Aug 30, 2019 at 5:51 AM Durgamahesh Manne wrote: > Hi > To respected international postgresql team > > I am using postgresql 11.4 version > I have scheduled logical dump job which runs daily one time at db level > There was one table that has write intensive activity for every 40 seconds

Re: Too many SET TimeZone and Application_name queries

On Fri, Oct 11, 2019 at 7:49 AM Amarendra Konda wrote: > Hi, > > In our test environment, it was observed that there are too many queries > were getting fired to the database server, > What does "too many" mean here? Is it just more than you like to see in your log file, or is there some

Re: Execute a function through fdw

On Fri, Oct 18, 2019 at 7:55 AM Tom Lane wrote: > Guillaume Lelarge writes: > > Le ven. 18 oct. 2019 à 11:51, Patrick FICHE > a > > écrit : > >> Is it possible to execute a function located on a server accessed > through > >> Postgres fdw. > > > It's probably easier to create a view on the

Re: Postgres Point in time Recovery (PITR),

On Fri, Oct 18, 2019 at 1:59 AM Daulat Ram wrote: > Hello All, > > Can you please share some ideas and scenarios how we can do the PITR in > case of disaster. > It depends on what you mean by "disaster". Usually I think that would mean your server (or entire data center) was destroyed. In

Re: Matching pgp_sym_encrypt() and gpg2 output

On Tue, Aug 27, 2019 at 1:33 PM Bruce Momjian wrote: > I am trying to generate output from the command-line program gpg2 that > matches the output of pgp_sym_encrypt(). gpg2 outputs: > > $ echo 'my access password' | tr -d '\n' | gpg2 --symmetric --batch > > --cipher-algo AES256

Re: [ext] Re: Pointers towards identifying bulk import bottleneck (walwriter tuning?)

On Tue, Aug 27, 2019 at 10:43 AM Holtgrewe, Manuel < manuel.holtgr...@bihealth.de> wrote: > Hi, > > I also tried creating the table as "UNLOGGED" which led to walwriter I/O > to drop drastically and I now get no wall-clock time increase with two > import processes but it gets slower with four. >

Re: Having difficulties partitionning with jsonb

On Thu, Aug 22, 2019 at 5:41 PM ouellet marc-andre < ouellet_marcan...@hotmail.com> wrote: > CREATE TABLE test ( id integer, data jsonb ) Partition by range (( data > #>> '{info,time}' )); > > CREATE TABLE test_part1 PARTITION OF test > FOR VALUES FROM ('3') TO ('4'); > > INSERT INTO

Re: A question aboout postgresql-server-dev versions

On Sun, Aug 25, 2019 at 8:34 AM stan wrote: > I am using Postgresql version 11 on Ubuntu 18.04. I am considering using an > extension called libphonenumber. It needs Postgresql-server-dev to build. I > found postgresql-server-dev-all in the list of available packages, BUT when > I ask apt-get to

Re: Databases and servers

On Tue, Aug 20, 2019 at 6:33 AM Karl Martin Skoldebrand < ks0c77...@techmahindra.com> wrote: > Hi, > > I just discovered that a client has done this: > > They have two web applications A1 and A2. They have seperate > hostnames/URLs. Both have a production and a test database A1p and A1t/ A2p >

Re: psql UPDATE field [tab] expands to DEFAULT?

On Mon, Jun 17, 2019 at 8:23 PM Adrian Klaver wrote: > On 6/17/19 4:33 PM, Ken Tanzer wrote: > > > > Thanks Adrian, though I wasn't really seeking tips for column names. I > > was instead trying to understand whether this particular tab expansion > > was intentional and considered useful, and

Re: psql UPDATE field [tab] expands to DEFAULT?

On Mon, Jun 17, 2019 at 6:03 PM Ken Tanzer wrote: > Hi. If I'm using psql, and type for example: > > UPDATE my_table SET my_field > (with a trailing space) > > and then hit Tab, it will expand that to an =, and then another tab will > expand to DEFAULT, so that I then have: > > UPDATE my_table

Re: perf tuning for 28 cores and 252GB RAM

On Mon, Jun 17, 2019 at 4:51 PM Michael Curry wrote: > I am using a Postgres instance in an HPC cluster, where they have > generously given me an entire node. This means I have 28 cores and 252GB > RAM. I have to assume that the very conservative default settings for > things like buffers and

Re: Questions about btree_gin vs btree_gist for low cardinality columns

On Sun, Jun 2, 2019 at 7:07 PM Tom Lane wrote: > Jeff Janes writes: > > On Fri, May 24, 2019 at 11:26 AM Jeremy Finzel > wrote: > >> I have been hoping for clearer direction from the community about > >> specifically btree_gin indexes for low cardina

Re: Questions about btree_gin vs btree_gist for low cardinality columns

On Fri, May 24, 2019 at 11:26 AM Jeremy Finzel wrote: > I have been hoping for clearer direction from the community about > specifically btree_gin indexes for low cardinality columns (as well as low > cardinality multi-column indexes). In general there is very little > discussion about this

Re: Connecting to NOTIFY with telnet

On Sat, May 4, 2019 at 3:04 PM Igal Sapir wrote: > Jeff, > > On Sat, May 4, 2019 at 11:34 AM Jeff Janes wrote: > >> On Sat, May 4, 2019 at 1:49 PM Igal Sapir wrote: >> >>> Christoph, >>> >>> On Sat, May 4, 2019 at 10:44 AM Christ

Re: Connecting to NOTIFY with telnet

On Sat, May 4, 2019 at 1:49 PM Igal Sapir wrote: > Christoph, > > On Sat, May 4, 2019 at 10:44 AM Christoph Moench-Tegeder < > c...@burggraben.net> wrote: > >> ## Igal Sapir (i...@lucee.org): >> >> > My main "issue" is that the official pgjdbc driver does not support the >> > notifications with

Re: Starting Postgres when there is no disk space

On Wed, May 1, 2019 at 10:25 PM Igal Sapir wrote: > > I have a scheduled process that runs daily to delete old data and do full > vacuum. Not sure why this happened (again). > If you are doing a regularly scheduled "vacuum full", you are almost certainly doing something wrong. Are these

Re: Forcing index usage

On Wed, Apr 3, 2019 at 12:13 PM Jonathan Marks wrote: Is there a way to tell Postgres “please don’t use index X when queries that > could use index Y instead occur?” > Late to the party here, but... Not directly. I've had luck in changing the procost of functions (or the functions which back

Re: shared_buffers on Big RAM systems

On Thu, Dec 13, 2018 at 11:51 PM Thomas Munro wrote: > On Fri, Dec 14, 2018 at 2:17 AM Ron wrote: > > https://www.postgresql.org/docs/9.6/runtime-config-resource.html > > > > The docs say, "If you have a dedicated database server with 1GB or more > of > > RAM, a reasonable starting value for

Re: When do vacuumed pages/tuples become available for reuse?

On Thu, Apr 11, 2019 at 1:48 PM Jeff Janes wrote: > If we just want to do a slight re-wording, I don't know what it would need > to look like. "remain" includes live, recently dead, and uncommitted new, > and uncommitted old (I think) so we can't just change "recent&qu

Re: When do vacuumed pages/tuples become available for reuse?

On Thu, Apr 11, 2019 at 12:18 PM rihad wrote: > On 04/11/2019 08:09 PM, Jeff Janes wrote: > > On Thu, Apr 11, 2019 at 11:44 AM rihad wrote: > >> >> Since we dump production DB daily into staging environment, the >> difference in size (as reported by psql's \l+) is

Re: When do vacuumed pages/tuples become available for reuse?

On Thu, Apr 11, 2019 at 11:44 AM rihad wrote: > On 04/11/2019 07:40 PM, Jeff Janes wrote: > > > The disk usage doesn't reach a steady state after one or two autovacs? Or > it does, but you are just unhappy about the ratio between the steady state > size and the theoretical

Re: When do vacuumed pages/tuples become available for reuse?

On Thu, Apr 11, 2019 at 11:14 AM rihad wrote: > autovacuum_vacuum_scale_factor = 0.01 > autovacuum_vacuum_threshold = 50 This seems counterproductive. You need to make the vacuum more efficient, not more frantic. > autovacuum_vacuum_cost_delay = 10ms > autovacuum_vacuum_cost_limit = 400 >

Re: When do vacuumed pages/tuples become available for reuse?

On Thu, Apr 11, 2019 at 10:28 AM rihad wrote: > > Yup, it's just that n_dead_tuples grows by several hundred thousand (the > table sees much much more updates than inserts) and disk usage grows > constantly between several hour long vacuum runs. Running vacuum full > isn't an option. > The disk

Re: CVE-2019-9193 about COPY FROM/TO PROGRAM

On Tue, Apr 2, 2019 at 11:31 AM Andres Freund wrote: > Hi, > > On 2019-04-02 07:35:02 -0500, Brad Nicholson wrote: > > > A blog post would be nice, but it seems to me have something about this > > clearly in the manual would be best, assuming it's not there already. I > > took a quick look, and

Re: Forks of pgadmin3?

On Fri, Mar 22, 2019 at 8:04 AM Steve Atkins wrote: > > > > On Mar 22, 2019, at 10:56 AM, Christian Henz > wrote: > > > > There's the BigSQL fork, which had at least some minimal support > for 10. I've no idea whether it's had / needs anything for 11 I just installed BigSQL's v11 of the

Re: WAL Archive Cleanup?

On Fri, Mar 22, 2019 at 6:28 AM Foo Bar wrote: > > Fast forward two weeks, this cluster has been running but not seeing any > traffic. And my master server has filled its archive directory. > Are you sure it is the archive directory (/hab/svc/postgresql/data/archive) which is filling up, and

Re: Optimizing Database High CPU

On Wed, Feb 27, 2019 at 5:01 PM Michael Lewis wrote: > If those 50-100 connections are all active at once, yes, that is high. >> They can easily spend more time fighting each other over LWLocks, >> spinlocks, or cachelines rather than doing useful work. This can be >> exacerbated when you have

Re: FDW and remote cursor_tuple_fraction

On Sun, Mar 3, 2019 at 12:38 PM auxsvr wrote: > Hi, > > One reason the remote plans via FDW are different than the plans of > queries running directly on the remote server is that a cursor is used, > which is optimized for low number of rows according to > cursor_tuple_fraction. Is there any way

Re: Optimizing Database High CPU

On Wed, Feb 27, 2019 at 2:07 PM Scottix wrote: > Hi we are running a Postgresql Database 9.4.18 and we are noticing a > high CPU usage. Nothing is critical at the moment but if we were to > scale up more of what we are doing, I feel we are going to run into > issues. > 9.4 is old. A lot of

Re: Future Non-server Windows support???

On Sun, Feb 24, 2019 at 3:10 PM Adrian Klaver wrote: > On 2/24/19 10:38 AM, Jeff Janes wrote: > > On Sun, Feb 24, 2019 at 7:06 AM Bill Haught > <mailto:wlhaught4754...@att.net>> wrote: > > > > I noticed that for 11.2, non-server versions of Windows are

Re: Future Non-server Windows support???

On Sun, Feb 24, 2019 at 7:06 AM Bill Haught wrote: > I noticed that for 11.2, non-server versions of Windows are not listed > as supported platforms. Listed where? The only thing I see is "Windows (Win2000 SP4 and later)", and that isn't a list and surely there are non-server versions of

Re: [External] Re: FDW, too long to run explain

On Sun, Feb 17, 2019 at 6:32 AM Vijaykumar Jain wrote: > I am yet to figure out the reason, what we have done is implement fake > columns to represent samples and giving them random numbers and keeping > other bulls to fake limit. > > Most of the queries that were impacted were the ones that did

Re: [External] Re: FDW, too long to run explain

On Sun, Feb 17, 2019 at 2:37 PM Vijaykumar Jain wrote: > > Ok, i raked this from the logs where enabled log_min_duration_statement = > 10s > > 2019-01-31 12:48:18 UTC LOG: duration: 29863.311 ms statement: EXPLAIN > SELECT blah, FROM public.view WHERE ((scheduled_bdt >= '2019-01-20'::date)) >

Re: [External] Re: FDW, too long to run explain

On Sun, Feb 17, 2019 at 1:52 PM Vijaykumar Jain wrote: > Assuming your questions as 1,2,3, please find my answers below. > > 1)"explain" on foreign servers run as "idle in transactions". coz they > were running very long (in the order of some minutes) , pgbouncer (in tx > level pooling) setting

Re: FDW, too long to run explain

On Mon, Feb 4, 2019 at 2:15 AM Vijaykumar Jain wrote: > > now we have some long running queries via FDW that take minutes and get > killed explain runs as idle in transaction on remote servers. > Are you saying the EXPLAIN itself gets killed, or execution of the plan generated based on the

Re: (multiplatform) replication problem

On Tue, Jan 8, 2019 at 10:49 AM W.P. wrote: > Hi there, > I have following setup: > - master database, 9.5.7, on I386 (Pentium M), > now i want to replicate database to: > - slave database. 9.5.7 on armhf (OrangePiPC+). > Is in possible? > I think the error message is telling you that physical

Re: getting pg_basebackup to use remote destination

On Sun, Dec 30, 2018 at 6:17 PM Chuck Martin wrote: > Maybe I need to rethink ths and take Jeff's advice. I executed this: > > pg_basebackup -h [main server's URL] -U postgres -P -v -X s -D > /mnt/dbraid/data > > 8 hours ago, and it is now still at 1%. Should it be that slow? The > database in

pg_stat_replication view

I'm not sure if this is a documentation issue, or something else. The description of the pg_stat_replication.state column gives: * catchup: This WAL sender's connected standby is catching up with the primary. * streaming: This WAL sender is streaming changes after its connected standby server

Re: Is there a way to speed up WAL replay?

On Wed, Oct 31, 2018 at 1:38 AM Torsten Förtsch wrote: > Hi, > > I am working on restoring a database from a base backup + WAL. With the > default settings the database replays about 3-4 WAL files per second. The > startup process takes about 65% of a CPU and writes data with something > between

Re: GIN Index for low cardinality

On Fri, Oct 26, 2018 at 12:27 PM Jeff Janes wrote: Here is a real-world example from one of my databases where each value is > about 17 characters long, and is present about 20 times: > > gin: 411 MB > btree: 2167 MB > hash: 2159 MB > For what it is worth, that was 9.6 wi

Re: GIN Index for low cardinality

On Thu, Oct 25, 2018 at 9:36 AM Олег Самойлов wrote: > > 17 окт. 2018 г., в 13:46, Ravi Krishna написал(а): > > In > https://www.cybertec-postgresql.com/en/ideas-for-scaling-postgresql-to-multi-terabyte-and-beyond/ > > it is mentioned: > > "GIN, the most know non-default index type perhaps, has

Re: GIN Index for low cardinality

On Wed, Oct 17, 2018 at 6:47 AM Ravi Krishna wrote: > In > https://www.cybertec-postgresql.com/en/ideas-for-scaling-postgresql-to-multi-terabyte-and-beyond/ > > it is mentioned: > > "GIN, the most know non-default index type perhaps, has been actually > around for ages (full-text search) and in

Re: Different memory allocation strategy in Postgres 11?

On Fri, Oct 26, 2018 at 9:12 AM Thomas Kellerer wrote: > I have a Postgres instance running on my Windows laptop for testing > purposes. > > I typically configure "shared_buffers = 4096MB" on my 16GB system as > sometimes when testing, it pays off to have a bigger cache. > > With Postgres 10 and

Re: Replication question

On Mon, Oct 22, 2018 at 1:20 PM Scot Kreienkamp < scot.kreienk...@la-z-boy.com> wrote: > I remember thinking it was pulling from archive with the restore command > if necessary to augment what it had on disk. If that was the case I wanted > to configure it. I don’t care for the replication

Re: Replication question

On Mon, Oct 22, 2018, 9:54 AM Scot Kreienkamp wrote: > Hi everyone, > > > > We just moved to PG9.6 from 9.1 (yeah, not my choice to wait this long). > In 9.1 I had to make the archive location (NFS in my case) available to all > the mirrors running PG so that they could catch up whenever they

Re: pg_dump backup utility is taking more time around 24hrs to take the backup of 28GB

On Thu, Oct 18, 2018 at 8:26 AM Raghavendra Rao J S V < raghavendra...@gmail.com> wrote: > Hi All, > > We are using *pg_dump *backup utility in order to take the backup of the > database. Unfortunately,it is taking around 24hrs of time to take the > backup of 28GB database. Please guide me how

Re: Advice on logging strategy

On Thu, Oct 11, 2018 at 6:27 AM Mike Martin wrote: > I have a question on logging strategy > > I have loggin set to > log_statement = 'all' on a network database with logging set to csv so I > can import it to a logging table > > However the database is populated via a nightly routine

Re: Dealing with latency to replication slave; what to do?

Please don't top-post, it is not the custom on this list. On Tue, Jul 24, 2018 at 4:08 PM, Rory Falloon wrote: > On Tue, Jul 24, 2018 at 4:02 PM Andres Freund wrote: > Hi, >> >> On 2018-07-24 15:39:32 -0400, Rory Falloon wrote: >> > Looking for any tips here on how to best maintain a

Re: Slow WAL recovery for DROP TABLE

There was a recent commit for a similar performance problem, which will appear in 9.6.10. But that was specifically for cases where there were multiple dropped tables per transaction, and large shared_buffers. I can't reproduce your single-drop-per-transaction problem. The replica has no

Re: About "Cost-based Vacuum Delay"

On Wed, Jun 27, 2018 at 3:19 AM, Laurenz Albe wrote: > Ilyeop Yi wrote: > > Currently, I am working with a workload that is mostly insert and > update, and its performance suffers from autovacuum. > Do you know what about the autovacuum causes the performance drop? Is it the reading, the

Re: DB size growing exponentially when materialized view refreshed concurrently (postgres 9.6)

On Mon, Jun 25, 2018 at 1:21 PM, Vikas Sharma wrote: > Hi All, > > I am looking for advice in a issue where two materialized views are being > refreshed concurrently and dbsize has grown to 150gb from 4gb in two days. > > We use two materialized views to keep processed data for faster query >

Re: Long running DDL statements blocking all queries

On Thu, May 31, 2018 at 9:19 AM, Ashu Pachauri wrote: > There was too much noise in the pg_stat_activity output, so I did not post > it. I'll collect the output again and post. > > But, when I checked in pg_stat_activity, PID 18317 is the session that's > running the ALTER statement and it was

Re: LDAP authentication slow

On Thu, May 31, 2018 at 8:23 AM, C GG wrote: In the meantime, I did what I promised Adrian Klaver I would do and I added > the AD servers to the /etc/hosts file. That had an immediate and dramatic > effect on the performance. That confirms (at least to me) that DNS > resolution was playing a

Re: Login with LDAP authentication takes 5 seconds

On Mon, May 28, 2018 at 10:26 AM, Andreas Schmid wrote: > Hi, > > I configured my PostgreSQL 10 DB on Debian 9.2 with LDAP authentication > (simple bind mode). While this basically works, it has the strange effect > that the first login with psql takes around 5 seconds. When I reconnect > within

Re: Fast logical replication jump start with PG 10

On Fri, May 25, 2018 at 5:12 PM, Olivier Gautherot wrote: Is there a way to speed up the replication or should I rather stick to > streaming replication? As I have only 1 database on the server, it would > not be a show-stopper. > You have a method that works, and a

Re: Sum of written buffers bigger than allocation?

On Sun, Apr 8, 2018 at 11:28 AM, pinker wrote: > > > It's kinda mysterious... Any explanations? > Does it mean that the same buffers were written over and over again? > > Yeah, checkpoints will write all dirty buffers, but doesn't evict them. Next time the page is needed, it

Re: shared_buffers 8GB maximum

On Fri, Feb 16, 2018 at 2:36 PM, Vitaliy Garnashevich < vgarnashev...@gmail.com> wrote: > Hi All, > > I've seen the shared_buffers 8GB maximum recommendation repeated many > times. I have several questions in this regard. > > - Is this recommendation still true for recent versions of postgres?

Re: PITR Multiple recoveries

On Thu, Feb 8, 2018 at 5:07 AM, Sébastien Boutté wrote: > Hi all, > > I'm trying to make my server doing PITR backup, i follow the rules on > https://www.postgresql.org/docs/9.5/static/continuous-archiving.html. > > On my local server, i would like to resync multiple

Re: Please help me understand unlogged tables

On Wed, Jan 31, 2018 at 8:52 AM, Alexander Stoddard < alexander.stodd...@gmail.com> wrote: > If a table is set to unlogged is it inherently non-durable? That, is any > crash or unsafe shutdown _must_ result in truncation upon recovery? > Yes. > I can imagine a table that is bulk loaded in a

Re: Using random() in update produces same random value for all

On Mon, Jan 22, 2018 at 9:16 AM, Tom Lane wrote: > Olleg Samoylov writes: > > Looked like random() is "volatile", but in subselect it works like > "stable". > > The point here is that that's an uncorrelated subselect --- ie, it > contains no outer references

Re: Deadlock between concurrent index builds on different tables

On Tue, Dec 26, 2017 at 10:03 PM, Jeremy Finzel wrote: > > > Many thanks for the great and simple explanation. > > I was able to get this compiled, and ran the test before on stock 9.6.6, > then on this patched version. I indeed reproduced it on 9.6.6, but on the > patched

Re: Select for update / deadlock possibility?

On Tue, Jan 2, 2018 at 3:22 AM, Durumdara wrote: > Dear Members! > > I have to ask something that not clear for me from description, and I > can't simulate it. > > Is "select for update" atomic (as transactions) or it isn't? > > I want to avoid the deadlocks. > > If it's

Re: Use of Port 5433 with Postgresql 9.6

On Mon, Jan 1, 2018 at 12:42 PM, Graeme wrote: > If the default port for v9.6 is 5433, why does the utility pg_isready > still default to searching for 5432? The Ubuntu packages use 5433 if you already have something (either a different packaged version, or an unpackaged

Re: Deadlock between concurrent index builds on different tables

On Fri, Dec 22, 2017 at 1:53 PM, Jeremy Finzel wrote: > I am attempting to build several indexes in parallel, guaranteeing that I > never build one on the same table twice. I understand I can't build two on > the same table at once or I will get a deadlock. However, I am

Re: clean out ./data/base/pgsql_tmp

On Wed, Dec 6, 2017 at 8:18 PM, Dylan Luong wrote: > Hi > > > > We have an issue where one of the developers ran a large query that hung > was filling up the DATA drive very rapidly. From 50% usage to 95% in less > than 2hrs. > > It created a very large pgsql_tmp size

Re: transaction wrap around

On Mon, Dec 4, 2017 at 5:52 PM, John R Pierce wrote: > On 12/4/2017 2:21 PM, chris kim wrote: > >> >> >> How would I investigate if my database is nearing a transaction wrap >> around. >> > > > it would be screaming bloody murder in the log, for one. > > Unfortunately, that

Re: Warm standby can't start because logs stream too quickly from the master

On Sat, Dec 2, 2017 at 11:02 AM, Zach Walton wrote: > Looking at the startup process: > > postgres 16749 4.1 6.7 17855104 8914544 ?Ss 18:36 0:44 postgres: > startup process recovering 00085B1C0030 > > Then a few seconds later: > > postgres 16749 4.2

  1   2   >