12 ms·

Why Use Postgres - Part 2

145 points by craigkerstiens 15y ago

sheff 15y ago
One of the other nice things about Postgres is the pretty much annual release of a new version. 9.2 should apparently hit beta in the next week or so, which will bring some cool new features : http://archives.postgresql.org/pgsql-hackers/2012-05/msg00344.php http://archives.postgresql.org/pgsql-hackers/2012-05/msg0034... .
- diminish 15y ago
  Are there any reasons not to use postgres, as a replacement for mysql?
  - timc3 15y ago
    If you have a DBA that knows MySQL extremely well and doesn't need to change. If you have a existing system running well, and the future roadmap of your development doesn't make staying with MySQL difficult. I am totally a Postgres fan, but there are many sensible reasons not to upset the apple cart.
  - vidarh 15y ago
    If you use multi-master replication, MySQL Cluster, or depend on properties of one of the alternative storage engines would be my big ones.
    - jeltz 15y ago
      Hopefully PostgreSQL-XC might fix this lack of good multi-master replciation in a couple of years. PostgreSQL-XC 1.0 will be released soon.
scotty79 15y ago
Has slowness of SELECT COUNT() on large datasets been addressed in any way? Last time I checked it was responded with "it's slow because of how we do things here" and if you want to count records fast "you count them yourself" (not actual quotes from anyone, just tl;dr of some answers I've seen).
- saurik 15y ago
  If you mean, "have other major database vendors, such as MySQL, become slower in most of the same ways", then "yes, and for the same reasons ;P"; the one place PostgreSQL could be better on that is that it can't use a covering index currently (which would let it get the count of a filtered query only with a scan of the index, without needing to read and validate the table rows), but that is being addressed in 9.2.
  - fdr 15y ago
    I basically find your sentiment accurate: databases with high concurrency support need to do a lot of work to do an accurate count(*). However, many that were not postgres supported index-only scans to do it. That doesn't change the algorithmic complexity of O(n) to grovel through the tuples, but it can have a large constant time difference. 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.
- Erwin 15y ago
  If you want an approximate count you can do something like to get data for all your tables: SELECT S.nspname || '.' || relname, reltuples FROM pg_class C, pg_namespace S WHERE C.relnamespace = S.oid ORDER BY reltuples DESC This is the estimate used by the query planner, so it's updated whenever a VACUUM occurs (not sure how often the autovacuum runs).
  - outworlder 15y ago
    As often as it is needed, depending on your database settings.
- fusiongyro 15y ago
  I kind of hate this "tl;dr" culture. I can explain the reason why this is never going to change (though it may get better with index scans) but I expect you'll just see a wall of text and decide not to read it. But if you never read long answers, you have no right to expect solutions to your problems or to understand anything. So I hope you'll read this, it may help. 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.
boyter 15y ago
Having recently been bitten by a 20 hour schema change to a very large table in MySQL I have been looking at Postgresql more seriously. 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.
- jeltz 15y ago
  What is your platform? If it is Debian then it is as simple as this to install PostgreSQL and set up a PostgreSQL root user with the same username as your UNIX username (this to avoid having to modify pg_hba and and also not having to specify user when connecting to PostgreSQL). sudo apt-get install postgresql sudo -u postgres createuser -s `whoami` After this you an just create a database and connect to it with: createdb my_db psql my_db
  - boyter 15y ago
    Usually Ubuntu, so I will have a look at this. However when running an application say in PHP its going to be running under the Apache user. How do I go about configuring that?
    - jeltz 15y ago
      I see, I usually do ruby development so the webserver is then usually running as my user on my development machine (obviously that is not the case in production). In 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).
  - KC8ZKF 15y ago
gtaylor 15y ago
I had no clue that Postgres supported LISTEN and NOTIFY. Granted, it's a new addition, but this is really slick: http://www.postgresql.org/docs/9.1/static/sql-listen.html http://www.postgresql.org/docs/9.1/static/sql-listen.html http://www.postgresql.org/docs/9.1/static/sql-notify.html http://www.postgresql.org/docs/9.1/static/sql-notify.html
- fdr 15y ago
  As the other posters have said, it has been around forever, but was rewritten to be much faster and support a payload in 9.0.
