![]() ![]() ![]() My application is going to have massive amounts of simple read operations, but it's only rarely going to be querying the spatial data. This would obviously have a high fixed overhead of memory usage, but if it scaled better, I would want to do it. running both on the same box and only using PostgreSQL for spatial data. It would also affect my choice of whether to go all-PostgreSQL vs. I'm nervous about what would happen with PostgreSQL under these circumstances, both with respect to speed and memory usage.ĭoes anyone have experience with this and know what kind of performance and memory usage I can expect? I'm probably going to be forced into using PostgreSQL whether or not the memory usage is an issue, and I want to know what to expect. Most operations on my applications and pageloads happen <2 sec. MySQL has performed gracefully in my applications even with spikes running into thousands of connections happening in a few seconds. My applications are always read-heavy and most of the queries are simple. This is a big issue for me because memory is usually the limiting resource on my cloud hosting on these applications. MySQL I came across a lot of sources that say that PostgreSQL can place greater memory demands on an application, especially a read-heavy application with many simultaneous connections like a website, because it starts a new process for each connection, using as much as 10MB per connection, whereas MySQL (and MariaDB) uses a new thread for each connection, thus saving on memory. PostGIS has a lot of features that I want, and it seems that most of the issues with migration are things I know how to deal with. MariaDB has field size limits that are forcing me to simplify polygons more than I want, and it lacks the ability to calculate distances using actual globe projections, and even lacks some basic operations that I would want to do like intersecting regions. I've run into the limitations of MySQL in developing an application that demands better spatial support. ![]() In addition PostGIS has over 300 functions available for data conversion into and out of the database, compared to SQL Server which has around 70-100.I've developed interactive websites with a LAMP (Linux+Apache+MySQL+PHP) model for years, switching to MariaDB, which is more-or-less similar to MySQL, a few years back. SQL Server requires you to use additional library and processing, or use ogr2ogr. This geoJSON result can then be passed directly to whatever can understand geoJSON. Well, PostGIS can easily return a geoJSON result directly from a database query using ST_AsGeoJSON(). ![]() For example, Google Maps, and to a lesser degree Bing Maps, has recently added full geoJSON support to their maps API. PostGIS vs SQL Server Spatial is similar to the above regarding documentation, but PostGIS beats the pants off SQL Server Spatial in functionality. SQL Server does have quite a bit of documentation, but I find a lot of it hard to read, with not enough examples and tutorials. It's a investment in research that broadens your view and helps you realize you may have been missing something that you were unaware of before, or simply confirm your current course is the right now.Īs far as the database goes, I found Postgres to have a shorter, and more shallow, learning curve. If you find that you are stuck or lack functionality within that time period, then you know it's not for you. For example, maybe a 2 week time period to install and learn some specific functionality that is currently in use. And while I'm going to briefly detail my findings below, I'd suggest this: Give yourself a brief but reasonable time period to review the unfamiliar solution over the one you know, with specific goals in mind. I found Postgres to be superior in GIS functionality. I've worked with both Postgres and SQL Server. ![]()
0 Comments
Leave a Reply. |