9 ms·

Rule #1: Always start with PostgreSQL unless you have a very compelling reason not to.

by bane 8y ago

revskill 8y ago
This reminds me of: Always bet on Javascript unless you have a very compelling reason not to.
liquidise 8y ago
"Very compelling reasons" means different things to different people. Which i suppose is a catch 22. The young but introspective dev may make the wrong call on a side project and learn to stick to psql in the future. The dev who is convinced "web scale" will arrive before their first AWS bill and picks Mongo as their production database will have a worse fate. As will their team and company by proxy. Rule #1: Always start with PostgreSQL. You don't have a compelling reason not to.
- webninja 8y ago
  Is there a nice web interface that is made for PostgreSQL? I’m in love with the power that PHPMyAdmin brings to MySQL. Also there are DigitalOcean Ubuntu droplet images with it all pre-configured.
  - h1d 8y ago
    It always feels way snappier to use a local GUI connected through SSH than PhpMyAdmin and its interface also feels very cluttered to me.
int_19h 8y ago
Always start with in-memory data structure with straightforward persistence (i.e. load and save it all at once, using some popular format). If you need ACID, then start with SQLite. If you need scalability as well, then PostgreSQL. Most other features aren't worth the hassle of configuration compared to a single file on disk and a library to link to. (But I suspect that most NoSQL apps these days would do just fine with SQLite, perf-wise. As an industry, we don't have a good sense of scale.)
- seanwilson 8y ago
  > Always start with in-memory data structure with straightforward persistence (i.e. load and save it all at once, using some popular format). Do you have any examples of this? It's rare I see suggestions like this. I've done this for HTML mobile apps a few times before where the entire app (fairly simple) state is saved and loaded as JSON when you make any changes. It's super simple and very little can go wrong. This was as opposed to using Web SQL (which wasn't deprecated at the time) where it's much more complex to keep what's in-memory synchronised with what's in the database. Edit: I'm talking about client side code to store local state here.
  - ousta 8y ago
    i struggle to see why in-memory data structure is the way to start can you please explain. do you create json for all your users?
    - chrisseaton 8y ago
      > i struggle to see why in-memory data structure is the way to start can you please explain. It’s much simpler. The idea is to start simple and avoid complexity. Do you need the extra complexity of a PostgreSQL instance or can you get away with doing it in-memory? If so you can save yourself a lot of complexity.
      - lowercased 8y ago
        > If so you can save yourself a lot of complexity. The complexity exists, you're just shifting it around some. The "complexity" of setting up a populate db server is non-zero, but generally a known-entity. The complexity of managing your own stuff 'in-memory', with all the attendant issues about concurrency/corruption/performance/etc - would almost always end up being far more custom to your particular project.
        int_19h 8y ago
        If that were true, we wouldn't store any data in memory at all - everything would be in the database to avoid "the attendant issues about concurrency/corruption/performance/etc". But we don't. Perhaps because the attendant issues often aren't issues in practice? There's no concurrency problem if it's just your app working with data, and it's logically single threaded (i.e. possibly async, but serialized) - which is by far the most common model for non-web apps. There's also no concurrency problem if you use immutable data structures. There's no corruption problem if you are using a memory-safe language, and your data structures enforce their invariants, as they should in any good design regardless of anything else. Performance is going to be better, often significantly so, until you have enough data that dumb queries over it are slower than the overhead of communicating with the database. And so on. So, no, the complexity doesn't necessarily exist. The complexity of your data is constant, and of the kind of processing that you need to do with it, true. But we're talking about complexity of the pipeline you use to access that data - and there, you can absolutely make it more complicated that it needs to be, and I'm arguing that it is what usually happens in practice.
leetrout 8y ago
In 2019. A LOT of people want MySQL over Postgres because of ignorance about how far Postgres has came and they don't know/care. I try to explain why I like transactional DDL and the guardrails that come with Postgres. With MySQL you end up having to use Percona to get a really robust DB setup with proper tooling that compares to Postgres in my experience. Both, of course, have plenty of known and unknown knobs you can turn to your liking and can make either database appear to be better by any number of metrics for your use case.
- bpye 8y ago
  And I'm sure one day some new DB will pop up, and for most workloads it'll be better. People will still use PostgreSQL because it was the right choice in 2019. So the cycle will repeat...
  - leetrout 8y ago
    Without a doubt. Lots of movement around specializations (ML/AI datasets) and decentralization... When the tasks we're solving are fundamentally different than I expect the tools to support those tasks will be fundamentally different, too.
- purple_ducks 8y ago
  > A LOT of people want MySQL over Postgres because of ignorance or you know, for multi master cluster pretty much out of the box. Gallera DB.
  - ricklepick 8y ago
    It's rarely as nice in reality as it sounds on paper though... Gallera DB setups aren't easy, and still have complex issues.
unethical_ban 8y ago
Compelling reason #1: We don't need hot-warm HA and don't want to fight for a database with IT every time we write a homebrew app. We use SQLite.
- krferriter 8y ago
  If you're writing apps for user computers that have no reason to sync to a central remote database then SQLite is totally fine. If you're writing an application that runs in a cluster or on the web, Postgres is the way to go. There almost never a reason to go with Mongo unless you're just trying to get buzzwords in a presentation and trying to seem hip. That's going away now though. My place exclusively uses Postgres on all of our webservers.
blauditore 8y ago
Why not something even simpler like H2?
- Scarbutt 8y ago
  Java dependency.

