Re: Is this a reasonable use for advisory locks?

[Michael Lewis]

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?
That way, no need to hold that db connection while doing external work via
api.

>

Re: LwLocks contention

[Michael Lewis]

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, huge number of indexes, etc etc.

There are lots of things to fix, but I just do not yet have a good sense of
the most important thing to address right now to reduce the odds of this
type of traffic jam occurring again. I very much appreciate you sharing
your experience. If I could reliably reproduce the issue or knew what data
points to start collecting going forward, that would at least give me
something to go on, but it feels like I am just waiting for it to happen
again and hope that some bit of information makes itself known that time.

Perhaps I should have posted this to the performance list instead of
general.

LwLocks contention

[Michael Lewis]

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
processes, there will suddenly be ~300 and many show active with LwLock and
they take much longer than usual. Any suggested options to monitor for such
issues or logging to setup so the next issue can be debugged properly?

It has seemed to me that this occurs when there are more than the usual
number of a particular process type and also something that is a bit heavy
in usage of memory/disk. It has happened on various tenant instances and
different application processes as well.

Would/how might the use of huge pages (or transparent huge pages, or OFF)
play into this scenario?

Re: Serializable read only deferrable- implications

[Michael Lewis]

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 SERIALIZABLE READ ONLY DEFERRABLE in terms of impact on logical or
physical replication, autovacuum, etc even if the much heavier OLTP
traffic is still running as the default read committed mode?

If the OLAP queries are moved to a physical read replica, I understand from
this post (
https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/
) that there are chances that a query will be killed on the replica even
with hot_standby_feedback is turned on. With them running on the same
server, is the main concern (other than load) that vacuum type cleanup is
delayed?

Maybe to sum up- If a long running report type query is run in default
"read committed" mode and uses no temp tables / does no writes, would there
be a benefit or change in behavior when using SERIALIZABLE READ ONLY
DEFERRABLE mode?

Re: Indexes in JSONB

[Michael Lewis]

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.

[Michael Lewis]

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

[Michael Lewis]

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?

[Michael Lewis]

>
> 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 SHARE UPDATE
EXCLUSIVE (pretty sure that would be the proper level), then retry if it
fails because a delete is already going on?

Also, are you confident that before you call 'begin' to do the update, you
are not already in a transaction which might have some lock on row(s) in
mach, or one of the other tables involved?

Re: Serializable read only deferrable- implications

[Michael Lewis]

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/transaction-iso.html#XACT-SERIALIZABLE
>
> and
>
>
> https://www.postgresql.org/docs/current/applevel-consistency.html#SERIALIZABLE-CONSISTENCY



Thanks to you both. If other concurrent sessions are using default
isolation level of Read committed, would putting long running reports
(read-only) into that read-only serializable deferrable mode be impactful
at all?

The documentation says that a transaction ID is only assigned to a
connection once a write is done, but is the assignment or not of a txn id
actually impactful on anything? I ask partly because it doesn't seem
possible to reset that once assigned, through discard all; or something
else like that which might be used by a connection pooler such as pg
bouncer. is there any way to check if a session has "done writes/updates up
to this point"? It seems pg_my_temp_schema() also returns the same value
even after 'discard temp' or 'discard all' is executed. That was surprising
to me, but would it be considered an issue by anyone?

Re: Serializable read only deferrable- implications

[Michael Lewis]

>
> 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

[Michael Lewis]

https://www.postgresql.org/docs/14/sql-set-transaction.html

"The DEFERRABLE transaction property has no effect unless the transaction
is also SERIALIZABLE and READ ONLY. When all three of these properties are
selected for a transaction, the transaction may block when first acquiring
its snapshot, after which it is able to run without the normal overhead of
a SERIALIZABLE transaction and without any risk of contributing to or being
canceled by a serialization failure. This mode is well suited for
long-running reports or backups."


Could anyone expound on the above? What are the 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  |  Database Engineer*
*Entrata*

Re: PostgreSQL's Max Identifier Length in Amazon RDS

[Michael Lewis]

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

[Michael Lewis]

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)?

