13 ms·
Postgres is the database I use for everything. I find databases of all sorts extremely interesting and I've tried many of them, of all flavors. In the end, I
by andrewstuart 6y ago
Postgres is the database I use for everything.
I find databases of all sorts extremely interesting and I've tried many of them, of all flavors.
In the end, I always come back to Postgres. It's unbelievably powerful, lightweight and there's not much it can't do.
Why not use sqlite? Here's one example - I like to access my database from my IDE remotely - my understanding is that remote access is not possible with sqlite.
Another example - multitenancy is dead easy in postgres, makes things more secure and makes coding easier.
Another is that Postgres had an extremely rich set of data types, including arrays and json.
Also Postgres is designed for symmetric multiprocessing which matters in today's multicore world.
- ellimilial 6y agoIt can’t run embedded. Having no live parts is a real paradigm shift for so many scenarios.
- andrewstuart 6y agoWhat does "can't run embedded" mean exactly? I've run Postgres in a pure RAM only configuration, never touching the disk except to start. If you mean "it can't be compiled in to another application as a library", then yes that's true - it's not an embedded database.
- mfontani 6y agoCan it be contained within the application, like SQLite? Or has it to run as a separate process?
- yaur 6y ago"can't run embedded" means you have to have a dedicated DB server process. For anything I would use a database for this is a non issue, but if you want a DB in your mobile app (for example) it might be.
- skohan 6y agoSo if SQLite is typically run embedded in the client application, does this mean it's not supported to have multiple clients operating on the same SQLite DB concurrently, or is there some support for this?
- hypertele-Xii 6y agoIt uses filesystem locking mechanisms to synchronize concurrent access from multiple processes. One writer, multiple readers.
- devin 6y agoThis is covered early on in the article.
- millerm 6y agoIt’s sad when most people discuss things based on a one line title of something they haven’t read. Many questions here were answered in that article. I recently asked someone on a different site “didn’t you read the article?”. The obvious answer I alluded to was “No, I don’t have time.” Yet they managed to post about 15 different comments asking more questions about the article (title). I really wish there was a way to have a public forum with proper discourse with the caveat that people must read the material before they comment on it.
- 5cents 6y agoThe technical blog of the public broadcaster of Norway has implemented exactly this: https://nrkbeta.no/2017/08/10/with-a-quiz-to-comment-readers-test-their-article-comprehension/ https://nrkbeta.no/2017/08/10/with-a-quiz-to-comment-readers... The quiz module is open-source (https://nrkbeta.no/2017/03/06/our-comment-quiz-module-is-now-open-source/ https://nrkbeta.no/2017/03/06/our-comment-quiz-module-is-now...) and available on github https://github.com/nrkbeta/nrkbetaquiz https://github.com/nrkbeta/nrkbetaquiz
- 6y ago
- aqme28 6y agoBut if the metric is "need in most cases," then that's fine. You don't need an embedded database in most cases.
- ellimilial 6y agoThe other way around. For most problems you don't need a DB. Only for the subset of those that do you need a dedicated process/container/machine. We have learnt to use full-fledged RDBMSs as a default because they proved really flexible and powerful over the last 30+ years. But they do have limitations and cost.
- NicoJuicy 6y agoAnd Postgress can be used as an event store + document store. It also has javascript V8 functions to update data for migrations. ( eg. migrating your events to a new model, instead of keeping the different versions)
- atat7024 6y agoYou can store files/documents within SQLite dbs as well.
- NicoJuicy 6y agoThere is JSON support, yeah. But while you need to do everything with a JSON1 extension for managing the json ( https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html ). Postgress has build-in v8 javascript functions to manage this ( as stated before). Which makes it much more useable. There's a good library for .net that handles postgress documents, which has an insane amount of usefull functions ( https://martendb.io/documentation/documents/ https://martendb.io/documentation/documents/ ). I think halve of those wouldn't be possible on another database.
- ngrilly 6y agoYou don't really need v8 JavaScript functions built-in in the database when you use SQLite, because SQLite is running in the same process than your application. The round trips between your application and SQLite cost almost nothing. Also see "Many Small Queries Are Efficient In SQLite": https://sqlite.org/np1queryprob.html https://sqlite.org/np1queryprob.html
- NicoJuicy 6y agoI was literally talking about using v8 functions when managing data ( eg. updating event versions should be a clear use-case) Round-trips and small queries don't have anything related to my comment?
- 6y ago
- TicklishTiger 6y agoWhat makes multi-tenancy easier in Postgres than in any other DB?
- andrewstuart 6y agoI wrote a full multitenancy row level security system in 3 short Python functions in Django. Simple, complete, compatible with Django's architecture. All the other multitenancy solutions I researched were complex and fiddly and heavyweight and required all sorts of considerations and caveats to implement and run.
- switch007 6y agoI’d love to see the code if you can share.
- andrewstuart 6y agoI'm preparing it into a minimal open source project over the next couple of weeks. I'll post to HN when it's ready. The simple explanation is that you set a postgres environment variable prior to each query. The Postgres row level security system looks at this variable and returns nothing if the ID in the variable does not match an ID in the table. So I wrote a function in Django which intercepts every database query and prefixes each query with a postgres environment variable set command. That's pretty much it.
- arthurcolle 6y agoif you dont mind sharing, I'm curious what the methods + method signatures are!
- hardwaresofton 6y agoOne of postgres's most underrated features. RLS is amazing, can be unseen/basically work silently if your programming language-side tools are good enough, and is documented well (like everything else): https://www.postgresql.org/docs/current/ddl-rowsecurity.html https://www.postgresql.org/docs/current/ddl-rowsecurity.html But the power of PG is that it doesn't stop there, if you combine this with a plugin like temporal_tables and you can segment by user and time: https://github.com/arkhipov/temporal_tables https://github.com/arkhipov/temporal_tables All of this mostly unknown to the thing that's accessing the DB. If that's not enough for you, why not add some auditing with pgaudit: https://www.pgaudit.org/#section_three https://www.pgaudit.org/#section_three All this value is just out there. There's even more if you can stomach browsing "ugly" sites like PGXN. Most of it works out of the box, though you may need to tinker for performance and some edge cases but it's there. I think it might not actually be hyperbole to say that Postgres is the greatest RDBMS database that has ever existed.
- eru 6y ago> In the end, I always come back to Postgres. It's unbelievably powerful, lightweight and there's not much it can't do. Well, depends on how wide your definition is: Filesystems are also databases. (And eg inside of Google, they are using databases to store filesystems.) These days, we often think of relational databases. Filesystems are more like the hierarchical databases of yore that Codd (also) talked about in his original papers.
- encryptluks2 6y agoWould love to be able to have a database file system and be able to use standard Linux tools to query and make changes, back up, etc. For example, having a folder of contacts with each file named after the person and having key/value pairs. Similar to how static site generators use YAML/TOML/JSON.
- nix23 6y agoThat sounds like ndb: https://9fans.github.io/plan9port/man/man1/ndb.html https://9fans.github.io/plan9port/man/man1/ndb.html https://www.youtube.com/watch?v=e9Y0iDXXQh8&feature=share https://www.youtube.com/watch?v=e9Y0iDXXQh8&feature=share
- yason 6y agoTry your nearest shell to experiment with this? In my scripts and programs I've stored data just in files many times: simple, transparent and effective. Works everywhere. There's so much in search that you can just brute-force through unless you have a huge amount of data or a lot of concurrent tasks that it doesn't matter much in today's hardware.
- eru 6y agoThe maildir format does something like this.
- busymom0 6y agoThis makes me wonder how efficient filesystems are with millions of file in a single directory? Do they create some sort of index? Are there limits to the number of files a directory can hold? Is that what inodes are for? I remember seeing “inode” limits on some VPS I was using a while back.
- dijit 6y agoit's important to note that the author of SQLite wasn't "competing" with RDMS engines -- it's competing with flat file access. To that goal, I think it's wildly successful, instead of writing files I almost always reach for SQLite first. When systems share a database, I, like you, reach straight for postgresql. Most people reach for mysql, which I do not prefer.
- Quarrelsome 6y agoYe I believe its best to think of Sqlite not as a database per se but more as a file format, kinda as you would a word doc but a file format that doesn't suck and is absolutely amazing at storing information.
- lucioperca 6y agoIf it is mostly text, then I would say git is better suited.
- Quarrelsome 6y agogit is ACID? I still believe Sqlite is the appropriate store. Unless.. does Git handle power issues elegantly? What happens if you yank power in the middle of a commit? Sqlite is designed to handle that.
- lbruder 6y agothat's one of the reasons I love Fossil so much. Way better backend than Git.
- utxaa 6y agoone of my team members really pushed fossil, and we did like the integrated wiki/ticket system. not bad at all. ... but it was so slow compared to git! at everything. merging, pushing, pulling ... you did not experience same?
- hannofcart 6y ago> Here's one example - I like to access my database from my IDE remotely - my understanding is that remote access is not possible with sqlite Not sure exactly what you mean by 'remote access', but if it's to debug something on a remote DB from an SQL IDE, I do this quite often. I use sshfs to mount the remote filesystem and open the SQLITE file in DBeaver. Even in case of Postgres, you would have to connect to a remote DB via SSH or via VPN. It's just that you will be using sshfs to connect to a remote SQLITE file.
- justsomeuser 6y agoI just scp that son of a b down and run all the queries on the SQLite file locally.
- spicybright 6y agoSounds like a pain for larger databases.
- justsomeuser 6y agoWell it depends how large you are talking about. With todays internet speeds, anything under 10GB is a less than a few minutes. I once accidentally locked a production MySQL database with some kind of recursive sub query. Running queries locally on a copy avoids lock/mutation issues, and I have a snapshot to re-run queries on if I need to go back and see the source of the data.
- spicybright 6y agoI guess there's a lot of cases I just want to peek into a table live without pulling it all down. I've only used postgres so it's weird to me you can't talk to any MySql style DB like it does. Anything that needs to be coded I'll pull it down though, it's nice to have options.
- nh2 6y agoNote that using SQLite over SSHFS is dangerous if you're not the only user of the DB, because SSHFS does not support file locking. According to: https://www.fossil-scm.org/forum/forumpost/8749496886 https://www.fossil-scm.org/forum/forumpost/8749496886 > There's a very real possibility that you could corrupt the SQLite DB by running it over SSHFS.
- Annatar 6y agoIt's unbelievably powerful, lightweight and there's not much it can't do. I agree, but there is one thing that it cannot do, one very important, critical thing: synchronous multimaster replication. Well, vanilla PostgreSQL cannot. Vertica can.
- 7thaccount 6y agoA lot of IDEs and other graphical tools can point towards a SQLite database. Does it really have to be local though? I assumed you could hit it from any external file share? Or did you mean something else?
- gher-shyu3i 6y agoWhat's wrong with MySQL? Genuine question.
- relaxing 6y agoThere’s nothing “wrong” with MySQL/MariaDB - it powers huge portions of the web. Postgres has a larger feature set. Whether or not you need it depends on your use case. There are ample pages out there that will give you a side by side comparison if you’re curious.
- blacktriangle 6y agoBy multi tenancy is dead easy in Postgres, are you talking about schemas? If so, have you run into any scaling issues with the number of tenants you can support. I looked into using schemas for multi tenancy but the various reports I found said around 50 tenants or so things started breaking down very quickly.
- andrewstuart 6y agoRefer to this comment elsewhere in this thread: https://news.ycombinator.com/item?id=26817468 https://news.ycombinator.com/item?id=26817468 No I am not using schemas - I found them to be brittle and complex as a solution for multitenancy. I use Postgres row level security. I use Python/Django. Read this entire comment thread for more info from other posters on row level security.
- bjacobt 6y agoCan you share more on how you do multitenancy? Are you using it for web/mobile? What language/Library do you use to access the database? I’m building a multi tenant web application on node/sequlize/feathers and implementing logic in code. I’ve seen table row level security but haven’t looked deeply.
- andrewstuart 6y agoRefer to this comment elsewhere in this thread: https://news.ycombinator.com/item?id=26817468 https://news.ycombinator.com/item?id=26817468 I use Python/Django. I use Postgres row level security. Read this entire comment thread for more info from other posters on row level security.
- jhgb 6y ago> Why not use sqlite? Here's one example - I like to access my database from my IDE remotely - my understanding is that remote access is not possible with sqlite. I found https://kristaps.bsd.lv/sqlbox/ https://kristaps.bsd.lv/sqlbox/ to be an interesting approach.
- tzs 6y ago> In the end, I always come back to Postgres. It's unbelievably powerful, lightweight and there's not much it can't do. How lightweight? SQLite itself is around 600 KB. That's with all features enabled. I believe you can get it it down to about half that if you disable features you don't need. RAM usage is typically under a dozen or so KB. It doesn't even require that you have an operating system. All you need for a minimal build is a C runtime that includes memcmp, memcpy, memmove, memset, strcmp, strlen, and strncmp, and by default also malloc, realloc, and free although it has provisions for providing different memory allocators, and you have to provide some code to access storage if you are running on a system that doesn't have something close to open, read, write, and the like. This means it can even be used on many embedded systems far too small to run a Unix. Heck, it is not even too unreasonable to compile SQLite to webasm and include it on a web page if for some reason you need client side SQL on the web.
- exyi 6y agoDepends on what you need. It does not seem like a great idea to run Postgres in a browser, but it is usually less resource hungry than the JVM-based app using it to return HTML or JSON, so I can call it lightweight for server use.
- nawgz 6y ago> Heck, it is not even too unreasonable to compile SQLite to webasm and include it on a web page if for some reason you need client side SQL on the web. With the deprecation of Web SQL, I highly doubt this is a use-case anyone cares for anymore. Anyhow, I see your main point: SQLite is indeed "Lite", so it makes sense to use in embedded or small single-consumer cases. I just don't think those are very interesting tools to make in 2021, we expect collaboration and concurrency, so PostgreSQL wins in my imagination-space. I am a web developer though, so I am inherently biased. I also am not the person you were just discussing with
- dragonwriter 6y ago> With the deprecation of Web SQL, I highly doubt this is a use-case anyone cares for anymore. WebSQL didn’t die as a standard because noone wanted client SQL on the web, it died because SQLite was the only reasonable way of providing it, preventing multiple really-independent implementations.
- znpy 6y agoI've seen mysql used in multi-master ha configuration (percona-based solution) successfully, do you have anything to recommend for a true multi-master postgresql solution (ideally open source)?
- rattray 6y agoPostgres-XL and Postgres-BDR are both multi-master. I don't think they're open-source.
- api 6y agoI like everything about Postgres except how arcane it is. It feels like 1980s Unix software, and everything about it is manual. It seems like there are multiple ways to do many things and I never feel like I am sure I am doing it the "right" way (as in both correct and modern and what you should do). I've been watching CockroachDB as a more modern alternative that can auto-scale easily, do master/master easily, etc. Those are all painful on Postgres to the point that hosted and managed pgsql is a cash cow for cloud vendors.
- aabbcc1241 6y agoYou can remote access sqlite database with sshfs or curlftpfs. Or simply sqlite cli over ssh