Re: [GENERAL] PostgreSQL - The Best Overall Database

[Ravi Krishna]

is there any details available on this poll ?

thanks.

On Thu, Aug 13, 2015 at 11:05 PM, Sachin Srivastava <
sachin.srivast...@cyient.com> wrote:

> Congrats to all PostgreSQL DBA’s for this achievement..
>
>
>
>
> HERE ARE THE WINNERS OF THE 2015 DBTA READERS' CHOICE AWARDS FOR BEST
> DATABASE (OVERALL):
>
> *Winner: *
>
> PostgreSQL 
> 
>
> *Finalists:*
>
> Oracle Database 
>
> Microsoft SQL Server 
>
>
>
>
>
> *Regards,*
>
> *Sachin Srivastava*
> *Consultant (Oracle/PostgreSQL) **| **Technical Support Group **| **DNO –
> T & N Tom Tom* *C**yient** |* *www.cyient.com  *
>
> *Mobile: **+91 981 114 9139** |*
> *sachin.srivast...@cyient.com  **Direct:**
> +91 **120 669 1078 **|* *Board:** +91 120 669  2000 - 10*
>
>
>
>
>
> *From:* pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org] *On Behalf Of *Melvin Davidson
> *Sent:* 13 August, 2015 6:34 PM
> *To:* pgsql-general@postgresql.org
> *Subject:* [GENERAL] PostgreSQL - The Best Overall Database
>
>
>
>
> This should put a smile on all PostgreSQL DBA's faces.
>
>
> The Best Overall Database
> 
>
>
> *Melvin Davidson*
>
> *I reserve the right to fantasize.  Whether or not you wish to share my
> fantasy is entirely up to you. *
>
> --
>
> DISCLAIMER:
>
> This email may contain confidential information and is intended only for
> the use of the specific individual(s) to which it is addressed. If you are
> not the intended recipient of this email, you are hereby notified that any
> unauthorized use, dissemination or copying of this email or the information
> contained in it or attached to it is strictly prohibited. If you received
> this message in error, please immediately notify the sender at Cyient and
> delete the original message.
>

Re: [GENERAL] Synchronous replication and read consistency

[Ravi Krishna]

"Not necessarily.  There has been discussion of adding a new mode
which will delay the commit on the primary until it is visible on a
synchronous standby, but I don't recall where that left off.  "

Joshua: THis essentially contradicts your statement to me.



On Wed, Jul 29, 2015 at 5:10 PM, Kevin Grittner  wrote:
> Ravi Krishna  wrote:
>
>> As per this:
>>
>> http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION
>>
>> "When requesting synchronous replication, each commit of a write
>> transaction will wait until confirmation is received that the commit
>> has been written to the transaction log on disk of both the primary
>> and standby server."
>>
>> Does it mean that, on the standby, when PG writes the transaction log
>> on the disk, it also updates the data buffers to make the transaction
>> visible for all sessions.
>
> No, it means that if the primary is hit by a meteor and you promote
> the standby, the data will not have been lost.  The time between
> the successful return of the commit on the primary and the time at
> which the change becomes visible on the standby is normally quite
> small; you may have trouble running into a case where you notice
> it, but it can happen.
>
>> Eg:
>> On the primary
>> A big transaction committed
>> Now if I issue a select on the primary looking for the transaction I
>> committed above, I will get what I want.
>> Will I get the same result if instead of primary I issue the select on
>> the standby.
>
> Not necessarily.  There has been discussion of adding a new mode
> which will delay the commit on the primary until it is visible on a
> synchronous standby, but I don't recall where that left off.  One
> of the issues is that with the current guarantee you need multiple
> replicas to prevent a failure of a standby from stalling the
> primary indefinitely, and you don't have an easy way to know
> *which* replica succeeded in persisting the transaction without
> doing a lot of work.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Synchronous replication and read consistency

[Ravi Krishna]

Chris/Joshua

I would like to know more details.

As per this:

http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION

"When requesting synchronous replication, each commit of a write
transaction will wait until confirmation is received that the commit
has been written to the transaction log on disk of both the primary
and standby server."

Does it mean that, on the standby, when PG writes the transaction log
on the disk, it also updates the data buffers to make the transaction
visible for all sessions.

Eg:

  On the primary
 A big transaction committed
  Now if I issue a select on the primary looking for the transaction I
committed above, I will get what I want.
Will I get the same result if instead of primary I issue the select on
the standby.

Hope it is clear.



