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

PostgreSQL as Schemaless Database [pdf]

wiki.postgresql.org

Post reply on HN
Print view View original

81–90 of 90 posts

Re: PostgreSQL as Schemaless Database [pdf]

#81

Post by nnq »

jacques_chester wrote: post #23
nnq wrote: post #22

Earlier quoted context omitted.

Care to elaborate on your last sentence? I'm really curious about some corners of software history and this seems like a hint of a corner I never knew existed...

Basically, hierarchical and network databases were around before relational databases. Both of them have the problem that they require a lot of up-front planning. When you build a hierarchy, you need to know how it's going to be used as that will determine the structure. Turning hierarchies into networks -- ie trees into graphs -- eases this a bit but you still have the problem of arranging things for easy navigation…

Kudos for the history lesson. I can't believe IBM still has a market for IMS (!?!), with all the nice ways of combining SQL and "NoSQL" nowadays... Are they hypnotizing their clients or what?

Re: PostgreSQL as Schemaless Database [pdf]

#82

Post by mvc »

jfb wrote: post #66
mvc wrote: post #59

Earlier quoted context omitted.

Here's a question for people who prefer to put their guarantees (i.e. unique/check constraints, foreign keys etc) in their database. How do you present these constraints to the user? How do you show when they've been violated? Just present them with the PG error message? Maintain a mapping of PG errors to more domain specific messages? Bite the bullet and just code them twice (once for the DB, and once for the user)?

One could build out from the database schema -- Postgres certainly provides sufficient type information to build consistency double-checks in other layers of the application. As an added bonus, other consumers of the data won't be flying blind.

Yeah, I'm trying to build something like that right now. Nice to know others have similar ideas.

Re: PostgreSQL as Schemaless Database [pdf]

#83

Post by forgotAgain »

sendob wrote: post #73
forgotAgain wrote: post #63

Almost 6,000 synchronous commits per second to a 7,200 rpm disk seems high to me. Anyone seeing those numbers for any database that synchronously writes the log to disk?

http://pgeoghegan.blogspot.com/2012/06/towards-14000-write-t... may interest you

While very impressive, I don't see how your post is applicable in this case. The OP at the very least implies that the transaction are done end to end and not batched. I find 6,000 distinct disk I/O operations per second, to append the log, very very high for a 7.2k disk.

Re: PostgreSQL as Schemaless Database [pdf]

#84

Post by sendob »

forgotAgain wrote: post #83
sendob wrote: post #73

Earlier quoted context omitted.

http://pgeoghegan.blogspot.com/2012/06/towards-14000-write-t... may interest you

While very impressive, I don't see how your post is applicable in this case. The OP at the very least implies that the transaction are done end to end and not batched. I find 6,000 distinct disk I/O operations per second, to append the log, very very high for a 7.2k disk.

that post, also discusses using a 7200rpm disk.

I think where you may be confused is considering them as distinct IO operations, vs seeing it as a point which must have been flushed past ( sequentially )

Re: PostgreSQL as Schemaless Database [pdf]

#85

Post by forgotAgain »

sendob wrote: post #84
forgotAgain wrote: post #83

Earlier quoted context omitted.

While very impressive, I don't see how your post is applicable in this case. The OP at the very least implies that the transaction are done end to end and not batched. I find 6,000 distinct disk I/O operations per second, to append the log, very very high for a 7.2k disk.

that post, also discusses using a 7200rpm disk. I think where you may be confused is considering them as distinct IO operations, vs seeing it as a point which must have been flushed past ( sequentially )

No, I don't think I'm confused. The OP says postgresql is used in its default configuration:

Stock PostgreSQL 9.2.2, from source. No changes to postgresql.conf.

Given that statement then either the writes are serialized or postgresql is not ACID compliant in its default configuration. I'm not an expert on postgresql but I assume that it is ACID compliant in its default configuration. Therefore my skepticism on the 6K writes per second.

Re: PostgreSQL as Schemaless Database [pdf]

#86

Post by sendob »

forgotAgain wrote: post #85
sendob wrote: post #84

Earlier quoted context omitted.

that post, also discusses using a 7200rpm disk. I think where you may be confused is considering them as distinct IO operations, vs seeing it as a point which must have been flushed past ( sequentially )

No, I don't think I'm confused. The OP says postgresql is used in its default configuration: Stock PostgreSQL 9.2.2, from source. No changes to postgresql.conf . Given that statement then either the writes are serialized or postgresql is not ACID compliant in its default configuration. I'm not an expert on postgresql but I assume that it is ACID compliant in its default configuration. Therefore my skepticism on the 6…

from the link: "In Postgres 9.2, this improvement automatically becomes available without any further configuration."

"Essentially, it accomplishes this by reducing the lock contention surrounding an internal lock called WALWriteLock. When an individual backend/connection holds this lock, it is empowered to write WAL into wal_buffers, an area of shared memory that temporarily holds WAL until it is written, and ultimately flushed to persistent storage."

