Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL for Everything

raphaelbauer.com

Post reply on HN
Print view View original

151–160 of 286 posts

Re: PostgreSQL for Everything

#151

Post by sgarland »

sgt wrote: post #148

Intrigued by this > After some performance checks it became clear that PostgreSQL was even faster than reading from the file system for our use-case. PostgreSQL uses the file system very efficiently for its data - and it adds a lot of caching and efficient reading and writing strategies that can outperform writing and reading raw data on a file system. This goes against conventional knowledge. I've always heard (and…

The primary reason to avoid doing so is avoiding thrashing your buffers, along with increased size of backups, WAL bloat, etc.

Can you? Yes. Should you? Not at anything beyond a toy scale, unless you want to pay for more RAM to ensure that your normal OLTP queries don’t take a performance hit.

Re: PostgreSQL for Everything

#152

Post by Kinrany »

Gluber wrote: post #33

I tend to agree with quite a few points in the article, but some topics warrant some careful scrutiny. * As a message queue: Only if your required features are very basic, like if you need cluster communication and run your own coordination protocol on top. * High Volume Time Series: TimeScale works, but composes badly with other workloads on the same DB server ( from an operational perspective at scale ) * Vector Da…

> Microservice: If your service is ONLY exposing json data from some database model, then it should not exist at all IMO. Create a view and be done with it.

Yeah, this has nothing to do with Postgres. If the service is accessing a database that isn't internal to the service, then that database is already a standalone service in itself.

Re: PostgreSQL for Everything

#153

Post by crazygringo »

andai wrote: post #140
psadauskas wrote: post #139

My general rule of thumb is "Use Postgres until you've discovered why you can't use Postgres." Anything you introduce is another moving part you have to operate and maintain, and in the beginning, Postgres can probably handle it. Wait for load, see where its failing, and then you'll have a better idea if adding another tool is worth the cost.

Doesn't the same argument apply even more to using SQLite instead?

Not really. They are two different paradigms. Use the one that is right for you.

SQLite is embedded for local applications with one writer mostly.

Postgres is for a client-server architecture with many writers.

When you start a project, you generally know which architecture you need.

Re: PostgreSQL for Everything

#154

Post by dwedge »

cyh555 wrote: post #105
stackskipton wrote: post #94

Earlier quoted context omitted.

As SRE dealing with this at current company, a benefit of using well known software like Kafka is a lot of problems you will run into have solutions/guidance already available vs you having to explore solutions which a lot of time end with “Kafka could easily do this. “

100% except when Kafka goes wrong, who maintains it?

This problem doesn't go away with postgres. It's totally anecdotal but this is one thing that I've noticed different in mysql shops and postgres shops - with mysql there is usually at least one person on staff who knows MySQL DBA and scaling pretty well, with postgres it's rarely the case to have someone who knows the internals well - like you said, the person capable of maintaining it when it goes wrong.

You could argue it's because postgres requires less poking though I would say you don't need the DBA for when things go right.

Of course most people are just handing the management off to the cloud and that's potentially why, but it doesn't cover everything

Re: PostgreSQL for Everything

#155

Post by andai »

crazygringo wrote: post #153
andai wrote: post #140

Earlier quoted context omitted.

Doesn't the same argument apply even more to using SQLite instead?

Not really. They are two different paradigms. Use the one that is right for you. SQLite is embedded for local applications with one writer mostly. Postgres is for a client-server architecture with many writers. When you start a project, you generally know which architecture you need.

So if it needs to work offline, but it syncs with a server, then you use both? (And the schema becomes some kind of lowest common denominator?)

Re: PostgreSQL for Everything

#157

Post by KronisLV »

> PostgreSQL allowed us to use a fulltext search plugin to do everything in one system. No need to sync any data. No need to maintain and run two systems. It just worked and made us smile (after some tweaks of course). Simplicity.

I found MariaDB to be wonderfully simple to use for somewhat casual use cases: https://mariadb.com/docs/server/ha-and-performance/optimizat... and still reach for it in some personal projects, however the whole growing MySQL incompatibility is a big issue if the tech you use only officially supports MySQL and you can't (easily) get MariaDB specific DB drivers.

Personally, one of the best things about PostgreSQL is transactional DDL, every DB should support it. Also they handle JSON pretty nicely (though I'd prefer not to store data like that unless necessary) alongside excellent plugins like pgvector and PostGIS.

On the other hand, for things like queues, or even any sort of blob storage, I'd look at things like RabbitMQ or Garage (S3 compatible). Sometimes specialized software is nice for keeping things logically separated. I maintain that it's good to be able to divide your stack up by mechanisms/concerns (rather than business domain necessarily).

Re: PostgreSQL for Everything

#158

Post by andriy_koval »

idoubtit wrote: post #18

Why write a fanboy text with unfair comparisons that hide the Postgres limitations? For instance, for many simple needs MySQL is simpler than Postgres, with similar performance and consistency. * No need for a connection pool, while many use cases with Postgres require PgBouncer and Co. * Easy sort (and basic search) of multilingual text, because MySQL has case insensitive UTF8 collations. * No need to VACUUM, which…

> * No need for a connection pool, while many use cases with Postgres require PgBouncer and Co.

is there a strong evidence you even need client side connection pool at all? What is the purpose?

The limitation is that you have many clients with connection pools, they hold internal PG connection without allowing it to be reused by other clients..

Re: PostgreSQL for Everything

#159

Post by sgt »

sgarland wrote: post #151
sgt wrote: post #148

Intrigued by this > After some performance checks it became clear that PostgreSQL was even faster than reading from the file system for our use-case. PostgreSQL uses the file system very efficiently for its data - and it adds a lot of caching and efficient reading and writing strategies that can outperform writing and reading raw data on a file system. This goes against conventional knowledge. I've always heard (and…

The primary reason to avoid doing so is avoiding thrashing your buffers, along with increased size of backups, WAL bloat, etc. Can you? Yes. Should you? Not at anything beyond a toy scale, unless you want to pay for more RAM to ensure that your normal OLTP queries don’t take a performance hit.

Agreed. Even putting them on the filesystem and rsyncing in a cronjob would be better, which says a lot.

Re: PostgreSQL for Everything

#160

Post by KronisLV »

zelphirkalt wrote: post #107
thatwasunusual wrote: post #73

Earlier quoted context omitted.

It's the other way around for me: as 99% of the stuff I develop is .NET (and I use EF Core for database stuff), I can get away with SQLite for local development, prototyping (and even staging), and then just "flip a switch" for it to run on production PostgreSQL. Both are amazing technologies.

I can't recommend this "switch". If you are not testing locally with the same relational database as in production, you can miss mistakes and bugs. This is not just theoretical. One example where I thought I will be fine using SQLite was with a small Django project. But time and time again I ran into limitations of either SQLite or Django's database adapter for SQLite, when it came to dealing with many to many relati…

> Anyway, it is a basic practice of keeping test and dev environment as close as feasible to production, to avoid missing issues and wrong assumptions.

Containers are great for this during development.

Testcontainers are great for tests in particular when you don't want to use some mocked in-memory DB because those have the same issues as using a different DB during development: https://testcontainers.com/

Post reply on HN