Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL Rising

wekeroad.com

Post reply on HN
Print view View original

161–170 of 204 posts

Re: PostgreSQL Rising

#161

Post by einhverfr »

AaronBBrown wrote: post #147
einhverfr wrote: post #66

Earlier quoted context omitted.

1) can applications set the SQL_MODE themselves? Can an admin configure the server so applications cannot specify mode? If not, what good is it since it won't guarantee your data? 2) My larger frustration with MySQL is I have run into cases of single transactions deadlocking against themselves. These always happen when the following is true: * Executing an insert statement in the form of INSERT foo (bar) VALUES (1),…

What version of MySQL? Sounds like the old AUTOINC lock mode behavior. http://insatiabledemand.ideeli.com/post/18850015294/diagnosi...

I don't think so. First ID's were being assigned manually, so I don't think a table lock would be pulled out, and secondly only one statement was executing at a time. Only one connection issuing sequential statements, and nobody else on the server.

I think that this is thread/mutex based because of the fact that I could try to run the same series over and over and get different sets failing. So it seems to me the issue might be something environmental affecting timing.

Again the really braindead thing here was that it was only one statement, only one concurrent transaction at any point in time.

Re: PostgreSQL Rising

#162

Post by jeltz »

mgkimsal wrote: post #156
lusr wrote: post #135

Earlier quoted context omitted.

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).

"The fact that multiple transactions can see different states of the data means that there can be no straightforward way for "COUNT(*)" to summarize data across the whole table; PostgreSQL must walk through all rows, in some sense." When operating on a table, my understanding is that pg will select a version and operate on that version. If other versions are being worked on in transactions - that's a different story.…

PostgreSQL does not track any versions at the table level, instead it tracks the versions for every row. This means two queries can modify different parts of the table concurrently without any lock contention.[1]

In PostgreSQL every row has two numbers. The transaction ID it was insert in and the transaction ID it was deleted in. An update is an insert plus a delete.[2] When running a select in PostgreSQL you just traverse the table and for each row check these two numbers to know if you are allowed to see the row.

The details above are PostgreSQL specific but most other databases have the same problem with there being no way to know the exact count without actually counting the rows.

Footnotes:

1. There is contention currently in PostgreSQL when writing the database journal (used for crash recovery and replication).

2. There are some optimization which are done here. For example HOT to avoid index updates.

Re: PostgreSQL Rising

#163

Post by harshreality »

taligent wrote: post #119
epo wrote: post #104

Earlier quoted context omitted.

Typical fanboy bluster. What's dishonest about the criticism, be specific? What you really mean to say is that most MySQL users use it because it is there and take it as it has been configured for them. MySQL is the storage platform for people who do not know about databases and probably only use it because some blogging product requires them to.

> MySQL is the storage platform for people who do not know about databases. Exactly. I mean seriously what do Facebook, Twitter, Yelp, LinkedIn, Flickr etc know about storing lots of data.

Even when a company like Google or Facebook uses "mysql", it's not stock Oracle MySQL -- which is what people are going to download and use when they hear that MySQL is good enough because major companies use it. This is an unfortunate situation caused by most distributions still favoring stock mysql over percona, and Oracle refusing to incorporate patches even when they're plenty of evidence that people who know what they're doing want those patches.

It seems like Oracle is more concerned about trying to keep mysql less well suited for major deployments, so it can upsell its flagship product. Or else their development process simply doesn't properly accommodate community assistance. Either way, as long as their resistance to community-aided development continues, mysql and oracle/mysql disparagement will continue, not because people hate Oracle, but because Oracle's management of mysql makes it less than ideal for those who simply want a good open source database.

Which is the point of the OP. Anyone who is open to looking at options and who wants a good open source database should look at PostgreSQL.

I doubt any of those example companies you listed even use stock Percona. Facebook for sure has extensive modifications beyond even the publicly available distributions like Percona and MariaDB.

Re: PostgreSQL Rising

#164

Post by ruckusing »

SkyMarshal wrote: post #49
mekoka wrote: post #16

On my way to build a multi-tenant application I went through a great deal of articles recommending various architecture strategies. I was looking for an approach to organize the data for the app's various customers (multi-tenant). Most recommendations revolved around 2 solutions: 1 db per tenant, or 1 db for all tenants with a tenant_id in each table. Lucky me , I eventually stumbled upon a thread where someone menti…