"With this patch, we don’t have the backends queue up for the WALWriteLock to write their WAL as before. Rather, they either immediately obtain the WALWriteLock, or else queue up for it. However, when the lock becomes available, no waiting backend actually immediately acquires the lock. Rather, each backend once again checks if WAL has been flushed up to the LSN that the transaction being committed needs to be flushed up to. Oftentimes, they will find that this has happened, and will be able to simply fastpath out of the function that ensures that WAL is flushed (a call to that function is required to honour transactional semantics). In fact, it is expected that only a small minority of backends (one at a time, dubbed “the leader”) will actually ever go through with flushing WAL. In this manner, we batch commits, resulting in a really large increase in throughput..."

I am sorry to cut and paste so much of the article, but I hope this is helpful?

Re: PostgreSQL as Schemaless Database [pdf]

#87

Post by forgotAgain »

sendob wrote: post #86
forgotAgain wrote: post #85

Earlier quoted context omitted.

No, I don't think I'm confused. The OP says postgresql is used in its default configuration: Stock PostgreSQL 9.2.2, from source. No changes to postgresql.conf . Given that statement then either the writes are serialized or postgresql is not ACID compliant in its default configuration. I'm not an expert on postgresql but I assume that it is ACID compliant in its default configuration. Therefore my skepticism on the 6…

from the link: "In Postgres 9.2, this improvement automatically becomes available without any further configuration." "Essentially, it accomplishes this by reducing the lock contention surrounding an internal lock called WALWriteLock. When an individual backend/connection holds this lock, it is empowered to write WAL into wal_buffers, an area of shared memory that temporarily holds WAL until it is written, and ultima…

Looking at your linked posting more closely I do not believe it is applicable to this situation for two reasons. Again I'm not an expert on postgresql but:

1) It appears to me that the technique discussed in your linked post is about ganging unrelated transactions together into a single flush to disk. I do not see that to be the case in the OP. Since all of the writes are going to the same table they are related.

2) Looking at the second graph in the linked post pgbench transactions/sec insert.sql. The number of clients is high. I got the impression from the OP that there was only a single client. Indeed if there were more than a few clients the benchmark would have been subject to the deficiencies of the client libraries used.

Re: PostgreSQL as Schemaless Database [pdf]

#88

Post by sendob »

forgotAgain wrote: post #87
sendob wrote: post #86

Earlier quoted context omitted.

from the link: "In Postgres 9.2, this improvement automatically becomes available without any further configuration." "Essentially, it accomplishes this by reducing the lock contention surrounding an internal lock called WALWriteLock. When an individual backend/connection holds this lock, it is empowered to write WAL into wal_buffers, an area of shared memory that temporarily holds WAL until it is written, and ultima…

Looking at your linked posting more closely I do not believe it is applicable to this situation for two reasons. Again I'm not an expert on postgresql but: 1) It appears to me that the technique discussed in your linked post is about ganging unrelated transactions together into a single flush to disk. I do not see that to be the case in the OP. Since all of the writes are going to the same table they are related. 2)…

you are good to be suspicious into any benchmark(as even the original slides note). I dont think we really have any information as it relates to transaction boundaries present, nor clients used:

From the slides: "Scripts read a CSV file, parse it into the appropriate format, INSERT it into the database. • We measure total load time, including parsing time. • (COPY will be much much much faster.) • mongoimport too, most likely."

Postgres does its best to use intelligent defaults, but it is only a part of the system, it is generally up to practioners to be wary. Tools like: http://www.postgresql.org/docs/current/static/pgtestfsync.ht... Assist in this goal, but generally have to watch out for things like raid controllers that are not battery backed etc ( depending upon your environment).

There is no mention of the number of clients used directly.

I was simply trying to highlight that with the (little) information available, it is very possible.

It is also possible, that the session(s) should themselves choose to pursue an asynchronous commit strategy ( on a session level see:http://www.postgresql.org/docs/9.2/static/wal-async-commit.h... ) which would also not require modifying the configuration, I do not know as I have not seen the scripts, but it is similar to how a library could interact with:

http://docs.mongodb.org/manual/reference/command/getLastErro...

Thanks.

Re: PostgreSQL as Schemaless Database [pdf]

#89

Post by forgotAgain »

sendob wrote: post #88
forgotAgain wrote: post #87

Earlier quoted context omitted.

Looking at your linked posting more closely I do not believe it is applicable to this situation for two reasons. Again I'm not an expert on postgresql but: 1) It appears to me that the technique discussed in your linked post is about ganging unrelated transactions together into a single flush to disk. I do not see that to be the case in the OP. Since all of the writes are going to the same table they are related. 2)…

you are good to be suspicious into any benchmark(as even the original slides note). I dont think we really have any information as it relates to transaction boundaries present, nor clients used: From the slides: "Scripts read a CSV file, parse it into the appropriate format, INSERT it into the database. • We measure total load time, including parsing time. • (COPY will be much much much faster.) • mongoimport too, mos…

Nice discussion, thank you.

Re: PostgreSQL as Schemaless Database [pdf]

#90

Post by eru »

xkcdfanboy wrote: post #68
weisk wrote: post #33

sorry but the first thing I readt was "PostgreSQL the shemales database". Confusing ..

Same here, don't feel bad. And to the downvoters, the correct word is aschematic or schema-less with a dash. If a word ends in a vowel, you must use a dash. Hatless is fine, but scrupleless should be scruple-less. The more you know, you scruple-less bastards!

> If a word ends in a vowel, you must use a dash.

Feeling prescriptive these days?

Post reply on HN