5 ms·

As a long time user of MySQL, I've switched to PostgreSQL for my most recent startup precisely because the PostgreSQL team continue to innovate in a sensible wa

As a long time user of MySQL, I've switched to PostgreSQL for my most recent startup precisely because the PostgreSQL team continue to innovate in a sensible way. One of the core requirements is a stable, fast and reliable initial import of existing data from 3rd party services, and because of throttling concerns that led us to a batched, retryable solution - and a requirement for simple, fast job control. We've built this around a parallelised solution using the FOR UPDATE/SKIP LOCKED feature from PostgreSQL 9.5 onwards. It's not bleeding edge, but for us it was a great example of how the pg team pick up the useful features from e.g. Oracle, and incorporate them into the product in a solid way, without all the enterprisey marketing crap that Oracle make you swallow before you can understand the actual feature-set. [the startup is a SaaS search tool that helps you index across Trello, email, GitHub, Slack, Drive - https://getctx.io https://getctx.io if you're interested.]

grogers 9y ago
It might be a good choice to switch to postgres, but it isn't like MySQL isn't innovating too. 8.0 will have a ton of features. This includes a bunch of "catch up" things like CTEs and SKIP LOCKED, but also stuff like synchronous replication built in.
- rhaas 9y ago
  The first release of PostgreSQL that had synchronous replication was released in September 2011. https://www.postgresql.org/docs/9.1/static/release-9-1.html https://www.postgresql.org/docs/9.1/static/release-9-1.html
- jimktrains2 9y ago
  I know the defaults have been changed to be safe, but I will forever feel like my trust was broken with MySQL the day I learned how bad the defaults are. Not just inefficent/generic, but actually bad. Additionally, I feel that postgresql is better engineered, more featureful, and has support for a much larger variety of use-cases (e.g. hstore, json, postgis, pg-routing, uuids, trigram indecies, &c). So between engineering perks, and the loss of trust, I only use MySQL in legacy applications that I cannot port.
bpicolo 9y ago
FOR UPDATE ... SKIP LOCKED solved a problem for us recently as well. It's a great feature for an adhoc sort of queue situation without needing a dedicated queue / workers.
devj 9y ago
For a product like yours, don't you think a NoSQL solution would be a better fit? If yes, why didn't you opt for it? If no, then why(especially considering the fact that your schemas are always in a state of flux)? PS: This is under the assumption that you have used Postgres for storing all the data from different sources.
- rorygibson 9y ago
  Hi devj, You're absolutely correct that an RDBMS would be an odd solution for a search index * Anyway, on CTX I use PostgreSQL for storing things that deserve to be relational - job control, batching, users, accounts, billing, invitation codes... All the indexed content is in an Elastic Search cluster (http://elastic.co http://elastic.co if you're unfamiliar - it's a specialised search indexing data store built atop Apache Lucene) * though actually PostgreSQL has a really good capability as a JSON store that means people do sometimes use it as a NoSQL solution
  - kuschku 9y ago
    PostgreSQL has really good capability not just as JSON store, but also for search and indexing. Its text search solutions come close to ElasticSearch, with - surprisingly - even better performance.
    - rorygibson 9y ago
      tbh I haven't looked into PostgreSQL as a direct competitor to ES in the text search space, mostly because I have quite a lot of ES experience of things like aggregation for faceting, stemming and textual analysis, and I had a (perhaps incorrect) assumption that PostgresSQL was less capable in those more specialised areas. One thing I would like to understand is how the PostgreSQL search experience scales with data volume. My main cluster for CTX will potentially get (very) big and I don't know enough about PostgreSQL, multi-master and full-text search across large amounts of data. I'll definitely do some digging though, thanks!
      - 9y ago

grogers 9y ago

It might be a good choice to switch to postgres, but it isn't like MySQL isn't innovating too. 8.0 will have a ton of features. This includes a bunch of "catch up" things like CTEs and SKIP LOCKED, but also stuff like synchronous replication built in.

rhaas 9y ago
The first release of PostgreSQL that had synchronous replication was released in September 2011. https://www.postgresql.org/docs/9.1/static/release-9-1.html https://www.postgresql.org/docs/9.1/static/release-9-1.html
jimktrains2 9y ago
I know the defaults have been changed to be safe, but I will forever feel like my trust was broken with MySQL the day I learned how bad the defaults are. Not just inefficent/generic, but actually bad. Additionally, I feel that postgresql is better engineered, more featureful, and has support for a much larger variety of use-cases (e.g. hstore, json, postgis, pg-routing, uuids, trigram indecies, &c). So between engineering perks, and the loss of trust, I only use MySQL in legacy applications that I cannot port.

bpicolo 9y ago

FOR UPDATE ... SKIP LOCKED solved a problem for us recently as well. It's a great feature for an adhoc sort of queue situation without needing a dedicated queue / workers.

devj 9y ago

For a product like yours, don't you think a NoSQL solution would be a better fit? If yes, why didn't you opt for it? If no, then why(especially considering the fact that your schemas are always in a state of flux)? PS: This is under the assumption that you have used Postgres for storing all the data from different sources.

rorygibson 9y ago
Hi devj, You're absolutely correct that an RDBMS would be an odd solution for a search index * Anyway, on CTX I use PostgreSQL for storing things that deserve to be relational - job control, batching, users, accounts, billing, invitation codes... All the indexed content is in an Elastic Search cluster (http://elastic.co http://elastic.co if you're unfamiliar - it's a specialised search indexing data store built atop Apache Lucene) * though actually PostgreSQL has a really good capability as a JSON store that means people do sometimes use it as a NoSQL solution
- kuschku 9y ago
  PostgreSQL has really good capability not just as JSON store, but also for search and indexing. Its text search solutions come close to ElasticSearch, with - surprisingly - even better performance.
  - rorygibson 9y ago
    tbh I haven't looked into PostgreSQL as a direct competitor to ES in the text search space, mostly because I have quite a lot of ES experience of things like aggregation for faceting, stemming and textual analysis, and I had a (perhaps incorrect) assumption that PostgresSQL was less capable in those more specialised areas. One thing I would like to understand is how the PostgreSQL search experience scales with data volume. My main cluster for CTX will potentially get (very) big and I don't know enough about PostgreSQL, multi-master and full-text search across large amounts of data. I'll definitely do some digging though, thanks!
    - 9y ago