Re: can stored procedures with computational sql queries improve API performance?

versions of the API when you upgrade your model - and it can become a nightmare. Last recommendation: activate the logs and review regularly the performance of your functions. You may identify occurrences that run very fast and others not so. It can help you identify potential conflicts or model optimizations. Hope it helps -- Olivier Gautherot

Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY

El mié, 7 feb 2024 8:07, Sean v escribió: > Exactly. I'm really just trying to understand if there's some functional > limitation to it being able to do that with how it executes these types of > queries, or if its just an optimization that hasn't been built into the > query planner yet. > > I

Re: Disk Groups/Storage Management for a Large Database in PostgreSQL

Hi Amit, El lun, 22 ene 2024 18:44, Amit Sharma escribió: > Hi, > > We are building new VMs for PostgreSQL v15 on RHEL 8.x For a large > database of 15TB-20TB. > > I would like to know from the experts that is it a good idea to create > LVMs to manage storage for the database? > > Or are there

Re: Very newbie question

Hi, El jue, 26 oct 2023 11:15, Peter J. Holzer escribió: > On 2023-10-25 17:48:46 +0200, Olivier Gautherot wrote: > > El mié, 25 oct 2023 16:58, Олег Самойлов escribió: > > Okey, I see no one was be able to solve this problem. But I could. > May be > > for som

Re: Very newbie question

Hi, El mié, 25 oct 2023 16:58, Олег Самойлов escribió: > Okey, I see no one was be able to solve this problem. But I could. May be > for someone this will be useful too. There is solution. > > Original query was: > > > 23 окт. 2023 г., в 18:13, Олег Самойлов написал(а): > > > > SELECT

Re: PostgreSQL and local HDD

El mié, 16 ago 2023 6:54, Ron escribió: > On 8/15/23 23:48, Olivier Gautherot wrote: > > > El mié, 16 ago 2023 5:39, Ron escribió: > >> On 8/15/23 04:24, Olivier Gautherot wrote: >> >> [snip] >> >> Does the database have to be on a storage like E

Re: PostgreSQL and local HDD

El mié, 16 ago 2023 5:39, Ron escribió: > On 8/15/23 04:24, Olivier Gautherot wrote: > > [snip] > > Does the database have to be on a storage like EMC or QNAP? >> > > Faster storage can only help but I would start by discarding functional > overhead. > &

Re: PostgreSQL and local HDD

isk. Try the command EXPLAIN ( https://www.postgresql.org/docs/current/sql-explain.html ) to see where the server is wasting time. > Thank you. > -- Olivier Gautherot

Re: Alter the column data type of the large data volume table.

On Fri, Dec 4, 2020 at 5:22 PM Michael Lewis wrote: > > On Fri, Dec 4, 2020 at 9:04 AM Kevin Brannen wrote: > >> *>From:* Olivier Gautherot >> >> >>5) If you're brave enough, convert your current table as a partition >> (rename it to something like ta

Re: Alter the column data type of the large data volume table.

Hi Charles, On 04-12-2020 9:44, Olivier Gautherot wrote: Hi Charles, On Fri, Dec 4, 2020 at 9:12 AM charles meng mailto:xly...@gmail.com>> wrote: What I mean is that it will take a lot of time to complete all data processing.I have to say that it is a good solution to

Re: Alter the column data type of the large data volume table.

