Re: Is this a reasonable use for advisory locks?

How many of these processes do you expect to have running concurrently? How long does that API call take? Might it be better to update the customer (or in a separate table as suggested) as "catch up charge process started at" and then clear that or set completed time in another column to serialize?

Re: LwLocks contention

On Thu, Apr 21, 2022 at 6:17 AM Chris Bisnett wrote: > We're working to update our application so that we can > take advantage of the pruning. Are you also using native partitioning? > No partitioned tables at all, but we do have 1800 tables and some very complex functions, some trigger insanity

LwLocks contention

We are occasionally seeing heavy CPU contention with hundreds of processes active but waiting on a lightweight lock - usually lock manager or buffer mapping it seems. This is happening with VMs configured with about 64 CPUs, 350GBs ram, and while we would typically only have 30-100 concurrent proce

Re: Serializable read only deferrable- implications

Sorry for the confusion I caused. The question about connection management and pg bouncer was a distraction and should have been addressed separately. When having a mixture of OLTP and OLAP on the same primary databases, is there any benefit to declaring long running report type connections as SER

Re: Indexes in JSONB

No matter what you do, you will not typically get the same level of performance as normalized tables as you do not get statistics on the values stored in your jsonb column. No knowledge of null fraction, number of distinct values, or most common values and their frequencies.

Re: Indexes that would span partitions.

How many partitions do you expect to have? Why are you partitioning on that column? Do you have need to drop old data all at the same time? How many rows in each partition or in total do you expect to have?

Re: delete query using CTE

You can't delete rows that are in a CTE. You want to delete rows that are in the table. Do you have a primary key that you can reference? Else, you may need to reference the system column ctid.

Re: foreign key on delete cascade order?

> > We are updating the entire status table every 5 minutes with > BEGIN; > UPDATE status SET () WHERE pollid = $1; > COMMIT; > > The issue is arriving when some does a DELETE during the UPDATE of status > DELETE FROM mach WHERE machid=$1; > Could you set lock_timeout, lock table explicitly for SH

Re: Serializable read only deferrable- implications

On Tue, Mar 8, 2022 at 9:27 AM Adrian Klaver wrote: > "PostgreSQL maintains this guarantee even when providing the strictest > level of transaction isolation through the use of an innovative > Serializable Snapshot Isolation (SSI) level." > > Then: > > > https://www.postgresql.org/docs/current/tr

Re: Serializable read only deferrable- implications

> > A transaction started with the first statement will not take any SI locks, > nor > will it ever receive a serialization error. > What is the meaning of SI? Anything you are aware of in source code or a blog post that discusses this?

Serializable read only deferrable- implications

real life differences between the two commands below? Would this be applicable at all to sessions on a physical replica? SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY; *Michael Lewis | Databas

Re: PostgreSQL's Max Identifier Length in Amazon RDS

Also- https://www.postgresql.org/docs/10/runtime-config-preset.html "The following “parameters” are read-only, and are determined when PostgreSQL is compiled or when it is installed." >

Re: PostgreSQL's Max Identifier Length in Amazon RDS

I believe that would not be possible. One quick read- https://til.hashrocket.com/posts/8f87c65a0a-postgresqls-max-identifier-length-is-63-bytes With it being RDS, changing source and recompiling is not an option.

Re: Rows From but with Subqueries (or a cleaner non-array-using alternative)?

In pseudo code, group_index is defined as: case when LAG(v) OVER (ORDER BY i) = v then lag(i) ELSE i END, right? If you have that in the first cte instead of the start/end business, then you can just select vals, group number, and row_num over that new grouping, right? Something like this? WITH

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

I think that theoretically if the planner sees a condition like a.column1 > constant_value, and it also has a condition like a.column1 = b.column2 then it could autogenerate the b.column2 > constant_value condition. And of course > could be <, <=, >= and <> But I could be wrong, particularly with

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

I would guess that autovacuum is analyzing the table and causing the stats to change which is resulting in different estimates that result in different plans. Unless you can get the estimate much more accurate, you won't get far with expecting a stable plan that performs well. How is data_class_pk

Re: pg_cron for vacuum - dynamic table set

Can't you use a do script to construct and execute the statement? >

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