Wow, just this week I started a project that will be my first multitenant website, using PostgreSQL no less, and have been wondering how to handle that in the db. I take a break, pop onto HN, and the top comment of the top story explains how to do exactly that. Thanks! You wouldn't happen to have come across any good tutorials on using PG schemas for this purpose have you? Also, do schemas provide enough separation o…

This is a good post all about using PG Schemas and Rails/ActiveRecord. Ideas could be adapted for Django, etc.

http://blog.jerodsanto.net/2011/07/building-multi-tenant-rai...

Re: PostgreSQL Rising

#165

Post by twerquie »

spudlyo wrote: post #33
chimi wrote: post #32

Earlier quoted context omitted.

PostgreSQL is massively more scalable than MySQL.

Definitely. When your application needs hundreds of read slaves in order to scale the load, PostgreSQL has always been everyone's first choice due to it's mature and historically awesome replication system. That's why companies who need to scale big (YouTube, Facebook, Yahoo, LinkedIn, Wikipedia, Twitter etc) all have hundreds (if not thousands) of PostgreSQL machines in their infrastructure.

The technology stacks for these companies, by and large, were selected a decade ago or more. I'm no more surprised they chose MySQL as their database as I am to see that they chose PHP or Java as their core language (in some cases).

Re: PostgreSQL Rising

#166

Post by atombender »

mgkimsal wrote: post #127
jeltz wrote: post #123

Earlier 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 -…

You can do a fast, conditionless count that ignores current transaction state and therefore risks being slightly out of date, but usually good enough for most use cases:

    select n_live_tup
    from pg_stat_user_tables
    where relname = 'mytable'

Re: PostgreSQL Rising

#167

Post by papsosouid »

lusr wrote: post #126
rosser wrote: post #121

Earlier 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…

>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.

That is how the postgresql API works as well. Most high level language modules built on top of it also work that way. Unfortunately python's DB API is not one of them, but you can just set a config option to "act like everyone expects".

Re: PostgreSQL Rising

#168

Post by jeffdavis »

taligent wrote: post #149
jeffdavis wrote: post #83

Earlier quoted context omitted.

I see it as a major success story. Postgres was developed over a long period of time with careful attention to architecture, robustness, and extensibility. It also focused on both external and internal documentation and cleanliness . And, it followed the traditional database system model with a cost-based optimizer and everything else. As a result, we see a very robust developer community. It's a real machine -- chur…

Your entire argument falls apart when you look at all of the companies that chose MySQL over PostgreSQL and are still using them today. Basically every major internet site today relies on MySQL.

First of all, the success of postgresql and mysql are not mutually exclusive. Postgres operates in a lot of markets that MySQL doesn't (as far as I know) like telecom and finance. Postgres gets entirely new users with a variety of use cases, as well as people from Oracle, SQL Server, MySQL, and even some converts from people who tried NoSQL and found it lacking in some way.

An undeniably, there is a shift happening, even in parts of the market that were a MySQL stronghold. Heroku and their customers use postgres for multi-tenancy. Instagram uses postgres for photo-sharing.

I think MySQL has been very complacent because they are the default for a lot of simple web apps, and that keeps their numbers high. They aren't really breaking into new markets -- postgres is winning the geospatial market big time, and is always coming out with new features to break into new markets (personally, I am trying to advance postgres into the temporal database space).

Re: PostgreSQL Rising

#169

Post by snambi »

i believe one of the main reasons for mysql's popularity is that it is available by default with many hosting providers who provide PHP based hosting for a very cheap price. These are low-medium volume sites developed by a small teams. They just don't need or care about the benefits of using a better database like 'postgres'.

IMHO, postgres developers and support organizations need to work with hosting companies to make postgres available along with myql.

Re: PostgreSQL Rising

#170

Post by j_s »

lusr wrote: post #126
rosser wrote: post #121

Earlier 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…

Can you point to any open source projects or other examples implementing this pattern? I think I understand where you're coming from on this but I have not seen many examples of this approach in the wild (except religiously using TransactionScope, eg. http://amnesia.codeplex.com/ ).

I'm wondering where it's best to manage the transactions... would they ever be in raw SQL or stored procedures instead of .NET code?

Post reply on HN