Re: Improving pg_dump performance when handling large numbers of LOBs

Might it be worth a modest amount of time using some basic profiling to see where the time is going? A week is a looonnngg time, even for 150e6 operations. For example, if there an unexpectedly high IO load, some temporary M.2 storage might help? On Tue, 6 Feb 2024, 01:36 Ron Johnson, wrote: >

Re: How to do faster DML

On Tue, 6 Feb 2024 at 10:45, Lok P wrote: > Thank you Greg. > > *"and keeping all your active stuff in cache. Since you have 127 columns, > only pull back the columns you absolutely need for each query."* > > Understood the point. As postgres is a "row store" database, so keeping > the size of

Re: How to do faster DML

Thank you Greg. *"and keeping all your active stuff in cache. Since you have 127 columns, only pull back the columns you absolutely need for each query."* Understood the point. As postgres is a "row store" database, so keeping the size of the row lesser by making the number of columns to

Re: How to do faster DML

> So when you said *"In that case, and based on the numbers you provided, > daily partitioning seems a decent solution."* > , does it mean that size of the partition (which will be ~100-150GB per > partition here) is not an issue for the postgres to serve read latency > sensitive queries? > Yes,

Re: How to do faster DML

On Tue, Feb 6, 2024 at 8:34 AM Greg Sabino Mullane wrote: > On Sun, Feb 4, 2024 at 3:52 PM Lok P wrote: > >> This table will always be queried on the transaction_date column as one >> of the filters criteria. But the querying/search criteria can span from a >> day to a month worth of

Re: How to do faster DML

On Sun, Feb 4, 2024 at 3:52 PM Lok P wrote: > This table will always be queried on the transaction_date column as one of > the filters criteria. But the querying/search criteria can span from a day > to a month worth of transaction date data. > In that case, and based on the numbers you

Re: Unused indexes

> > The pg_stat_*_tables tables idx_* columns has accumulated usage since the > last time you started the postmaster. > Actually, those persist at restart - you can use select datname, stats_reset from pg_stat_database; to see when/if they were reset. You can look for zero/low entries in

Re: select from composite type

On 2/5/24 16:35, Lorusso Domenico wrote: ah ehm.. I solved, it was very easy but I believed it should use the from clause... execute 'select ($1).* ' using _attribute into _r; Beat me to it For the reason why it works:

Re: select from composite type

