Re: [GENERAL] JSONB performance enhancement for 9.6
Hello: I'd like to bring this JSONB performance issue again. Below is a link of MySQL way of storing/retrieving Json key/value https://dev.mysql.com/doc/refman/5.7/en/json.html Instead of providing column indexing(like GIN for JSONB in Postgresql). it provides only internal data structure level indexing within each individual json object for fast retrieval. compression is not used. Perhaps without implementing complicated column level GIN indexing, implementing a new variant JSON type that only handle individual json object indexing would be feasible? Combined with current JSONB implementation, both common use cases (one is global doc indexing, the other is fast retrieval of individual values) would work out and make postgresql unbeatable. On Tue, Jan 19, 2016 at 8:51 PM, Bruce Momjian wrote: > On Mon, Jan 11, 2016 at 09:01:03PM -0500, Tom Smith wrote: > > Hi, > > > > Congrats on the official release of 9.5 > > > > And I'd like bring up the issue again about if 9.6 would address the > jsonb > > performance issue > > with large number of top level keys. > > It is true that it does not have to use JSON format. it is about > serialization > > and fast retrieval > > of dynamic tree structure objects. (at top level, it might be called > dynamic > > columns) > > So if postgresql can have its own way, that would work out too as long > as it > > can have intuitive query > > (like what are implemented for json and jsonb) and fast retrieval of a > tree > > like object, > > it can be called no-sql data type. After all, most motivations of using > no-sql > > dbs like MongoDB > > is about working with dynamic tree object. > > > > If postgresql can have high performance on this, then many no-sql dbs > would > > become history. > > I can give you some backstory on this. TOAST was designed in 2001 as a > way to store, in a data-type-agnostic way, long strings compressed and > any other long data type, e.g. long arrays. > > In all previous cases, _part_ of the value wasn't useful. JSONB is a > unique case because it is one of the few types that can be processed > without reading the entire value, e.g. it has an index. > > We are going to be hesitant to do something data-type-specific for > JSONB. It would be good if we could develop a data-type-agnostic > approach to has TOAST can be improved. I know of no such work for 9.6, > and it is unlikely it will be done in time for 9.6. > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Roman grave inscription + >
Re: [GENERAL] intermittent issue with windows 7 service manager not able to correctly determine or control postgresql 9.4
I have the same problem routinely on Windows 10. The postgresql-x64-9.5 service shows up in Task Manager as Stopped, but is actually running just fine. BTW pg_ctl does nothing – silently. The only way to restart the server is to kill off a process or two. Regards David M Bennett FACS _ Andl - A New Database Language - andl.org From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tom Hodder Sent: Sunday, 1 May 2016 12:36 PM To: pgsql-general@postgresql.org Subject: [GENERAL] intermittent issue with windows 7 service manager not able to correctly determine or control postgresql 9.4 Hi All, I've got several machines running windows 7 which have postgresql 9.4 installed as a service, and configured to start automatically on boot. I am monitoring these services with zabbix and several times a week I get a notification that the postgresql-x64-9.4 service has stopped. When I login to the machine, the service does appear to be stopped; However when I check the database, I can query it ok; C:\Program Files\PostgreSQL\9.4>bin\psql.exe -U postgres -c "SELECT count(*) from media;" association Password for user postgres: count - 1167846 (1 row) If I try to start the service from the service manager, I see the following error in the logs; 2016-04-30 05:03:13 BST FATAL: lock file "postmaster.pid" already exists 2016-04-30 05:03:13 BST HINT: Is another postmaster (PID 2556) running in data directory "C:/Program Files/PostgreSQL/9.4/data"? The pg_ctl tool seems to correctly query the state of the service and return the correct PID; C:\Program Files\PostgreSQL\9.4>bin\pg_ctl.exe -D "C:\Program Files\PostgreSQL\9.4\data" status pg_ctl: server is running (PID: 2556) The other thing that seems to happen is the pgadmin3 tool seems to have lost the ability to control the service as all the options for start/stop are greyed out; The only option to get the control back is to kill the processes in the task manager or reboot the machine. Any suggestions on what might be causing this? Thanks, Tom
Re: [GENERAL] Skip trigger?
On 2016-04-30 02:08, wolfg...@alle-noten.de wrote: > Hi, > > I have a table with a row update trigger that is quite slow. > The trigger finction basically sets some bits in a "changed" column > depending on which values really changed. > For some bulk updates it can be determined in advance that the > trigger function will not have any effect. > Is there any way to run an update query and specify that it should not > activate the trigger. > I know that I can disable the trigger and reenable it later; > however other concurrent updates mights need it I always disable the trigger, run the update, and enable the trigger within a transaction. This locks the table and will prevent other sessions from doing updates without the trigger (I run it during off-hours if it is going to take more time than is acceptable). -- Stephen -- 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] Vacuum of large tables causing replication delays to hot standby
9.2.12 Sent from my iPhone > On Apr 30, 2016, at 12:37 PM, Tom Lane wrote: > > Jeff Mcdowell writes: >> 95% of the time, the delay is only microseconds. But we have discovered that >> whenever the master does an auto vacuum of a large table, the transaction >> replay delay can climb is high as 1 hour. These delays don’t seem to >> correlate with any particular queries that are running against the master or >> the standby, and the delay only subsides when the vacuum completes. > > What PG version might this be? > >regards, tom lane -- 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] Vacuum of large tables causing replication delays to hot standby
Tom Lane wrote: > Jeff Mcdowell writes: > > 95% of the time, the delay is only microseconds. But we have discovered > > that whenever the master does an auto vacuum of a large table, the > > transaction replay delay can climb is high as 1 hour. These delays don�t > > seem to correlate with any particular queries that are running against the > > master or the standby, and the delay only subsides when the vacuum > > completes. > > What PG version might this be? This sounds related to commit 3e4b7d87988f0835f137f15f5c1a40598dd21f3d Author: Simon Riggs AuthorDate: Sun Apr 3 17:46:09 2016 +0100 CommitDate: Sun Apr 3 17:46:09 2016 +0100 Avoid pin scan for replay of XLOG_BTREE_VACUUM in all cases -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- 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] Vacuum of large tables causing replication delays to hot standby
Jeff Mcdowell writes: > 95% of the time, the delay is only microseconds. But we have discovered that > whenever the master does an auto vacuum of a large table, the transaction > replay delay can climb is high as 1 hour. These delays dont seem to > correlate with any particular queries that are running against the master or > the standby, and the delay only subsides when the vacuum completes. What PG version might this be? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Vacuum of large tables causing replication delays to hot standby
Hello All, In an attempt to offload some of the pressure off our master postgres node, We recently decided to start running reports off of our hot-standby. There is a desire for these reports to return fairly current data, so we have been monitoring the replication delay between the master -> standby. We currently have max_standby_archive_delay and max_streaming_archive_delay set to -1, to avoid any timeouts in the application (when pulling reports). hot_standby_feedback is enabled on the slave node, but we are not currently setting vacuum_defer_cleanup_age. 95% of the time, the delay is only microseconds. But we have discovered that whenever the master does an auto vacuum of a large table, the transaction replay delay can climb is high as 1 hour. These delays don’t seem to correlate with any particular queries that are running against the master or the standby, and the delay only subsides when the vacuum completes. Does anyone have any recommendations for a configuration that can minimize the replay delays that occur during the vacuums of large tables. -- Thank you, Jeff McDowell Email: jeff.mcdow...@panerabread.com
Re: [GENERAL] Why don't custom GUCs show in pg_settings?
On Sat, Apr 30, 2016 at 9:39 AM, Tom Lane wrote: > "David G. Johnston" writes: > > I've seen and can work with various work-arounds but it seems odd that > > pg_settings doesn't show these custom GUCs. > > Because we don't know what type they should be. > > If we're ever to support custom GUCs properly, rather than as a hack that > accidentally sorta works, there needs to be a way to declare them and > establish their properties; whereupon there would be info for pg_settings > to display. > It seems that a "reasonable defaults" policy would be better than nothing...though given the "missing_ok" feature the work-around with pg_settings isn't needed. The existing set_config would setup the text-oriented defaults while, if someone cares enough, a set_custom_config (or overloaded set_config) function could be written that would allow specifying stuff like description, unit, initial value, etc. But what amounts to a session-local hstore seem, whose contents are displayed in pg_settings along with the system GUCs, and is documented, seems like an adequate feature to acknowledge officially. > > Is there a simple way to read the value without receiving an error if the > > value is missing? > > See 10fb48d66, which I don't particularly approve of because it piled > another hack on top of that mess without doing a thing to make it cleaner. > Thanks. I recalled seeing that, just didn't remember that it is new to 9.6 - I only looked at the 9.5 documentation and didn't see it. David J.
Re: [GENERAL] Why don't custom GUCs show in pg_settings?
"David G. Johnston" writes: > I've seen and can work with various work-arounds but it seems odd that > pg_settings doesn't show these custom GUCs. Because we don't know what type they should be. If we're ever to support custom GUCs properly, rather than as a hack that accidentally sorta works, there needs to be a way to declare them and establish their properties; whereupon there would be info for pg_settings to display. > Is there a simple way to read the value without receiving an error if the > value is missing? See 10fb48d66, which I don't particularly approve of because it piled another hack on top of that mess without doing a thing to make it cleaner. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why don't custom GUCs show in pg_settings?
Version - 9.5
O/S - not applicable
SELECT set_config('davidj.testvar', 'value');
SELECT current_setting('davidj.testvar') --value
SELECT * FROM pg_settings WHERE name ~ 'testvar' --or ~davidj... either
way nothing shows
and:
SELECT current_setting('davidj.testvar2') -- unrecognized configuration
parameter
The underlying goal is to use the custom GUC within a CREATE VIEW in a
where clause. If the user issues a valid set_config for the GUC the query
is appropriately filtered otherwise I set things up so that particular
clause will not apply.
I've seen and can work with various work-arounds but it seems odd that
pg_settings doesn't show these custom GUCs.
Is there a simple way to read the value without receiving an error if the
value is missing?
David J.
Re: [GENERAL] Skip trigger?
On Sat, Apr 30, 2016 at 1:38 AM, wrote: > I have a table with a row update trigger that is quite slow. > The trigger finction basically sets some bits in a "changed" column > depending on which values really changed. > For some bulk updates it can be determined in advance that the > trigger function will not have any effect. > Is there any way to run an update query and specify that it should not > activate the trigger. > I know that I can disable the trigger and reenable it later; > however other concurrent updates mights need it Indeed the main issue is how you want to handle concurrency. ALTER TABLE statements to disable triggers works and is transactional, but locks the table, which may be undesired. Here are some useful pointers: http://blog.endpoint.com/2015/07/selectively-firing-postgres-triggers.html -- 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] Skip trigger?
If your other inserts can wait you could maybe combine DISABLE TRIGGER with LOCK TABLE? http://www.postgresql.org/docs/current/static/sql-lock.html Someone else may know a better solution... -- 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] Why are data files stored in /var/lib
W dniu 30.04.2016 o 05:45, Manuel Gómez pisze: > On Fri, Apr 29, 2016 at 11:00 PM, Charles Clavadetscher > wrote: >> I had a discussion yesterday with some friends, who are sysadmins about the >> location of database files. In a default installation >> from a distribution (apt-get install) PostgreSQL creates a cluster unter >> /var/lib/. According to my colleagues /var/lib should not >> contain data that is supposed to last over time. > > Your sysadmin friends should read the excellent > http://www.pathname.com/fhs/pub/fhs-2.3.html#VARLIBVARIABLESTATEINFORMATION I always wandered why /SRV isn't used for that (see: http://www.pathname.com/fhs/pub/fhs-2.3.html#SRVDATAFORSERVICESPROVIDEDBYSYSTEM) Personally, (irrespectively of the package/distribution defaults) I always put my databases in /srv/pgdat - upgrades that disrupt such initial override are seldom. -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Skip trigger?
Hi, I have a table with a row update trigger that is quite slow. The trigger finction basically sets some bits in a "changed" column depending on which values really changed. For some bulk updates it can be determined in advance that the trigger function will not have any effect. Is there any way to run an update query and specify that it should not activate the trigger. I know that I can disable the trigger and reenable it later; however other concurrent updates mights need it Best regards Wolfgang Hamann -- 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] Why are data files stored in /var/lib
On 4/29/2016 8:30 PM, Charles Clavadetscher wrote: I had a discussion yesterday with some friends, who are sysadmins about the location of database files. In a default installation from a distribution (apt-get install) PostgreSQL creates a cluster unter/var/lib/. According to my colleagues /var/lib should not contain data that is supposed to last over time. I am aware that the location can be modified to suit the needs of sysadmins as described under these links. some random thoughts on this... apt-get is a debian/ubuntu thing. the rhel/centos/fedora builds also put postgres in /var/lib, although the details differ. a default built-from-source postgres puts everything in /usr/local on centos, I generally mount a dedicated file system as /var/lib/pgsql/x.y/data, or even as /var/lib/pgsql ... or if the data file storage is already mounted somewhere arbitrary like /u01, then I'll symlink /var/lib/pgsql/9.4 to /u01/pgsql/9.4 or whatever. -- john r pierce, recycling bits in santa cruz