- sophacles 15y ago
  LISTEN and NOTIFY are a great feature. We used them heavily in one system I worked on. As the project evolved though, we decided the best way to use them was to have clients to both our main message brokers and the postgres server, and translate between them. Largely this was to avoid overloading the pg server as a message broker also -- particularly because for our uses dbus was a much better alternative for local system message passing. Now that NOTIFYs officially have payload support, YMMV.
- viraptor 15y ago
  I'm not sure how much I like stuffing random functionality into the database... Looking at the docs: "and then must periodically call the function PQnotifies to find out whether any notification events have been received" - it's actually polling via an SQL connection - that means even more persistent connections to the DB, another timer in the application, more difficult connection sharing (two pools rather than one). Without knowing much about how it works - why would I want to use this system rather than just ZMQ/RMQ/...?
  - jeltz 15y ago
    You do not have to poll. You can wait on the socket until you get data and only then poll.
    - 15y ago
aidos 15y ago
Can anyone point me at a HOW to use Postgres guide? I know my way around MySQL and SQL Server pretty well, but Postges is fairly new to me. I did one project where I migrated several websites from one server to another but I kind of fumbled my way through it. Would love to see a guide for getting started with Postgres for those who are already familiar with MySQL.
- RowanH 15y ago
  The official docs are fantastic. The newer PacktPub books are also pretty good http://www.packtpub.com/postgresql-90-high-performance/book http://www.packtpub.com/postgresql-90-high-performance/book and http://www.packtpub.com/postgresql-9-admin-cookbook http://www.packtpub.com/postgresql-9-admin-cookbook
  - timc3 15y ago
    It should be said that the older books have never been very good. I have read probably all of them, none come close to the documentation on the website.
- lobo_tuerto 15y ago
  You can check the guide mentioned in the article: http://www.postgresguide.com/ http://www.postgresguide.com/

145 points by craigkerstiens 15y ago

sheff 15y ago

One of the other nice things about Postgres is the pretty much annual release of a new version. 9.2 should apparently hit beta in the next week or so, which will bring some cool new features : http://archives.postgresql.org/pgsql-hackers/2012-05/msg00344.php http://archives.postgresql.org/pgsql-hackers/2012-05/msg0034... .

diminish 15y ago
Are there any reasons not to use postgres, as a replacement for mysql?
- timc3 15y ago
  If you have a DBA that knows MySQL extremely well and doesn't need to change. If you have a existing system running well, and the future roadmap of your development doesn't make staying with MySQL difficult. I am totally a Postgres fan, but there are many sensible reasons not to upset the apple cart.
- vidarh 15y ago
  If you use multi-master replication, MySQL Cluster, or depend on properties of one of the alternative storage engines would be my big ones.
  - jeltz 15y ago
    Hopefully PostgreSQL-XC might fix this lack of good multi-master replciation in a couple of years. PostgreSQL-XC 1.0 will be released soon.

scotty79 15y ago

