tag:blogger.com,1999:blog-7476449567742726187.post1013217834410362230..comments2023-12-18T12:25:52.296-08:00Comments on Database Soup: PostgreSQL New Development Priorities 5: New User ExperienceJosh Berkushttp://www.blogger.com/profile/09671139717468724246noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-7476449567742726187.post-18271052171674360282013-07-01T11:09:20.878-07:002013-07-01T11:09:20.878-07:00you mean www.postgresguide.com - not .orgyou mean www.postgresguide.com - not .orgAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-54080342491148774252013-05-22T04:10:40.737-07:002013-05-22T04:10:40.737-07:00Thanks for a reply Josh!
Yes, "Huh" ind...Thanks for a reply Josh!<br /><br />Yes, "Huh" indeed :). When I first start working for a client I was overwhelmed by their ETL process (if I can name the process as such!?) - 2.5TB (on three production servers, not one!) of logs are generated on ~20 schemas. A lot of data matching and data imputation is going on each day with SAS, then based on "what-if" analysis statisticians decide when the data is good enough, so yes many updates (often repeated) are running against relatively small data sets until our "statisticians" are satisfied withe the results. Of course, not all indexes could be disabled before each update runs, so Oracle is generating undo + redo for index entries as well...the net results is that at the peak time I get 1GB redo generated / per minute.<br />Not a typical RDBMS workload, I guess. We're not dealing with OLTP at all. I'm sure we could cut redo in half if we could rebuild (rethink) the system from scratch -- I hope Oracle will follow PG some day and introduce unlogged tables (even better if we could get off the Oracle bandwagon someday).<br />Nevertheless, I can't imagine doing recovery with PostregreSQL on Friday, restoring Sunday backup and then rolling forward with 2+TB (or even half of that) of logs!?<br /><br />Hi, beaud76<br /><br />thanks, I'll take a look. However, keeping audit trail for all SELECT's on tables with personal data (national wide id number, name, tax id, address) of is a must have feature for us. When we get auditor the first question is: "Can you tell me who was looking at my data and when?". Having audit trail about I/U/D is unfortunately not enough. We have to provide the trail who browsed what, when. Having full select statement with bind variable value in a trace is essential for us. That's why I'm stuck with Oracle for now. :-(<br /> Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-69286000609266850272013-05-22T01:21:28.570-07:002013-05-22T01:21:28.570-07:00Hi AlesK,
Regarding auditing feature, you can have...Hi AlesK,<br />Regarding auditing feature, you can have a look at E-Maj. It's a pg extension available on pgfoundry, github or PGXN. It allows to keep a trace of all updates (INSERT/UPDATE/DELETE) on set of tables, with some rollback capabilities. It is already used by some large organizations on test or production databases. A plugin for phpPgAdmin also provides a nice GUI for E-Maj administration.<br />If you give it a try, I would love to get your feedback on what improvements would be helpful for your needs, if any. (email address at the end of the extension README file).<br />Regards.<br />Philippe Beaudoin.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-70677441112008028292013-05-21T14:03:57.041-07:002013-05-21T14:03:57.041-07:00Mongo and Postgres are very different beasts.
If y...Mongo and Postgres are very different beasts.<br />If you use Postgres it's because you need a relational database otherwise there are other maybe simpler choices.<br />Compared to Oracle, Postgres is far easier to install and use and was one of the reasons we switched from Oracle to Postgres and never come back.<br />If you are a serious developer you owe to yourself and your customers to know your database very well. It's part of what makes you a professional.<br />I understand many people don't write ERP programs nowadays but I think the other four priorities cited are by far more important.<br />Luca Veronesehttps://www.blogger.com/profile/10889674744901410391noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-82351163767337343492013-05-21T13:48:33.198-07:002013-05-21T13:48:33.198-07:00Huh. How is it you're generating 2.5TB of WAL...Huh. How is it you're generating 2.5TB of WAL logs, but that only represents 10GB of actual changes? Are you rewriting the same rows over and over again?Josh Berkushttps://www.blogger.com/profile/09671139717468724246noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-87303477079330423172013-05-21T13:41:55.560-07:002013-05-21T13:41:55.560-07:00Here are two of the priorities that I would love t...Here are two of the priorities that I would love to see be done in the future PG releases.<br /><br />I'm an Oracle DBA looking for a way to migrate some of our databases to PostgreSQL. I truly love PG, but, and this is a big but for us, our databases store personal data, hence auditing is a must have feature that PG is completely missing. Built-in AUDIT statement that can track insert,update,delete *and* selects would be a good starting point, but having something like Oracle Fine Grain Auditing in PG would take our socks off...a killer feature.<br />I believe that a complete lack of AUDIT capabilities in PG is certainly a show stopper for many enterprises that are obligated to keep auditing trail. (Btw. coding our own triggers is out of the question for several reasons, not to mention that I don't see an easy (easy for a DBA) solution to track select statements with values passed as bind variables in PG?) <br /><br />Second show stopper is "weak" (elementary?) backup & recovery tools available in PG. We can't really treat pgdump as a backup tool that we would want to use for any db larger than a few gigabytes; at least not on a daily basis. PG needs true full/incremental *binary* backups and recovery (comparable to Oracle RMAN); if we changed today five blocks out of fifty, we want to backup just those five blocks at night, nothing else. We're generating a half a terabyte of redo logs each day, still nightly incremental backup produces a couple of gigabytes incremental backup file at best.We could not afford to apply 2.5TB WAL logs generated during weekdays on top of a base backup - if we know that all that is really needed is applying 10GB from incremental backup. Am I alone on this? Hope not! :) <br />Keep up with the good work!!<br /><br />Regards,<br />AlesKAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-55917686992470748202013-05-21T08:11:47.050-07:002013-05-21T08:11:47.050-07:00User experience is a tough one. But I also do thin...User experience is a tough one. But I also do think a low barrier to entry is related to adoption. <br /><br />When you are just starting you go with what is easy and works out of the box. Then as you grow you tend to stay with it even if its not the "best" tool for the job. The MySQL scale-out stories are often along these lines, especially historically when MySQL was not as robust. <br /><br />Without getting into what is right the user account configuration stories are generally simpler in new databases.<br /><br />The lowest barrier to startup I can think of easily is a quickstart option that would run under any account (including root) without complaint, bind to a non-privileged port, allow all local connections and have a blank postgres password. <br /><br />The blank password I've noticed speeds up quickstart tooling integration. If you run phpPgAdmin / pgAdmin or anything else, they can auto-check the port / postgres / blank password combo and also "just work" out of the box, no mucking around in configuration files. <br /><br />It seems like a trivial difference to folks with experience getting things going, but ages ago in another life I tried postgresql and mysql, and ended up going with mysql because it "just worked" when we built our "test project" over a weekend that then grew of course into a multi-year project with a scale out story. If I'm not mistaken MySQL at the time didn't just have blank passwords for their root user but may also have had anonymous user super user access? I just remember the default as being essentially no security. <br /><br />I also have to agree with David, SQL isn't that bad. What's odd is that it can take more work to understand how an ORM will join something up then to code the joins and relationships directly. With the exception of projects with very broad adoption (django etc) I wonder if for many projects being database "agnostic" actually is a value add, it's still hard to move databases and you generally can't exploit database features fully and remain agnostic. <br /><br />Very much enjoying these posts<br /><br /> Augusthttps://www.blogger.com/profile/05520124197669792376noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-53142965058841840462013-05-21T06:33:36.049-07:002013-05-21T06:33:36.049-07:00I'd like to see what fruits are lower-hanging....I'd like to see what fruits are lower-hanging. SQL has an undeserved reputation for being difficult, a driving force behind many of the ORMs. Maybe something that helps explain it in clearer terms...David Fetterhttps://www.blogger.com/profile/06408194859600882631noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-67741963145481319682013-05-21T06:26:33.099-07:002013-05-21T06:26:33.099-07:00Would it help the user experience if error message...Would it help the user experience if error messages contained http links to the Postgres documentation? For example, if a user tries to connect to a database, but gets the "no entry in pg_hba.conf" error, would it help to include a link to http://www.postgresql.org/docs/9.2/static/auth-pg-hba-conf.html? You could do this for SQL syntax errors as well, providing links to the documentation for DML.<br /><br />I understand that you're seeking more than just better error messages, but this might make things better.<br />RobJhttps://www.blogger.com/profile/03646889184124137782noreply@blogger.com