Re: EXPLAIN BUFFERS: dirtied

Thanks!

session_replication_role meaning?

Hi all, now this should be trivial, but I cannot udnerstand what is the purpose of session_replication_role or better, when I should use it in a way different from 'origin'. I've a logical replicatio

Re: Meaning of query age in pg_stat_activity

On Mon, Jan 29, 2018 at 9:52 PM, Nikolay Samokhvalov wrote: > > > On Mon, Jan 29, 2018 at 3:45 PM, Jan De Moerloose > wrote: > >> So the query is just the latest query and the time is the transaction >> time since this query, i suppose ? >> Thanks for your answer, i will try to make the transact

Re: Meaning of query age in pg_stat_activity

On Mon, Jan 29, 2018 at 3:45 PM, Jan De Moerloose wrote: > So the query is just the latest query and the time is the transaction time > since this query, i suppose ? > Thanks for your answer, i will try to make the transaction shorter as you > suggest. > Yep. This is a very common confusion, how

Re: Using AWS ephemeral SSD storage for production database workload?

Thanks Ben and Sam for sharing your experience. On Jan 30, 2018 8:52 AM, "Ben Chobot" wrote: > > > On Jan 29, 2018, at 8:05 PM, Sam Gendler > wrote: > > > > Why not use EBS storage, but don’t use provisioned iops SSDs (io1) for > the ebs volume. Just use the default storage type (gp2) and live

Re: Using AWS ephemeral SSD storage for production database workload?

> On Jan 29, 2018, at 8:05 PM, Sam Gendler wrote: > > Why not use EBS storage, but don’t use provisioned iops SSDs (io1) for the > ebs volume. Just use the default storage type (gp2) and live with the 3000 > IOPS peak for 30 minutes that that allows. You’d be amazed at just how much > I/o can

Re: Using AWS ephemeral SSD storage for production database workload?

Why not use EBS storage, but don’t use provisioned iops SSDs (io1) for the ebs volume. Just use the default storage type (gp2) and live with the 3000 IOPS peak for 30 minutes that that allows. You’d be amazed at just how much I/o can be handled within the default IOPS allowance, though bear in mind

Re: Using AWS ephemeral SSD storage for production database workload?

G'day all, We have been doing this in production for about five years, the client is aware of the trade off between speed, cost and availability. (By this I mean, if it goes down for a few minutes, no big concern to them). We had around 2 million users, but very small payloads. We take full datab

Re: Information on savepoint requirement within transctions

On Mon, Jan 29, 2018 at 6:59 AM, Robert Zenz wrote: > > It may be worth updating the docs here... > > I'd vote for that. I would have expected to see this mentioned in the > documentation a little bit more prominent than just a single sentence at > the end > of the transaction tutorial. A short s

Re: best way to storing logs

On Tue, 30 Jan 2018 00:38:02 +0300 Ibrahim Edib Kokdemir wrote: > Hi, > In our environment, we are logging "all" statements because of the security > considerations (future auditing if necessary). But the system is very big > and produces 100GB logs for an hour and we expect that this will be muc

Re: Meaning of query age in pg_stat_activity

So the query is just the latest query and the time is the transaction time since this query, i suppose ? Thanks for your answer, i will try to make the transaction shorter as you suggest. On Tue, Jan 30, 2018 at 12:29 AM, Nikolay Samokhvalov wrote: > On Mon, Jan 29, 2018 at 3:19 PM, Jan De Moerl

Re: Meaning of query age in pg_stat_activity

On Mon, Jan 29, 2018 at 3:19 PM, Jan De Moerloose wrote: > The state is 'idle in transaction'. > So you have long-running *transactions*, not queries. This is not good for an OLTP system, because some transaction can wait of others, which are "idle in transaction" but do nothing at the moment. T

Re: Meaning of query age in pg_stat_activity

The state is 'idle in transaction'. On Tue, Jan 30, 2018 at 12:10 AM, Nikolay Samokhvalov wrote: > On Mon, Jan 29, 2018 at 3:06 PM, Jan De Moerloose > wrote: > ... > >> SELECT pid, age(clock_timestamp(),query_start) as age, usename, query, >> state from pg_stat_activity order by age; >> >> When

Re: Meaning of query age in pg_stat_activity

On 01/29/2018 03:06 PM, Jan De Moerloose wrote: Hi, I'm using the following to detect long running queries in a webapp that is high on cpu: SELECT pid, age(clock_timestamp(),query_start) as age, usename, query, state from pg_stat_activity order by age; I would add WHERE state = 'active'

Re: Meaning of query age in pg_stat_activity

On Mon, Jan 29, 2018 at 3:06 PM, Jan De Moerloose wrote: ... > SELECT pid, age(clock_timestamp(),query_start) as age, usename, query, > state from pg_stat_activity order by age; > > When the cpu is 100% and the app slowing down, i can see that some queries > have a long age. > What is the value