s in advance. >>>>> >>>> >>>> You can add a new column with NO default value and null as default and >>>> have it be very fast. Then you can gradually update rows in batches (if on >>>> PG11+, perhaps use do script with a loop to comm

Re: vacuum vs vacuum full

On Wed, Nov 18, 2020 at 10:45 AM Ron wrote: > On 11/18/20 3:41 AM, Olivier Gautherot wrote: > > Hi Atul, > > On Wed, Nov 18, 2020 at 9:33 AM Atul Kumar wrote: > >> Hi, >> >> We have a table of 3113GB, and we are planning to vacuum it in non >> busi

Re: vacuum vs vacuum full

Hi Atul, On Wed, Nov 18, 2020 at 9:33 AM Atul Kumar wrote: > Hi, > > We have a table of 3113GB, and we are planning to vacuum it in non > business hours i.e. 12AM to 4AM, So my queries are: > > 1. What should be perform on the table Vacuum or Vacuum full ? > Vacuum full will do a complete

Re: autovacuum recommendations for Large tables

Hi Rob, On Tue, Nov 17, 2020 at 10:24 PM Rob Sargent wrote: > If it helps, I put together a few thoughts and own experience on a blog: > https://sites.google.com/gautherot.net/postgresql/vacuum > > Hope you find it useful. > -- > > That URL does not work for me (not even >

Re: autovacuum recommendations for Large tables

e to absolute size in many cases, hence the scale factors). > > David J. > David is correct. If it helps, I put together a few thoughts and own experience on a blog: https://sites.google.com/gautherot.net/postgresql/vacuum Hope you find it useful. -- Olivier Gautherot <https://www.

Re: RITM18130676_Query_PostgreSQL support subscription

Service Centre > For Internal NEC Query, please dial 500-63-51- 4052. > > Ref:MSG44486374 > Best regards Olivier Gautherot >

Re: "Go" (lang) standard driver

Hi Edson, Le mar. 18 août 2020 à 09:36, Tony Shelver a écrit : > -- Forwarded message - > From: Tony Shelver > Date: Tue, 18 Aug 2020 at 09:33 > Subject: Re: "Go" (lang) standard driver > To: Edson Richter > > > A quick Google search found https://github.com/lib/pq. Has 6.1K

Re: Install PostgreSQL on windows 10 home 64-bit machine

n you please point me to the > right version (latest) of PostgreSQL DB that I can install for practice? > It works fine on Windows 10 Home. If it is a personal laptop (and probably not dedicated to the database), just make sure you don't load huge datasets. That being said, it is fully functional. Happy learning! -- Olivier Gautherot

Re: Slow SELECT

PostgreSQL does too on consecutive calls to the same table. What execution time do you get if you issue the query a second time? My first guess would be to add an index on my_table.tran_date and check in EXPLAIN that you don't have a SEQUENTIAL SCAN on that table. > I have looked at the EXPLAIN, but I don't really know what to look for. > I can supply it if that would help. > > Thanks for any advice. > > Frank Millman > > -- Olivier Gautherot

Re: Column reset all values

a VACUUM FULL manually at the end. In my case, I had to compute individual numbers so the processing was a bit more complex but it happily processed over 60 millions rows in a few days. Hope it helps -- Olivier Gautherot

Re: relationship of backend_start, query_start, state_change

in transaction (aborted)', 'disabled'); >> >> > Including the "state" field should clear things up considerably. > > > https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW > > > David J. > The transactions are idle, they are filtered in the WHERE statement. -- Olivier Gautherot

Re: relationship of backend_start, query_start, state_change

ecause the > query_start keeps updating, and the state_change is basically keeping up > with query_start? > > - > Si Chen > Open Source Strategies, Inc. > > Our Mission: https://www.youtube.com/watch?v=Uc7lmvnuJHY > > There is no significant harm in having this thread. Check why the client is not disconnecting - it may have a good reason. -- Olivier Gautherot Tel: +33 6 02 71 92 23 https://www.linkedin.com/in/ogautherot/

Re: possibilities for SQL optimization

Id20" >>> (cost=0.00..11.92 rows=335 width=0) (actual time=0.036..0.036 rows=254 >>> loops=1) >>>Index Cond: (("pixelId" >= '10729621028864'::bigint) AND >>> ("pixelId" <= '

Re: Recursive Queries

but fast to run): WITH q AS (select date_part('hour', ts) AS hr, ts::date AS mydate FROM your_table_or_query) SELECT hr, sum(CASE WHEN mydate = '2020-04-01'::date THEN 1 ELSE 0 END), sum(CASE WHEN mydate = '2020-04-02'::date THEN 1 ELSE 0 END), ... FROM q ORDER BY hr; Hope it helps -- Olivier Gautherot Tel: +33 6 02 71 92 23 https://www.linkedin.com/in/ogautherot/