[Michael Lewis]

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 vals (i,v) AS (VALUES
(0,1),(1,0),(2,0),(3,1),(4,0),(5,0),(6,1),(7,1),(8,0),(9,1)),
grouped_vals AS (SELECT *,
case when LAG(v) OVER (ORDER BY i) = v then lag(i) OVER (ORDER BY i) ELSE i
END AS group_index
FROM vals
)
select *, row_number() OVER (PARTITION BY group_index ORDER BY i)
from grouped_vals
where v = 0;

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

[Michael Lewis]

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 respect to nulls and how = is
mostly the same as 'is not distinct from' except for 'select null = null'
being null and 'select null is not distinct from null' being true. Perhaps
if either/both columns have a not null constraint, then this could still be
done. Whether or not it should be a separate question.

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

[Michael Lewis]

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_pkey? If you run a query like this, how far off are the
estimates?


explain analyze
select d.time as time,d.id as id, a.query_symbol as query_symbol

from
data as d
join data_class as dc ON dc.data_id = d.id
join class as a ON dc.class_id = a.id
where
d.id > 205284974
order by d.id
limit 1000;

If you run 'analyze data( id );' and then run the query again, do you get a
better estimate? Have you tried adjusting default_stats_target? Are you
running the default value for random_page_cost with SSDs?

I'm seeing Index Only Scan nodes, but a high number of fetches so it seems
like you would benefit from vacuum to update pg_class.relallvisible value.

Re: pg_cron for vacuum - dynamic table set

[Michael Lewis]

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

[Michael Lewis]

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) (actual time=0.024..0.482 *rows=854 *loops=1)
Index Cond: (id > 205284974)

Re: pg_try_advisory_lock is waiting?

[Michael Lewis]

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 being updated or not is unrelated to advisory locks. Your example
shows you locking on key 0 and then on key 1. That should be allowed. If
you tried key 0 twice, then that is when you would get back "false" from
the function call.

You could establish a pattern of using the table OID as the first lock key
and the primary key value (if a single column) as the second advisory lock
key with the two parameter version of the function. But it is totally up to
your code to honor that advisory lock, or not.

Again, why use advisory locks and not select for update? Perhaps just
because you don't want to deal with the failed transaction? What should
happen when some other process cannot get a lock on that row? Do you want
to wait and retry? Have you looked into the "skip locked" option? If you
use "returning id" with that, you'd be able to check if you got the lock or
not.

Re: pg_try_advisory_lock is waiting?

[Michael Lewis]

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

[Michael Lewis]

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
variables for either to be a definite winner.

Re: Undetected Deadlock

[Michael Lewis]

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

[Michael Lewis]

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

[Michael Lewis]

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?
Materialized views are not maintained automatically, but you have local
statistics and can create indexes. Just a thought in case the data is not
changing constantly and this might fit the need.

Also, it seems like perhaps the foreign queries might be more targeted if
some data was encouraged to be pre-computed. What would be the expected row
count from just table1?

Note- your explain plan doesn't mention "fichero_origen" field name. Is
that just a copy/paste error?


with cte_interesting_t1_rows_precomputed AS materialized(
select
t1.*,
substring(t1.bbb from 1 for 3) in () AND t1.file =
'file_name.csv' AS needs_t2,
substring(t1.bbb from 1 for 3) in () AS needs_t3
FROM
table1 t1
where t1.c = 'ACTIVE'
and t1.fichero_origen = 'file_name.csv'
)

 select t1. as maindb_, t1.bbb as maindb_bbb, t1.c as
maindb_c, t1.timestamp_create as maindb_create,
t1.timestamp_closed as maindb_close, t1.d as maindb_d,
null::text as db1_sth,
t2. as db1_, t2. as db1_, null::text as
db2_sth,
t3. as db2_, t3. as db2_
from cte_interesting_t1_rows_precomputed AS t1
left join database1_fdw.table2 AS t2 on t1. = t2.btatpd_ and
t2.btatpd_fecha = '2022011912' AND needs_t2
left join database2_fdw.table2 AS t3 on t1. = t3.btatpd_ and
t3.btatpd_fecha = '2022011912' AND needs_t3
where
(t2. is null and t3. is null)
or
(t2. is not null and t1.d <> t2.)
or
(t3. is not null and t1.d <> t3.)
order by t1.bbb nulls last;

Re: Default values in functions

[Michael Lewis]

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

[Michael Lewis]

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 array[]::int[].)
>

Understood. My hope is to pass a parameter that gets overridden by the
default so that I can pass other parameters that come after.


