14 ms·

Is PostgreSQL good enough?

403 points by richardboegli 10y ago

Aeyris 10y ago
Is anyone actually utilising a recent version of PostgreSQL for full-text searching beyond a hobby project? How do you find the speed and accuracy versus Elasticsearch?
- flukus 10y ago
  This comes from experience with SQL Server, not postgres and lucene instead of elastic search, but no, I don't think full-text search replaces lucene/elastic for any but the simplest searches. Especially if you want to rank results based on a number of factors, not just a text match. The last time I was using them for instance, we wanted geographic location to be weighted strongly and the database approach did not under performed on result speed, result accuracy and developer time (excluding learning lucene, which isn't that hard anyway).
  - saurik 10y ago
    PostgreSQL is pretty unique as an RDBMS in its ability to do multi-facet weighted search over disparate types including geographical information as part of a single index query (due to GiST indices): there is a reason why the GIS community has essentially standardized around PostgreSQL (via PostGIS); I do not think it works to use experience from SQL Server to comment on PostgreSQL for this use case.
    - flukus 10y ago
      Interesting, thanks. Is query performance on par with Lucerne? How about indexing cost/latency?
- mwpmaybe 10y ago
  http://rachbelaid.com/postgres-full-text-search-is-good-enough/ http://rachbelaid.com/postgres-full-text-search-is-good-enou...
- mrmondo 10y ago
  Yes we've used full text search for quite some time for one of our products, it's great, we really haven't had any issues performance or otherwise with it. For one of our other products we index data from PostgreSQL / PostGIS into elasticsearch and that's also good, in other ways.
  - Aeyris 10y ago
    At a guess stemming from ignorance of Elasticsearch, I'm going to assume you'd join PostgreSQL and Elasticsearch like that in situations where you need to perform more complex analysis over the queries to provide results, is this correct? I've seen this particular architecture before and my biggest question was whether or not it was a piece that could be eliminated. For, say, a tag-based search system (think any forum or booru-style imageboard), is Elasticsearch completely overkill? I realise I could probably just google it™, but it's a lot easier to understand a product's strengths when they're put in a situational context.
    - dguaraglia 10y ago
      Your guess is pretty much it: Postgres full text search is great for simpler scenarios, but elasticsearch provides more powerful search capabilities and simpler implementation of complex features (such as "search all shops in this geographical area with products that match these words the user typed".) That said, I've had good results with both systems, and always found the elasticsearch query syntax pretty arcane and difficult to put together from the docs. The docs essentially assume you are very familiar with Lucene concepts and that you'll be able to figure out what goes where in the JSON object from your past experience. If you are implementing a simple tag system, then I'd use Postgres first and only when performance becomes an issue move to elasticsearch.
- renesd 10y ago
  Used it for 10,000+ document and 1 million document projects. Was good enough for me (but I guess I wouldn't have written the article if it was bad). I've also used Elasticsearch, and I reckon that's pretty damn amazing. Anyone wanting more in-depth information should read or watch this FTS presentation from last year. It's by some of the people who has done a lot of work on the implementation, and talks about 9.6 improvements, current problems, and things we might expect to see in version 10. https://www.pgcon.org/2016/schedule/events/926.en.html https://www.pgcon.org/2016/schedule/events/926.en.html There's also some previous presentations on the same topic which are interesting. You can see the RUM index (which has faster ranking here): https://github.com/postgrespro/rum https://github.com/postgrespro/rum
- combatentropy 10y ago
  For our intranet's collection of tens of thousands of pages, results come back in a fraction of a second. Accuracy seems as good as the ten-year-old Google Mini Search Appliance that it replaced, but I had to write a good chunk of SQL. I weigh whether the terms are in the web address, title, or just the body, and also the number of backlinks. This took making a few tables, views, and SQL functions, as well as a daily cron job.
- einhverfr 10y ago
  I worked on a huge (12TB) bioinformatic project which was using PostgreSQL full text search as a primary component. One would compare with Solr which also was looked at as a possible replacement. Solr had more functionality (largely by being able to throw more nodes at the problem), but for our purposes, PostgreSQL's fts was certainly good enough. There were a few cases where we had to be careful so the full text index would be used (because data distribution did not always match the planner's assumptions) but on the whole it worked very well.
- Beltiras 10y ago
  Several years ago I was responsible for a CMS for a local newspapers website. A contractor making updates used ElasticSearch to search for articles. It was a rather botched setup with ES crashing once in a while and the search index sliding out of sync with the database. I was using Django and rewrote the search just simply collecting a dictionary of terms that I would then explode into filter on the model. A database of order 100k articles with something like 50 fields, about 20 of them foreign keys, bulk text fields of several to tens of KB responded within milliseconds on a 4 core 4GB machine under load. The "weird thing" about it that I didn't understand at the time was that using a more complex query would often result in speedups. I understand it better now after this article since PG builds some sort of index on text fields. PG is one of the infrastructure pieces that you install and then it just runs performantly without much fuss. There probably are cases where it fails horribly but in almost a decade of use I have yet to see a single operations failure.
rosser 10y ago
Using an RDBMS as a work queue is an anti-pattern, but if you're going to do it, you probably can't do much better than LISTEN/NOTIFY.
- Veratyr 10y ago
  > Using an RDBMS as a work queue is an anti-pattern I don't want to appear too ignorant but why? I hear many claims of this but few explanations. The best I've heard is something about not being able to handle the load but I don't really buy that unless you don't need persistence/integrity.
  - rosser 10y ago
    Primarily, because it's hard to get right. Of course it can be done well, or correctly (and as The Fine Article points out, adding SKIP LOCKED to SELECT ... FOR UPDATE will go a long way towards that). But most people do them terribly. As just one example, from a previous job: they were using SQL Alchemy in the code that interacted with the work queue, but somehow didn't realize that the DBAPI being implicitly transactional meant they'd need to manage transaction-state themselves. Consequently, they never released the implicitly opened transactions that every poll for new work created. No-one thought it was strange that the work queue system needed to be shut down weekly, so that a VACUUM FULL could reclaim the gibibytes of space that were wasted because the db couldn't clean up tuples (row version) that still appeared live to stale, abandoned transactions. Until I was all, "Uh, guys?" The fix was obvious: disable implicit transactions. But that's just one example, if particularly egregious. I've seen and heard countless more.
    - ioquatix 10y ago
      That's a nice summary. But, does that really make it an anti-pattern?
      - rosser 10y ago
        I'd argue that a pattern as prone to subtly (or grossly) incorrect implementation as databases-as-work-queues is qualifies, yes.
