4 ms·

I did an extensive survey of NoSQL databases recently and for my particular requirements Postgres ended up being the best choice. I examined MySQL too in this p

by hoodoof 11y ago

I did an extensive survey of NoSQL databases recently and for my particular requirements Postgres ended up being the best choice. I examined MySQL too in this particular survey. Can't remember why but its JSON handling didn't meet my needs. Every time I consider alternative databases -and I've done so several times in the past ten years - I do a thorough examination and the answer always seems to come back to Postgres. I'm not a particular Postgres fanboy but the answer just ends up at the same place.

sterl 11y ago
I've experienced this time and time again. Postgres is an outstanding database for most use cases. I also love all the enhancements it has been getting over the last twenty four months. If you haven't considered Postgres before, definitely check the project out.
- djsumdog 11y ago
  My last few jobs were heavily Postgres-based. From what other stuff I've read on NoSQL, the benefits against relational databases just didn't outweigh learning a bunch of new NoSQL concepts. I know the MonogoDB drivers had some data loss issues (you were required to check an error code each time) that they've since then fixed. But the general consensus from blogs I've read say that NoSQL should really be used more as a quick cache. Redius seems to replace memcache more than it acts as a real data-store. People who try to go the full NoSQL route tend to have to use several systems just to get what they had with a regular relational db. What bothers me more than anything is that it seems like PostgreSQL is it. MySQL/MariaDB/Percona show the fragmented former MySQL landscape. I've heard some people talk about Firebird, which I really need to check out. But is that really it? In the OSS world, are we down to PostgreSQL and Firebird (will MySQL really continue to grow under Oracle's control? Or is it on a dead end path?)
namelezz 11y ago
When and how did you evaluate PostgreSQL? Can you comment on PostgreSQL's horizontal scale?
- baudehlo 11y ago
  There are benchmarks out there doing hundreds of thousands of queries per second with Pg. I've had it do about 200k myself. Do you need more?
  - threeseed 11y ago
    Do I still get that query rate when I have hundreds of millions of rows in a table and database sizes in the Tens of TBs -> PBs. Do you need more ? For pretty much all of the Fortune 500 who are doing Big Data Analytics. Yes. There is a reason Hadoop and the related NoSQL databases are so popular today.
    - baudehlo 11y ago
      The question you need to ask and I'll state it more clearly: do YOU need more? Those companies probably aren't you, and can afford to experiment with commercial and open source options that may or may not fit their scale. People who reject PostgreSQL based on scaling are 99.9% of the time wrong about how much they will end up scaling.
- hoodoof 11y ago
  I cannot comment on its horizontal scaling. I can say however that Postgres, from what I understand, has nearly linear SMP vertical scaling up to 64 cores. There's an awful lot of headroom there. I read a long while back Jeff Atwood saying that StackOverflow ran on a single vertically scaled database server with MSSQL for a long time. If I needed to go beyond vertical then I'd maybe horizontally scale in a custom manner based around the application data access characteristics, which plays a huge role in designing sensible horizontal scaling solutions.
  - namelezz 11y ago
    Thank you for your response. I was asking the questions because when I was evaluating MySQL and PostgreSQL 3 years ago I ended up choosing MySQL due to its community and the rumor about difficulties in horizontal scaling of PostgreSQL even though I really liked PostgreSQL JSON support. I hear PostgreSQL is getting better at scaling horizontally although I do not know how good it is now.
frankthedog 11y ago
Care to share an overview of what requirements swayed you?
- hoodoof 11y ago
  For this project I wanted to be able to throw arbitrary JSON structures into a database, optionally index on specific fields if I wanted. Strong nice to have was for the database to intelligently read the JSON to prevent junk being dumped into a text field used as a json store field. I wanted to run purely from RAM, no hard disk, and a strong nice to have was to have an HTTP interface. Postgres doesn't have an HTTP interface but you can make nginx talk to it directly which cut out the need for any sort of web application at all. No requirement for data durability or persistence. I can't remember exactly why I eliminated everything else but I looked at everything from MongoDB to RethinkDB to Arrango to Redis to the K/V stores to Maria, Drizzle, MySQL, SQLite, Unqlite and a few pure Python and pure JavaScript databases too. Also Couchbase, membase and memsql I seems to recall. Short answer is that if you want what is effectively a query-able JSON RAM cache then Postgres is a good solution.
  - threeseed 11y ago
    Sure. But you can do that with MySQL and MongoDB as well. These support JSON data types, access via Nginx (with Lua) and caching data into RAM. Also for small data sets you can run data directorties on RAM disks.