> SELECT pTimeToDisplay AT TIME ZONE'UTC' INTO pTimeToDisplay;
>
> This is very unlikely to produce anything sane.


Sorry for that confusion. It was intended as an example of non default
later in the list after a default.

Re: Default values in functions

[Michael Lewis]

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
absence of the third parameter if I need to set a value for the fourth
parameter.

Default values in functions

[Michael Lewis]

I am on PG 13.4 and found some surprising behavior with default values. Can
anyone give insight why the last two cases (especially the last one) do not
give the result I expected? If I uncomment the line to set pArrayToCheck
with coalesce, then it gives the expected results though.

If I can somehow pass "use default for this parameter" to functions like I
can for insert statements, then that would be great to know. Generally, the
use case I am looking at is having a function with a few required
parameters leading and then many optional and I'd like to ensure the
optional ones get the default set if a value is used that "is not distinct
from null" basically. Hopefully the example is clear.


CREATE OR REPLACE FUNCTION public.check_if_input_is_empty_array(
pArrayToCheck integer[] DEFAULT ARRAY[]::integer[], inout pTimeToDisplay
timestamptz DEFAULT CURRENT_DATE::timestamptz, out lReturnValue boolean )
LANGUAGE plpgsql
AS $function$
BEGIN
--pArrayToCheck = COALESCE( pArrayToCheck::integer[],
ARRAY[]::integer[] );

SELECT icount ( pArrayToCheck ) = 0 INTO lReturnValue;
SELECT pTimeToDisplay AT TIME ZONE 'UTC' INTO pTimeToDisplay;

END;
$function$;