On Wed, Jul 29, 2015 at 2:20 PM, Chris Mair  wrote:
>> Does sync replication guarantee that any inserted data on primary is
>> immediately visible for read on standbys with no lag.
>
> Basically yes. Of course there is *some* latency, at the very least
> from the network.
>
> If I run a process on a standby machine that displays a value every
> 0.1 sec and update the value on the master, I see the standby updating
> with a lag that feels less than 0.2 sec or so.
>
> You might have lag, however, in situations where you have so much
> write into the master that the network or standby is not able to
> catch up. After the write burst is over, the stanby will catch up
> as it quickly as possible, though.
>
> Also, you use the word "consistency", that would be something else...
> Of course you always get consistent data, lag or not. This is Postgres
> after all :)
>
> Bye,
> Chris.
>
>
>
>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Synchronous replication and read consistency

[Ravi Krishna]

Does sync replication guarantee that any inserted data on primary is
immediately visible for read on standbys with no lag.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] alter column type

[Ravi Krishna]

> In the above case PG will simply do a dictionary update of meta
> tables. So all new rows will reflect col-T and as and when the old

I will clarify it bit further:

All new rows will have space allocated for col-T and no space
allocated for col-S, while existing dormant rows are left unmodified .


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] alter column type

[Ravi Krishna]

> On 6/5/2015 11:37 AM, Ravi Krishna wrote:
>>
>> Why is PG even re-writing all rows when the data type is being changed
>> from smaller (int) to larger (bigint) type, which automatically means
>> existing data is safe. Like, changing from varchar(30) to varchar(50)
>> should involve no rewrite of existing rows.
>
>
>
> int to bigint requires storage change, as all bigints are 64 bit while all
> ints are 32 bit. it would be a MESS to try and keep track of a table
> that has some int and some bigint storage of a given field.
>
> now, varchar 30 to 50, that I can't answer, are you sure that does a
> rewrite?   the storage is exactly the same for those.

Perhaps I was not clear. I don't expect any re-write for a change of
varchar(30) to 50 for the same reason you mentioned above.

Yes it is normal to expect the storage size for bigint to be different
than 32 bit, but then PG uses MVCC. If and when current row gets
updated, MVCC will ensure a new row to be written, which can fix the
data type.

I believe PG adds or drops a col without rewrite because of MVCC. For
eg, I add a new col-T in a table and drop col-S via a single ALTER
TABLE command. I am assuming this is what happens internally:

In the above case PG will simply do a dictionary update of meta
tables. So all new rows will reflect col-T and as and when the old
rows get modified, it too will get updated to the new structure.

If my above understand is correct, why it is not applied in case of
int -> bigint change.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] alter column type

[Ravi Krishna]

Why is PG even re-writing all rows when the data type is being changed
from smaller (int) to larger (bigint) type, which automatically means
existing data is safe. Like, changing from varchar(30) to varchar(50)
should involve no rewrite of existing rows.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Automatic Client routing

[Ravi Krishna]

Are there any plans to introduce the concept of automatic client
routing to the principal server in a cluster of N machines. For
example, if there is a four node replication cluster N1 .. N4, at any
time only one can be principal (the one which does the writing). In
Oracle and DB2, client side libraries provide a way for the clients to
connect to the principal writer regardless of where it is running on
N1 .. N4. This way client need to be aware of only one connection
string.

EnterpriseDb is a failover manager which relies on virtual IP
management, not the one I described above.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partitioning and performance

[Ravi Krishna]

On Thu, May 28, 2015 at 12:50 PM, Tom Lane  wrote:

> Sure, because you don't have a constraint forbidding the parent from
> having a matching row, no?

As suggested by you, I included a bogus condition in the parent table
which will prevent any row addition in the parent table and made the
constraint NO INHERIT.

i run this

SET constraint_exclusion = on;
explain select * from tstesting.account where account_row_inst = 1001 ;



Append  (cost=0.14..8.16 rows=1 width=832)
   ->  Index Scan using account_part1_pkey on account_part1
(cost=0.14..8.16 rows=1 width=832)
 Index Cond: (account_row_inst = 1001)
(3 rows)

The planner shows this for the non partitioned table

 Index Scan using account_pkey on account  (cost=0.14..8.16 rows=1 width=832)
   Index Cond: (account_row_inst = 1001)
(2 rows)

So cost wise they both  look same, still when i run the sql in a loop
in large numbers, it takes rougly 1.8 to 2 times more than non
partitioned table.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partitioning and performance

[Ravi Krishna]

> By and large, though, this doesn't really matter, since an empty
> parent table won't cost anything much to scan.  If it's significant
> relative to the child table access time then you probably didn't
> need partitioning in the first place.

Is there a rule of thumb as to at what size does the partitioning
start performing better than non partitioned table.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partitioning and performance

[Ravi Krishna]