Re: Using Oracle SQL Client commands with PSQL 12.2 DB

Hi Fred, Le lun. 13 avr. 2020 à 21:49, Fred Richard a écrit : > PGSQLCommunities, > > > We migrated Oracle 11.x Database to PostgreSQL 12.x Database on a RH Linux > 7.x server. > On a different RH Linux 7.x Server, I have Oracle Client installed. Since > we have many scripts developed in

Re: Using compression on TCP transfer

Hi Andrus, Le sam. 4 avr. 2020 à 10:09, Andrus a écrit : > Hi! > > >> In case of varchar field values will appear in database sometimes with > >> trailing spaces and sometimes without. > >> This requires major application re-design which much is more expensive > than > >> continuing using char

Re: Using compression on TCP transfer

count, compression will require that both ODBC and PostgreSQL are set up with compression enabled. I could not figure out quickly whether this requires also recompiling the code... -- Olivier Gautherot Tel: +33 6 02 71 92 23 https://www.linkedin.com/in/ogautherot/

Re: ESQL/C FETCH of CHAR data delivers to much data for UTF-8

Hi Matthias, On Thu, Jan 9, 2020, 20:21 Matthias Apitz wrote: > Hello, > > We encounter the following problem with ESQL/C: Imagine a table with two > columns: CHAR(16) and DATE > > The CHAR column can contain not only 16 bytes, but 16 Unicode chars, > which are longer than 16 bytes if one or

Re: Upgrade PostgreSQL 9.6 to 10.6

On Thu, Jan 9, 2020, 21:47 github kran wrote: > > > On Wed, Jan 8, 2020 at 11:03 PM Michael Lewis wrote: > >> On Wed, Jan 8, 2020 at 8:52 PM github kran wrote: >> >>> You are right on RDS but I believe the problem is on Aurora PostgreSQL >>> where the pglogical throws an error during

Re: READ UNCOMMITTED in postgres

accepting the lag. > Again, as long as you can ensure that there won't be any ROLLBACK. Otherwise you could end up waiting for ever... > > -- > Simon Riggs http://www.2ndQuadrant.com/ > <http://www.2ndquadrant.com/> > PostgreSQL Solutions for the Enterprise > -- Olivier Gautherot Tel: +33 6 02 71 92 23 https://www.linkedin.com/in/ogautherot/

Re: How to get column and identifier names in UPPERCASE in postgres?

Hi Amine, El mié., 20 de noviembre de 2019 10:11, Amine Tengilimoglu < aminetengilimo...@gmail.com> escribió: > Hi all; > > I want to get the column and other identifier names in UPPERCASE > form rather than a lowercase one without changing application code like > qouting the identifiers.

pg_logical for RHEL 8

-- Olivier Gautherot https://www.linkedin.com/in/ogautherot/

Re: Monitor Postgres database status on Docker

Hi Daulat, El mar., 24 de septiembre de 2019 07:05, Daulat Ram < daulat@exponential.com> escribió: > Thanks but how we can use it for docker container. > You have basically 2 ways: 1) Publish the port 5432 on the container and access it from the host, or 2) Use "docker exec" to run the

Re: FW: Re: FW: Re: Shouldn;t this trigger be called?

Hi Stan, On Sun, Sep 15, 2019 at 2:47 PM stan wrote: > Forgot to cc the list again. Have to look at settings in mutt. > > > > > > > > > Sorry forgot to cc the list > > > > > > > > On Sun, Sep 15, 2019 at 08:42:02AM -0700, Adrian Klaver wrote: > > > > > On 9/15/19 8:33 AM, stan wrote: > > > > >

Re: Handling case variatiions on a user defined type?