What does the row estimate look like on the scan of data table with that statistic in place? Anytime the stats give a mis-estimate this far off, I wouldn't expect that plans would be optimal except by luck. Index Scan using data_pkey on data t (cost=0.57..21427806.53 *rows=58785023* width=131) (ac

Re: pg_try_advisory_lock is waiting?

On Fri, Jan 28, 2022 at 5:34 PM Mladen Gogala wrote: > pg_try_advisory_lock returned TRUE even without "FOR UPDATE" clause in the > subquery. Shouldn't it return false because it can't lock the row until the > uncommitted update finishes? > > > > The rows bein

Re: pg_try_advisory_lock is waiting?

pg_try_advisory_lock and other advisory lock functions take a lock on a key. The keys you have tried to lock are 0 and 1. Why? What problem are you trying to solve by changing from "for update" lock to advisory lock anyway?

Re: Undetected Deadlock

There may be a bug so perhaps still pursue reproducing the issue, but I would expect drop concurrently to resolve your issue with the two processes conflicting. Also, perhaps trying"insert, on conflict do update" could be more efficient than the copy but obviously there are too many unknowns and va

Re: Undetected Deadlock

I must be missing something. You mentioned dropping a partition, so is there an actual need for the delete? Could you detach concurrently and then drop the table or delete rows if needed? https://www.postgresql.org/docs/14/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION

Re: tstzrange on large table gives poor estimate of expected rows

If interval_end_date is always 1 day ahead, why store it at all? Dependencies on a custom stats object wouldn't do anything I don't think because they are offset. They are 100% correlated, but not in a way that any of the existing stat types capture as far as I can figure.

Re: Query much slower from php than psql or dbeaver

When dealing with foreign tables, I believe planning is not the same because of access to statistics (maybe has improved since 9.6 though). I just wonder... Would it be a viable option to create a materialized view using the FDW but then use the PHP script against the local tables only? Materialize

Re: Default values in functions

On Wed, Dec 29, 2021, 5:31 PM Tom Lane wrote: > The way to do that is to use named parameters and the associated > call syntax, ie something like > > select myfunc(param1 => 42, param3 => 99); > Thanks very much. I have not seen that before. >

Re: Default values in functions

On Wed, Dec 29, 2021, 4:31 PM Tom Lane wrote: > You didn't say what icount() is, but if it's the one from > contrib/intarray, it's STRICT meaning it'll return NULL, > not zero, for a null array input. Thanks for that. Very good to know. (Or IOW, null::int[] is not at all the same thing as arra

Re: Default values in functions

On Wed, Dec 29, 2021, 4:34 PM David G. Johnston wrote: > That isn’t how it works. Absence is what is important. Null is not > absence. As you showed, if you want nulls to be converted to defaults you > can use coalesce. > Thanks sir. It seems unfortunate that there is not a way to indicate ab

Default values in functions

ATE + interval '2 hour', false ), ( null::int[] , CURRENT_DATE + interval '3 hour', true ), ( null , CURRENT_DATE + interval '4 hour', true ) )AS sub ( pArrayToCheck, pTimeToDisplay, expected_result ); *Michael Lewis | Database Engineer* *Entrata*

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

On Tue, Dec 21, 2021 at 11:50 AM Pavel Stehule wrote: > I wrote about it. Did you read this article? > > https://okbob.blogspot.com/2018/02/schema-variables.html > > The goals of this project: > >- fast non transactional non persistent (session based) storage, > > Would there be statistics th

Re: Postgresql + containerization possible use case

Interesting. I'm not sure that moving an image for a VM with Postgres is compatible with the goal of minimizing time/data but if upgrades are yearly or something, perhaps that is reasonable. It has been 9+ years since that post as well. But the full data will need to be included in the image if the

Re: Postgresql + containerization possible use case

On Fri, Dec 10, 2021, 2:24 AM Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > Hi > we are running some 140 remote servers (in the 7 seas via satellite > connections) How are they used? What is in Postgres? Should that all have the exact same read only data at all times? >

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

What is your default_statistics_target and how accurate is that estimate of 5668 rows? What is random_page_cost set to by the way? More importantly, what is the better plan that you'd like the planner to use with your existing indexes? It would seem logical to me to scan for the matching shipment_

Re: INSERT ... ON CONFLICT doesn't work

