Re: DB Server slow down & hang during Peak hours of Usage

tgres 12: https://www.postgresql.org/docs/12/runtime-config-autovacuum.html Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Aug 8, 2023 at 1:59 PM Marc Millas wrote: > Hello, > in the postgresql.conf joined, 2 things (at least) look strange: > 1) the values for b

pb with big volumes

omplete data model and the exact requests can be considered mandatory, but when I change the request I get the very same behaviour... Marc MILLAS Senior Architect +33607850334 www.mokadb.com

Re: pb with big volumes

s persistent volumes. > > What is the pipe connecting the database server to the disks? If it's > NAS, well that would explain it. > > HTH, > Adam > > > > On Thu, Aug 10, 2023 at 2:37 PM Marc Millas > wrote: > >> Hi, >> >> I have a 15 TB db

Re: pb with big volumes

Hi David, that's exactly my question. does the analyze buffers data, generated when track_io_timing is on, keep track of multiple reloads of the same data while executing one operation ? I ll do the test asap and report the results. Marc MILLAS Senior Architect +33607850334 www.mokad

shared buffers

ifferently, what could be advantages/disadvantages of such a setup ? thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com

event trigger clarification

s when the line is executed or at commit time. thanks. Marc MILLAS Senior Architect +33607850334 www.mokadb.com

Re: event trigger clarification

Thanks ! Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Wed, Aug 30, 2023 at 8:44 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, August 30, 2023, Marc Millas wrote: > >> Hi, >> the doc v15 states: " pg_event_trigger_ddl_c

Re: event trigger clarification

Thanks ! Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Wed, Aug 30, 2023 at 6:18 PM Erik Wienhold wrote: > > On 30/08/2023 17:12 CEST Marc Millas wrote: > > > > the doc v15 states: " pg_event_trigger_ddl_commands returns a list of DDL > > commands

Re: pg_dump/pg_restore --jobs practical limit?

Marc MILLAS Senior Architect +33607850334 www.mokadb.com Testing pg_restore with different --jobs= values will be easier. pg_dump > is what's going to be reading from a constantly varying system. > > Hello, > each time I do a replatforming of this kind, with DB up to 2 TB,

Re: Partitioning options

On Thu, Feb 8, 2024 at 10:25 PM Justin wrote: > Hi Sud, > > Would not look at HASH partitioning as it is very expensive to add or > subtract the number of partitions. > > Would probably look at a nested partitioning using customer ID using > range or list of IDs then by transaction date, Its e

ldap fdw

from scratch ?!? what s your advice ? thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com

Re: postgresql order lowercase before uppercase

to me, collate 'C' ask for the raw ascii order which put caps before because the hexa coding is lower did you try any thing else (POSIX is same as 'C') Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, Mar 18, 2021 at 4:18 PM basti wrote: > This doe

Re: postgresql order lowercase before uppercase

or, maybe, just nothing ? Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, Mar 18, 2021 at 4:44 PM Ron wrote: > Exactly. "C" collation is the opposite of what he wants. > > OP needs something like "de_DE". > > On 3/18/21 10:36 AM, Marc

need clarification on CTE/join

ll my goal) thanks, Marc MILLAS Senior Architect +33607850334 www.mokadb.com

Re: need clarification on CTE/join

TE Scan on numb (cost=0.00..0.10 rows=5 width=12) (actual time=0.004..0.005 rows=5 loops=1) Planning Time: 0.070 ms Execution Time: 0.313 ms (11 lignes) Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Wed, Mar 24, 2021 at 3:22 AM David G. Johnston < david.g.johns...@gmail

Re: need clarification on CTE/join

Got it :-) thanks ! Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Wed, Mar 24, 2021 at 4:21 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tuesday, March 23, 2021, Marc Millas wrote: > >> Hi, >> >> I cannot agree. >> I did an

storage in mem

2) I may have to do a POC with Sap HANA on one side and postgres on the other on machines with a lot of RAM. so... can someone shade some light ? thanks, Marc MILLAS Senior Architect +33607850334 www.mokadb.com

Re: Postgresql 13 query engine regression

...) Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Mon, May 10, 2021 at 11:13 PM Jonathan Chen wrote: > Hi, > > I am running Postgresql 13 as a backend for Odoo, and I believe I have > discovered a regression with the query engine. > > This (simplified) query ge

syntax question

Hi, within a function, I want to create another function. no pb. but if I write: declare bidule text; begin bidule:='myfunc'; create function bidule() ... it does create a function named bidule and not myfunc. so I am obviously missing something too obvious. can someone help ? than