El dom., 15 de septiembre de 2019 09:37, stan escribió: > I have several enumerated types that look like: > > CREATE TYPE confidence_level AS ENUM ('HIGH' , > 'MEDIUM' , > 'LOW' , >

Re: backing up the data from a single table?

Hi Stan, El vie., 13 de septiembre de 2019 11:14, stan escribió: > My development methodology is to create scripts that init the db, and load > test data, as I make changes. > > Now, I am starting to move toward a test instance with real data. The end > user has provided "real" test data, n

Re: pgmodeler ?

> HINT: Perhaps you meant to reference the column > > -- > "They that would give up essential liberty for temporary safety deserve > neither liberty nor safety." > -- Benjamin Franklin > Can you confirm the versions of p

Re: Code of Conduct plan

Dear all, On Fri, Sep 14, 2018 at 5:18 PM Tom Lane wrote: > Robert Haas writes: > > It's not clear to me that there IS a general consensus here. It looks > > to me like the unelected core team got together and decided to impose > > a vaguely-worded code of conduct on a vaguely-defined group

Re: Safe operations?

On Sun, Aug 12, 2018 at 11:06 PM, Tim Cross wrote: > > On Mon, 13 Aug 2018 at 11:24, Adrian Klaver > wrote: > >> On 08/12/2018 05:41 PM, Samuel Williams wrote: >> > I wish the documentation would include performance details, i.e. this >> > operation is O(N) or O(1) relative to the number of

Re: How can i install contrib modules in pg11 via source

On Tue, Jul 24, 2018 at 3:41 PM, Márcio Antônio Sepp < mar...@zyontecnologia.com.br> wrote: > > > > If so I can tell you how I do it in Linux and you can make the > > appropriate translations to BSD. > > > > 1) cd to contrib/hstore/ > > > > 2) make > > > > 3) sudo make install > > > > 4) In psql

Re: Can't compile postgresql 11 on FreeBSD 11.1

On Tue, Jul 17, 2018 at 4:30 PM, Christoph Moench-Tegeder < c...@burggraben.net> wrote: > ## Olivier Gautherot (oliv...@gautherot.net): > > > To: Christoph Moench-Tegeder > > I think you meant to hit List-Reply... > Ooops, my bad... > > I just pulled

Re: Extremely slow autovacuum:vacuum to prevent wraparound

On Wed, Jul 11, 2018 at 8:17 AM, Rijo Roy wrote: > +pgsql-general > > Sent from Yahoo Mail on Android > > > On Wed, 11 Jul 2018 at

Re: Whither 1:1?

nces - and the second table is significantly wider. This can make a difference on big tables but this gain may be offset by the cost of the join. In this perspective, I don't think that there is a clear benefit or drawback: it should be evaluated on a case-by-case basis. Olivier Gautherot

Re: unable to write 'raise' messages to log file?

Hi Ian, On Thu, May 31, 2018 at 3:19 PM, Adrian Klaver wrote: > On 05/31/2018 12:15 PM, Olivier Gautherot wrote: > >> On Thu, May 31, 2018 at 3:07 PM, Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote: >> >> On 05/31/2018 11:20 AM, Ian B

Re: unable to write 'raise' messages to log file?

; > -- > Adrian Klaver > adrian.kla...@aklaver.com > > I've used the logs successfully here. What are the values of log_min_messages and log_min_error_statement in your postgresql.conf? Olivier Gautherot

Re: Fast logical replication jump start with PG 10

On Sat, May 26, 2018 at 1:27 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/26/2018 06:23 AM, Olivier Gautherot wrote: > >> On Sat, May 26, 2018 at 12:11 AM, Adrian Klaver < >> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>>

Re: Fast logical replication jump start with PG 10

Hi Adrian! On Sat, May 26, 2018 at 12:11 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/25/2018 06:35 PM, Olivier Gautherot wrote: > >> Hi Adrian, thanks for your reply. Here is the clarification. >> >> 1) It is indeed a pg_upgrade from 9.2 to 10.4. De

Re: Fast logical replication jump start with PG 10

ower the load, reason to use the logical replication... if the execution time is not too excessive). Hope it clarifies the question Best regards Olivier Olivier Gautherot oliv...@gautherot.net Cel:+56 98 730 9361 Skype: ogautherot www.gautherot.net http://www.linkedin.com/in/ogautherot On Fri, May 25, 20

Fast logical replication jump start with PG 10

only 1 database on the server, it would not be a show-stopper. Thanks in advance Olivier Gautherot http://www.linkedin.com/in/ogautherot