Perhaps I missed something, but why all this effort to reference the column and not just reference IX_Lockers_Uuid for the on conflict clause?

Re: Max connections reached without max connections reached

On Wed, Dec 1, 2021 at 9:06 PM Dilip Kumar wrote: > IMHO, it is good to LOG such information if we are not already logging > this anywhere. > +1 I expect my 'vote' counts for naught, but I fully expect seeing these show up in the logs would have helped me much more quickly have insight into wha

Re: Max connections reached without max connections reached

On Wed, Dec 1, 2021 at 8:54 PM Rob Sargent wrote: > On 12/1/21 7:08 PM, Michael Lewis wrote: > > On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent wrote: > >> Also be on the look-out for begin trans; begin trans; etc I read >> Kumar's report as nested transacti

Re: Max connections reached without max connections reached

On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent wrote: > Also be on the look-out for begin trans; begin trans; etc I read Kumar's > report as nested transactions. If your gang is doing a transaction per > row, they need a kick in the rear. Anyone not closing those needs a pink > slip. > I have see

Re: [EXTERNAL] Re: Inserts and bad performance

example and run it in a transaction with explain (analyze, buffers, verbose) and then rollback? *Michael Lewis | Database Engineer* *Entrata*

Re: Execute command in PL/pgSQL function not executing

You could also just return the name of the temp table, or return nothing and directly use that name manually outside the function to do select * from pg_temp.my_data

Row estimate on empty tables

ref: https://www.postgresql.org/message-id/9772.1510348781%40sss.pgh.pa.us This functionality hasn't changed in the years since this post, right? I'm dealing with temp tables specifically, but seem to be getting bit by this behavior occasionally so I would like to confirm. *Mic

Re: Are all unlogged tables in any case truncated after a server-crash?

Curious... why keep the table as unlogged if it is static? If you can spare the disk space, perhaps just create a regular table with same definition, gradually copy the data to spread the impact on WAL, and when complete, just drop the old table and rename the new one.

Re: Are all unlogged tables in any case truncated after a server-using

Why keep them as unlogged tables? If data is static, can you spare the disk space to gradually copy data from existing unlogged table to new copy that is logged, and then have brief exclusive lock to drop unlogged and rename new one?

Re: Logical Replication - Should Destination Table Columns Be Defined With Default Value

Is there any advantage to not defining the default on the replica? If it is not a static value and the publishing database will trigger row updates, I could see waiting to set the default until after the table re-write is done, but otherwise there doesn't seem to be any benefit to skipping column d

Re: Debugging features needed

For my purposes, app name isn't long enough so we put a comment at the start of every SQL that has a unique ID generated in the application. This ensures that we can tell one connection apart from another even when both are coming from the same feature/action (applicationName is set to this) even u

Re: Model clause and

Mea culpa. "PostgreSQL currently implements only stored generated columns." I should re-read what I share references to rather than trust my memory, particularly while sick. Thanks for kindly correcting.

Re: Model clause and

On Thu, Oct 28, 2021 at 1:57 PM SQL Padawan wrote: > I presume that VIRTUAL GENERATED columns are on the to-do list? > https://www.postgresql.org/docs/current/ddl-generated-columns.html Is this not what you want?

Re: Determining if a table really changed in a trigger

If you end up with no rows changing from an insert or delete, something seems awry. Unless you mean 0 rows affected. Do after statement triggers still execute? I suppose they very well might. Would the statement even execute if no rows get updated and that is prevented with before update? I would

Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

> > Planning Time: 10.351 ms > > Execution Time: 0.283 ms > Nearly all of the time is in planning... What is your default_statistics_target?

Re: How to copy rows into same table efficiently

On Tue, Oct 26, 2021 at 1:07 AM Arun Suresh wrote: > Current approach taken is to build a query like below: > INSERT INTO mytable (col1, col2, col3, col4) SELECT col1, 'XYZ', col3, > col4 FROM mytable WHERE col2 = 'ABCD' > > Is there a better way to do this? > There could be other tables with for

Re: Determining if a table really changed in a trigger

Does this perform differently from suppress_redundant_updates_trigger? https://www.postgresql.org/docs/current/functions-trigger.html

Re: Avg/max size of these JSON docs in Postgres