Rule #1: Always start with PostgreSQL unless you have a very compelling reason not to.

by bane 8y ago

Rule #1: Always start with PostgreSQL unless you have a very compelling reason not to.

revskill 8y ago

This reminds me of: Always bet on Javascript unless you have a very compelling reason not to.

liquidise 8y ago

"Very compelling reasons" means different things to different people. Which i suppose is a catch 22. The young but introspective dev may make the wrong call on a side project and learn to stick to psql in the future. The dev who is convinced "web scale" will arrive before their first AWS bill and picks Mongo as their production database will have a worse fate. As will their team and company by proxy. Rule #1: Always start with PostgreSQL. You don't have a compelling reason not to.

webninja 8y ago
Is there a nice web interface that is made for PostgreSQL? I’m in love with the power that PHPMyAdmin brings to MySQL. Also there are DigitalOcean Ubuntu droplet images with it all pre-configured.
- h1d 8y ago
  It always feels way snappier to use a local GUI connected through SSH than PhpMyAdmin and its interface also feels very cluttered to me.

int_19h 8y ago

Always start with in-memory data structure with straightforward persistence (i.e. load and save it all at once, using some popular format). If you need ACID, then start with SQLite. If you need scalability as well, then PostgreSQL. Most other features aren't worth the hassle of configuration compared to a single file on disk and a library to link to. (But I suspect that most NoSQL apps these days would do just fine with SQLite, perf-wise. As an industry, we don't have a good sense of scale.)

seanwilson 8y ago
> Always start with in-memory data structure with straightforward persistence (i.e. load and save it all at once, using some popular format). Do you have any examples of this? It's rare I see suggestions like this. I've done this for HTML mobile apps a few times before where the entire app (fairly simple) state is saved and loaded as JSON when you make any changes. It's super simple and very little can go wrong. This was as opposed to using Web SQL (which wasn't deprecated at the time) where it's much more complex to keep what's in-memory synchronised with what's in the database. Edit: I'm talking about client side code to store local state here.
- ousta 8y ago
  i struggle to see why in-memory data structure is the way to start can you please explain. do you create json for all your users?
  - chrisseaton 8y ago
    > i struggle to see why in-memory data structure is the way to start can you please explain. It’s much simpler. The idea is to start simple and avoid complexity. Do you need the extra complexity of a PostgreSQL instance or can you get away with doing it in-memory? If so you can save yourself a lot of complexity.
    - lowercased 8y ago
      > If so you can save yourself a lot of complexity. The complexity exists, you're just shifting it around some. The "complexity" of setting up a populate db server is non-zero, but generally a known-entity. The complexity of managing your own stuff 'in-memory', with all the attendant issues about concurrency/corruption/performance/etc - would almost always end up being far more custom to your particular project.
      - int_19h 8y ago
        If that were true, we wouldn't store any data in memory at all - everything would be in the database to avoid "the attendant issues about concurrency/corruption/performance/etc". But we don't. Perhaps because the attendant issues often aren't issues in practice? There's no concurrency problem if it's just your app working with data, and it's logically single threaded (i.e. possibly async, but serialized) - which is by far the most common model for non-web apps. There's also no concurrency problem if you use immutable data structures. There's no corruption problem if you are using a memory-safe language, and your data structures enforce their invariants, as they should in any good design regardless of anything else. Performance is going to be better, often significantly so, until you have enough data that dumb queries over it are slower than the overhead of communicating with the database. And so on. So, no, the complexity doesn't necessarily exist. The complexity of your data is constant, and of the kind of processing that you need to do with it, true. But we're talking about complexity of the pipeline you use to access that data - and there, you can absolutely make it more complicated that it needs to be, and I'm arguing that it is what usually happens in practice.

leetrout 8y ago

In 2019. A LOT of people want MySQL over Postgres because of ignorance about how far Postgres has came and they don't know/care. I try to explain why I like transactional DDL and the guardrails that come with Postgres. With MySQL you end up having to use Percona to get a really robust DB setup with proper tooling that compares to Postgres in my experience. Both, of course, have plenty of known and unknown knobs you can turn to your liking and can make either database appear to be better by any number of metrics for your use case.

bpye 8y ago
And I'm sure one day some new DB will pop up, and for most workloads it'll be better. People will still use PostgreSQL because it was the right choice in 2019. So the cycle will repeat...
- leetrout 8y ago
  Without a doubt. Lots of movement around specializations (ML/AI datasets) and decentralization... When the tasks we're solving are fundamentally different than I expect the tools to support those tasks will be fundamentally different, too.
purple_ducks 8y ago
> A LOT of people want MySQL over Postgres because of ignorance or you know, for multi master cluster pretty much out of the box. Gallera DB.
- ricklepick 8y ago
  It's rarely as nice in reality as it sounds on paper though... Gallera DB setups aren't easy, and still have complex issues.

unethical_ban 8y ago

Compelling reason #1: We don't need hot-warm HA and don't want to fight for a database with IT every time we write a homebrew app. We use SQLite.

krferriter 8y ago
If you're writing apps for user computers that have no reason to sync to a central remote database then SQLite is totally fine. If you're writing an application that runs in a cluster or on the web, Postgres is the way to go. There almost never a reason to go with Mongo unless you're just trying to get buzzwords in a presentation and trying to seem hip. That's going away now though. My place exclusively uses Postgres on all of our webservers.

blauditore 8y ago

Why not something even simpler like H2?

Scarbutt 8y ago
Java dependency.