yorhel 10y ago
There was a similar talk[1] at FOSDEM, where the speaker describes how, as an experiment, he replaces a full ELK stack plus other monitoring tools with PostgreSQL. He even goes as far as implementing a minimal logstash equivalent (i.e. log parsing) into the database itself. It wasn't an "we do this at scale" talk, but I'd love to see more experiments like it. For the impatient: Skip to 17 minutes into the video, where he describes the previous architecture and what parts are replaced with Postgres. 1. https://fosdem.org/2017/schedule/event/postgresql_infrastructure_monitoring/ https://fosdem.org/2017/schedule/event/postgresql_infrastruc...
- ngrilly 10y ago
  This talk is specifically mentioned in the article :-)
- dozzie 10y ago
  > It wasn't an "we do this at scale" talk, but I'd love to see more experiments like it. Well, I will be conducting such thing in near future. From ELK stack, I never used Logstash in the first place and used Fluentd instead (and now I'm using a mixture of my own data forwarder and Fluentd as a hub). I'm planning mainly to replace Elasticsearch, and probably will settle with a command line client for reading, searching, and analyzing (I dislike writing web UIs). All this because I'm tired of elastic.co. I can't upgrade my Elasticsearch 1.7.5 to the newest version, because then I would need to upgrade this small(ish) 4MB Kibana 3.x to a monstrosity that weihgs more than whole Elasticsearch engine itself for no good reason at all. And now that I'm stuck with ES 1.x, it's only somewhat stable; it can hang up for no apparent reasonat unpredictable intervals, sometimes three times per week, and sometimes working with no problem for two months. And to add an insult to an injury, processing logs with grep and awk (because I store the logs in flat files as well as in ES) is often faster than letting ES do the job. I only keep ES around because Kibana gives nice search interface and ES provides a declarative query language, which is easier to use than building awk program. > He even goes as far as implementing a minimal logstash equivalent (i.e. log parsing) into the database itself. As for parsing logs, I would stay away from database. Logs should be parsed earlier and available for machine processing as a stream of structured messages. I have implemented such thing using Rainer Gerhards' liblognorm and I'm very happy with the results, to the point that I derive some monitoring metrics and was collecting inventory from logs.
jaequery 10y ago
is it good enough? yes. in fact, its probably an overkill for most. i think the question of good enough wouldve been perfect for sqlite.
- siscia 10y ago
  This a thousand of times. My biggest concern for sqlite WAS that it is only embedded and not clients server which is a problem if I have more process trying to access it. I solved it writing a Redis modules that embed sqlite [1] so you get a client server sqlite for all your relational/ACID need and redis for everything else. [1]: https://github.com/redbeardlab/redisql https://github.com/redbeardlab/redisql
  - mivade 10y ago
    This looks pretty cool.
    - siscia 10y ago
      Thanks, someone else call it the "worst idea ever"... It is nice to have some positive feedback once in a while. Definitely if you have suggestions or feedback (the negative) please please share it. Either open an issue, write me an email or keep this conversation going ;) Best
- rwmj 10y ago
  I love sqlite but it's really difficult to recommend it in a situation where you need parallel r/w access to the database (ie. any web-type situation, even for light loads).
  - marktangotango 10y ago
    If care is taken to cache query results and clear on insert/update, one can get stupendous performance from sqlite. I used Apache, mod_lua, and redis to implement a system that does this for example.
cel1ne 10y ago
I do use PostgreSQL wherever possibly. Add http://postgrest.com/ http://postgrest.com/ and nginx as a url-rewriting proxy and you have a performant, highly adaptable REST-Server.
- einhverfr 10y ago
  I know of some relatively high end systems which are doing that, which used postgrest for prototyping purposes and then found it performed too well to give up!
  - ruslan_talpa 10y ago
    Ha :)! can you maybe give some links?
    - einhverfr 10y ago
      I think internally the upcoming Kosovo property tax management system will use it.
      - ruslan_talpa 10y ago
        This is one of those things you frame and put on the wall :)
- BerislavLopac 10y ago
  Nice, Postgrest looks really amazing, especially for long-term production needs. I've previously used Sandman, which is much easier to set up, which makes it ideal for more quick-and-dirty needs: https://github.com/jeffknupp/sandman2 https://github.com/jeffknupp/sandman2
- StreamBright 10y ago
  Me too, never failed me for the things I use it for. Unlike other data stores, it has predictable, consistent performance, production ready releases, and reasonable opensource contributors who really deliver the awesomeness. Its architecture is unique, at least in the FOSS word.
einhverfr 10y ago
Nice writeup though I would add a few things. Listen/Notify work great for short-term job queues. For longer term ones, you have some serious difficulties on PostgreSQL which require care and attention to detail to solve. In those cases, of course, you can solve them, but they take people who know what they are doing. Also in terms of storing images in the database, this is something that really depends on what you are doing, what your database load is, and what your memory constraints are. At least when working with Perl on the middleware, decoding and presenting the image takes several times the RAM that loading it off the filesystem does. That may not be the end of the world, but it is something to think about. Also TOAST overhead in retrieved columns doesn't show up in EXPLAIN ANALYZE because the items never get untoasted. Again by no means a deal breaker, but something to think about. In general, PostgreSQL can be good enough but having people know know it inside and out is important as you scale. That's probably true with any technology, however.
anko 10y ago
I love Postgres, but the one thing I think sucks is it's COUNT() performance. I've read all sorts of hacks but I would love for someone to solve this for me!
- bottled_poe 10y ago
  How about this: https://www.periscopedata.com/blog/use-subqueries-to-count-distinct-50x-faster.html https://www.periscopedata.com/blog/use-subqueries-to-count-d...
- einhverfr 10y ago
  I suspect one physical order index-only scans are supported this should be a lot faster.
  - jeltz 10y ago
    I do not think anyone is working on this (I cannot recall seeing any discussion on the mailing list at least the last 2-3 years) and this is only true assuming the primary key is significantly narrower than the table, otherwise a sequential scan is the fastest way to implement count(*).
- falcolas 10y ago
  One trick with counts is that you very rarely need a perfectly accurate count for that exact moment in time; doing an explain on an appropriate 'SELECT' and capturing the estimated number of rows returned by that is usually good enough in 98% of the cases. When you do need an accurate count, phrasing the query so the results can be pulled exclusively from the table index is also usually good enough.
  - ishi 10y ago
    Never thought of using EXPLAIN to get an estimated count... nice trick!
- pmontra 10y ago
  Triggers on writes to update a counter? Basically you spread the cost of COUNT on INSERTs and UPDATEs. It might be a good tradeoff or a horrible one depending on your workloads.
TheAceOfHearts 10y ago
It's really amazing how far you can with a relational database. If you have very minimal constraints, keeping everything in a single place can make life so much easier. Configuration hell is real. I hadn't considered using PG for storing binary data, but I've hacked together a few toy projects where I used mongo and just shamelessly shoved everything in there. I have a some slightly tangential questions, which I'd love to hear people's thoughts on: How do you decide where to draw the line between what's kept and defined in the application and database? For example, how strict would you make your type definitions and constraints? Do you just accept that you'll end up duplicating some of it in both places? Also, how do you track and manage changes when you have to deal with multiple environments?
- barrkel 10y ago
  In a heterogeneous language environment (Java, Ruby and Coffeescript where I am), defining types in the application doesn't give sufficient coverage because not all languages are statically typed. Referential integrity is important, but sometimes has to be sacrificed for performance reasons (ideally foreign keys are only temporarily disabled for bulk actions). Other column-level constraints are nice to have but less critical. For the kind of work we do at my company - involving bulk upload, bulk inserts, bulk joins - thinking relationally is much more productive than thinking in objects, and really fast key-based lookups are far less important than really fast joins. Only configuration data lives in objects; other data is served up using a lightweight translation of the result set to JSON, with no business entity intermediary; one of the primary reasons being that the user gets to define their own schema, so any object would have a variable number of fields and types anyway. Writing the same logic in different places is definitely a problem; you want to avoid that. We've taken to putting some logic in JRuby just to be able to use the same source in Rails and Java. For other logic, we have a Java service API endpoint that Rails can call. For maintenance purposes, it's really worthwhile not duplicating very complex logic in multiple places. Relational databases - or rather, relational algebra, even if computed over something like Spark or Impala - is, to me, worth far more than something more suited for storing objects. Thinking in terms of relations is just more productive for efficient code than navigating object graphs.
brightball 10y ago
I've been attempting to preach the PostgreSQL Gospel (http://www.brightball.com/articles/why-should-you-learn-postgresql http://www.brightball.com/articles/why-should-you-learn-post...) for a few years now about this exact same thing. When you look at your database as a dumb datastore, you're really selling short all of the capabilities that are in your database. PG is basically a stack in a box. Whenever I started getting into Elixir and Phoenix and realized that the entire Elixir/Erlang stack was also basically a full stack on it's own...and that by default Phoenix wants to use PG as it's database...I may have gone a little overboard with excitement. If you build with Elixir and PostgreSQL you've addressed almost every need that most projects can have with minimal complexity.
- qaq 10y ago
  Very true, the only downside to PG is a bit of naming craziness still using names from pre SQL days for some things, but overall very solid product.
  - ibotty 10y ago
    care to give a few examples?
    - qaq 10y ago
      tuple - row, relation - table
      - dragonwriter 10y ago
        PG calls tables "tables". It does refer to "relation" as the general term for a class of objects that includes tables and views, both of which are realizations of the abstract concept of a "relation" from relational database theory.
- josho 10y ago
  This reminds me of the days of J2EE containers where we tried to write applications that never took advantage of vendor specific features so that our applications could be portable across application servers and databases. It was extremely rare to swap out from IBM to Oracle or vice versa but conceptually we could. The upside was that when a vendor's licensing fees became prohibitive we could conceptually swap platforms. The downside was, well plenty. Like dealing with generic errors, building your own services that the platform stack already provided. So, I love that we now have a OSS stack that folks are taking full advantage of rather than trying to abstract away the platform.
mattferderer 10y ago
Very nice article for those of us who have never used PostgreSQL much. I've been starting to use it with Elixir & this gives me a good understanding of why someone would use it, especially when when starting a new app. Out of curiosity, does anyone have a favorite article saved that does a great comparison of when to use certain databases?
agentgt 10y ago
We had to learn this the hard way. We have many of the data/services the article mentions and while we still use them when ever it gets massive we actually will go back to Postgresql. For example for our internal analytics/logs/metrics we use ELK and Druid but believe it or not these tools despite their purported scaling abilities are actually damn expensive. These new cloud "elastic" stuff cheat and use lots and lots of memory. For a bootstrapped solvent self-funded startup like us we do care about memory usage. For customer analytics we use... yes Postgresql. For counters and stream like things we don't use Redis we use Pipelinedb (Postgresql fork). For Cassandra like stuff we use Citus (Postgresql extension). Some of our external search uses SOLR (for small fields) but Postgresql text search is used for big fields. The only part of our platform we don't really leverage on Postgresql is the message queue and this because RabbitMQ so far has done a damn good job (that and the damn JDBC driver isn't asynchronous so LISTEN/NOTIFY isn't really useful).
- ledgerdev 10y ago
  Here's an async JDBC driver. https://impossibl.github.io/pgjdbc-ng/ https://impossibl.github.io/pgjdbc-ng/
  - agentgt 10y ago
    Interesting. There was a project a while back that offered async through netty but it was trying to do everything async (ie not follow the JDBC spec). This project appears to follow the JDBC spec and thus is synchronous with the exception of the Listen/Notify (of which you have to work the driver directly as there is no JDBC analog).
crudbug 10y ago
Has someone played with threading model within Postgres. I was reading the documents, looks like for every client request Postgres forks a new Process and uses shared memory model. Using multi-processor threads/coroutines might be useful for scaling it further.
- falcolas 10y ago
  I'm more familiar with MySQL, but if PostgreSQL does not use threads, I'd eat my hat. They're just too useful in helping to spread the load of all the IO required to return even one result.
  - dboreham 10y ago
    PG pre-dates (working) threads in Unix by a long time. Processes and threads are much the same thing as far as concurrency and parallelism are concerned. PG maintains a pool of pre-forked processes so there is typically no process startup delay. Shared memory regions and SysV cross-process locks facilitate IPC. Back in the day this is how threads were done in user-land: https://www.postgresql.org/docs/9.6/static/kernel-resources.html https://www.postgresql.org/docs/9.6/static/kernel-resources.... It does have parallel scans though : http://rhaas.blogspot.co.uk/2015/11/parallel-sequential-scan-is-committed.html http://rhaas.blogspot.co.uk/2015/11/parallel-sequential-scan...
    - crudbug 10y ago
      That is correct Linux treats Process & threads as identical tasks. I was thinking about control/data location. We had this three tier achitecturr, which is becoming 2-tier. I am seeing more control co-located with data for more optimal use. It will be useful to have coroutine like supprt, if someone integrates the whole Middleware layer inside postgres.
      - dboreham 10y ago
        That would be a bad idea(tm).
seibelj 10y ago
Yes, for all projects and small businesses I start, Postgres and Redis is what I use from the beginning. Then if it ever gets to the point where I need a different DB for something, I replace components with the new tool. People get fascinated with these fly-by-night data stores and put their operations at serious risk. Start with the tried and tested technologies, then carefully augment your stack as needed.
ausjke 10y ago
still using php+mysql here as I can find so many documents about their various usage easily.
treve 10y ago
Or just MySQL. Popular choice, unpopular opinion. I trust it more because it gives me a tried and tested path when I need replication (which tends to happen rather early). My understanding is that Postgres replication is not nearly as battle-tested.
- einhverfr 10y ago
  Especially if your application wants to turn off strict mode and then store Feb 30, 2016 as a date in the db! Cannot do that in PostgreSQL!
- knz 10y ago
  If you need GIS/spatial capabilities then MySQL isn't anywhere near as mature as PostgreSQL with PostGIS. I'd be curious if anyone passing by can recommend a FOSS replacement/alternative for PostGIS.
- qaq 10y ago
  PostgreSQL replication is very well tested and is used by a very large number of very prominent projects.
  - api 10y ago
    Can you give me a link? Searching for how to deploy it turns up dozens of guides of varying age that refer to different projects of different levels of maturity.
    - qaq 10y ago
      https://www.youtube.com/watch?v=n5yt67uZdgQ https://www.youtube.com/watch?v=n5yt67uZdgQ is fairly decent talk.
- bma13 10y ago
  "Postgres replication is not nearly as battle-tested." - I maintain about 2,000 Postgresql database clusters at work, in various replication configurations. Over the past several years we have had maybe a handful of issues with replication, and each one related in some way to infrastructure problems. Dozens of those systems are doing over 20k transactions per second, so they are pushing decent data through the WAL stream.
api 10y ago
From my experience with both PostgreSQL and RethinkDB (and other NoSQL stores): For SQL, complex queries, and data warehousing: yes. It's an excellent database and I'm not sure why you'd pick another SQL DB unless it were a lot better on point two. For high availability and scaling: no, absolutely not. The problem with the latter is an arcane deployment process and arcane error messages that provide constant worry that you're doing something wrong. It's a many week engineering project to deploy HA Postgres, while HA RethinkDB takes hours -- followed by some testing for prudence... our testing revealed that it does "just work" at least at our scale. We were overjoyed. The docs for Postgres HA and clustering are also horrible. There are like five different ways to do it and they're all in an unknown state of completion or readiness. Of course if/when we do want complex queries and more compact storage, we will probably offload data from the RethinkDB cluster to... drum roll... a PostgreSQL database. Of course that will probably be for analytics done "offline" in the sense that if the DB goes down for a bit we are fine. HA is not needed there. TL;DR: everything has its limitations.
- jeltz 10y ago
  I do not get what you refer to by arcane error messages that give constant worry. While some error messages need to be googled I think PostgreSQL is pretty good at only printing error messages when something is wrong. So if you are getting strange errors from PostgreSQL it is almost certainly an issue which should be fixed.
- manigandham 10y ago
  Scaling (horizontal + vertical) and HA are the biggest weaknesses of Postgres. It's modern in features and flexibility but still struggles with core infrastructure issues and performance.
mooneater 10y ago
How do you get a "hotstandby replica for $5/month"?
mooneater 10y ago
Another big plus for postgres: PL/Python, PL/R, etc
mooneater 10y ago
Postgres' awesome extensible type system means it will continue to increase in functionality much more easily than most comparable DBs. https://www.postgresql.org/docs/9.6/static/extend-how.html https://www.postgresql.org/docs/9.6/static/extend-how.html
ckdarby 10y ago
Why is the font so small on this site?
hartator 10y ago
I am more the MongoDB bandwagon. Shemaless makes prototyping so much easier. And no migrations!
- qaq 10y ago
  create a table with uuid and jsonb, collection_id columns and you've got a fully ACID compliant document store :).
- madhadron 10y ago
  Just make sure that you replace it before you try to load it with replication and sharding. Mongo's replication is not atomic, despite their claims otherwise. This includes versions in the last six months. Mind you, you should be very cautious about loading it in a single instance as well, since it has unpleasant behaviors there, too, but you simply cannot trust it when replication is involved.
mamcx 10y ago
Now imagine if we understand that the relational model is no for "just data storage" but also can be use for everything. The closest thing(1) was dbase/foxpro. You can actually build a full app with it. Send email from the database? Yes. Is not that wrong? Is wrong just because RDBMS (2) made it wrong, not because is actually wrong. Why is better to split in separated languages/run times/models a app than one integrated? (1): Taking in consideration that neither Fox or any "modern" rdbms have take the relational model to it full extension. (2): A RDBMS is a full-package with a defined role, and limited capabilities. A relational-alike language will not be a exact replica of that. Not even is demanded to implement a full-storage solution. The biggest mistake the relational guys have commited is to think always in terms of full-databases instead of micro-database. Ironically, kdb+ (or lisp? or rebol?) could be the closest thing to the idea (where data+code are not enemies but friends).
- discreteevent 10y ago
  Have a look at Eve http://witheve.com http://witheve.com
  - mamcx 10y ago
    Yeah, it look interesting. I wonder how it could be for a full realistic app.