On Thu, May 28, 2015 at 12:42 PM, Melvin Davidson  wrote:
>
> Generally, when you partition, data should only be in child tables, and the 
> parent table should be empty, otherwise you defeat the purpose of 
> parttioning.`

yes of course the parent table is empty. The trigger on insert is
redirecting it to the proper child table.

select count(*) from only tstesting.account ;
 count
---
 0
(1 row)

select count(*) from only tstesting.account_part1 ;
 count
---
 83659
(1 row)

select count(*) from only tstesting.account_part5 ;
 count
---
 83659
(1 row)


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partitioning and performance

[Ravi Krishna]

> Have you set up constraints on the partitions? The planner needs to know
> what is in the child tables so it can avoid scanning them.

Yes. each child table is defined as follows

CREATE TABLE TSTESTING.ACCOUNT_PART1

 ( CHECK (ACCOUNT_ROW_INST BETWEEN 1001 and 271660))

 INHERITS (TSTESTING.ACCOUNT);

ALTER TABLE TSTESTING.ACCOUNT_PART1 ADD CONSTRAINT ACCOUNT_PART1_PKEY
PRIMARY KEY (ACCOUNT_ROW_INST);

Perhaps I was not clear. The planner is excluding partitions which can
not contain the rows looked up in the WHERE clause. However it is
still scanning the parent table.

Aggregate (cost=8.45..8.46 rows=1 width=0)
-> Append (cost=0.00..8.44 rows=2 width=0)
-> Seq Scan on account (cost=0.00..0.00 rows=1 width=0)
Filter: (account_row_inst = 101)
-> Index Only Scan using account_part1_pkey on account_part1
(cost=0.42..8.44 rows=1 width=0)
Index Cond: (account_row_inst = 101)
(6 rows)


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Partitioning and performance

[Ravi Krishna]

I am testing partitioning of a large table. I am INHERITING child tables.
It is using a range
partitioning based on a sequence col, which also acts as the primary
key. For inserts I am using a trigger which will redirect insert to
the right table based on the value of the primary key.

Based on my testing, I see that the insert speed is less than 10%
different than a non partitioned table. I am using  SET
constraint_exclusion = on and I checked that via ANALYZE that the
planner does not consider non qualifying child tables.

yet, selects and updates based on the primary key show anywhere from
40 to 200% slowness as compared to non partition. One thing I notice
is that, even with partition pruning, the planner scans the base table
and the table matching the condition. Is that the additional overhead.

I am attaching below the output of analyze.

===
On a non partitioned table

explain select count(*) from tstesting.account where account_row_inst = 101 ;
Aggregate (cost=8.16..8.17 rows=1 width=0)
-> Index Only Scan using account_pkey on account (cost=0.14..8.16
rows=1 width=0)
Index Cond: (account_row_inst = 101)
(3 rows)


With partition pruning:

Aggregate (cost=8.45..8.46 rows=1 width=0)
-> Append (cost=0.00..8.44 rows=2 width=0)
-> Seq Scan on account (cost=0.00..0.00 rows=1 width=0)
Filter: (account_row_inst = 101)
-> Index Only Scan using account_part1_pkey on account_part1
(cost=0.42..8.44 rows=1 width=0)
Index Cond: (account_row_inst = 101)
(6 rows)

On a partitioned table, with no partition pruning.

explain analyze select count(*) from tstesting.account where
account_row_inst = 101 ;
Aggregate (cost=29.77..29.78 rows=1 width=0) (actual time=0.032..0.032
rows=1 loops=1)
-> Append (cost=0.00..29.76 rows=5 width=0) (actual time=0.029..0.029
rows=0 loops=1)
-> Seq Scan on account (cost=0.00..0.00 rows=1 width=0) (actual
time=0.000..0.000 rows=0 loops=1)
Filter: (account_row_inst = 101)
-> Index Only Scan using account_part1_pkey on account_part1
(cost=0.42..4.44 rows=1 width=0) (actual time=0.008..0.008 rows=0
loops=1)
Index Cond: (account_row_inst = 101)
Heap Fetches: 0
-> Index Only Scan using account_part2_pkey on account_part2
(cost=0.42..8.44 rows=1 width=0) (actual time=0.007..0.007 rows=0
loops=1)
Index Cond: (account_row_inst = 101)
Heap Fetches: 0
-> Index Only Scan using account_part3_pkey on account_part3
(cost=0.42..8.44 rows=1 width=0) (actual time=0.007..0.007 rows=0
loops=1)
Index Cond: (account_row_inst = 101)
Heap Fetches: 0
-> Index Only Scan using account_part4_pkey on account_part4
(cost=0.42..8.44 rows=1 width=0) (actual time=0.006..0.006 rows=0
loops=1)
Index Cond: (account_row_inst = 101)
Heap Fetches: 0
Planning time: 0.635 ms
Execution time: 0.137 ms
(18 rows)


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PG and undo logging

[Ravi Krishna]

On Sat, May 23, 2015 at 10:12 PM, Scott Marlowe  wrote:

> Ever run an insert with 1M rows, and roll it back in postgresql and
> compare that to oracle. Time the rollback in both. That should give
> you an idea of how differently the two dbs operate.
>
> A rollback in postgres is immediate because it's already "rolled back"
> so to speak. NONE of it's in the main data store yet, it's all in the
> transaction log waiting.
>
> Oracle spends it's time and effort creating an "undo" log because it
> commits every so often, whether or not you've committed your
> transaction.
>
> PostgreSQL doesn't. The whole transaction exists in the transaction
> log (called xlog dir in pg lingo.)
>
> When you roll back a pg transaction it literally requires almost no
> work. Mark the transaction as aborted etc and get on with life.
> Transaction logs get cleaned up as usual in the background and we go
> on our way.
>
> This means that Oracle uses space for rollback, while postgres uses
> space for "roll forward" (aka the transaction logs) so to speak.

Thanks for the detailed explanation. The explanation makes me wonder
that PG must do more work at commit time, right?


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PG and undo logging

[Ravi Krishna]

undo means that reading the WAL logs and able to rollback a row back
to its original state before the update. Typically it is used to
rollback a long running transaction which got aborted due to a crash.
Here is an example:

2.pm You kick off a huge transaction to update say 1 million rows.
 between 2.01 pm and 2.05pm, the db did multiple checkpoints.
2.06pm - machine crashed.

Note that at 2.06, your transaction of 2pm was still running. So when
the db starts later on, after redoing all transactions from the last
checkpoint @2.05pm till the time of crash 2.06pm, it also has to
rollback the 2pm update also because it never got completed. This
means, the rollback will have to go past several check points (between
2.01pm and 2.05pm).

Hope this explains it clearly.


On Sat, May 23, 2015 at 4:48 PM, David G. Johnston
 wrote:
> On Sat, May 23, 2015 at 1:34 PM, Ravi Krishna 
> wrote:
>>
>> Is it true that PG does not log undo information, only redo. If true,
>> then how does it bring a database back to consistent state during
>> crash recovery. Just curious.
>
>
> What does "undo" mean?
>
> David J.
>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PG and undo logging

[Ravi Krishna]

Is it true that PG does not log undo information, only redo. If true,
then how does it bring a database back to consistent state during
crash recovery. Just curious.

thanks.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SQL] [GENERAL] Does PG support bulk operation in embedded C

[Ravi Krishna]

Not sure whether I am understanding this. I checked embedded C and did not find any section which describes what I have asked, that is the ability to do multiple inserts, or updates or deletes in one sql call. For example, if my application does the following

BEGIN TRANSACTION
   INSERT INTO TABLE_A
   UPDATE TABLE_B
   INSERT INTO TABLE_C

COMMIT TRANSACTION


DB2 provides to combine the three sql operations into an array and make a call to DB2 which executes the array (that is all 3 sqls as one single call).

I am looking for something similar in PG.

thanks

 

Sent: Tuesday, May 19, 2015 at 8:13 PM
From: "Joshua D. Drake" 
To: "Ravi Krishna" , pgsql-...@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: Re: [SQL] [GENERAL] Does PG support bulk operation in embedded C


On 05/19/2015 04:47 PM, Ravi Krishna wrote:
>
> To explain pls refer to this for DB2
>
> http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0002329.html
>
>
> Essentially in one single sql call, we can do
> -- Add new rows
> -- Update a set of rows where each row is identified by a bookmark
> -- Delete a set of rows where each row is identified by a bookmark
> -- Fetch a set of rows where each row is identified by a bookmark
>
> This gives tremendous performance benefits as the network round trip is
> avoided for each sql.
>
> I am looking for an equivalent of this in PG and C language.

For embedded C, I believe you are looking for:

http://www.postgresql.org/docs/9.4/static/ecpg.html


>
> Thanks.
>
>
>


--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
Sent via pgsql-sql mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[GENERAL] Does PG support bulk operation in embedded C

[Ravi Krishna]

To explain pls refer to this for DB2

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0002329.html

Essentially in one single sql call, we can do
-- Add new rows
-- Update a set of rows where each row is identified by a bookmark
-- Delete a set of rows where each row is identified by a bookmark
-- Fetch a set of rows where each row is identified by a bookmark

This gives tremendous performance benefits as the network round trip is 
avoided for each sql.


I am looking for an equivalent of this in PG and C language.

Thanks.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to clear buffer

[Ravi Krishna]

I am writing bench mark scripts and as part of it would like to clear the cache
programmatically. This is to ensure that when we run select queries the data is
not read read from the cache. Does PG provide any easy way to do it other than
the obvious way to restart the database.

Thanks.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general