On Tue, Oct 12, 2021 at 12:51 PM Simon Riggs wrote: > On Tue, 12 Oct 2021 at 18:53, Michael Lewis wrote: > > > > On Tue, Oct 12, 2021 at 1:43 AM Simon Riggs < > simon.ri...@enterprisedb.com> wrote: > >> > >> On Tue, 12 Oct 2021 at 08:14, Ram Pratap Ma

Re: Avg/max size of these JSON docs in Postgres

On Tue, Oct 12, 2021 at 1:43 AM Simon Riggs wrote: > On Tue, 12 Oct 2021 at 08:14, Ram Pratap Maurya > wrote: > > > Confirm what is Avg/max size of these JSON docs in Postgres. > > JSON and JSONB datatypes can both be max 1GB in size. > That is per row. Just to confirm- The compression associa

Re: Query time related to limit clause

What's the definition for abc_serv_nch_q1_2021_expr_idx3? That is a jsonb field I assume? Statistics aren't great on jsonb data, so you may benefit greatly from pulling keys out to be stored as a standard column. I would be curious for more "pure" estimates on each quarterly partition directly for

Re: DELETE ... USING LATERAL

On Mon, Oct 4, 2021, 10:30 AM Nikhil Benesch wrote: > you can't reuse the FROM table name in the USING clause: > > # delete from int_arrays using int_arrays; > ERROR: table name "int_arrays" specified more than once > Don't you need to use an alias for the table in the using clause?

Re: Using a single sequence for all tables

If your processes somehow allow updates on the wrong table, then fix that. If you run out of space in whatever value range you choose initially, the pain to upgrade to a type that allows larger values would seem to be very large.

Re: Faster distinct query?

> > It’s only when I add in the AND data.channels=channels.channel that the > query time blows up to 5+ minutes. I personally don’t understand why there > would be such a large difference between the two queries - something wrong > with my indexes? > Did you do the manual vacuum as suggested by To

Re: Faster distinct query?

On Wed, Sep 22, 2021 at 2:48 PM Tom Lane wrote: > "David G. Johnston" writes: > > There is no where clause so I'm doubtful there is much to be gained going > > down this path. The Index-Only scan seems like an optimal way to obtain > > this data and the existing query already does that. > > The

Re: Faster distinct query?

In the future, please share the plan returned by explain analyze, and some data about how many rows in the involved tables, what type of system you are running it on, any changes from default configs, etc. How does the below work? It should do a very simple index scan only, then aggregate the rela

Re: Timestamp with vs without time zone.

On Wed, Sep 22, 2021 at 12:44 AM cen wrote: > On 21. 09. 21 23:34, Michael Lewis wrote: > > Related to this current discussion and exchange of ideas... is there a > best practice for retrieving data in such a way as the rows are localized > to a timezone for where/group by purpos

Re: Question about behavior of conditional indexes

Just to clarify, I suggested fillfactor likely could *not* help because you are indexing a column that has the value change. The idea with reducing fillfactor is that the rows can be updated with the new versions remaining in the same 8KB block in the file for table/row storage. If the new version

Re: Timestamp with vs without time zone.

Related to this current discussion and exchange of ideas... is there a best practice for retrieving data in such a way as the rows are localized to a timezone for where/group by purposes. That is, if I have a table which has events, but those events belong to a tenant or some entity that has a loca

Re: Question about behavior of conditional indexes

You likely need to tune your autovacuum settings such that the index bloat reaches a steady-state and does not continue to increase. When vacuum runs, it will remove dead (and no longer visible to any running transactions) tuples aka row versions from each page (8KB block by default) in the file fo

Re: Azure Postgresql High connection establishment time

This is not a Postgres issue. Please reach out to the Azure team. >

Re: Fastest option to transfer db?

What version of Postgres is the source? Can you make use of logical replication?

Re: Query takes around 15 to 20 min over 20Lakh rows

. *Michael Lewis | Database Engineer* *Entrata*

Re: Query takes around 15 to 20 min over 20Lakh rows

What is T and how many rows are in there? How many rows in task_history? What indexes exist? Are you confident you want 2 million rows in that result set? What version is this on? What pg_settings have been changed from defaults?

Re: Querying a table with jaccard similarity with 1.6 million records take 12 seconds