ah ehm.. I solved, it was very easy but I believed it should use the from clause... execute 'select ($1).* ' using _attribute into _r; Il giorno mar 6 feb 2024 alle ore 01:01 Lorusso Domenico < domenico@gmail.com> ha scritto: > here an example (the actual case in more complex, but the point

Re: select from composite type

here an example (the actual case in more complex, but the point it's the same) do $$ declare _attribute_list temp1.my_type[]; _attribute temp1.my_type; _r record; begin _attribute_list=array[row(1,'Hello') , row(2,'Goodbye')]; _attribute= row(1,'Doh'); raise notice '%', _attribute_list;

Re: Deleting duplicate rows using ctid ?

On Mon, Feb 5, 2024 at 4:09 PM David Gauthier wrote: > > I want the result to be just 2 recs, one for each dog. > My present goto link for this question: https://harshitjain.home.blog/2019/06/17/postgresql-how-to-delete-duplicate-rows-in-postgresql/ David J.

Deleting duplicate rows using ctid ?

I have a table with 4 columns, none of them unique. I want to delete all but one of the duplicate records. I think there's a way to do this with ctid. Examples that pop up in google searches always rely on something like a unique (id) field, like a primary key, (no good in my case) create

Re: Improving pg_dump performance when handling large numbers of LOBs

On 2/5/24 12:32, Ken Marshall wrote: On Mon, Feb 05, 2024 at 09:17:53PM +0100, Andreas Joseph Krogh wrote: På mandag 05. februar 2024 kl. 21:10:53, skrev Wyatt Tellis < wyatt.tel...@gmail.com >: No, we don't have the ability to make schema changes and the schema

Re: Improving pg_dump performance when handling large numbers of LOBs

Wyatt Tellis writes: > Yes, the LOBs themselves are tiny, but there are a lot of them (~150 > million) which seem to be slowing down pg_dump. Note, we did not > design/build this system and agree that use of LOBs for this purpose was > not necessary. I don't know of anything much you can do at

Re: Improving pg_dump performance when handling large numbers of LOBs

On Mon, Feb 05, 2024 at 09:17:53PM +0100, Andreas Joseph Krogh wrote: > > På mandag 05. februar 2024 kl. 21:10:53, skrev Wyatt Tellis < > wyatt.tel...@gmail.com >: > > No, we don't have the ability to make schema changes and the schema in the > PG15 copy needs to

Re: Improving pg_dump performance when handling large numbers of LOBs

På mandag 05. februar 2024 kl. 21:10:53, skrev Wyatt Tellis < wyatt.tel...@gmail.com >: No, we don't have the ability to make schema changes and the schema in the PG15 copy needs to match what's in the PG 12 versions Well then, I guess it boils down to how badly

Re: Improving pg_dump performance when handling large numbers of LOBs

Just checking... and I sympathize with your situation. On Mon, Feb 5, 2024 at 2:56 PM Wyatt Tellis wrote: > Yes, the LOBs themselves are tiny, but there are a lot of them (~150 > million) which seem to be slowing down pg_dump. Note, we did not > design/build this system and agree that use of

Re: Improving pg_dump performance when handling large numbers of LOBs

No, we don't have the ability to make schema changes and the schema in the PG15 copy needs to match what's in the PG 12 versions Wyatt On Mon, Feb 5, 2024 at 12:05 PM Andreas Joseph Krogh wrote: > På mandag 05. februar 2024 kl. 20:55:43, skrev Wyatt Tellis < > wyatt.tel...@gmail.com>: > > Yes,

Re: Improving pg_dump performance when handling large numbers of LOBs

På mandag 05. februar 2024 kl. 20:55:43, skrev Wyatt Tellis < wyatt.tel...@gmail.com >: Yes, the LOBs themselves are tiny, but there are a lot of them (~150 million) which seem to be slowing down pg_dump. Note, we did not design/build this system and agree that

Re: Improving pg_dump performance when handling large numbers of LOBs

Yes, the LOBs themselves are tiny, but there are a lot of them (~150 million) which seem to be slowing down pg_dump. Note, we did not design/build this system and agree that use of LOBs for this purpose was not necessary. Wyatt On Mon, Feb 5, 2024 at 11:36 AM Ron Johnson wrote: > On Mon, Feb

Re: Improving pg_dump performance when handling large numbers of LOBs

On 2/5/24 11:35 AM, Ron Johnson wrote: On Mon, Feb 5, 2024 at 2:01 PM Wyatt Tellis wrote: Hi, We've inherited a series of legacy PG 12 clusters that each contain a database that we need to migrate to a PG 15 cluster. Each database contains about 150 million large objects

Re: Improving pg_dump performance when handling large numbers of LOBs

On Mon, Feb 5, 2024 at 2:01 PM Wyatt Tellis wrote: > Hi, > > We've inherited a series of legacy PG 12 clusters that each contain a > database that we need to migrate to a PG 15 cluster. Each database contains > about 150 million large objects totaling about 250GB. > 250*10^9 / (150*10^6) = 1667

Re: How to do faster DML

I have not much experience with postgres on this scale though, others may provide better suggestions. However, with this scale you will have a single table size ~30TB+. And someone just pointed out in this thread, ~180 partitions/table as the good to have limit, and if that is true it would be

Improving pg_dump performance when handling large numbers of LOBs

Hi, We've inherited a series of legacy PG 12 clusters that each contain a database that we need to migrate to a PG 15 cluster. Each database contains about 150 million large objects totaling about 250GB. When using pg_dump we've found that it takes a couple of weeks to dump out this much data.

Re: Question on partitioning

On Mon, 5 Feb 2024 at 17:52, Laurenz Albe wrote: > On Mon, 2024-02-05 at 03:09 +0530, veem v wrote: > > In postgresql, Is it possible to partition an existing nonpartitioned > table having data > > already residing in it and indexes and constraints defined in it, > without the need of > >

Re: Yum Update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common

Hi, On Mon, 2024-02-05 at 08:16 -0800, Marcelo Marques wrote: > Yes, the EPEL repo is enabled. (Please keep the list CC'ed) Armadillo 12 packages *are* in the EPEL repo: https://dl.fedoraproject.org/pub/epel/9/Everything/x86_64/Packages/a/ If you have a local mirror, make sure that it is

Re: Unused indexes

On Mon, Feb 5, 2024 at 9:02 AM arun chirappurath wrote: > Hi All, > > Do we have a script to get unused indexes for 30 days and once identified > do we have an option to disable and enable when required? > The pg_stat_*_tables tables idx_* columns has accumulated usage since the last time you

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

On Mon, Feb 5, 2024 at 8:55 AM Ron Johnson wrote: > > Who knows which users are going to be in that list??? > > It doesn't matter. Worse case scenario there is only one user in the result and so all 50 rows are their earliest 50 rows. The system will thus never need more than the earliest 50

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

On Mon, Feb 5, 2024 at 7:23 AM Sean v wrote: > This is related to a question I asked on dbs.stackexchange.com: > https://dba.stackexchange.com/questions/335501/why-doesnt-postgres-apply-limit-on-groups-when-retrieving-n-results-per-group > > But to reiterate - I have a query like this: > >

Unused indexes

Hi All, Do we have a script to get unused indexes for 30 days and once identified do we have an option to disable and enable when required? I sql server we have this option to disable it and need to rebuild it to ensemble it Thanks, Arun

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

This is related to a question I asked on dbs.stackexchange.com: https://dba.stackexchange.com/questions/335501/why-doesnt-postgres-apply-limit-on-groups-when-retrieving-n-results-per-group But to reiterate - I have a query like this: SELECT "orders".* FROM "orders" WHERE (user_id IN ?, ?, ?)

Re: Question on partitioning

On Mon, 2024-02-05 at 03:09 +0530, veem v wrote: > In postgresql, Is it possible to partition an existing nonpartitioned table > having data > already residing in it and indexes and constraints defined in it, without the > need of > manually moving the data around, to make it faster? Similarly

Re: How to do faster DML

On Mon, 5 Feb, 2024, 2:21 am Lok P, wrote: On Sun, Feb 4, 2024 at 9:18 PM Greg Sabino Mullane wrote: Partitioning will depend a lot on how you access the data. Is it always using that transaction_date column in the where clause? Can you share the table definition via \d? We've talked about

Re: Yum Update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common

Hi, On Fri, 2024-02-02 at 11:05 -0800, Marcelo Marques wrote: > *yum update nothing provides libarmadillo.so.12()(64bit) needed by > gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common  * Is EPEL repo enabled? If not, you can refer to our docs about installing EPEL on RHEL/Rocky: