upvote

points

by petcat21 hours ago |
comments
upvoteby nemothekid20 hours ago|
next
[-]
There are so many unfortunate footguns with unlogged tables, that I'd argue that the goroutine route is preferable.
reply
upvoteby petcat18 hours ago|
parent|
next
[-]
What are the "footguns" with unlogged tables in Postgres?
reply
upvoteby nemothekid1 hours ago|
parent|
[-]
1. If postgres shutsdowns uncleanly, your entire table is truncated; you lose everything.

2. You should check if your backup method backs up unlogged tables. For example, RDS Snapshots on AWS do not backup unlogged tables.

These 2 are a double whammy where if you aren't aware of these tradeoffs you can find that a bad restart has deleted all your data, plus your unlogged tables were never backed up.

reply
upvoteby jeltz19 hours ago|
parent|
prev|
[-]
Such as?
reply
upvoteby glzone111 hours ago|
prev|
[-]
Running postgresql is an order of magnitude more complicated than sqlite.

130k tps even with unlogged is not always super easy especially if getting hit concurrently. Postgresql connection overhead alone can be pretty brutal if you are setting up and tearing down connections or have 1,000 writers etc.

Postgresql generally requires good network connectivity. Folks doing sqlite distributed tend to have everything independent, you literally don't need to worry about connection / security / firewall / permissioning / internode escape or data leaking etc, can even have problems in local side networking and services can still serve.

reply
upvoteby jklmnopqrstuvw7 hours ago|
parent|
[-]
even with wal, postgresql can easily reach 130k tps in pipeline mode.
reply
upvoteby lanstin2 hours ago|
parent|
[-]
That was per container, with 16 containers per data center, so would be a lot of DBA tickets to get something that large; SQLite scaled with the horizontal scaling of the app; and we did have a flaky network - something like one in 100,000 tcp connections would fail. And occasionally the whole network would just go away for a number seconds. And the persistent container storage was managed by the same storage team that managed storage for the DB team, so base scalability and availability high.
reply