Why Use Postgres - Part 2
craigkerstiens.com
craigkerstiens.com
My biggest issue with it having used it for other projects is getting the dammed thing setup and working to begin with. I could never find a decent tutorial (or rather one that fits my mindset) of how to do the following,
1. Install 2. Setup users, including how to set it up on a local development machine with 'root' user who can do anything (saves time in dev) 3. Import/Export SQL/Backup files
I managed to do all 3. Once.
I'm sure its out there, and I would switch in a heartbeat were these steps much easier to work out. As it is I can just copy the table, run the alter in the background and rename the tables the next day. Slower, but doesn't cost me any time and works.
sudo apt-get install postgresql
sudo -u postgres createuser -s `whoami`
createdb my_db
psql my_dbIn your case, then I would just change pg_hba to allow all local connections as shown by moe above. (I would not set any password though since that is normally not useful or necessary for local development).
sudo -u postgres createuser -s `whoami`
EDIT: No it won't:
Ophelia ~ $ sudo -u postgres whoami
postgres
Ophelia ~ $ sudo -u postgres echo `whoami`
rich
In my experience most people stumble because the network security in postgres is pretty tight by default.
This is easily fixed and needs to be done only once.
First: Find your pg_hba.conf. It's in the database-directory, that's often linked to /etc/postgresql.
# Backup the original
$ cp pg_hba.conf pg_hba.conf_orig
# Now replace it with our desired security settings
$ cat >pg_hba.conf <<EOF
# Require password auth from remote hosts
# localhost and local socket are trusted
host all all 0.0.0.0/0 md5
local all all trust
host all all 127.0.0.1/32 trust
EOF
# restart
$ /etc/init.d/postgresql restart
# connect as user postgres (the super-user)
$ psql -U postgres
postgres=# create database dummy;
postgres=# create user bob with password 'pony';
postgres=# grant all on database dummy to bob;
postgres=# ^D
# Now you have a database that user bob can use.
# From remote he will have to use the password 'pony'.
# From localhost no password needed because of our pg_hba settings above.
$ psql -U bob dummy
bob=> create table ... /etc/init.d/postgresql reloadHowever, once you're over that initial learning hump and start looking for more specific things you'll quickly notice why the postgres manual is often cited as being one of the best documentations ever written (inside and outside the OSS world). It's really that good - once you're familiar with a few basics.
However, once you're over that initial learning hump...you'll quickly notice why the postgres manual is often cited as being one of the best documentations ever written
"It's really that good - once you're familiar with a few basics."
Outside the official docs you can find the same number of newbie tutorials for postgres that you'll find for MySQL. The difference is: Postgres has a stellar documentation once you're leaving the newbie stage. What MySQL calls documentation, let's just not talk about it...
I'm sorry if in this comment I spoke out of frustration, but I do stand by the thoughts about what a gold standard of a manual has to at a minimum provide. We're not talking about a "little investment". Read his original comment again.
if someone has enough experience with sql and enough mysql experience that they're really hitting limitations in mysql, then a merely "good" or even "ok" manual should at a minimum be able to enable that person's good-faith, dedicated attempt to start using this alternative to succeed. This is a pure horizontal transition. Postgresql is not only "also a database" but "also sql" and "a direct horizontal competitor to mysql" -- the technologies substitute each other other, often on a drop-in basis (in LAMP-type and modern stacks, especially with a framework that uses an ORM mapper that can use either). If someone is familiar with SQL and familiar with an alternative, the manual should at a minimum help the person transition to using this alternative. If nothing else. I would say this is the absolute minimum that a manual would have to provide to meet its basic mission.
At an absolute minimum, a manual MUST let you move horizontally from a drop-in competitor you have some competency with.
(I feel like, even historically, this has been missed by a lot of people.)
This is why I took umbrage with your attitude "why the postgres manual is often cited as being one of the best documentations ever written" after saying: "Yes, the docs could use indeed a few lighter tutorials for people starting out" in response to the guy who simply could not get past the installation hurdle.
This is like a stereo that someone can't turn on. They've used other stereos, but they just can't figure it out. They read the manual, but still can't figure it out. I would argue that, unless it's a real "duh, I was such an idiot" moment (happens to everyone) and isolated case, and it sounds like this isn't what you're saying, then in such a case the manual failed to do its primary mission. If the guy could have turned it on, he would have figured everything else out by himself.
If this guy can't do what he's trying to do after knowing mysql the way he's talking about and having the experience with attempting postgresql the way he talks about, I'd say that while the manual might be a fine technical reference, it is not yet complete and must not be held up as a great manual until this hole is filled.
I think I'm basically not winning any friends with this line of thinking, but I just see this as a long, recurring problem. It would be the same with a certain open source movement's long-standing alternative to a certain very well entranched operating system: they are horizontal substitutes for each other, and yet people who are expert on the entrenched system (power users, even) have given up on the open source alternative, because they "could not turn it on". (Get to the same place they get to after a fresh install of their entrenched operating system).
I'm not going to say more because these are (whatever those-things-that-are-landmines-floating-in-water are called)-filled waters.
The only place where this is an issue would be hosts with multiple untrusted users, and these are becoming very rare.
getting a local unprivileged shell isn't exactly hard
Sorry, that's nonsense. The entire internet relies on the fact that this is relatively hard, unless you neglect basic security precautions.
That's an invalid assumption (extremely unlikely).
You must have defence in depth, and not just a hard shell of security
When you can't trust localhost anymore then your defenses have long failed.
You misread me. I meant the opposite.
Your argument was based on the premise "assuming there isn't a local privilege escalation attack to which your kernel is vulnerable". I said this premise is invalid (as you just confirmed yourself).
You don't trust your locked front door to protect your valuables, you put them in a safe.
The front door is your firewall. The safe is your host. When someone breaks into your host then it's game over. When you have too much spare time you can attempt to layer further at the host-level but that's usually an exercise in futility.
http://petereisentraut.blogspot.com.au/2010/03/running-sql-s...
http://lobotuerto.com/blog/2009/07/20/como-instalar-postgres...
It covers instalation, setting up the postgres user (the _root_ you want), changing the authentication schema so you can use it for Rails dev (or something else), and finally how to install the Postgres gem for your Ruby.
I am totally a Postgres fan, but there are many sensible reasons not to upset the apple cart.
http://www.postgresql.org/docs/9.1/static/sql-listen.html http://www.postgresql.org/docs/9.1/static/sql-notify.html
listen and notify go back to at least 6.4 which came out 1998 ish http://www.postgresql.org/docs/6.4/static/sql-listen.html http://www.postgresql.org/docs/6.4/static/sql-notify.html
What might have changed is the implementation though. I seem to remember that some point in the past I was investigating listen/notify and the driver (libpq) was forcing you to handle LISTEN by polling (calling PQnotifies periodically) which doesn't really help compared to polling on your own.
This has not changed so far:
http://www.postgresql.org/docs/9.1/static/sql-listen.htm
states
> With the libpq library, the application issues LISTEN as an ordinary SQL command, and then must periodically call the function PQnotifies to find out whether any notification events have been received.
It might be possible for drivers working without libpq by talking the postgres protocol directly on the wire to get real asynchronous behavior, though I don't know anything about how this is being handled on the server right now (it might still be polling internally on the server end).
http://www.postgresql.org/docs/7.1/static/libpq-notify.html
What has happened recently though is that more language bindings to libpq have started exposing the NOTIFY functionality in more convenient ways.
A recent addition to NOTIFY is the ability for notifies to carry payloads (which makes them even more useful)
* the LISTEN/NOTIFY asynchronous notification mechanism now work
Without knowing much about how it works - why would I want to use this system rather than just ZMQ/RMQ/...?
No anymore, though....9.2 supports index only scans in many common cases.
Still, as you say, counting all your tuples repeatedly in a table is going to be much, much more expensive than maintaining the count on categories you care about up-front, paying the lock contention and update-costs as one goes in virtually any database that supports SMP.
SELECT S.nspname || '.' || relname, reltuples
FROM pg_class C, pg_namespace S
WHERE C.relnamespace = S.oid
ORDER BY reltuples DESC
PostgreSQL and most modern, ACID-compliant RDBMSes use MVCC instead of locks. MVCC has a lot of upsides, namely, you almost never need table-level locking for writes, but it ensures you need to look at the whole table to do COUNT. That's because MVCC is basically multiple parallel universes for data. What you see depends critically on which transaction you're in when you look.
For example, suppose you have a process that needs to know the number of users in some user table. You start your transaction, and then another process starts a transaction that goes through and deletes certain users. While this is happening, you need the count. In MVCC, deletion doesn't delete, it just marks rows with the transaction ID range in which they're visible. The rows that are being deleted by the other process are just being marked dead as of that process's transaction ID, but your transaction ID is below that one, so they're not dead to you. Later on, when the youngest transaction alive is older than the oldest transaction that can see these rows, PostgreSQL will actually expunge the data (VACUUM does this).
Because of MVCC, there is more than one legitimate answer the question of COUNT; potentially, one for each transaction in progress. The answer is changing constantly. People tend not to think in these terms; we tend to think, well the table has so many rows in it, right? Why don't you just increment the count when you write one? It's not true, because with ACID compliance, uncommitted transactions should not have visible effects until they commit and transactions in progress should not see anything vary during their operation. So there really are multiple right answers at any given moment. The expectation that SELECT COUNT(*) will be fast is built on the assumption that the database has some sort of master count of what rows are in the table it can just glance at. But it doesn't, and to have one would require removing MVCC altogether and using locks instead.
So, your proposal is basically this: make a metadata table. On every insert or delete from the table, update the metadata table accordingly. MVCC will make sure anybody reading from it will get the right answer. Is that about right?
Now I see it could be worth taking that penalty on specified tables, jsut like you do for indexes. And some databases do support this in a more general form: materialized views.
Explain how you're going to maintain this metadata in more depth. If it can be done in an MVCC manner (i.e. without introducing locks) then it should be explored. How's it going to work?
sorry, I couldn't help myself :-)
I understand that it's semi-hard problem given MVCC but I could keep track of counts myself for each set of conditions and each table that I need. It's as simple as keeping count as record in some table, incrementing it in on insert, decrementing in on delete and adjusting on update if row begins or stops to satisfy conditions I want to count by. The only grudge I have with PostrgreSQL is that when confronted with this problem PostgreSQL fans respond with "We've got MVCC so it is supposed to be slow. Stop thinking it's supposed to be fast." not with "Hmm... Maybe we should introduce new facility similar to indexes where you could define what you want to count and the db will count by this conditions and provide results for you fast."
Plausible, but somewhat bothersome. It could be a special case of the materialized view problem, but it would take some convincing that to suggest that a special count-optimizing physical structure is worth the additional knob and maintenance burden it brings with it. Not impossible to convince, but it would require some tight argumentation with ample evidence.
In my quick assessment, the need for faster counts over lots of data is subsumed by materialized views, which unfortunately is a very tough feature to write, but has the advantage of generally considered being being worth it. A cut-down version of materialized views that just supports counting on various dimensions that adds more bulk to the planner as well as requiring execution maintenance and bug-fixing seems less-worth it. Somewhere in-between is a variant that supports more aggregate functions besides count(), particularly ones where one can define an inverse transition function (which in the case of count is "add one for every record").
There are many interesting features one could add -- and this is one of them -- but at the end of the day there is going to be an assessment by the long-time community members who have the privilege of fixing obscure bugs for years as to:
* Who is going to write it?
* How long will it take vs. other features?
* Is a feature is going to be maintained to a level of quality we find acceptable into the foreseeable future?
* Is the impact worthwhile?
* Could this in any way be done as an extension?
The level of the bar of quality has changed over time, too, pretty much inexorably moving up. Getting LISTEN/NOTIFY in now would be much harder than when it showed up in...Postgres95 (and, granted, it was pretty busted back then, apparently, along with...a lot of stuff). LISTEN/NOTIFY turned out to have a good impact/complexity ratio, so it gets good maintenance, but in a hypothetical world where it was not already committed with a good history of service and use I bet it would have required quite some convincing to add.
There is some old code in existence now that would not be accepted again today. A more sad example is dusty implementation of hash indexes that thankfully few people use or see reason to use. Hash indexes possess ample warnings in the manual to not use them. Yet, it doesn't seem reasonable to deprecate them for people that rely on them, and nobody seems to care enough to improve it, or even convincingly whine about improving it. The community is pretty sensitive to these code barnacles, so if one goes in wanting such a feature, one has to be well-prepared to argue both feasibility of implementation and size and duration of impact.
Some of the people try and get burned because features that MySQL did without anyone noticing now take so much time that one wonders if he triggered some strange bug that caused db to do full table scan where one is unnecessary. Then he goes to the internet and look for clues and he sees response "oh, it's how it supposed to work, nobody is working on because it does not seem like a problem" over and over.
I think that feature might have an impact.
Humble question from person who never tried to build a database engine:
Do you know why db can't just keep track of number of entries in a full or partial primary key indexes and use them to give count fast ?
Just about every non-trivial database implementation will have a pathology that others do not. In this case, InnoDB is was faster because it supported index-only scans, a feature that was very useful, but hard to implement in PG because of some details of its MVCC implementation -- now there is an implementation in 9.2 that gives it a shot at being in the same class of performance even on wide-row tables. However, both are still much slower than MyISAM: if you search for "why is count slow on innodb?" you'll get a lot of hits -- it suffers much the same defect vs. MyISAM (which has poor SMP concurrency, allowing it to count things as a luxury) that Postgres does.
Perhaps "not a problem" is a lazy answer for "it's hard to get exactly right, and nobody in the same class of implementation is bothering, and this has been discussed to death and is more work than you realize, so don't expect anyone to implement it soon unless you intend to argue it (unless you are also a long-term maintainer) and do it." As you can see...that was many more words, including giving background on two MySQL storage engines.
> Do you know why db can't just keep track of number of entries in a full or partial primary key indexes and use them to give count fast ?
It could. But you'd have to spec out a brand new top-level set of utility commands (like CREATE INDEX) to make this physical structure, it now is yet another knob that can drastically change performance characteristics, the planner gets to scan the projections and qualifiers for yet another little optimization, someone else gets to maintain it (unless you are maintainer), and then there's going to be the person that asks "why isn't is this snapshot-isolated version not even nearly as fast as caching the count in memcached?" (or, if one does it the other way, the reverse), and then finally when you get around to implementing a more general set of functionality you'll be stuck with this old form for basically eternity (depending on your release policy) much like hash indexes. Don't forget to add backup-and-restore support, manual pages, and EXPLAIN and/or other diagnostic output, if necessary.
On the plus side, it could end the endless howling about this problem.
Personally, I think we can get enough hooks in place that someone should be able to implement this secondary structure in a satisfying way without coupling it inextricably into the database, and that's a project I'd have enthusiasm for.
If someone else made this feature in another popular database that serves similar use cases and it got used a lot, then I think the understanding of the upsides would be such that such a feature is more likely to happen. Index-scans fall into that category, and a special structure not seen in other databases doesn't appear to at this time.
> On the plus side, it could end the endless howling about this problem.
:-) I think that would be also immense relief for the howlers.