select ( check_if_input_is_empty_array() ).*, true as expected_result
UNION ALL
select ( check_if_input_is_empty_array( pArrayToCheck, pTimeToDisplay )
).*, expected_result
from(
values
( ARRAY[]::int[], CURRENT_DATE + interval '1 hour', true ),
( ARRAY[1]::int[], CURRENT_DATE + 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

[Michael Lewis]

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 that are considered in query planning, or would
that be impossible or just out of scope initially?

Re: Postgresql + containerization possible use case

[Michael Lewis]

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 goal is not requiring any (significant) tech support at each location,
right? Unless the data that needs to be stored at each site is small, that
seems a bit unlikely to be a feasible option.

Re: Postgresql + containerization possible use case

[Michael Lewis]

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

[Michael Lewis]

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_import_id if the estimate is saying 5868 out of 29
million should match and then sort and only get the smallest ID. Doing an
index scan on ID and looking up in the table to see if shipment_import_id
matches when the planner expects that to be about a .0001 chance... I can't
imagine that plan performing well at all.

Certainly a composite index would be very helpful here. Using explain
analyze and sharing the output would give more info to go on.

>

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

[Michael Lewis]

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

[Michael Lewis]

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
what was going on during times of very high concurrency and extreme
slowness with many processes showing as waiting on LwLocks.

Re: Max connections reached without max connections reached

[Michael Lewis]

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 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 seen the second attempt at calling begin seem to be ignored with
> "WARNING:  there is already a transaction in progress" similar to calling
> rollback without a transaction open. Are you stating or suggesting that
> calling begin a second time actually is more similar to
> creating a savepoint? That would be rather unexpected from the messaging,
> at least for me.
>
> No.  Hard to know what the devs are up to here (what stack they're using,
> etc), but I'm suggesting one transaction is getting started and not closed.
> Warnings have been know to be ignored.  I admit I am not familiar with
> Kumar's term of "sub-transaction" and how those might be created but it
> sounds to me like a transaction is begun early in the processing and more
> work happens unaware of the transaction.  And possible not committed.
>

Okay cool. Subtransactions are interesting. They are used when a savepoint
is issued, and with exception blocks in PL/pgSQL functions. They allow the
system to keep track of which "sub" process made changes to rows. So just
like the main transaction gets a transaction ID and rows get marked with
xmin or xmax as needed for MVCC, the subtransactions get their own
transaction ID and the objects they modify get updated to set xmin and xmax
with their transaction ID. There are some interesting performance
bottlenecks when many subtransactions get involved, but I am still learning
about that.

Disclaimer- I am no kind of PG contributor and very much still learning,
the above explanation is my best understanding but I welcome any
corrections or clarifications.

Re: Max connections reached without max connections reached

[Michael Lewis]

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 seen the second attempt at calling begin seem to be ignored with
"WARNING:  there is already a transaction in progress" similar to calling
rollback without a transaction open. Are you stating or suggesting that
calling begin a second time actually is more similar to
creating a savepoint? That would be rather unexpected from the messaging,
at least for me.

Re: [EXTERNAL] Re: Inserts and bad performance

[Michael Lewis]

How many partitions? How many rows do they have when performance is slowing
considerably? Does this table get many updates or is it insert only? What
version of PostgreSQL? Are the inserts randomly distributed among the
partitions or targeting one or a few partitions? Are you able to capture an
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

[Michael Lewis]

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

[Michael Lewis]

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.


*Michael Lewis  |  Database Engineer*
*Entrata*

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

[Michael Lewis]

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

[Michael Lewis]

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

[Michael Lewis]

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
defaults on subscribers.

Re: Debugging features needed

[Michael Lewis]

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 using connection pooling. Before, we had different requests reusing
the same pid of course and if they happened to have the same appName, we
didn't know it was a new process on app side.

We also log that same request ID to NewRelic and when an app process needs
to use multiple databases, it links those for research of events after the
fact as well.

I would be interested to hear what others are doing.

Re: Model clause and

[Michael Lewis]

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

[Michael Lewis]

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

[Michael Lewis]

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 assume null is being returned rather
than old if the trigger finds the row to be identical.

Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

[Michael Lewis]

>
>  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

[Michael Lewis]

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 foreign key reference, would a simple
> ordering of the copy based on table relationship suffice?
>

The other concern I would have is having a rolling view of the data in
default read committed mode. If you copy data from a primary table (with
other tables having fkey to that table coming later in the process), then
you may need to use some created_on < script_start_time, else when you try
to insert dependent rows where the record in the primary table did not
exist yet when you started your process and your rows in dependent table
want to reference that primary table row... error.

Or, use REPEATABLE READ, but WAL buildup may be a concern.

Re: Determining if a table really changed in a trigger

[Michael Lewis]

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

[Michael Lewis]

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 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.
>
> No, that is per column.
>

Yes, sorry. My attempt at clarification only muddled things. Each column
within each row can be up to 1GB in size is how I understand the limit.

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

[Michael Lewis]

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 associated with TOAST is only if the value
can fit in line, right? Columns don't get stored out-of-line in a toast
table as a compressed value as I read the documentation. I suppose that
would make reads a bit crazy.

Re: Query time related to limit clause

[Michael Lewis]

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
only the condition below (explain analyze, just looking at estimated vs
actual row counts) since they seem to be rather severe overestimates but
I'm not sure how much the date condition is obscuring that.

(((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->>
'subGridName'::text) = '905811-22_MISCN_data'::text)"

The planner is choosing to use ONLY the index on the created timestamp when
limit is 1 and finding they nearly all match (I hope all on the q2
partition) and needs to filter almost all of those out (all from q1
partition I think, and nearly all from the others). I believe that the
planner thinks the other criteria in the query are not nearly as selective
as they are, and so it thinks it will find 1 match very quickly and be
done. That isn't the case.

When you want more rows, the planner decides that using both indexes is
less costly and it is correct.

Re: DELETE ... USING LATERAL

[Michael Lewis]

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

[Michael Lewis]

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?

[Michael Lewis]

>
> 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 Tom? Maybe at least check
pg_stat_user_tables for the last vacuum or autovacuum timestamp, and how
many records have changed since then. The system is deciding on an index
scan because that should be very fast, but if it needs to look at the
actual table table to determine if the process executing that query should
still be allowed to see that tuple (row version) then the index only scan
slows down a lot I believe. The huge number of heap fetches that Tom
pointed out mean that the system doesn't know that ALL processes should be
able to see those tuples. As someone else suggested lowering the max freeze
age, you might just do a manual "vacuum freeze" as well to allow future
auto vacuum processes to quickly skip over those pages as well.


I've heard of the loose indexscan before mentioned on this thread, but I'm
not seeing how to implement that for multiple columns. Anyone have an
example or pseudo code perhaps?

Re: Faster distinct query?

[Michael Lewis]

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 "index-only" scan is reported to do 86m heap fetches along the
> way to returning 812m rows, so the data is apparently pretty dirty.
>

Do you say that because you would expect many more than 10 tuples per page?

Re: Faster distinct query?

[Michael Lewis]

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 relative few rows after the fact.

select station, array_agg(distinct(channel)) as channels
FROM(
SELECT station,channel FROM data GROUP BY station,channel
) AS sub
group by station;

If there is correlation between station & channel, then you might look at
creating a multivariate statistics object and analyzing the table so the
planner can make better choices, knowing that channel is dependent on
station perhaps. I wouldn't necessarily think that it would help this
query, but perhaps others. Also, you might try creating only dependencies,
only ndistinct type, or some combination other than all 3 types.

ref- https://www.postgresql.org/docs/current/sql-createstatistics.html

Re: Timestamp with vs without time zone.

[Michael Lewis]

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 purposes. That is, if I have a table which
> has events, but those events belong to a tenant or some entity that has a
> location which implies a timezone (or at least an offset), is there a best
> way to write a query similar to the below? Please forgive and overlook if
> there is some obvious syntax error, as this is just a quick and dirty
> example. Might it make sense to store a "localized" version of the
> timestamp *without* timezone on the event record such that an index can be
> used for fast retrieval and even grouping?
>
> select
> date_trunc( 'month', e.event_datetime AT TIMEZONE t.time_zone_name ) AS
> event_date,
> count( e.id )
> from events AS e
> join tenants AS t ON t.id = e.tenant_id
> where e.event_datetime AT TIMEZONE t.time_zone_name >= '01/01/2021'::DATE
> AND e.event_datetime AT TIMEZONE t.time_zone_name < '09/01/2021'::DATE;
>
>
> This is an interesting case. A simplified query example would be to "give
> me all events for this year".
>
> I am not sure what the cost of shifting UTC is, probably not much, but
> depending on use case it might make sense to deconstruct into date and time
> for query optimization.
>

Does that solve anything? My concern is that for the events at the begin
and end of the where (or group by) we may want to include them or exclude
them depending on the time in that location. If I want to know how many
events happen between 8am and 5pm for each location in California and New
York for example, I'm not certain if a second timestamp that is "localized"
should be stored, or if there is some better way to achieve that goal. At
least for some data in the evening, the date value will be
different between UTC and local timezone for that location. Just setting my
session to an east coast equivalent timezone when I pull the data still
doesn't give me "what are all the events that happened in California"
accurately. The goal is that the data is accurate for that location. Not as
much a concern if there are only a few hours apart, but if the user is
running a report across properties all over the world... what's the proper
way to ensure each date means the day for that location?

Re: Question about behavior of conditional indexes

[Michael Lewis]

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
of the row remains in the same page, then the index pointer doesn't have to
be updated until that old version of the row gets vacuumed away. But alas,
when the value in the index changes, then all bets are off. Although, I
suppose in your workflow you might update these rows frequently and NOT
change the status column, then I would certainly consider reducing the
fillfactor, but it will mean perpetual "reserved space" (normally called
bloat though that has a different implication) in the blocks/pages that
only hold old records that won't be getting updates anymore.

If you check pg_stat_user_tables, then you will see autovauum count and can
check it periodically to see how often it is doing work. I'd lower
autovacuum_vacuum_scale_factor and perhaps autovacuum_vacuum_cost_delay
(default was 20ms and lowered to 2ms in PG12) to ensure work is done more
often, and more work is done in each cycle before it pauses to avoid
overloading the I/O system.

>

Re: Timestamp with vs without time zone.

[Michael Lewis]

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
location which implies a timezone (or at least an offset), is there a best
way to write a query similar to the below? Please forgive and overlook if
there is some obvious syntax error, as this is just a quick and dirty
example. Might it make sense to store a "localized" version of the
timestamp *without* timezone on the event record such that an index can be
used for fast retrieval and even grouping?

select
date_trunc( 'month', e.event_datetime AT TIMEZONE t.time_zone_name ) AS
event_date,
count( e.id )
from events AS e
join tenants AS t ON t.id = e.tenant_id
where e.event_datetime AT TIMEZONE t.time_zone_name >= '01/01/2021'::DATE
AND e.event_datetime AT TIMEZONE t.time_zone_name < '09/01/2021'::DATE;

Re: Question about behavior of conditional indexes

[Michael Lewis]

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
for that table's data. It will also update the index, except in newer
versions of Postgres where that behavior becomes optional (see manual for
which version and whether it still runs by default). If you are I/O bound
and cannot tune the system defaults to autovacuum more proactively (when a
smaller percent of rows are dead), then perhaps just change the settings
for that table as it seems to be functioning a bit like a queue. Or you
might consider setting up a daily job to vacuum analyze on all tables, if
there is a period of low system activity. If you didn't have the index on
the columns you are updating, then reducing fillfactor would be an option
to increase HOT updates and thereby prevent the index bloat. Alas, that's
not an option with the index needing to reference those values that are
being changed.

"index002" btree (action_performed, should_still_perform_action,
action_performed_at DESC) WHERE should_still_perform_action = false
AND action_performed = true

That index seems odd to me. Why index a single value for the first two
columns? I would recreate it with those skipped. Also, based on the
names, I'd expect the opposite for true and false. That is, the
"interesting" rows are where the action has NOT yet been performed yet
and it is needed. I'd expect the index as defined to cover most of the
table rather than a small fraction. Perhaps just a typo from manual
obfuscation.

For what it's worth, you can create new concurrently, drop old
concurrently, then rename new to old. That would be the same result as a
reindex concurrently.

>

Re: Azure Postgresql High connection establishment time

[Michael Lewis]

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

>

Re: Fastest option to transfer db?

[Michael Lewis]

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]

Have you ever used this site to visualize the explain plan and spot bad
estimates and slow nodes? https://explain.depesz.com/s/WE1R

This stands out to me-

*Subquery Scan on npiactionjoin (cost=10,165,289.40..10,167,192.01 rows=293
width=16) (actual time=118,413.432..118,806.684 rows=446,782
loops=1)Filter: (npiactionjoin.rn = 1)*

It seems that estimate is pretty far off and this node and the final node
above this are the biggest slowdowns. If you filtered down to the record
you want from task_history BEFORE the join, then maybe you would have
quicker results. I might try a materialized CTE or even an analyzed temp
table if that option is available to you, so the planner makes informed
decisions.

By the way, the order by on that row_number seems like you are getting the
OLDEST activity related to the task which could maybe be cached rather than
re-calculated daily as this query runs.


*Michael Lewis  |  Database Engineer*
*Entrata*

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

[Michael Lewis]

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

[Michael Lewis]

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 scan. I don't know GIN indexes super well, but I would guess that
including words that are very common will yield false positives that get
filtered out later.

>

Re: string_agg distinct order by

[Michael Lewis]

I believe that you could define an enumerated type to use for those status
colors such that the ordering is defined as you like without two separate
columns for the name and sort_value or whatever.


https://www.postgresql.org/docs/current/datatype-enum.html

Example in the documentation expanded a little to demonstrate-

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (
name text,
current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
INSERT INTO person VALUES ('Joe', 'sad');
INSERT 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

[Michael Lewis]

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

Re: Regexp_replace bug / does not terminate on long strings

[Michael Lewis]

/20,200/10,200/40,200/50,200/7,200/25,200/200,240/15,250/20,250/50,250/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.

*Michael Lewis  |  Database Engineer*
*Entrata*


On Thu, Aug 19, 2021 at 4:42 PM Tom Lane  wrote:

> "Markhof, Ingolf"  writes:
> > BRIEF:
> > regexp_replace(source,pattern,replacement,flags) needs very (!) long to
> > complete or does not complete at all (?!) for big input strings (a few k
> > characters). (Oracle SQL completes the same in a few ms)
>
> Regexps containing backrefs are inherently hard --- every engine has
> strengths and weaknesses.  I doubt it'd be hard to find cases where
> our engine is orders of magnitude faster than Oracle's; but you've
> hit on a case where the opposite is true.
>
> The core of the problem is that it's hard to tell how much of the
> string could be matched by the (,\1)* subpattern.  In principle, *all*
> of the remaining string could be, if it were N repetitions of the
> initial word.  Or it could be N-1 repetitions followed by one other
> word, and so on.  The difficulty is that since our engine guarantees
> to find the longest feasible match, it tries these options from
> longest to shortest.  Usually the actual match (if any) will be pretty
> short, so that you have O(N) wasted work per word, making the runtime
> at least O(N^2).
>
> I think your best bet is to not try to eliminate multiple duplicates
> at a time.  Get rid of one dup at a time, say by
>  str := regexp_replace(str, '([^,]+)(,\1)?($|,)', '\1\3', 'g');
> and repeat till the string doesn't get any shorter.
>
> I did come across a performance bug [1] while poking at this, but
> alas fixing it doesn't move the needle very much for this example.
>
> regards, tom lane
>
> [1]
> https://www.postgresql.org/message-id/1808998.1629412269%40sss.pgh.pa.us
>
>
>

Re: Using a TRIGGER with window functions.

[Michael Lewis]

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.sort_order IS NOT DISTINCT FROM old.sort_order to prevent an infinite
loop, and executing a different function when NEW.sort_order IS DISTINCT
FROM OLD.sort_order such that you ensure all other fields have not changed.

By the way, your window function could be row_number() - 1 instead of
activity_id - FIRST_VALUE(activity_id).

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

[Michael Lewis]

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

[Michael Lewis]

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

[Michael Lewis]

((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 result on an analyzed temp table at least, I'd expect
that the planner would have enough info to make good decisions.

Re: On partitioning, PKs and FKs

[Michael Lewis]

Why are you using hash partitioning?

Re: EXPLAIN with anonymous DO block?

[Michael Lewis]

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?

[Michael Lewis]

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 sampling rate would matter at all if you are reading the
entire set of data.

What version?

>

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

[Michael Lewis]

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 fairly low row count to be partitioned. What
version is this on?

Re: Use case stuck due to Partitioning

[Michael Lewis]

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.589'.

However, if modified date is not null, and is set to the created_date by
default, then there seems no reason to check the created_date except if
partition pruning at plan time is very important.

Are you experiencing an actual performance problem, are you unsure how to
write the query for the data you want, or is this premature optimization?

Re: Use case stuck due to Partitioning

[Michael Lewis]

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

[Michael Lewis]

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

[Michael Lewis]

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 index). Note that increasing
> it increases the costs of maintaining statistics (so you don't want to
> increase this by several orders of magnitude blindly) but the default value
> seems fairly conservative to me.
>

It also increases planning time since those distribution statistics need to
be consumed and decisions have to be made.

Re: ON CONFLICT DO NOTHING ignored on bulk insert

[Michael Lewis]

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 remaining rows.

Re: trigger impacting insertion of records

[Michael Lewis]

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.

CREATE OR REPLACE FUNCTION bonzipay.ussf_accountnumber_update()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$ BEGIN
if( left(NEW.accountnumber,3) = 'US_' ) then
NEW.accountnumber=replace(accountnumber,'_',' ');
RETURN NEW; END; $function$

Re: index only scan taking longer to run

[Michael Lewis]

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 often a bad code smell for me with
expecting the system to sort data and remove duplicates, when I should have
controlled my set through proper joins.

Also, when I see a join condition on the result of a subquery, I question
the data structure. re:
bravo_lima = ( SELECT max(bravo_lima) FROM five_lima foxtrot_four ...)

Re: BRIN index on timestamptz

[Michael Lewis]

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 updates and deletes and
> new tuples (inserts and updates) come in and fill in those gaps left behind
> in early pages even though timestamp is high, then correlation will go down
> and brin will no longer be a good fit.
>
> Note- timestamp *with* timezone is recommended.
>
> The timestamptz isn't deleted or updated. It is only inserted. Another
> ENUM column will be updated.
> It looks like I should use brin. We also have other history tables like
> this.
>
> Thanks.
>

That's not a correct conclusion. Reply all btw.

Updating any value in the row means a new version of the row is inserted
and old one is marked expired and will be cleaned up by vacuum after no
transactions might need that row version (tuple). Research a bit about how
MVCC is implemented in Postgres.

If those updates would only be on recent rows and autovacuum is tuned
aggressively to keep the maintenance task under control, then the
correlation may remain high as only recent rows are being updated. If the
updates go back a long ways, then BRIN is unlikely to be a good fit. Maybe
it still could be if table fillfactor is lowered a bit and the enum is not
indexed so you get mostly HOT (heap only tuple) updates, but I wouldn't
count on it.

Why not use a btree index for the timestamptz column?

Re: BRIN index on timestamptz

[Michael Lewis]

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.03
> rows=12403 width=0) (actual time=0.141..0.141 rows=0 loops=1)"
> "Index Cond: ((date >= '2019-08-08 14:40:47.974791'::timestamp
> without time zone) AND (date <= '2019-08-08 14:50:47.974791'::timestamp
> without time zone))"
> "Planning Time: 0.126 ms"
> "Execution Time: 0.161 ms"
>

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 updates and deletes and
new tuples (inserts and updates) come in and fill in those gaps left behind
in early pages even though timestamp is high, then correlation will go down
and brin will no longer be a good fit.

Note- timestamp *with* timezone is recommended.

>

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

[Michael Lewis]

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?

[Michael Lewis]

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
want this-

select sensor_id, date_trunc( 'minute', timestamptz_field_name_here ),
last( value_from_hstore ) over ( partition by sensor_id, date_trunc(
'minute', timestamptz_field_name_here ) ) as last_value_recorded
from data_table_here
where timestamptz_field_name_here BETWEEN NOW() - interval '1 hour' and
NOW()
group by sensor_id, date_trunc( 'minute', timestamptz_field_name_here )


You could also use the left join "where is null" pattern to check that a
prior record in the minute period of time does not exist for that same key.
Something like this-

select d1.sensor_id, date_trunc( 'minute', d1.timestamptz_field_name_here
), d1.timestamptz_field_name_here as last_value_recorded
from data_table_here as d1
left join data_table_here as prior_d1 ON prior_d1.sensor_id = d1.sensor_id
AND prior_d1.timestamptz_field_name_here < d1.timestamptz_field_name_here
and prior_d1.timestamptz_field_name_here >= date_trunc( 'minute',
d1.timestamptz_field_name_here )
where d1.timestamptz_field_name_here BETWEEN NOW() - interval '1 hour' and
NOW()

Re: Slick way to update multiple tables.

[Michael Lewis]

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.

[Michael Lewis]

Joins are allowed though.

Re: Slick way to update multiple tables.

[Michael Lewis]

You can have an updatable view.

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

[Michael Lewis]

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
> distributed service
> is received we need them and in such cases we have to join using them.
>

I haven't used them so I don't recall exactly, but I believe there is a
type of UUID generation which has some leading correlation to time which
would help with reducing the random I/O issue that Tom Lane mentioned. A
quick search of the archive may lead you to that, or someone else may chime
in with the name I expect.


> But for local data we can identify another composite unique key. Does
> PostgreSql
> create a unique index for us ? What about a FK that references this
> composite
> unique key ? Does it create a FK index ?
>

It is up to you to create whichever fkeys and indexes you require.

Re: MultiXactId wraparound and last aggressive vacuum time

[Michael Lewis]

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 added benefit, index only scans would be more likely to
be chosen since the visibility map would be updated and the regular
autovacuum runs would be faster since they can skip frozen pages. I think
that applies back to 9.6 at least. It's possible it got implemented in 10.

My caffeine hasn't fully kicked in yet and I am still a bit junior on this
list, but I would hope and expect senior people to correct me if I have
misunderstood or misrepresented things.

>

Re: Question about when PostgreSQL 11.0 was released

[Michael Lewis]

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

[Michael Lewis]

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

[Michael Lewis]

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 recommended.

Re: Duplicate key error

[Michael Lewis]

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

Re: Batch update million records in prd DB

[Michael Lewis]

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 row estimates are stable, but the
number of rows filtered out and that are found by those two index
scans changes so dramatically. Is your underlying data changing
significantly during this run? Maybe I am not seeing something that should
be obvious.

3) What is the execution plan for the update based on the temp table? It is
hard to believe it takes 2 seconds to update 1000 rows. By the way, that
temp table needs to be analyzed after it is created & populated with data,
or the planner won't know how many rows it contains or any other stats
about it. One advantage of the temp table should be that you have already
found all the candidate rows and so the time that locks are held to update
the 1000 target rows is smaller. Given you are doing a order by & limit in
the use of the temp table, I might actually create an index on the id
column to help the later runs. The temp table should likely remain in
memory (temp_buffers) but still, btree is nice for ordered use.

>

Re: Batch update million records in prd DB

[Michael Lewis]

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
updated constantly.

>

Re: Server hangs on pg_repack

[Michael Lewis]

Why not use reindex concurrently?