I've been neck deep in MySQL for the past 5-6 years, simply b/c that's what my company uses.
I've been wanting to pick up postgres but my only motivation has been curiosity. Any good resources/tutorials people here can recommend?
PostgreSQL Rising
131–140 of 204 posts
Re: PostgreSQL Rising
#132Earlier quoted context omitted.
Can you give any good reason why you need to leave transactions open for extended periods? Offhand, other than xid-level consistency for a backup as mentioned by the sibling post, no. [1] No, this case was pretty clearly the engineers not understanding what they were doing, as evidenced by it being easier to turn off autocommit, and add explicit BEGIN statements where they were needed, than add explicit ROLLBACKs eve…
I'm not familiar with PG but I am planning on using it in my current project, which is why I'm interested in understanding whether these are really DBMS issues. In the .NET world, for all the database adapters I've used (MSSQL, SQLCE, Oracle, MDB), individual statements are implicitly wrapped in transactions when no explicit transaction has been specified - not sessions/connections. I agree the "implicit transaction…
The PostgreSQL official C client library, libpq, (which most language specific wrappers are based on) works the same way as you say the .NET adapters do. Every statement is by default implicitly wrapped in a transaction until you run "BEGIN;".
Re: PostgreSQL Rising
#133I would really appreciate it folks can try out my free and open source GUI client for postgres: http://pgXplorer.com . It is available for Mac, Ubuntu (64) and Windows (64).
That looks great actually. I've recently switched to PG and use pgAdmin III, but find it too confusing with tables being hidden so deep in the tree (or maybe that's "pg way" of doing things with tables not being centric?)
Thanks a lot for your kind words. If there are any specific features you would like, feel free to email me or raise an issue on the projects' github page.
By the way, pgXplorer has a quick find feature that can highlight the searched table/view/function. Hopefully, you find that useful. It is a really useful feature especially when dealing with CJK table names.
Re: PostgreSQL Rising
#134Earlier quoted context omitted.
Your benchmarks especially demonstrate the fact that PostgreSQL tries to do as little work as possible when adding a column or dropping a column (or in recent versions of PostgreSQL when changing the type of a column). If you do not have a default value for the new column PostgreSQL only has to update the table definition which is almost instant. Another interesting part about your benchmarks are that they throw seri…
"... throw serious doubts on the popular myth that count( ) is slow in PostgreSQL." Whew! Glad that was only a myth! I'll remind myself of that whenever my code takes > 10 seconds to return the count( ) from a table. "Just a myth - this isn't really happening". I'll try repeating that to myself while waiting for the results - probably should only take 10-15 repeats of the phrase before the row returns, right? Whoah -…
I was unclear the part that is a myth is that they are slow in _PostgreSQL_. COUNT( ) is actually slow in general on databases with high concurrency requirements. Because you have to sacrifice something to make COUNT( ) fast.
If you had read his benchmark you would have seen COUNT( ) was slow in both databases (three times as slow in MySQL).
Re: PostgreSQL Rising
#135Earlier quoted context omitted.
Your benchmarks especially demonstrate the fact that PostgreSQL tries to do as little work as possible when adding a column or dropping a column (or in recent versions of PostgreSQL when changing the type of a column). If you do not have a default value for the new column PostgreSQL only has to update the table definition which is almost instant. Another interesting part about your benchmarks are that they throw seri…
"... throw serious doubts on the popular myth that count( ) is slow in PostgreSQL." Whew! Glad that was only a myth! I'll remind myself of that whenever my code takes > 10 seconds to return the count( ) from a table. "Just a myth - this isn't really happening". I'll try repeating that to myself while waiting for the results - probably should only take 10-15 repeats of the phrase before the row returns, right? Whoah -…
http://wiki.postgresql.org/wiki/Slow_Counting
Are there DBMSs where this doesn't happen?
I just did a SELECT COUNT(*) on a table here in our QA environment; 20.5 seconds to count 42385875 records from one table and 68.8 seconds to count 191906711 records from another table (Oracle 10g 64-bit).
Re: PostgreSQL Rising
#136Can the lack of mixed DML/DDL transactions be worked around with a change to my.conf?
non transactional migrations always seem to trip me up in mysql, cleaning up the mess is a nightmare
Re: PostgreSQL Rising
#137Earlier quoted context omitted.
That certainly is odd behavior however reading through the whole email thread that seems to be one complicated issue with a two processes obtaining different locks to the same row. Probably not ideal, but maybe I need to recalibrate my definition of braindead because in real-world examples, that would be highly annoying.
It happened to us in the real world. And was fixed in 9.2
ouch. Reminds me of the (one) data corruption bug I found in PostgreSQL 7.3, allowing me to create write-only tables....
Re: PostgreSQL Rising
#138I'd love to move away from Oracle to Postgres, I really would. I'm trying to. But for massive amounts of data the partitioning and some other features of Oracle just work better. The partitioning is a huge thing, especially for our data which is partitioned by week then organized according to a hierarchical triangular mesh with bitmapped indexes. This works so well for us (at 8 billion rows) it's silly. MySQL couldn'…
There could be a good reason for Oracle to choose a hash join. Do you have a large table joining to a small table? Multiple processors? Setting the PGA might also be influencing optimiser...
The biggest issue with Oracle is that even experts don't really know how the optimiser does something. Tuning queries in Oracle s a bit of a black art. It really shouldn't be!
Re: PostgreSQL Rising
#139One thing I've always loved about SQL Server is the Adventure Works OLTP and data warehouse DB samples, they are absolutely priceless when it comes to experimenting and learning. Is there something like that for Postgres? Is Postgres even intended to be used as a data warehouse and, if so, are there any decent business intelligence tools that can work with a Postgres warehouse? I'm not looking for an open source Analysis Services/Business Intelligence Studio equivalent, just something that I can build some cubes with and run MDX queries against.
Re: PostgreSQL Rising
#140Earlier quoted context omitted.
Unrelated, but hopefully a good place to stick this: What was that Mac 10.7-only, command-line but standalone database tool, designed for high-level analysis of data via queries?
Are you thinking of this? http://inductionapp.com/
Yes, __Thank You!__
I've been spending hours at a time looking for it, spamming here was a last resort. (Throwing "Postgres" in my searches was what was fouling them—turns out it was just spawned by Heroku's Postgres team...)