Re: [GENERAL] PostgreSQL - The Best Overall Database
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 http://www.postgresql.org/ http://www.postgresql.org/ *Finalists:* Oracle Database http://www.oracle.com/ Microsoft SQL Server http://www.microsoft.com/ *Regards,* *Sachin Srivastava* *Consultant (Oracle/PostgreSQL) **| **Technical Support Group **| **DNO – T N Tom Tom* *C**yient** |* *www.cyient.com http://www.cyient.com/ * *Mobile: **+91 981 114 9139** |* *sachin.srivast...@cyient.com 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 http://www.dbta.com/Editorial/Trends-and-Applications/Best-Database-Overall%C2%AD-105362.aspx *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.
[GENERAL] Synchronous replication and read consistency
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] Synchronous replication and read consistency
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 kgri...@ymail.com wrote: Ravi Krishna sravikrish...@gmail.com 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
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 ch...@1006.org 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
Re: [GENERAL] alter column type
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
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
Re: [GENERAL] alter column type
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
[GENERAL] Automatic Client routing
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
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
On Thu, May 28, 2015 at 12:50 PM, Tom Lane t...@sss.pgh.pa.us 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
[GENERAL] Partitioning and performance
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] Partitioning and performance
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
Re: [GENERAL] Partitioning and performance
On Thu, May 28, 2015 at 12:42 PM, Melvin Davidson melvin6...@gmail.com 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] PG and undo logging
On Sat, May 23, 2015 at 10:12 PM, Scott Marlowe scott.marl...@gmail.com 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
[GENERAL] PG and undo logging
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: [GENERAL] PG and undo logging
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 david.g.johns...@gmail.com wrote: On Sat, May 23, 2015 at 1:34 PM, Ravi Krishna sravikrish...@gmail.com 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
Re: [SQL] [GENERAL] Does PG support bulk operation in embedded C
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 j...@commandprompt.com To:Ravi Krishna srkris...@gmx.com, 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 Im offended is basically telling the world you cant 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
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
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