Has slowness of SELECT COUNT() on large datasets been addressed in any way? Last time I checked it was responded with "it's slow because of how we do things here" and if you want to count records fast "you count them yourself" (not actual quotes from anyone, just tl;dr of some answers I've seen).

saurik 15y ago
If you mean, "have other major database vendors, such as MySQL, become slower in most of the same ways", then "yes, and for the same reasons ;P"; the one place PostgreSQL could be better on that is that it can't use a covering index currently (which would let it get the count of a filtered query only with a scan of the index, without needing to read and validate the table rows), but that is being addressed in 9.2.
- fdr 15y ago
  I basically find your sentiment accurate: databases with high concurrency support need to do a lot of work to do an accurate count(*). However, many that were not postgres supported index-only scans to do it. That doesn't change the algorithmic complexity of O(n) to grovel through the tuples, but it can have a large constant time difference. 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.
Erwin 15y ago
If you want an approximate count you can do something like to get data for all your tables: SELECT S.nspname || '.' || relname, reltuples FROM pg_class C, pg_namespace S WHERE C.relnamespace = S.oid ORDER BY reltuples DESC This is the estimate used by the query planner, so it's updated whenever a VACUUM occurs (not sure how often the autovacuum runs).
- outworlder 15y ago
  As often as it is needed, depending on your database settings.
fusiongyro 15y ago
I kind of hate this "tl;dr" culture. I can explain the reason why this is never going to change (though it may get better with index scans) but I expect you'll just see a wall of text and decide not to read it. But if you never read long answers, you have no right to expect solutions to your problems or to understand anything. So I hope you'll read this, it may help. 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.

boyter 15y ago

Having recently been bitten by a 20 hour schema change to a very large table in MySQL I have been looking at Postgresql more seriously. 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.

jeltz 15y ago
What is your platform? If it is Debian then it is as simple as this to install PostgreSQL and set up a PostgreSQL root user with the same username as your UNIX username (this to avoid having to modify pg_hba and and also not having to specify user when connecting to PostgreSQL). sudo apt-get install postgresql sudo -u postgres createuser -s `whoami` After this you an just create a database and connect to it with: createdb my_db psql my_db
- boyter 15y ago
  Usually Ubuntu, so I will have a look at this. However when running an application say in PHP its going to be running under the Apache user. How do I go about configuring that?
  - jeltz 15y ago
    I see, I usually do ruby development so the webserver is then usually running as my user on my development machine (obviously that is not the case in production). In 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).
- KC8ZKF 15y ago

gtaylor 15y ago

I had no clue that Postgres supported LISTEN and NOTIFY. Granted, it's a new addition, but this is really slick: http://www.postgresql.org/docs/9.1/static/sql-listen.html http://www.postgresql.org/docs/9.1/static/sql-listen.html http://www.postgresql.org/docs/9.1/static/sql-notify.html http://www.postgresql.org/docs/9.1/static/sql-notify.html

fdr 15y ago
As the other posters have said, it has been around forever, but was rewritten to be much faster and support a payload in 9.0.
sophacles 15y ago
LISTEN and NOTIFY are a great feature. We used them heavily in one system I worked on. As the project evolved though, we decided the best way to use them was to have clients to both our main message brokers and the postgres server, and translate between them. Largely this was to avoid overloading the pg server as a message broker also -- particularly because for our uses dbus was a much better alternative for local system message passing. Now that NOTIFYs officially have payload support, YMMV.
viraptor 15y ago
I'm not sure how much I like stuffing random functionality into the database... Looking at the docs: "and then must periodically call the function PQnotifies to find out whether any notification events have been received" - it's actually polling via an SQL connection - that means even more persistent connections to the DB, another timer in the application, more difficult connection sharing (two pools rather than one). Without knowing much about how it works - why would I want to use this system rather than just ZMQ/RMQ/...?
- jeltz 15y ago
  You do not have to poll. You can wait on the socket until you get data and only then poll.
  - 15y ago

aidos 15y ago

Can anyone point me at a HOW to use Postgres guide? I know my way around MySQL and SQL Server pretty well, but Postges is fairly new to me. I did one project where I migrated several websites from one server to another but I kind of fumbled my way through it. Would love to see a guide for getting started with Postgres for those who are already familiar with MySQL.

RowanH 15y ago
The official docs are fantastic. The newer PacktPub books are also pretty good http://www.packtpub.com/postgresql-90-high-performance/book http://www.packtpub.com/postgresql-90-high-performance/book and http://www.packtpub.com/postgresql-9-admin-cookbook http://www.packtpub.com/postgresql-9-admin-cookbook
- timc3 15y ago
  It should be said that the older books have never been very good. I have read probably all of them, none come close to the documentation on the website.
lobo_tuerto 15y ago
You can check the guide mentioned in the article: http://www.postgresguide.com/ http://www.postgresguide.com/