Meaning of query age in pg_stat_activity

Hi, I'm using the following to detect long running queries in a webapp that is high on cpu: SELECT pid, age(clock_timestamp(),query_start) as age, usename, query, state from pg_stat_activity order by age; When the cpu is 100% and the app slowing down, i can see that some queries have a long age.

Re: pgpool Connections Distributions Among Nodes

> We have 4-node cluster (1 master and 3 hot standby). We are using pgpool as > load balancer. We have an observation where if application requests for 3 > connections, pgpool connects to all 4 servers and I see 3 connections on each > of them. I was expecting it have a total of 3 connections f

best way to storing logs

Hi, In our environment, we are logging "all" statements because of the security considerations (future auditing if necessary). But the system is very big and produces 100GB logs for an hour and we expect that this will be much more. We are having trouble to find the disk for this amount of data. N

pgcrypto Documentation/Function Mismatch

Hello, I wanted to pass along a pgcrypto documentation/function definition mismatch in (at least) v9.3 and v9.6. The mismatch occurs in the hmac function definition: Documentation: hmac(data bytea, key text, type text) returns bytea As defined in 9.3.17 and 9.6.6: Lis

Re: Using AWS ephemeral SSD storage for production database workload?

Thank you, Paul and Steven, for the information. @Paul: Thanks for the link. Planning to read through most of fdr's comments on Hacker News. @Steven: > Q: Why not just use RDS? > It'll be simpler. Already using it for multiple deployments. I am primarily a programmer. But now want to get out o

Re: EXPLAIN BUFFERS: dirtied

On 01/29/2018 08:21 PM, Vitaliy Garnashevich wrote: > I've read this article: https://wiki.postgresql.org/wiki/Hint_Bits > > It says: > >> A plain SELECT, count(*), or VACUUM on the entire table will check >> every tuple for visibility and set its hint bits. > > Suppose, a new page was create

Re: pg 10.1 missing libpq in postgresql-devel

On 01/29/2018 11:18 AM, support-tiger wrote: On 01/27/2018 06:43 PM, Adrian Klaver wrote: On 01/27/2018 04:34 PM, support-tiger wrote: sorry for delay but ran some tests on older version pg gem - still fails With what error message? What Ruby gem? it fails on building the config file wit

Re: Using AWS ephemeral SSD storage for production database workload?

> I have come across some ideas on the Internet > where people hinted at running production PostgreSQL workloads > on AWS ephemeral SSD storage. I think people were more serious about that before AWS introduced PIOPS. I wouldn't do this unless I had streaming replication to a standby, plus regular

Re: Using AWS ephemeral SSD storage for production database workload?

On Mon, 29 Jan 2018 23:27:32 +0530 Pritam Barhate wrote: > In short, I am just trying to learn from other people's experience. This is identical to solutions that use tmpfs on linux for database storage or dealing with a fully failed storage system. Think about what you'd do if a RAID controll

Re: EXPLAIN BUFFERS: dirtied

I've read this article: https://wiki.postgresql.org/wiki/Hint_Bits It says: A plain SELECT, count(*), or VACUUM on the entire table will check every tuple for visibility and set its hint bits. Suppose, a new page was created using many INSERTs, and then was written to disk during a checkpoi

Re: pg 10.1 missing libpq in postgresql-devel

On 01/27/2018 06:43 PM, Adrian Klaver wrote: On 01/27/2018 04:34 PM, support-tiger wrote: sorry for delay but ran some tests on older version pg gem - still fails With what error message? What Ruby gem? it fails on building the config file with simply missing libpq-fe.h, cannot find libp

Re: EXPLAIN BUFFERS: dirtied

Vitaliy Garnashevich writes: > But what is "dirtied" statistics? When a SELECT query could make pages > dirty? Setting hint bits on recently-committed rows. regards, tom lane

EXPLAIN BUFFERS: dirtied

Hi, In EXPLAIN (ANALYZE, BUFFERS) for a SELECT query, I see the following statistics under an Index Scan node: Buffers: shared hit=8357288 read=6165444 dirtied=44820 written=5590 As far as I understand, that's the statistics for accesses to shared buffers during the query: - hit = required

Re: Using AWS ephemeral SSD storage for production database workload?

>> Both log shipping and async replication are ancient features, and should >> be well understood. What exactly is unclear? I know about these and I know how to operate them also. The only part I am concerned about is the ephemeral storage. The risk appetite around it and the steps people take in

Re: Using AWS ephemeral SSD storage for production database workload?

On 01/29/2018 05:41 PM, Pritam Barhate wrote: > Hi everyone,  > > As you may know, EBS volumes though durable are very costly when you > need provisioned IOPS. As opposed to this AWS instance attached > ephemeral SSD is very fast but isn't durable. > > I have come across some ideas on the Inte