nanistheonlyist 10y ago
Yes, yes it is.

403 points by richardboegli 10y ago

Is anyone actually utilising a recent version of PostgreSQL for full-text searching beyond a hobby project? How do you find the speed and accuracy versus Elasticsearch?

flukus 10y ago
This comes from experience with SQL Server, not postgres and lucene instead of elastic search, but no, I don't think full-text search replaces lucene/elastic for any but the simplest searches. Especially if you want to rank results based on a number of factors, not just a text match. The last time I was using them for instance, we wanted geographic location to be weighted strongly and the database approach did not under performed on result speed, result accuracy and developer time (excluding learning lucene, which isn't that hard anyway).
- saurik 10y ago
  PostgreSQL is pretty unique as an RDBMS in its ability to do multi-facet weighted search over disparate types including geographical information as part of a single index query (due to GiST indices): there is a reason why the GIS community has essentially standardized around PostgreSQL (via PostGIS); I do not think it works to use experience from SQL Server to comment on PostgreSQL for this use case.
  - flukus 10y ago
    Interesting, thanks. Is query performance on par with Lucerne? How about indexing cost/latency?
mwpmaybe 10y ago
http://rachbelaid.com/postgres-full-text-search-is-good-enough/ http://rachbelaid.com/postgres-full-text-search-is-good-enou...
mrmondo 10y ago
Yes we've used full text search for quite some time for one of our products, it's great, we really haven't had any issues performance or otherwise with it. For one of our other products we index data from PostgreSQL / PostGIS into elasticsearch and that's also good, in other ways.
- Aeyris 10y ago
  At a guess stemming from ignorance of Elasticsearch, I'm going to assume you'd join PostgreSQL and Elasticsearch like that in situations where you need to perform more complex analysis over the queries to provide results, is this correct? I've seen this particular architecture before and my biggest question was whether or not it was a piece that could be eliminated. For, say, a tag-based search system (think any forum or booru-style imageboard), is Elasticsearch completely overkill? I realise I could probably just google it™, but it's a lot easier to understand a product's strengths when they're put in a situational context.
  - dguaraglia 10y ago
    Your guess is pretty much it: Postgres full text search is great for simpler scenarios, but elasticsearch provides more powerful search capabilities and simpler implementation of complex features (such as "search all shops in this geographical area with products that match these words the user typed".) That said, I've had good results with both systems, and always found the elasticsearch query syntax pretty arcane and difficult to put together from the docs. The docs essentially assume you are very familiar with Lucene concepts and that you'll be able to figure out what goes where in the JSON object from your past experience. If you are implementing a simple tag system, then I'd use Postgres first and only when performance becomes an issue move to elasticsearch.
renesd 10y ago
Used it for 10,000+ document and 1 million document projects. Was good enough for me (but I guess I wouldn't have written the article if it was bad). I've also used Elasticsearch, and I reckon that's pretty damn amazing. Anyone wanting more in-depth information should read or watch this FTS presentation from last year. It's by some of the people who has done a lot of work on the implementation, and talks about 9.6 improvements, current problems, and things we might expect to see in version 10. https://www.pgcon.org/2016/schedule/events/926.en.html https://www.pgcon.org/2016/schedule/events/926.en.html There's also some previous presentations on the same topic which are interesting. You can see the RUM index (which has faster ranking here): https://github.com/postgrespro/rum https://github.com/postgrespro/rum
combatentropy 10y ago
For our intranet's collection of tens of thousands of pages, results come back in a fraction of a second. Accuracy seems as good as the ten-year-old Google Mini Search Appliance that it replaced, but I had to write a good chunk of SQL. I weigh whether the terms are in the web address, title, or just the body, and also the number of backlinks. This took making a few tables, views, and SQL functions, as well as a daily cron job.
einhverfr 10y ago
I worked on a huge (12TB) bioinformatic project which was using PostgreSQL full text search as a primary component. One would compare with Solr which also was looked at as a possible replacement. Solr had more functionality (largely by being able to throw more nodes at the problem), but for our purposes, PostgreSQL's fts was certainly good enough. There were a few cases where we had to be careful so the full text index would be used (because data distribution did not always match the planner's assumptions) but on the whole it worked very well.
Beltiras 10y ago
Several years ago I was responsible for a CMS for a local newspapers website. A contractor making updates used ElasticSearch to search for articles. It was a rather botched setup with ES crashing once in a while and the search index sliding out of sync with the database. I was using Django and rewrote the search just simply collecting a dictionary of terms that I would then explode into filter on the model. A database of order 100k articles with something like 50 fields, about 20 of them foreign keys, bulk text fields of several to tens of KB responded within milliseconds on a 4 core 4GB machine under load. The "weird thing" about it that I didn't understand at the time was that using a more complex query would often result in speedups. I understand it better now after this article since PG builds some sort of index on text fields. PG is one of the infrastructure pieces that you install and then it just runs performantly without much fuss. There probably are cases where it fails horribly but in almost a decade of use I have yet to see a single operations failure.

rosser 10y ago

Using an RDBMS as a work queue is an anti-pattern, but if you're going to do it, you probably can't do much better than LISTEN/NOTIFY.

Veratyr 10y ago
> Using an RDBMS as a work queue is an anti-pattern I don't want to appear too ignorant but why? I hear many claims of this but few explanations. The best I've heard is something about not being able to handle the load but I don't really buy that unless you don't need persistence/integrity.
- rosser 10y ago
  Primarily, because it's hard to get right. Of course it can be done well, or correctly (and as The Fine Article points out, adding SKIP LOCKED to SELECT ... FOR UPDATE will go a long way towards that). But most people do them terribly. As just one example, from a previous job: they were using SQL Alchemy in the code that interacted with the work queue, but somehow didn't realize that the DBAPI being implicitly transactional meant they'd need to manage transaction-state themselves. Consequently, they never released the implicitly opened transactions that every poll for new work created. No-one thought it was strange that the work queue system needed to be shut down weekly, so that a VACUUM FULL could reclaim the gibibytes of space that were wasted because the db couldn't clean up tuples (row version) that still appeared live to stale, abandoned transactions. Until I was all, "Uh, guys?" The fix was obvious: disable implicit transactions. But that's just one example, if particularly egregious. I've seen and heard countless more.
  - ioquatix 10y ago
    That's a nice summary. But, does that really make it an anti-pattern?
    - rosser 10y ago
      I'd argue that a pattern as prone to subtly (or grossly) incorrect implementation as databases-as-work-queues is qualifies, yes.