This is showing many false positives from the index scan that get removed when the actual values are examined. With such a long search parameter, that does not seem surprising. I would expect a search on "raj nagar ghaziabad 201017" or something like that to yield far fewer results from the index s

Re: string_agg distinct order by

NSERT INTO person VALUES ('Roe', 'ok'); SELECT * FROM person order by current_mood; SELECT * FROM person order by current_mood desc; Note- using enum may complicate other things in your usage, so I am not suggesting this is ideal, just one option. *Michael Lewis | Database Engineer* *Entrata*

Re: Regexp_replace bug / does not terminate on long strings

Btw- My apologies for top posting. I think my caffeine wore off.

Re: Regexp_replace bug / does not terminate on long strings

0/10,250/25,250/250,300/20,300/30,300/7,300/300,300/10,300/50,400/20,400/25,450/50,500/500,500/50,500/35,500/25,600/40,1000/20,1000/40,1000/1000,1000/35,1000/50,1000/500 */ If you don't need the order maintained, it becomes a much simpler problem and you can strip off some of this complexity. *

Re: Using a TRIGGER with window functions.

If you want to update many rows after updating N rows, you want an after STATEMENT trigger which is executed after insert, update or delete. You also want to ensure that only that function maintains sort_order field and that you don't update recursively, perhaps by executing that function when NEW.

Re: Partitioning a table by integer value (preferably in place)

On Fri, Aug 13, 2021 at 3:02 AM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > ... use binary split for large partitions, to avoid large row movements. > Would you expound on this?

Re: Frequetly updated partial index leads to bloat on index for Postresql 11

Have you tried setting autovacuum to run quite aggressively, perhaps just on this table? Have you tried an index on the status column only, rather than partial?

Re: optimization issue

((current_setting('env.groupid'::text))::integer)::numeric If you pass this value in directly as part of the query string, how does it perform? It seems like calling the function to get this value may be creating a planning problem with the value unknown at plan time. If you were able to put the r

Re: On partitioning, PKs and FKs

Why are you using hash partitioning?

Re: EXPLAIN with anonymous DO block?

It sounds like you are wanting to run 'explain analyze [query]' inside a loop inside a DO block. That isn't possible as far as I know, but auto_explain and log_nested_statements should be able to let you profile the whole thing and perhaps you can pick out the part you want from the logs.

Re: Do table-level CHECK constraints affect the query optimizer?

Other than rows being frozen on test (and not on live), I'm not aware of anything that would push the planner to choose to do an index scan on an entire table. Maybe someone else will chime in. Or, if you try running vacuum freeze on live and can verify if that changes the result. I'm not sure why

Re: Do table-level CHECK constraints affect the query optimizer?

Are vacuum and analyze happening regularly on the live system? What's an example query that uses indexes on test and does not on live? Does the live system show poor estimates when executing 'explain analyze select...' and the test system show semi-accurate row estimates? 50 million seems to be a

Re: Use case stuck due to Partitioning

I am unclear exactly what you want to do with modified_date. Can you write pseudo code perhaps? It sounds like you are wanting to union all the results of the query you gave, with the results of a similar query where modified_date >= '2021-03-01 08:16:13.589' and created_at < '2021-03-01 08:16:13.

Re: Use case stuck due to Partitioning

Do you have an index on the "updated_at" field and found that the query is too slow? Do you have an example query?

Re: [Extern] Re: autovacuum on pg_catalog tables

Why not change the defaults? How many tables would hit this new threshold and you would NOT want autovacuum to process them? >

Re: AWS forcing PG upgrade from v9.6 a disaster

