131–140 of 181 posts
What about binary blobs? I hate having to store them outside of the database and that you can't just do a dbdump/import to clone a project.
[1] https://www.postgresql.org/docs/current/datatype-binary.html
Earlier quoted context omitted.
Not a problem until it's a problem.
"Until it's a problem" is doing a lot of heavy lifting in your sentence. For 99% of projects that "until" will never come.
Glad to see pg_render on the list! Here's a demo site rendered entirely using just Postgres(t): https://pgrender.org
macOS, Safari and Firefox.
Postgres, ClickHouse and NATS for everything
Also, what's Clickhouse for? Logs, observability data?
Just don't use a single Postgres DB for everything as you scale up to 100+ engineers. You'll inevitably get database-as-the-API. Now if you have the actual technical leadership [1] to scale your systems by drawing logical and physical boundaries so that each unit has its own Postgres? Yeah Postgres for everything is solid. [1] Surprisingly rare I've found. Lots of "successful" CTOs who don't do this hard part.
You don't have to plan this very early; most companies won't get to 100+ engineers. Let's ship first and worry about this, much much much much later. Overarchitecting stuff makes life hard; coming into companies that have 40 servers running with everything architected for 1000000 engineers and billions of visitors while in reality there aren't even 2 users and there is 1 overworked engineer. Stop doing that and stop…
Earlier quoted context omitted.
If you don't have any discipline it becomes hell. Not to mention that a random team writing a migration that locks a key shared table (or otherwise chokes resources) now causes outages for everyone .
Right but in this "100-engineer" scenario you'd have hoped the following would have happened: - Docs and guidelines on migrations would have been written - Some level of approval and review is required before execution These are things that isn't really postgres specific, any company that doesn't have those is going to be a nightmare.
e.g. User management deploy has somehow taken down core payment processing.
Just don't use a single Postgres DB for everything as you scale up to 100+ engineers. You'll inevitably get database-as-the-API. Now if you have the actual technical leadership [1] to scale your systems by drawing logical and physical boundaries so that each unit has its own Postgres? Yeah Postgres for everything is solid. [1] Surprisingly rare I've found. Lots of "successful" CTOs who don't do this hard part.
Postgres is inherently multi-tenant. Have separate logic DBs in one physical instance, connect using roles with minimal permissions, expose views (and materialized views!) for querying so you can mutate the underlying tables without requiring applications to change.
Earlier quoted context omitted.
You definitely don’t need a special database for bitemporal data. Just a datetime and as of data time column, your value column and whatever metadata you want (or a jsonb col for metadata if you want more flexibility at the cost of some speed of filtering by metadata)
How do you find it when you scale it up to every table, every query?
It scales just fine depending of course on the usual stuff - load pattern etc. If you want really high scalability you should be using something like clickhouse anyway.
Edit to add: the rationale behind having separate intraday and daily time series tables in those systems is the type of the value and as of timestamps is different (in one its a date, in one its a datetime), and storing dates as datetimes is a rich source of bugs.
Earlier quoted context omitted.
In that second case the string is better represented as "bytea", which has most (but not all) of the features of the "text" type.
I agree with your take, it's just that many programmers want to easily jump from "byte array" to "string in XYZ encoding". I personally prefer byte arrays for unsafe data and to do deserialization in application code.
There are two types of programmers, those that are wrong and those that are very wrong
Earlier quoted context omitted.
I agree with your take, it's just that many programmers want to easily jump from "byte array" to "string in XYZ encoding". I personally prefer byte arrays for unsafe data and to do deserialization in application code.
In other words, considering we are talking about string and unicode... There are two types of programmers, those that are wrong and those that are very wrong
Funny but not entirely true. I had cases when we had to urgently store a firehose of data and figure out the right string encoding later. Just dumping the strings with uncertain encoding in `bytea` columns helped us there.
Plus for some fields it helps with auditability f.ex. when you get raw binary-encoded telemetry from devices in the field, you should store their raw payloads _and_ the parsed data structures that you got from them. Being this paranoid has saved my neck a few times.
The secret is to accept you are not without fault and take measures to be able to correct yourself in the future.