yorhel 10y ago

There was a similar talk[1] at FOSDEM, where the speaker describes how, as an experiment, he replaces a full ELK stack plus other monitoring tools with PostgreSQL. He even goes as far as implementing a minimal logstash equivalent (i.e. log parsing) into the database itself. It wasn't an "we do this at scale" talk, but I'd love to see more experiments like it. For the impatient: Skip to 17 minutes into the video, where he describes the previous architecture and what parts are replaced with Postgres. 1. https://fosdem.org/2017/schedule/event/postgresql_infrastructure_monitoring/ https://fosdem.org/2017/schedule/event/postgresql_infrastruc...

ngrilly 10y ago
This talk is specifically mentioned in the article :-)
dozzie 10y ago
> It wasn't an "we do this at scale" talk, but I'd love to see more experiments like it. Well, I will be conducting such thing in near future. From ELK stack, I never used Logstash in the first place and used Fluentd instead (and now I'm using a mixture of my own data forwarder and Fluentd as a hub). I'm planning mainly to replace Elasticsearch, and probably will settle with a command line client for reading, searching, and analyzing (I dislike writing web UIs). All this because I'm tired of elastic.co. I can't upgrade my Elasticsearch 1.7.5 to the newest version, because then I would need to upgrade this small(ish) 4MB Kibana 3.x to a monstrosity that weihgs more than whole Elasticsearch engine itself for no good reason at all. And now that I'm stuck with ES 1.x, it's only somewhat stable; it can hang up for no apparent reasonat unpredictable intervals, sometimes three times per week, and sometimes working with no problem for two months. And to add an insult to an injury, processing logs with grep and awk (because I store the logs in flat files as well as in ES) is often faster than letting ES do the job. I only keep ES around because Kibana gives nice search interface and ES provides a declarative query language, which is easier to use than building awk program. > He even goes as far as implementing a minimal logstash equivalent (i.e. log parsing) into the database itself. As for parsing logs, I would stay away from database. Logs should be parsed earlier and available for machine processing as a stream of structured messages. I have implemented such thing using Rainer Gerhards' liblognorm and I'm very happy with the results, to the point that I derive some monitoring metrics and was collecting inventory from logs.

jaequery 10y ago

is it good enough? yes. in fact, its probably an overkill for most. i think the question of good enough wouldve been perfect for sqlite.

siscia 10y ago
This a thousand of times. My biggest concern for sqlite WAS that it is only embedded and not clients server which is a problem if I have more process trying to access it. I solved it writing a Redis modules that embed sqlite [1] so you get a client server sqlite for all your relational/ACID need and redis for everything else. [1]: https://github.com/redbeardlab/redisql https://github.com/redbeardlab/redisql
- mivade 10y ago
  This looks pretty cool.
  - siscia 10y ago
    Thanks, someone else call it the "worst idea ever"... It is nice to have some positive feedback once in a while. Definitely if you have suggestions or feedback (the negative) please please share it. Either open an issue, write me an email or keep this conversation going ;) Best
rwmj 10y ago
I love sqlite but it's really difficult to recommend it in a situation where you need parallel r/w access to the database (ie. any web-type situation, even for light loads).
- marktangotango 10y ago
  If care is taken to cache query results and clear on insert/update, one can get stupendous performance from sqlite. I used Apache, mod_lua, and redis to implement a system that does this for example.

cel1ne 10y ago

I do use PostgreSQL wherever possibly. Add http://postgrest.com/ http://postgrest.com/ and nginx as a url-rewriting proxy and you have a performant, highly adaptable REST-Server.

einhverfr 10y ago
I know of some relatively high end systems which are doing that, which used postgrest for prototyping purposes and then found it performed too well to give up!
- ruslan_talpa 10y ago
  Ha :)! can you maybe give some links?
  - einhverfr 10y ago
    I think internally the upcoming Kosovo property tax management system will use it.
    - ruslan_talpa 10y ago
      This is one of those things you frame and put on the wall :)
BerislavLopac 10y ago
Nice, Postgrest looks really amazing, especially for long-term production needs. I've previously used Sandman, which is much easier to set up, which makes it ideal for more quick-and-dirty needs: https://github.com/jeffknupp/sandman2 https://github.com/jeffknupp/sandman2
StreamBright 10y ago
Me too, never failed me for the things I use it for. Unlike other data stores, it has predictable, consistent performance, production ready releases, and reasonable opensource contributors who really deliver the awesomeness. Its architecture is unique, at least in the FOSS word.

einhverfr 10y ago

Nice writeup though I would add a few things. Listen/Notify work great for short-term job queues. For longer term ones, you have some serious difficulties on PostgreSQL which require care and attention to detail to solve. In those cases, of course, you can solve them, but they take people who know what they are doing. Also in terms of storing images in the database, this is something that really depends on what you are doing, what your database load is, and what your memory constraints are. At least when working with Perl on the middleware, decoding and presenting the image takes several times the RAM that loading it off the filesystem does. That may not be the end of the world, but it is something to think about. Also TOAST overhead in retrieved columns doesn't show up in EXPLAIN ANALYZE because the items never get untoasted. Again by no means a deal breaker, but something to think about. In general, PostgreSQL can be good enough but having people know know it inside and out is important as you scale. That's probably true with any technology, however.

anko 10y ago

I love Postgres, but the one thing I think sucks is it's COUNT() performance. I've read all sorts of hacks but I would love for someone to solve this for me!

bottled_poe 10y ago
How about this: https://www.periscopedata.com/blog/use-subqueries-to-count-distinct-50x-faster.html https://www.periscopedata.com/blog/use-subqueries-to-count-d...
einhverfr 10y ago
I suspect one physical order index-only scans are supported this should be a lot faster.
- jeltz 10y ago
  I do not think anyone is working on this (I cannot recall seeing any discussion on the mailing list at least the last 2-3 years) and this is only true assuming the primary key is significantly narrower than the table, otherwise a sequential scan is the fastest way to implement count(*).