RE: pgpool Connections Distributions Among Nodes

Attached is config file. [pgpool@usdf23v0550 ~]$ pgpool -v pgpool-II version 3.7.1 (amefuriboshi) Reading should go to all nodes. Regards, Virendra From: Glauco Torres [mailto:torres.gla...@gmail.com] Sent: Monday, January 29, 2018 11:53 AM To: Kumar, Virendra Cc: pgsql-gene...@postgresql.org S

Re: Which specific flags to use for bash client applications for DaVinci Resolve?

Thanks, Steve. Yes, Blackmagic Design changed the pricing last year, and now ship a free Linux version. On Windows and Mac, it uses a "disk" database by default, which is a special configuration of files and folders on the boot drive. However, on Windows, Mac, and Linux, a PostgreSQL database ca

Re: pgpool Connections Distributions Among Nodes

> > > > > We have 4-node cluster (1 master and 3 hot standby). We are using pgpool > as load balancer. We have an observation where if application requests for > 3 connections, pgpool connects to all 4 servers and I see 3 connections on > each of them. I was expecting it have a total of 3 connecti

Re: pgpool Connections Distributions Among Nodes

On 01/29/2018 08:19 AM, Kumar, Virendra wrote: Hi Gurus, We have 4-node cluster (1 master and 3 hot standby).  We are using pgpool as load balancer. We have an observation where if application requests for 3 connections, pgpool connects to all 4 servers and I see 3 connections on each of them

Re: PG Sharding

> On Jan 29, 2018, at 09:34 , Matej wrote: > > Hi Everyone. > > We are looking at a rather large fin-tech installation. But as scalability > requirements are high we look at sharding of-course. > > I have looked at many sources for Postgresql sharding, but we are a little > confused as to

Using AWS ephemeral SSD storage for production database workload?

Hi everyone, As you may know, EBS volumes though durable are very costly when you need provisioned IOPS. As opposed to this AWS instance attached ephemeral SSD is very fast but isn't durable. I have come across some ideas on the Internet where people hinted at running production PostgreSQL worklo

pgpool Connections Distributions Among Nodes

Hi Gurus, We have 4-node cluster (1 master and 3 hot standby). We are using pgpool as load balancer. We have an observation where if application requests for 3 connections, pgpool connects to all 4 servers and I see 3 connections on each of them. I was expecting it have a total of 3 connection

Re: PG Sharding

When I worked at Wanelo, we built a sharded data store for a giant join table with 4B records and growing. We too could not find any generic sharding solution at the level of postgresql, and after some research decided to implement it in the application. As it was written in ruby, here are some

Re: [GENERAL] Matching statement and duration log lines

On Mon, Oct 23, 2017 at 01:06:07PM +, Popov Aleksey wrote: > Hello! > > I am sending PG logs to Elasticsearch and want to merge a line with statement > and a line with duration into one document. > Having a statement line and a duration line, can I assume that if a session > ids > (%c) of the

Re: Which specific flags to use for bash client applications for DaVinci Resolve?

Interesting. I was unaware that Resolve used PostgreSQL. I looked at Resolve a year or two ago but the Linux version was still pricey while the basic Mac/Windows versions could be downloaded for free. Looks like there may be a free version for Linux, now. I'll have to check it out. In any case, yo

Re: Information on savepoint requirement within transctions

On Mon, Jan 29, 2018 at 9:00 AM, Tom Lane wrote: > "David G. Johnston" writes: > > On Mon, Jan 29, 2018 at 8:33 AM, Tom Lane wrote: > >> What we do have though is client-side support for appropriate behaviors. > >> In psql, see the AUTOCOMMIT and ON_ERROR_ROLLBACK control variables. > > > Not q

Re: PG Sharding

Hello Matej, I found some interesting implementation of postgres_XL at TenCent(WeChat) and Javelin. You can find video capture of conferences of IT people from these companies. Moreover, I attended to PgConf.eu at Warsaw in October, and The ESA (European Space Agency) made a lightning talk on thei

Re: Information on savepoint requirement within transctions

"David G. Johnston" writes: > On Mon, Jan 29, 2018 at 8:33 AM, Tom Lane wrote: >> What we do have though is client-side support for appropriate behaviors. >> In psql, see the AUTOCOMMIT and ON_ERROR_ROLLBACK control variables. > Not quite the same. I think what people probably want is for psql

Re: Information on savepoint requirement within transctions

On 29.01.2018 16:33, Tom Lane wrote: > That behavior does exist, and so does documentation for it; you're just > looking in the wrong place. > > Years ago (7.3 era, around 2002) we experimented with a server-side > GUC variable "AUTOCOMMIT", which switched from the implicit-commit- > if-you-don't-

