On Wed, Mar 18, 2020, 12:55 PM Michael Lewis wrote:
> work_type.work_type_key = work_type.work_type_key
>
> You've done a cross join.
>
You meant to do permitted_work.work_type_key = work_type.work_type_key I
expect
>
of that. This would mean that we are
either missing the generation of some .ready file, or that some .done
file gets generated when they should not in archive_status/. What
kind of server shutdown are you doing? Immediate so as recovery
happens at the follow-up startup. Or is that a
6a367c382d0a3595238eff2e777222dbc91911b
author: Tom Lane
date: Thu, 10 Oct 2019 14:24:56 -0400
Put back pqsignal() as an exported libpq symbol.
Here is the thread of the discussion:
https://www.postgresql.org/message-id/e1g5vmt-0003k1...@gemulon.postgresql.org
--
Michael
signature.asc
Description: PGP signature
On Thu, Mar 19, 2020 at 9:31 AM Justin King wrote:
> On Wed, Mar 18, 2020 at 1:40 PM Michael Lewis wrote:
> >
> > Do you have default fillfactor set on this table? If not, I would wonder
> if reducing it to 50% or even 20% would allow many more HOT updates that
> would redu
On Thu, Mar 19, 2020, 5:48 PM David G. Johnston
wrote:
> However, one other consideration with sequences: do you care that
> PostgreSQL will cache/pin (i.e., no release) every single sequence you
> touch for the lifetime of the session? (I do not think DISCARD matters here
> but I'm just guessing
>
> We haven't isolated *which* table it is blocked on (assuming it is),
> but all autovac's cease running until we manually intervene.
>
> When we get into this state again, is there some other information
> (other than what is in pg_stat_statement or pg_stat_activity) that
> would be useful for f
swordcheck_extra
> (I seem to recall some recent discussion about deprecating/removing
> passwordcheck altogether, but I can't find it right now.)
This was mentioned last here if I recall correctly:
https://www.postgresql.org/message-id/ac785d69-41ec-4d0a-ac37-1f9ff55c9...@amazon.com
>
> select * from test where id between client_id-10 and client_id+10 and
> client_id=?;
>
> does not (it scans all partitions in parallel) .
> Is it expected?
>
Yes. But the below would work fine I expect since the planner would know a
constant range for id. I would be very surprised if the opti
bench. I'm not sure
>> if we'll be able to catch the 1h45 interval when the system stays alive
>> after the issue though.
>
> Could you just script something to stop the benchmark once the disk is
> 90% full or so?
Hmm. I think that this one is possible still tricky. There are some
alarms in place in this system.
> Did you see any errors / fatals around the time autovacuum stopped
> working?
Before going rogue (we are not sure if autovacuum didn't launch any
workers or if the workers were spawned and exited early as we did not
capture any worker information in pg_stat_activity), we saw a bunch of
aggressive wraparound jobs. Even after that, we have traces in the
logs of one autovacuum analyze happening at equal interval of time (17
minutes) on one single table, which is... Er... uncommon to say the
least.
--
Michael
signature.asc
Description: PGP signature
On Mon, Mar 23, 2020 at 10:40:39PM -0700, Andres Freund wrote:
> On 2020-03-24 14:26:06 +0900, Michael Paquier wrote:
>> Nothing really fancy:
>> - autovacuum_vacuum_cost_delay to 2ms (default of v12, but we used it
>> in v11 as well).
>> - autovacuum_naptime = 15s
>
Chris,
Does it actually need to be a different server and database, or would it be
possible to have another storage device added to your existing database and
make use of tablespaces to accomplish pseudo-archive of older partitions?
Just a thought.
>
ch are mentioned in the logs
of the type "skipping redundant vacuum to prevent of table"?
> Let me know if there's anything else useful I can provide.
Thanks!
--
Michael
signature.asc
Description: PGP signature
ually what I was thinking yesterday. In
heap_vacuum_rel(), xidFullScanLimit may be calculated right, but an
incorrect value of rd_rel->relminmxid or rd_rel->relfrozenxid could
lead to a job to become not aggressive. It should be actually easy
enough to check that.
--
Michael
signature.asc
Description: PGP signature
hose anti-wraparound and
non-aggressive jobs (this looks like a relcache issue with the so-said
catalogs).
--
Michael
signature.asc
Description: PGP signature
wasn't the only one seeing the issue!
Yes. We have been able to confirm that 2aa6e33 is a direct cause of
your problem.
--
Michael
signature.asc
Description: PGP signature
ons with the revert though, this will address
the problem reported by Justin.
--
Michael
signature.asc
Description: PGP signature
e begun on
-hackers a couple of days ago.
--
Michael
signature.asc
Description: PGP signature
On Sat, Mar 28, 2020 at 05:53:59PM +0900, Michael Paquier wrote:
> And I'll follow up there with anything new I find. Please let me know
> if there are any objections with the revert though, this will address
> the problem reported by Justin.
Okay. Done with this part now as of
If you didn't turn it off, you have parallel workers on by default with
v12. If work_mem is set high, memory use may be much higher as each node in
a complex plan could end up executing in parallel.
Also, do you use a connection pooler such as pgbouncer or pgpool? What is
max_connections set to?
tgres.
There are however multiple ways to solve this problem, like a
background worker (for the slot monitoring as well as optionally
killing and/or dropping), a simple cron job or even check_postgres.
--
Michael
signature.asc
Description: PGP signature
There is nothing native to compute this. This was asked a month or so ago.
The best the poster came up with was a regular query on master DB which
updates a timestamptz field. Assuming master and slave have clocks in sync,
it is simple to compute the lag from that.
>
lled with most of the hot data).
--
Michael
signature.asc
Description: PGP signature
there is also a version on out of core for older versions of
Postgres: https://github.com/credativ/pg_checksums. On apt-based
distributions like Debian, this stuff is under the package
postgresql-12-pg-checksums.
--
Michael
signature.asc
Description: PGP signature
than it is, no? There is a copy of the file so we may be
able to do a block-to-block copy and update of the checksum, but you
cannot do that with the --link mode.
--
Michael
signature.asc
Description: PGP signature
Yes, the system will do a full table rewrite to compute the value and store
it. Unfortunately, I believe it is an access exclusive lock during that
entire time.
n lclContext or equivalent as that's an
important piece of the error message.
--
Michael
signature.asc
Description: PGP signature
You don't want recursion, you want pivot table (Excel) behavior to reformat
rows into columns. The easiest way to get this data in its raw form would
be to group by date and hour of day and compute the count.
If you have the option to add extensions in your environment, then you
should be able to
>
> My other thought was to range partition by pixelID + brin index.
>>>
I would expect brin index to be INSTEAD of partitioning. You didn't share
buffer hits, which I expect were 100% on the subsequent explain analyze
runs, but the index scan may still be faster if the planner knows it only
n
the error message provided is clear.
> This was in part what led to that long blog article I wrote about
> checksums, and it's why enabling checksums was happiness hint #1 until I
> broke them into categories.
Reference? ;p
--
Michael
signature.asc
Description: PGP signature
You say 12.2 is in testing but what are you using now? Have you tuned
configs much? Would you be able to implement partitioning such that your
deletes become truncates or simply a detaching of the old partition?
Generally if you are doing a vacuum full, you perhaps need to tune
autovacuum to be mor
drbdsetup allows you to control the sync rates.
On Tue, Apr 21, 2020 at 14:30 Kevin Brannen wrote:
> I have an unusual need: I need Pg to slow down. I know, we all want our
> DB to go faster, but in this case it's speed is working against me in 1
> area.
>
>
>
> We have systems that are geo-red
On Tue, Apr 21, 2020 at 15:05 Kevin Brannen wrote:
> *From:* Michael Lewis
>
> > You say 12.2 is in testing but what are you using now? Have you tuned
> configs much? Would you be able to implement partitioning such that your
> deletes become truncates or simply a detaching of
Reviewing pg_stat_user_tables will give you an idea of how often autovacuum
is cleaning up those tables that "need" that vacuum full on a quarterly
basis. You can tune individual tables to have a lower threshold ratio of
dead tuples so the system isn't waiting until you have 20% dead rows before
va
What do the statistics look like for an example table that the index I used
vs not? Is ((instance_id)::text = 'test01'::text) rare for the tables where
an index scan is happening and common for the tables where a sequential
scan is chosen? How many rows in these tables generally?
If you use a connection pooler, this would likely be expected behavior
since the connection is getting reused many times. Else, some app is
connected and not closing their connection between queries. At least they
aren't idle in transaction though.
cluster that has just been freshly
promoted: when connecting to the server libpq may see the connection
as read-only but if the standby gets promoted that could become
incorrect.
--
Michael
signature.asc
Description: PGP signature
Indexes larger than the table may be expected if there are many. It may be
prudent to check if they are being used in pg_stat_all_indexes.
If there are just a few indexes that are becoming bloated quickly, you'd
want to ensure your autovacuum settings are tuned more aggressively, and
consider lowe
https://www.postgresql.org/docs/release/11.7/
It doesn't seem like it. Always best to run the most current minor version
though.
https://www.postgresql.org/docs/11/runtime-config-replication.html
>
On Thu, May 7, 2020 at 8:50 AM Ashish Chugh <
ashish.ch...@lavainternational.in> wrote
> To improve performance and release index space from database, We are
> running FULL Vacuum on monthly basis.
>
> On PostgreSQL website it is not recommended to run FULL Vacuum on
> Production Database and this
It is trying to do a vacuum freeze. Do you have autovacuum turned off? Any
settings changed from default related to autovacuum?
https://www.postgresql.org/docs/9.6/routine-vacuuming.html
Read 24.1.5. Preventing Transaction ID Wraparound Failures
These may also be of help-
https://info.crunchydata
autovacuum_naptime being only 5 seconds seems too frequent. A lock_timeout
might be 1-5 seconds depending on your system. Usually, DDL can fail and
wait a little time rather than lock the table for minutes and have all
reads back up behind the DDL.
Given you have autovacuum_vacuum_cost_limit set t
k here:
https://lists.postgresql.org/
--
Michael
signature.asc
Description: PGP signature
The documentation shows it is just a modulus operation. If you partition on
object_key % 3 then you will create three partitions for remainder values
0-2 for instance.
Afaik, hash partition doesn't have real world expected use cases just yet.
List or range is probably what you want to use.
On Mon, May 11, 2020 at 3:13 PM Alvaro Herrera
wrote:
> On 2020-May-11, Michael Lewis wrote:
>
> > Afaik, hash partition doesn't have real world expected use cases just
> yet.
>
> I don't think I agree with this assertion.
>
I didn't mean to be critical a
is set in the
target cluster's configuration. This way, you can fetch missing WAL
segments from archives during the rewind operation without the need to
rely on wal_keep_segments or such.
--
Michael
signature.asc
Description: PGP signature
ur configuration, we may be able
to help, but it is not really possible to help out without more
details. For example, the first sentence of your first email mentions
the use of replication slots. You may want to explain better where
the slots are used, how they get either dropped and/or recre
can be much cheaper than a toasted field, as the
latter would update/read the value as a whole.
--
Michael
signature.asc
Description: PGP signature
mm select generate_series(1, 1000);
> INSERT 0 1000
>
> I can get the log for "prepared command" only, but nothing was found for
> the insert statement. what should I do?
Because in your previous sequence you inserted the data after
preparing the transaction and they are par
On Thu, May 14, 2020 at 2:20 PM Eduard Rozenberg
wrote:
> I did verify postgresql.conf has always been properly configured re:
> autovacuum: 'autovacuum = on'and 'track_counts = on'
>
This may be insufficient to keep up if you have large tables. The default
scale factor allows for 20% of the ro
Just wonder, have you compared these on the two servers?
select * from pg_settings where name = 'DateStyle';
On Fri, May 15, 2020 at 12:51 PM Ravi Krishna
wrote:
>
> Why should the backup land in S3, and not local somewhere?
> Any good reason why one should pay for the additional storage and transfer
> costs?
>
> Good question. The key point in my statement was "db of this size".
>
> The problem with lo
On Sat, May 16, 2020 at 10:19 AM Alex Magnum wrote:
> Hi,
>
> I have a string that I want to cut to 60 char and then remove the last
> field and comma.
>
> substring('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class
> X,Class XI,Class IX,Class XII',1,60);
>
> substring | Class V,Class
What does pg_stats say about column customer_id? Specifically, how many
ndistinct, and what is the sum of the most common values? If you have 1000
distinct customer_id values, and the (default 100) most common values only
cover 2% of the total rows, then the optimizer will assume that any given
cus
a check on the size, which would fail
if the WAL segment is still compressed. This logic is in
RestoreArchivedFile() in xlogarchive.c.
--
Michael
signature.asc
Description: PGP signature
if the page is
compressed with pglz or not. Then WAL replay looks at those flags,
and determines what to do by itself when the page image is needed.
--
Michael
signature.asc
Description: PGP signature
On Thu, May 21, 2020 at 11:41 AM Adam Brusselback
wrote:
> As an optimization I just worked on for my database earlier this week, I
> decided to logically replicate that table from my main authentication
> database into a each cluster, and I replaced all references to the FDW for
> read-only quer
You might find Materialize interesting:
https://materialize.io/
https://youtu.be/zWSdkGq1XWk
On Thu, May 21, 2020 at 10:36 AM Rory Campbell-Lange <
r...@campbell-lange.net> wrote:
> We have quite a few databases of type a and many of type b in a cluster.
> Both a and b types are fairly complex
Your indexes and operators are not compatible. You have added a btree index
on md5 function result and are not using md5 in your query, and also using
LIKE operator not one of the supported ones. I believe it might use a btree
operator (plain value, not md5 result) if you are always searching for
"
On Fri, May 22, 2020 at 2:09 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Friday, May 22, 2020, postgann2020 s wrote:
>
>
>>
>> We are looking for a better query than "*SELECT 1 FROM
>> schema.table_name WHERE column1=structure_id1*" this query for data
>> validation.
>>
>
If
On Fri, May 22, 2020 at 7:27 AM Greg Nolle
wrote:
> The crux seems to be that test_b does not have an even distribution for
> a_id values: it only has records for two of the values in the referenced
> table. This is how our real dataset is too and isn’t something we can
> really change.
>
How do
I believe something like this is what you want. You might be able to do it
without a sub-query by comparing the current name value to the lag value
and null it out if it's the same.
select
case when row_number = 1 then id end AS id,
case when row_number = 1 then name end as name,
phone.number
from
r without any platform, architecture or even
not-too-many major version constraints, there is also logical
replication available since v10.
--
Michael
signature.asc
Description: PGP signature
hat can become useful:
https://www.postgresql.org/docs/devel/logical-replication.html
--
Michael
signature.asc
Description: PGP signature
not show me any leaks similar to what you have here
after testing on HEAD and REL_11_STABLE. It is likely possible that
we are missing something though, so could you send a test case to
reproduce what you are seeing?
--
Michael
signature.asc
Description: PGP signature
I spent about 10 years as an Oracle DBA (back around Oracle 7 and 8) and
the last 20 or so years doing PostgreSQL.
My initial impressions were that Oracle did a better job providing tools
and options that users and DBAs need and PostgreSQL was pretty much
roll-your-own.
Things like being able to
Recently I was typing in a query in PG 10.4.
What I MEANT to type was: Where xyz >= 2400
What I actually typed was: Where xyz >- 2400
The latter was interpreted as 'where xyz > -2400', but I'm wondering if it
shouldn't have thrown an error on an unrecognized operator '>-'
Thoughts?
--
Mike N
On Wed, Jun 3, 2020 at 5:21 PM Martin Mueller <
martinmuel...@northwestern.edu> wrote:
> On the topic of what other databases do better: I much prefer Postgres to
> Mysql because it has better string functions and better as well as very
> courteous error messages.
>
Martin, I definitely sympathiz
Those row estimates are pretty far off.
Standard indexes and partial indexes don't get custom statistics created on
them, but functional indexes do. I wonder if a small function needs_backup(
shouldbebackedup, backupperformed ) and an index created on that function
would nicely alleviate the pain.
able to detect any leaks. And I am
afraid that it is hard to act on this report without more information.
--
Michael
signature.asc
Description: PGP signature
On Mon, Jun 8, 2020 at 2:34 PM Koen De Groote wrote:
> So, this query:
>
> select * from item where shouldbebackedup=true and
> itemCreated<='2020-06-05 00:00:00.000' and backupperformed=false order by
> filepath asc, id asc limit 100 offset 10400;
>
> Was made into a function:
>
> create or repl
On Tue, Jun 9, 2020 at 12:34 PM Sebastian Dressler
wrote:
> - Add an index on top of the whole PK
> - Add indexes onto other columns trying to help the JOIN
> - Add additional statistics on two related columns
>
> Another idea I had was to make use of generated columns and hash the PKs
> together
On Tue, Jun 9, 2020 at 8:35 AM Ishan Joshi wrote:
> I have using postgresql server v12.2 on CentOS Linux release 7.3.1611
> (Core).
>
>
>
> My application is working fine with non partition tables but recently we
> are trying to adopt partition table on few of application tables.
>
> So we have
>
> the join selectivity functions have yet to learn about extended statistics.
>
That is very interesting to me. So, extended statistics would help to
properly estimate the result set coming out of a single table when
comparing each of those columns to one or many values, but not when joining
up
I don't know if it would be relevant to this problem, but you are missing
almost 1 full year of bug fixes. 11.4 was released on 20 June last year.
Upgrading minor versions asap is recommended.
I do see this in the release notes from 11.8 last month (
https://www.postgresql.org/docs/release/11.8/)-
On Wed, Jun 10, 2020 at 12:05 AM Ishan Joshi wrote:
> How many rows did these tables have before partitioning? à We starts
> test with 0 rows in partition table.
>
Partitions are far from free and pruning is great but not guaranteed. How
many total rows do you currently have or foresee hav
ven simpler now that we just need update
scripts when bumping a module's version (those named foo--1.0--1.1.sql
and not foo--1.1.sql). Here is the related documentation:
https://www.postgresql.org/docs/devel/extend-extensions.html#id-1.8.3.20.15
--
Michael
signature.asc
Description: PGP signature
On Tue, Jun 9, 2020 at 6:24 AM Koen De Groote wrote:
> Right. In that case, the function I ended up with is this:
>
> create or replace function still_needs_backup(bool, bool)
> returns BOOLEAN as $$
> BEGIN
> PERFORM 1 from item where shouldbebackedup=$1 and backupperformed=$2;
> IF FO
On Tue, Jun 16, 2020, 4:52 AM Eugene Pazhitnov wrote:
> xbox=> \d herostat
>Table "public.herostat"
> Indexes:
> "herostat_pkey" PRIMARY KEY, btree (xuid, titleid, heroid) INCLUDE
> (valfloat)
>
> WARNING: relation "public.herostat" must have a primary key or not-null
> un
On Tue, Jun 16, 2020 at 10:01 AM Jim Hurne wrote:
> Other than the increasing elapsed times for the autovacuum, we don't see
> any other indication in the logs of a problem (no error messages, etc).
>
> We're currently using PostgreSQL version 10.10. Our service is JVM-based
> and we're using the
On Tue, Jun 16, 2020 at 1:45 PM Jim Hurne wrote:
> Thanks Michael,
>
> Here are our current autovacuum settings:
>
> autovacuum | on
> autovacuum_analyze_scale_factor | 0.1
> autovacuum_analyze_threshold| 50
> a
>
> I spoke too soon. While this worked fine when there were no indexes
> and finished within 10 minutes, with GIN index on the jsonb column, it
> is taking hours and still not completing.
>
It is always recommended to create indexes AFTER loading data. Sometimes it
can be faster to drop all index
CA3FD9B0%40AM5PR0901MB1587.eurprd09.prod.outlook.com
--
Michael
signature.asc
Description: PGP signature
On Sun, Jun 21, 2020 at 10:43 PM Sankar P
wrote:
> I have a table with the schema:
>
> CREATE TABLE fluent (id BIGSERIAL, record JSONB);
>
> Then I created a couple of indexes:
> 1) CREATE INDEX idx_records ON fluent USING GIN (record);
>
What about using non-default jsonb_path_ops?
> 2) CREA
>
> In the example of "select distinct expression", the planner will never
> notice
> that that expression has anything to do with an index.
>
Thanks for that explanation. I assume re-writing as a 'group by' would have
no bearing on that planner decision.
On Tue, Jun 16, 2020 at 2:41 PM Michael Lewis wrote:
> On Tue, Jun 16, 2020 at 1:45 PM Jim Hurne wrote:
>
>> Thanks Michael,
>>
>> Here are our current autovacuum settings:
>>
>> autovacuum | on
>>
>
> > > On 23/06/2020 14:42, Klaudie Willis wrote:
> > >
> > > > I got my first hint of why this problem occurs when I looked at the
> > > > statistics. For the column in question, "instrument_ref" the
> > > > statistics claimed it to be:
> > > > The default_statistics_target=500, and analyze has
>
> >Are you updating *every* row in the table?
>>
>> No I am using an update like so: UPDATE members SET regdate='2038-01-18'
>> WHERE regdate='2020-07-07'
>>
>> DB=# select count(*) from members where regdate = '2020-07-07';
>>
>> count
>>
>> --
>>
>> 17333090
>>
>> (1 row)
>>
>>
Just u
On Tue, Jun 23, 2020 at 2:34 PM Jim Hurne wrote:
> Sure! Below are more of the details from the same set of logs. Looking at
> them myself, I see that there is always some percentage of tuples that are
> dead but are not yet removable. And that number increases on every vacuum,
> which might expl
On Tue, Jun 23, 2020 at 2:29 PM Bee.Lists wrote:
> I have an issue with a server (v10) that’s seeing increasing connections
> until it’s maxxed-out.
>
> max_connections for my 4-core server is set to 12.
>
> I’ve installed pg_stat_activity and pg_stat_statements.
>
Do you see anything in pg_stat
>
> But how can I set the edited vale = 1 on the objects (line segments) that
> are not deleted (in the current buffer) at the same time so it won’t be
> deleted in the next run with an adjacent buffer?
>
You might want to create a temporary table to hold unique identifiers of
all records that you
On Wed, Jun 24, 2020, 2:35 PM Peter J. Holzer wrote:
> Yes, estimating the number of distinct values from a relatively small
> sample is hard when you don't know the underlying distribution. It might
> be possible to analyze the sample to find the distribution and get a
> better estimate. But I'm
On Thu, Jun 25, 2020 at 7:27 AM Pavel Luzanov
wrote:
> I have tried to increase the statistics target to 5000, and it helps, but
> it reduces the error to 100X. Still crazy high.
>
>
> As far as I know, increasing default_statistics_target will not help. [1]
>
> I have considered these fixes:
>
Sorry, I don't know much about postgis at all. I assume you meant to have
THEN 1 in your update statement as well.
I notice b.fid=l.fid and NOT b.fid=l.fid in the two clauses. How about
separate update statements?
UPDATE linesegments l
set edited = 1
WHERE l.gid IN (SELECT li.gid FROM linesegmen
Per the release notes, there are some enhancements to logical replication
that came after 11.5 like 11.8 particularly related to replication identity
full. Do you have a primary key or unique index that is being used for the
replication identity?
Is now() computed at the time the view is defined and not at refresh? If
this were a function, I would be more suspicious of that but a plain view,
surely not. I hope.
>
On Mon, Jul 6, 2020 at 5:37 AM Robins Tharakan wrote:
> This need came up while reviewing generated SQL, where the need was to
> return true when
> at least one of two lists had a row.
>
Generated SQL... yep. That will happen. Manual SQL may be more work, but
often has significant reward.
If yo
Does this give the same result and do the optimization you want?
select
c1,
min(c2) AS c2,
min(c3) AS c3,
min(c4) AS c4
from
t
group by
c1;
>
Distinct is a great way to get quick results when writing quick &
dirty queries, but I rarely have them perform better than a re-write that
avoids the need. It collects a ton of results, orders them, and throws away
duplicates in the process. I don't love the idea of that extra work. Did
you say yo
On Monday, July 6, 2020, Michael Lewis wrote:
> Did you say you have an index on c1?
> [...]
> I don't know the data, but I assume there may be many rows with the same
> c1 value, so then you would likely benefit from getting that distinct set
> first like below as your
201 - 300 of 889 matches
Mail list logo