falcolas 10y ago
One trick with counts is that you very rarely need a perfectly accurate count for that exact moment in time; doing an explain on an appropriate 'SELECT' and capturing the estimated number of rows returned by that is usually good enough in 98% of the cases. When you do need an accurate count, phrasing the query so the results can be pulled exclusively from the table index is also usually good enough.
- ishi 10y ago
  Never thought of using EXPLAIN to get an estimated count... nice trick!
pmontra 10y ago
Triggers on writes to update a counter? Basically you spread the cost of COUNT on INSERTs and UPDATEs. It might be a good tradeoff or a horrible one depending on your workloads.

TheAceOfHearts 10y ago

It's really amazing how far you can with a relational database. If you have very minimal constraints, keeping everything in a single place can make life so much easier. Configuration hell is real. I hadn't considered using PG for storing binary data, but I've hacked together a few toy projects where I used mongo and just shamelessly shoved everything in there. I have a some slightly tangential questions, which I'd love to hear people's thoughts on: How do you decide where to draw the line between what's kept and defined in the application and database? For example, how strict would you make your type definitions and constraints? Do you just accept that you'll end up duplicating some of it in both places? Also, how do you track and manage changes when you have to deal with multiple environments?

barrkel 10y ago
In a heterogeneous language environment (Java, Ruby and Coffeescript where I am), defining types in the application doesn't give sufficient coverage because not all languages are statically typed. Referential integrity is important, but sometimes has to be sacrificed for performance reasons (ideally foreign keys are only temporarily disabled for bulk actions). Other column-level constraints are nice to have but less critical. For the kind of work we do at my company - involving bulk upload, bulk inserts, bulk joins - thinking relationally is much more productive than thinking in objects, and really fast key-based lookups are far less important than really fast joins. Only configuration data lives in objects; other data is served up using a lightweight translation of the result set to JSON, with no business entity intermediary; one of the primary reasons being that the user gets to define their own schema, so any object would have a variable number of fields and types anyway. Writing the same logic in different places is definitely a problem; you want to avoid that. We've taken to putting some logic in JRuby just to be able to use the same source in Rails and Java. For other logic, we have a Java service API endpoint that Rails can call. For maintenance purposes, it's really worthwhile not duplicating very complex logic in multiple places. Relational databases - or rather, relational algebra, even if computed over something like Spark or Impala - is, to me, worth far more than something more suited for storing objects. Thinking in terms of relations is just more productive for efficient code than navigating object graphs.

brightball 10y ago

I've been attempting to preach the PostgreSQL Gospel (http://www.brightball.com/articles/why-should-you-learn-postgresql http://www.brightball.com/articles/why-should-you-learn-post...) for a few years now about this exact same thing. When you look at your database as a dumb datastore, you're really selling short all of the capabilities that are in your database. PG is basically a stack in a box. Whenever I started getting into Elixir and Phoenix and realized that the entire Elixir/Erlang stack was also basically a full stack on it's own...and that by default Phoenix wants to use PG as it's database...I may have gone a little overboard with excitement. If you build with Elixir and PostgreSQL you've addressed almost every need that most projects can have with minimal complexity.

qaq 10y ago
Very true, the only downside to PG is a bit of naming craziness still using names from pre SQL days for some things, but overall very solid product.
- ibotty 10y ago
  care to give a few examples?
  - qaq 10y ago
    tuple - row, relation - table
    - dragonwriter 10y ago
      PG calls tables "tables". It does refer to "relation" as the general term for a class of objects that includes tables and views, both of which are realizations of the abstract concept of a "relation" from relational database theory.
josho 10y ago
This reminds me of the days of J2EE containers where we tried to write applications that never took advantage of vendor specific features so that our applications could be portable across application servers and databases. It was extremely rare to swap out from IBM to Oracle or vice versa but conceptually we could. The upside was that when a vendor's licensing fees became prohibitive we could conceptually swap platforms. The downside was, well plenty. Like dealing with generic errors, building your own services that the platform stack already provided. So, I love that we now have a OSS stack that folks are taking full advantage of rather than trying to abstract away the platform.

mattferderer 10y ago

Very nice article for those of us who have never used PostgreSQL much. I've been starting to use it with Elixir & this gives me a good understanding of why someone would use it, especially when when starting a new app. Out of curiosity, does anyone have a favorite article saved that does a great comparison of when to use certain databases?

agentgt 10y ago

We had to learn this the hard way. We have many of the data/services the article mentions and while we still use them when ever it gets massive we actually will go back to Postgresql. For example for our internal analytics/logs/metrics we use ELK and Druid but believe it or not these tools despite their purported scaling abilities are actually damn expensive. These new cloud "elastic" stuff cheat and use lots and lots of memory. For a bootstrapped solvent self-funded startup like us we do care about memory usage. For customer analytics we use... yes Postgresql. For counters and stream like things we don't use Redis we use Pipelinedb (Postgresql fork). For Cassandra like stuff we use Citus (Postgresql extension). Some of our external search uses SOLR (for small fields) but Postgresql text search is used for big fields. The only part of our platform we don't really leverage on Postgresql is the message queue and this because RabbitMQ so far has done a damn good job (that and the damn JDBC driver isn't asynchronous so LISTEN/NOTIFY isn't really useful).

ledgerdev 10y ago
Here's an async JDBC driver. https://impossibl.github.io/pgjdbc-ng/ https://impossibl.github.io/pgjdbc-ng/
- agentgt 10y ago
  Interesting. There was a project a while back that offered async through netty but it was trying to do everything async (ie not follow the JDBC spec). This project appears to follow the JDBC spec and thus is synchronous with the exception of the Listen/Notify (of which you have to work the driver directly as there is no JDBC analog).

crudbug 10y ago

Has someone played with threading model within Postgres. I was reading the documents, looks like for every client request Postgres forks a new Process and uses shared memory model. Using multi-processor threads/coroutines might be useful for scaling it further.