Re: PG Sharding

Hi Thomas. Thanks. Also looked at those solutions: - PGXL Am a ltille afraid we would be the test dummies. Did not hear of many production installs. - Citus seems heavily limited scalability vise, because of the master node design. Regarding partitioning we are considering ourselves pg_pathman.

Re: Information on savepoint requirement within transctions

On Mon, Jan 29, 2018 at 8:33 AM, Tom Lane wrote: > What we do have though is client-side support for appropriate behaviors. > In psql, see the AUTOCOMMIT and ON_ERROR_ROLLBACK control variables. > Other interfaces such as JDBC have their own ideas about how this ought > to work. > ​Not quite the

Which specific flags to use for bash client applications for DaVinci Resolve?

Hello all, I apologize if this is off-topic for this specific mailing list--if it is, let me know, and I can post it to the right spot instead. I'm a complete newbie with PostgreSQL, or any kind of database language for that matter, but I'm trying to figure out how I might automate some workflow

Re: Information on savepoint requirement within transctions

Robert Zenz writes: > On 29.01.2018 15:11, Alban Hertroys wrote: >> If you start a transaction and something goes wrong in the process, >> the logical behaviour is to fail - the user will want to rollback to a >> sane state, doing any more work is rather pointless because of that. >> Allowing a co

Re: PG Sharding

Hello, Facing the same situation, I'm considering 3 solutions: - Sharding with postgres_xl (waiting for a Pg10 release) - Sharding with citusdata (Release 7.2, compatible with Pg10 and pg_partman, seems interesting) - Partitioning with PG 10 native partitioning or pg_partman With colleagues, we h

Re: Information on savepoint requirement within transctions

On 29.01.2018 15:11, Alban Hertroys wrote: > IMHO, the burden of explaining that is with those RDBMSes that don't > behave properly: > > If you start a transaction and something goes wrong in the process, > the logical behaviour is to fail - the user will want to rollback to a > sane state, doing

Re: PG Sharding

On Mon, Jan 29, 2018 at 9:34 AM, Matej wrote: > Hi Everyone. > > We are looking at a rather large fin-tech installation. But as scalability > requirements are high we look at sharding of-course. > > I have looked at many sources for Postgresql sharding, but we are a little > confused as to shared

PG Sharding

Hi Everyone. We are looking at a rather large fin-tech installation. But as scalability requirements are high we look at sharding of-course. I have looked at many sources for Postgresql sharding, but we are a little confused as to shared with schema or databases or both. So far our understandin

Re: Information on savepoint requirement within transctions

On 29 January 2018 at 14:59, Robert Zenz wrote: > On 29.01.2018 14:36, David G. Johnston wrote: ... > From my point of view, no, it shouldn't be changed. It has always been this > way > and I find nothing wrong with the approach, it is only something that you need > to be aware of, that's all. >

Re: Information on savepoint requirement within transctions

On 29.01.2018 14:36, David G. Johnston wrote: > ​Those questions would not be answered in user-facing documentation. You > can explore the git history and search the far-back mailing list archives if > you wish to satisfy your curiosity. For me this is how it works - the only > question for me is

Re: Information on savepoint requirement within transctions

On Mon, Jan 29, 2018 at 1:37 AM, Robert Zenz wrote: > Documentation, bug report, mailing list discussions, > something like that. In particular I'm interested in the questions: > > * Why are they required in combination with failing statements (when every > other database does an "automatic save

Fwd: Re: Information on savepoint requirement within transctions

On 29.01.2018 12:37, Adam Tauno Williams wrote: > It this statement true? I very much feel *not*. This depends on how > you have set AUTO_COMMIT - and it certainly is not true for > transactions of multiple statements. Maybe I should clarify at that point that AUTO_COMMIT is OFF, and yes, that i

Re: Information on savepoint requirement within transctions

>  * Why are they required in combination with failing statements (when > every other database does an "automatic savepoint and rollback" for a > failed statement)? It this statement true?  I very much feel *not*.  This depends on how you have set AUTO_COMMIT - and it certainly is not true for tra

Re: Information on savepoint requirement within transctions

On 26.01.2018 17:11, David G. Johnston wrote: > ​The convention for these lists is to inline or bottom-post. Top-posting > is discouraged. Okay, I'll keep it in mind. > Here's my take, the docs support this but maybe take some interpretation... > > A SAVEPOINT ​begins what is effectively a sub-

Re: FW: Setting up streaming replication problems

Hi, Am 29.01.2018 um 06:03 schrieb Thiemo Kellner: Thanks for your patience. you are welcome. From the other mail (in german) i think i know the issue now: you have placed the recovery.conf NOT in the data_directoy but in /etc/... PostgreSQL is looking for the recovery.conf in the data_d