by hoodoof 11y ago

sterl 11y ago

I've experienced this time and time again. Postgres is an outstanding database for most use cases. I also love all the enhancements it has been getting over the last twenty four months. If you haven't considered Postgres before, definitely check the project out.

djsumdog 11y ago
My last few jobs were heavily Postgres-based. From what other stuff I've read on NoSQL, the benefits against relational databases just didn't outweigh learning a bunch of new NoSQL concepts. I know the MonogoDB drivers had some data loss issues (you were required to check an error code each time) that they've since then fixed. But the general consensus from blogs I've read say that NoSQL should really be used more as a quick cache. Redius seems to replace memcache more than it acts as a real data-store. People who try to go the full NoSQL route tend to have to use several systems just to get what they had with a regular relational db. What bothers me more than anything is that it seems like PostgreSQL is it. MySQL/MariaDB/Percona show the fragmented former MySQL landscape. I've heard some people talk about Firebird, which I really need to check out. But is that really it? In the OSS world, are we down to PostgreSQL and Firebird (will MySQL really continue to grow under Oracle's control? Or is it on a dead end path?)

namelezz 11y ago

When and how did you evaluate PostgreSQL? Can you comment on PostgreSQL's horizontal scale?

baudehlo 11y ago
There are benchmarks out there doing hundreds of thousands of queries per second with Pg. I've had it do about 200k myself. Do you need more?
- threeseed 11y ago
  Do I still get that query rate when I have hundreds of millions of rows in a table and database sizes in the Tens of TBs -> PBs. Do you need more ? For pretty much all of the Fortune 500 who are doing Big Data Analytics. Yes. There is a reason Hadoop and the related NoSQL databases are so popular today.
  - baudehlo 11y ago
    The question you need to ask and I'll state it more clearly: do YOU need more? Those companies probably aren't you, and can afford to experiment with commercial and open source options that may or may not fit their scale. People who reject PostgreSQL based on scaling are 99.9% of the time wrong about how much they will end up scaling.
hoodoof 11y ago
I cannot comment on its horizontal scaling. I can say however that Postgres, from what I understand, has nearly linear SMP vertical scaling up to 64 cores. There's an awful lot of headroom there. I read a long while back Jeff Atwood saying that StackOverflow ran on a single vertically scaled database server with MSSQL for a long time. If I needed to go beyond vertical then I'd maybe horizontally scale in a custom manner based around the application data access characteristics, which plays a huge role in designing sensible horizontal scaling solutions.
- namelezz 11y ago
  Thank you for your response. I was asking the questions because when I was evaluating MySQL and PostgreSQL 3 years ago I ended up choosing MySQL due to its community and the rumor about difficulties in horizontal scaling of PostgreSQL even though I really liked PostgreSQL JSON support. I hear PostgreSQL is getting better at scaling horizontally although I do not know how good it is now.

frankthedog 11y ago

Care to share an overview of what requirements swayed you?

hoodoof 11y ago
For this project I wanted to be able to throw arbitrary JSON structures into a database, optionally index on specific fields if I wanted. Strong nice to have was for the database to intelligently read the JSON to prevent junk being dumped into a text field used as a json store field. I wanted to run purely from RAM, no hard disk, and a strong nice to have was to have an HTTP interface. Postgres doesn't have an HTTP interface but you can make nginx talk to it directly which cut out the need for any sort of web application at all. No requirement for data durability or persistence. I can't remember exactly why I eliminated everything else but I looked at everything from MongoDB to RethinkDB to Arrango to Redis to the K/V stores to Maria, Drizzle, MySQL, SQLite, Unqlite and a few pure Python and pure JavaScript databases too. Also Couchbase, membase and memsql I seems to recall. Short answer is that if you want what is effectively a query-able JSON RAM cache then Postgres is a good solution.
- threeseed 11y ago
  Sure. But you can do that with MySQL and MongoDB as well. These support JSON data types, access via Nginx (with Lua) and caching data into RAM. Also for small data sets you can run data directorties on RAM disks.