Re: syntax question

good reading, thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, Jun 3, 2021 at 9:21 PM Tom Lane wrote: > Adrian Klaver writes: > > On 6/3/21 12:01 PM, Bruce Momjian wrote: > >> On Thu, Jun 3, 2021 at 08:58:03PM +0200, Marc Millas wrote: > >>

Re: syntax question

automatize the building of the whole thing, taking list of columns and constraints (for PK) directly from pg_catalog. Now it works :-) but.. why do you ask that question ? is there any king of hidden wolf we didnt see ? Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, Jun 3, 2021

index unique

he PK.* *OK, we may try to use a function to get the bounding box around the geometry objects and use the result into a btree index* *Any idea (I mean: another idea !) to tackle this ?* *Or any critic on the "solution" ??* *thanks,* Marc MILLAS Senior Architect +33607850334 www.mokadb.com

Re: syntax question

I take note of this. thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, Jun 3, 2021 at 10:23 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Jun 3, 2021 at 1:02 PM Marc Millas wrote: > >> about knowing if I should... >> We

Re: index unique

Hi Paul, obviously the bounding box is not a perfect choice. we also think of one of the "centers" point, but quite similar non uniqueness. so, if no "tech" solution, we continue to work with the business to try to find an appropriate PK thanks Marc MILLAS Senior Ar

Re: syntax question

no pb: I am french, so quite skilled on that topic :-) there is only 50 bottles of various malt on the presentoir close to my desk so I must stay reasonnable :-) Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, Jun 3, 2021 at 11:17 PM Adrian Klaver wrote: > On 6/3/21 1

Re: index unique

text in a btree index and NOT a geom column which wkt is 10 000 bytes long. there, sure, is a reason. please, let me know :-) Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Jun 8, 2021 at 12:09 AM Peter J. Holzer wrote: > On 2021-06-07 10:20:22 -0700, David G. Johnston

Re: index unique

nks Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Jun 8, 2021 at 8:42 PM Peter J. Holzer wrote: > On 2021-06-08 18:30:16 +0200, Marc Millas wrote: > > the only pb observed is the size of the object accepted. if the geom is > a bit > > "big" then the

Re: index unique

a PK, is there any way to make something useful and not this unuseful "thing" ? Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Jun 8, 2021 at 10:51 PM Thomas Kellerer wrote: > Marc Millas schrieb am 03.06.2021 um 22:51: > > on a table we need a prima

Re: index unique

reating an index that will never be of any use, just for technical reasons. People in this list have been quite clear that the btree limitation will NOT change. Which ends the point. regards Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, Jun 10, 2021 at 10:33 PM Alban Hertr

sort order

wing about french characters AND taking beginning white spaces into account ? thanks, regards, Marc MILLAS Senior Architect +33607850334 www.mokadb.com

Re: sort order

Re-reading my post, I see that even the élise is not sorted correctly on w10... Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Jul 27, 2021 at 7:07 PM Marc Millas wrote: > Hi, > > context: > one postgres 12 on centos 7 > one postgres 12 on windows 10 > bot

streaming replication different versions

upgraded slave (step 2 ) ?? thanks, Marc MILLAS Senior Architect +33607850334 www.mokadb.com

Re: streaming replication different versions

it does work well for logical replication. still its sad that it doesnt work for physical replication as, then, how to ensure continuous availability for generic apps ?? Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Wed, Oct 6, 2021 at 5:03 PM Guillaume Lelarge wrote: > Le

Re: Execute command in PL/pgSQL function not executing

Hi, Just one question Isn't it possible to write a function that return an oid? So.. One can write a function which create a temp table, feed it according to parameters and then return the oid of the created temp table. No?? Le ven. 19 nov. 2021 à 23:48, David G. Johnston a écrit : > On Fri, No

download question

Hi, on the postgres repo, on centos 7 x86, I can found the old postgres 9.5, and a postgis 2.4 I need to found a postgres 9.5 with a postgis 2.5.1 where can I found it ? (I know... its obsolete...) thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com

Re: performance expectations for table(s) with 2B recs

partition at a time) great on perf, too IF the partition key is in the where clause. if not, postgres will need to aggregate and sort the results gathered for each partition. Could still be efficient if it allows to parallel execution. my 2 cents Marc MILLAS Senior Architect +33607850334

locks within select

example, within a join, some kind of lock may be acquired so that the dataset concerned is not changed during execution ? (for example a delete then autovacuum ??) thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com

log shipping with pg_receivewal

