Re: Where **not** to use PostgreSQL?

On Thu, Feb 28, 2019 at 1:09 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

> Hi
>
> čt 28. 2. 2019 v 12:47 odesílatel Thomas Güttler <
> guettliml(at)thomas-guettler(dot)de> napsal:
>
>> Hi experts,
>>
>> where would you suggest someone to **not** use PostgreSQL?
>>
>
Hard question. There are a lot of general places where PostgreSQL is not
by itself the best fit, and where a careful weighing of pros and cons would
need to be made before deciding to use it.

Having used PostgreSQL in place of ElasticSearch for PB-scale deployments,
as a high-throughput queue system, and near-real-time OLAP workloads, I am
fairly aware of how hard it can be pushed.

So the answers here are not "don't use PostgreSQL here" but "think about it
first and consider alternatives."

>
>> Why would you do this?
>>
>
Replacing with "What would you consider to be the tradeoffs?"

>
>> What alternative would you suggest instead?
>>
>
So a few possibilities:

1. a) TB-scale full text search systems.
b) PostgreSQL's full text search is quite capable but not so powerful
that it can completely replace Lucene-based systems. So you have to
consider complexity vs functionality if you are tying with other data that
is already in PostgreSQL. Note further that my experience with at least
ElasticSearch is that it is easier to scale something built on multiple
PostgreSQL instances into the PB range than it is to scale ElasticSearch
into the PB range.
c) Solr or ElasticSearch

2. a) High performance job queues
b) PostgreSQL index and table structures are not well suited to large
numbers of ordered deletes. There are ways around these problems and again
if other data is in PostgreSQL, the tradeoff is around transactional
behavior and complexity there vs ease of scaling performance.
c) Redis if the job queue easily fits into a small enough amount of
memory, or Kafka if it does not

On to where you actually should never use PostgreSQL:

Don't use PostgreSQL for things where you do not want or cannot guarantee
transactional atomicity.
While it is possible to have untrusted languages have side effects in the
real world, the fact is that mixing transactions and non-transactional
behavior in this way adds a lot of really ugly complexity.
Use another development environment instead.

>
> Don't use Postgres like cache, don't use Postgres for non transactional
> short life often updated data.
>
> Use inmemory databases instead
>
> Pavel
>
>
>>
>> Regards,
>> Thomas Güttler
>>
>>
>> --
>> Thomas Guettler http://www.thomas-guettler.de/
>> I am looking for feedback:
>> https://github.com/guettli/programming-guidelines
>>
>>

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more