falcolas 10y ago
I'm more familiar with MySQL, but if PostgreSQL does not use threads, I'd eat my hat. They're just too useful in helping to spread the load of all the IO required to return even one result.
- dboreham 10y ago
  PG pre-dates (working) threads in Unix by a long time. Processes and threads are much the same thing as far as concurrency and parallelism are concerned. PG maintains a pool of pre-forked processes so there is typically no process startup delay. Shared memory regions and SysV cross-process locks facilitate IPC. Back in the day this is how threads were done in user-land: https://www.postgresql.org/docs/9.6/static/kernel-resources.html https://www.postgresql.org/docs/9.6/static/kernel-resources.... It does have parallel scans though : http://rhaas.blogspot.co.uk/2015/11/parallel-sequential-scan-is-committed.html http://rhaas.blogspot.co.uk/2015/11/parallel-sequential-scan...
  - crudbug 10y ago
    That is correct Linux treats Process & threads as identical tasks. I was thinking about control/data location. We had this three tier achitecturr, which is becoming 2-tier. I am seeing more control co-located with data for more optimal use. It will be useful to have coroutine like supprt, if someone integrates the whole Middleware layer inside postgres.
    - dboreham 10y ago
      That would be a bad idea(tm).

seibelj 10y ago

Yes, for all projects and small businesses I start, Postgres and Redis is what I use from the beginning. Then if it ever gets to the point where I need a different DB for something, I replace components with the new tool. People get fascinated with these fly-by-night data stores and put their operations at serious risk. Start with the tried and tested technologies, then carefully augment your stack as needed.

ausjke 10y ago

still using php+mysql here as I can find so many documents about their various usage easily.

treve 10y ago

Or just MySQL. Popular choice, unpopular opinion. I trust it more because it gives me a tried and tested path when I need replication (which tends to happen rather early). My understanding is that Postgres replication is not nearly as battle-tested.

einhverfr 10y ago
Especially if your application wants to turn off strict mode and then store Feb 30, 2016 as a date in the db! Cannot do that in PostgreSQL!
knz 10y ago
If you need GIS/spatial capabilities then MySQL isn't anywhere near as mature as PostgreSQL with PostGIS. I'd be curious if anyone passing by can recommend a FOSS replacement/alternative for PostGIS.
qaq 10y ago
PostgreSQL replication is very well tested and is used by a very large number of very prominent projects.
- api 10y ago
  Can you give me a link? Searching for how to deploy it turns up dozens of guides of varying age that refer to different projects of different levels of maturity.
  - qaq 10y ago
    https://www.youtube.com/watch?v=n5yt67uZdgQ https://www.youtube.com/watch?v=n5yt67uZdgQ is fairly decent talk.
bma13 10y ago
"Postgres replication is not nearly as battle-tested." - I maintain about 2,000 Postgresql database clusters at work, in various replication configurations. Over the past several years we have had maybe a handful of issues with replication, and each one related in some way to infrastructure problems. Dozens of those systems are doing over 20k transactions per second, so they are pushing decent data through the WAL stream.

api 10y ago

From my experience with both PostgreSQL and RethinkDB (and other NoSQL stores): For SQL, complex queries, and data warehousing: yes. It's an excellent database and I'm not sure why you'd pick another SQL DB unless it were a lot better on point two. For high availability and scaling: no, absolutely not. The problem with the latter is an arcane deployment process and arcane error messages that provide constant worry that you're doing something wrong. It's a many week engineering project to deploy HA Postgres, while HA RethinkDB takes hours -- followed by some testing for prudence... our testing revealed that it does "just work" at least at our scale. We were overjoyed. The docs for Postgres HA and clustering are also horrible. There are like five different ways to do it and they're all in an unknown state of completion or readiness. Of course if/when we do want complex queries and more compact storage, we will probably offload data from the RethinkDB cluster to... drum roll... a PostgreSQL database. Of course that will probably be for analytics done "offline" in the sense that if the DB goes down for a bit we are fine. HA is not needed there. TL;DR: everything has its limitations.

jeltz 10y ago
I do not get what you refer to by arcane error messages that give constant worry. While some error messages need to be googled I think PostgreSQL is pretty good at only printing error messages when something is wrong. So if you are getting strange errors from PostgreSQL it is almost certainly an issue which should be fixed.
manigandham 10y ago
Scaling (horizontal + vertical) and HA are the biggest weaknesses of Postgres. It's modern in features and flexibility but still struggles with core infrastructure issues and performance.

mooneater 10y ago

How do you get a "hotstandby replica for $5/month"?

mooneater 10y ago

Another big plus for postgres: PL/Python, PL/R, etc

mooneater 10y ago

Postgres' awesome extensible type system means it will continue to increase in functionality much more easily than most comparable DBs. https://www.postgresql.org/docs/9.6/static/extend-how.html https://www.postgresql.org/docs/9.6/static/extend-how.html

ckdarby 10y ago

Why is the font so small on this site?

hartator 10y ago

I am more the MongoDB bandwagon. Shemaless makes prototyping so much easier. And no migrations!

qaq 10y ago
create a table with uuid and jsonb, collection_id columns and you've got a fully ACID compliant document store :).
madhadron 10y ago
Just make sure that you replace it before you try to load it with replication and sharding. Mongo's replication is not atomic, despite their claims otherwise. This includes versions in the last six months. Mind you, you should be very cautious about loading it in a single instance as well, since it has unpleasant behaviors there, too, but you simply cannot trust it when replication is involved.

mamcx 10y ago

Now imagine if we understand that the relational model is no for "just data storage" but also can be use for everything. The closest thing(1) was dbase/foxpro. You can actually build a full app with it. Send email from the database? Yes. Is not that wrong? Is wrong just because RDBMS (2) made it wrong, not because is actually wrong. Why is better to split in separated languages/run times/models a app than one integrated? (1): Taking in consideration that neither Fox or any "modern" rdbms have take the relational model to it full extension. (2): A RDBMS is a full-package with a defined role, and limited capabilities. A relational-alike language will not be a exact replica of that. Not even is demanded to implement a full-storage solution. The biggest mistake the relational guys have commited is to think always in terms of full-databases instead of micro-database. Ironically, kdb+ (or lisp? or rebol?) could be the closest thing to the idea (where data+code are not enemies but friends).

discreteevent 10y ago
Have a look at Eve http://witheve.com http://witheve.com
- mamcx 10y ago
  Yeah, it look interesting. I wonder how it could be for a full realistic app.

nanistheonlyist 10y ago

Yes, yes it is.