3 ms·

"keep their size under the memory allocated to postgres." Assuming that you can do that, explain to me how this doesn't totally wipe away any scalability/perfo

by steve 19y ago

"keep their size under the memory allocated to postgres." Assuming that you can do that, explain to me how this doesn't totally wipe away any scalability/performance advantage gained by using slow sql databases. Well, yes, I guess it depends what you're doing. A speed increase of ~100x and increased simplicity made the crucial difference in my decision to drop the database for most of its traditional uses in my app.

epi0Bauqu 19y ago
I don't really understand your comment, but I'll just try to just restate more clearly and then maybe it will answer it anyway. If anyone has a specific situation, I'd be happy to speculate on it. By keeping the indexes under the memory allocated to postgres, I mean the total memory that can be set aside to the DB on the machine. If you look at the pg data files you will find the indexes are just large flat files like BerkeleyDB or whatever homegrown file system thing you want to create. So you do not need to increase the shared buffers to some crazy amount. In fact, you will have a negative performance result if you do that. The kernel will automatically cache these files upon repeated use. So if you have a machine with 4GB of memory and you can allocate 3GB to the db, then these indexes will just remain cached in memory forever. And you can easily get relatively cheap machines now with 16GB and higher, so this gets you really far. If you only run sql queries that are indexed appropriately and the indexes are in memory, then I assure you postgres will not be the bottleneck in your app. This is not hard to do; it just takes a little postgresql.conf tweaking and data model forethought. In particular, you turn of sequential scans and decrease the index tuple cost and run select explain on all your queries to ensure that the indexes are always being used. As for the data model, use the smallest data types for indexes, and numeric types whenever possible. That will keep the size down to a minimum. PostgreSQL takes care of the rest. Again, it depends on what you are doing, but this configuration will usually tie or be significantly better than some home grown file system thing when you get some scale because, like I said, postgres primarily uses the file system memory cache as its memory cache. But if you have some file system process that is accessing a ton of different files you will get an I/O bottleneck just from the lookups alone. And if you use a big flat file, you might as well stick with postgres because of its reliability, security, concurrency, network and backup features. That being said, I still wouldn't store big static blobs in the db. I would just put them on another machine and let apache serve them up statically and put references to them in the db (indexed by some number id). That way you get the best of both worlds.
- joshwa 19y ago
  I'll answer my own question: http://www.slideshare.net/Blaine/scaling-twitter http://www.slideshare.net/Blaine/scaling-twitter cache, partition, denormalize.
  - deleted 19y ago
    [deleted]

by steve 19y ago

epi0Bauqu 19y ago

I don't really understand your comment, but I'll just try to just restate more clearly and then maybe it will answer it anyway. If anyone has a specific situation, I'd be happy to speculate on it. By keeping the indexes under the memory allocated to postgres, I mean the total memory that can be set aside to the DB on the machine. If you look at the pg data files you will find the indexes are just large flat files like BerkeleyDB or whatever homegrown file system thing you want to create. So you do not need to increase the shared buffers to some crazy amount. In fact, you will have a negative performance result if you do that. The kernel will automatically cache these files upon repeated use. So if you have a machine with 4GB of memory and you can allocate 3GB to the db, then these indexes will just remain cached in memory forever. And you can easily get relatively cheap machines now with 16GB and higher, so this gets you really far. If you only run sql queries that are indexed appropriately and the indexes are in memory, then I assure you postgres will not be the bottleneck in your app. This is not hard to do; it just takes a little postgresql.conf tweaking and data model forethought. In particular, you turn of sequential scans and decrease the index tuple cost and run select explain on all your queries to ensure that the indexes are always being used. As for the data model, use the smallest data types for indexes, and numeric types whenever possible. That will keep the size down to a minimum. PostgreSQL takes care of the rest. Again, it depends on what you are doing, but this configuration will usually tie or be significantly better than some home grown file system thing when you get some scale because, like I said, postgres primarily uses the file system memory cache as its memory cache. But if you have some file system process that is accessing a ton of different files you will get an I/O bottleneck just from the lookups alone. And if you use a big flat file, you might as well stick with postgres because of its reliability, security, concurrency, network and backup features. That being said, I still wouldn't store big static blobs in the db. I would just put them on another machine and let apache serve them up statically and put references to them in the db (indexed by some number id). That way you get the best of both worlds.

joshwa 19y ago
I'll answer my own question: http://www.slideshare.net/Blaine/scaling-twitter http://www.slideshare.net/Blaine/scaling-twitter cache, partition, denormalize.
- deleted 19y ago
  [deleted]