--synchronous option. My question: as the synchronous option is supposed to make pg_receivewal write transaction immediately in the wal files, is there a way to ask the standby to apply them on the fly ie. without waiting a wal file change ? thanks, Marc MILLAS Senior Architect +33607850334

Re: locks within select

... during business hours, obviously ! --but a bunch of read request are sent to the secondary, some of them a bit big in terms of multiple join, etc.. hence my original question. So, if its not a locking scheme, what can block hundreds of transaction to get to the secondary ??? Marc MILLAS Senior

Re: locks within select

Excellent ! thanks a lot, it was exactly what I was looking for. Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Dec 14, 2021 at 6:14 PM Laurenz Albe wrote: > On Tue, 2021-12-14 at 13:38 +0100, Marc Millas wrote: > > but... when you do setup a streaming replication,

Re: Best Strategy for Large Number of Images

, even the disk option, you "may" encounter phase pb ie. id existing in one db and not in the other. but this depends on how you maintain the images. my 2 cents Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Wed, Dec 15, 2021 at 9:12 PM Estevan Rech wrote: > Adrian, &g

Re: Moving the master to a new server

onf file in the old slave, and restart it. maybe adding first: recovery_target_timeline latest in the recovery.conf file Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Mon, Feb 14, 2022 at 8:59 PM Glen Eustace wrote: > > On 15/02/22 8:39 am, Alan Hodgson wrote: &g

Re: old OS

l, as said, I will try to pipe data through the network, but this imply that data will be put exactly as psql, on the new machine, will be able to 'ingest' it. easy for most things, somewhat more boring for blobs... Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, F

old OS

on a redhat 6.5 ? If nothing, I will try to pipe the data directly through the network connection, but this generates other problems. thanks for your help Marc MILLAS Senior Architect +33607850334

Re: old OS

👍👍👍 thanks Marc MILLAS Senior Architect +33607850334 On Tue, Feb 4, 2025 at 7:04 PM Tim Gerber wrote: > Appears PG 12 is available for RHEL 6 via the following repository: > > https://download.postgresql.org/pub/repos/yum/12/redhat/ > <https://download.postgresql.org/p

psql meta command

Hello, on a customer postgres db, using psql, if I post \ to initiate, for exemple \x that psql immediately exits. somewhat boring as it blocks all psql meta commands. Is there a way to get the extended display without posting a \ ? thanks, Marc MILLAS Senior Architect +33607850334

explain

=52655.694..62533.811 rows=346 loops=1) Buffers: shared hit=10068265 read=396705 dirtied=1858 written=218, temp read=429687 written=115187 I/O Timings: read=79368.246 write=11.486 So, the total execution time is 52655 ms ok and the total time for i/o is...79368 ms how ??? thanks Marc MILLAS Senior

Re: explain

Thanks ! Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, Apr 24, 2025 at 3:41 PM David Rowley wrote: > On Fri, 25 Apr 2025 at 01:32, Marc Millas wrote: > > Nested Loop Left Join (cost=941400.77..966327.57 rows=3 width=653) > (actual time=52655.694..62533.811 ro

shared buffers

times I rerun the explain (analyze, buffers) of the same request, each time, the explain shows the same volume of reads. again and again. If I check with pg_buffercache, the set of objects stay the same, WITHOUT the objects of my request, just like if those objects where sticky. any idea ? thanks

replication

Hello, One of my customers tells me he did a replication with the master on a debian 10 and the slave on a debian 12, both with a pg13.20, one on a pgdg110+1, the other a pgdg120.1 is this a good idea ??? (I don't think so...) thanks, Marc MILLAS Senior Architect +33607850334

Re: shared buffers

Sorry, 'someone' launches some kind of batches without telling. Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Fri, Apr 25, 2025 at 3:42 PM Marc Millas wrote: > hello, > > got something strange to me: > Same db ie. same data, around 1.2TB,one on pg13, on

Re: shared buffers

I didnt know this. thanks, Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Sat, Apr 26, 2025 at 12:46 AM Laurenz Albe wrote: > On Fri, 2025-04-25 at 15:42 +0200, Marc Millas wrote: > > got something strange to me: > > Same db ie. same data, around 1.2TB,one on pg

end of COPY

type numeric "\." then the 2 lines stating the copy command that was executed. what am I missing ? thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com

Re: end of COPY

Greg... 👍 works fine now. thanks ! Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Wed, Mar 5, 2025 at 4:53 PM Greg Sabino Mullane wrote: > On Wed, Mar 5, 2025 at 10:22 AM Marc Millas > wrote: > >> Then the flow contains a single line: \. to my understan

<    1   2