On Sat, May 29, 2021, 4:40 AM Lionel Bouton wrote: > The last time I had to use this setting to solve this kind of problem I > ended with : > > default_statistics_target = 500 > > But obviously the value suited to your case could be different (I'd > increase it until the planner uses the correct

Re: ON CONFLICT DO NOTHING ignored on bulk insert

Are you wanting to minimize the locking time, or ensure the whole process completes as soon as possible? If the prior, you can insert into a temp table like the real one (which is maybe what pricelistnew is already), delete the rows where they don't have a valid reference, and then insert all remai

Re: trigger impacting insertion of records

It seems like there is some flaw here. From my reading, on insert of any row, you are updating ALL rows in the same table to just remove an underscore if it matches the pattern of 'US_' at the beginning. That doesn't seem likely to be what you want. I'd think you would want something like the below

Re: index only scan taking longer to run

Without knowledge of the structure of your data, or even real table/field names to be able to read the query with some sense of what the tables might be like, offering advice is rather difficult. I didn't notice any nodes with huge estimation problems at least. I will suggest that DISTINCT is ofte

Re: BRIN index on timestamptz

On Sat, Apr 24, 2021, 1:27 AM Mohan Radhakrishnan < radhakrishnan.mo...@gmail.com> wrote: > What's your question exactly? If you have confidence that correlation will > remain high (insert only table, or occasional cluster/repack with cluster > is done), then BRIN can be a good fit. If you do upda

Re: BRIN index on timestamptz

On Fri, Apr 23, 2021, 2:32 AM Mohan Radhakrishnan < radhakrishnan.mo...@gmail.com> wrote: > I execute this on the AWS RDS instance. Is there something in the plan I > should pay attention to ? I notice the Execution Time. > > > " -> Bitmap Index Scan on "testtab_date_brin_idx " (cost=0.00..20.0

Re: Suboptimal plan when IN(...), ORDER BY and LIMIT are used (no JOINs)

Your query and explain analyze output do not seem to match. Filter: (cred_id = '1001344096118566254'::bigint) I don't see anything like that in your query, nor an index that would support accomplishing that without filtering after fetching the 184k rows initially like the planner does. >

Re: Timestamp/hstore query?

If you share example schema and desired output (like a dummy table or even pseudo code SQL), then I'm sure many people could help you. Right now, the description of your desired result seems a bit unclear, at least to me. If you wanted to run this hourly for the last 1 hour, it sounds a bit like

Re: Slick way to update multiple tables.

postgresql.org/docs/current/sql-createview.html My apologies. It seems INSTEAD OF triggers are required to implement updates across multiple tables. I thought not if all were simple joins. My mistake.

Re: Slick way to update multiple tables.

Joins are allowed though.

Re: Slick way to update multiple tables.

You can have an updatable view.

Re: Primary keys and composite unique keys(basic question)

Etiquette on these lists is to reply in line or below the relevant portion, not top-post with full quoting like default gmail behavior. On Wed, Mar 31, 2021 at 9:18 AM Mohan Radhakrishnan < radhakrishnan.mo...@gmail.com> wrote: > But we don't search using UUIDs always. Only when data from another

Re: MultiXactId wraparound and last aggressive vacuum time

I don't believe you can determine a date/time that it happened, but querying the age of the table is simple to do and then you can compare that with the freeze age parameters. A periodic manual vacuum freeze may preclude the system ever needing to perform the emergency autovacuum freeze, and as an

Re: Question about when PostgreSQL 11.0 was released

I can't find a page on the website right now that specifies it, but the feature freeze is around April each year then general availability in October. Minor releases are scheduled quarterly I believe. Major versions are EOL'd after 5 or so years.

Re: Duplicate key error

I just meant a regular update (which might impact 0 rows) and then insert (not exists) like you are doing already. --transaction still ways. Should manual locking used or is there better method. I don't follow what you mean.

Re: Duplicate key error

Why just do a plain update, relying on row level locking to serialize requests properly, and then just do an insert where not exists? Is there value in doing the delete? I don't see it. Note- On conflict clause is supported from 9.5+ and that is already past EOL. Upgrading to at least v10 is recom

Re: Duplicate key error

It sounds like this should be re-written as 'insert on conflict do update' statement.

Re: Batch update million records in prd DB

1) Don't pretend it is a left join when your where clause will turn it into an INNER join. LEFT JOIN pol gp ON gab.policy_id = gp.id WHERE *AND gp.name LIKE 'Mobile backup%' AND gp.deleted_at IS NOT NULL;* 2) It is interesting to me that the ro

Re: Batch update million records in prd DB

It might be a concern, but generally that should be a row level lock and only block other update/delete options on those rows. It might be helpful to look at the explain analyze output early on vs later in the process. It might be that you are getting very few hot updates and indexes are being upda

Re: Server hangs on pg_repack

Why not use reindex concurrently?

  1   2   3   4   >