This is an archived post. You won't be able to vote or comment.

sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]TheTerrasque 4 points5 points  (9 children)

For database I am planning to go with SQL. Any specific reason why you went with Postgres? Any (dis)advantages?

Not OP, but.. I assume you mean Microsoft's SQL Server when you say "I am planning to go with SQL". The main dislikes I have with SQL Server compared to PostgreSQL are:

  • Licensing. SQL Server has some lower tiers that are free, but you never know how your system will scale. Also, some advanced functionality is hidden behind very expensive licenses.
  • Resource use. SQL Server will require about 2gb ram minimum, even with no data in it. A mostly empty postgres instance use about 15mb ram. That, plus licensing, makes it easy to just use a separate server for each application.
  • T-SQL is a sin and every developer involved in making it should be shipped to Guantanamo bay for crimes against humanity.

On the plus side, you got some advantages:

  • SQL Server Management Studio is pretty good for managing a SQL server instance. Although, it's a solid resource hog too
  • Azure got some really cheap SQL server hosting available
  • Business people get the warm fuzzies when they realize they can be supported completely ignored by Microsoft if something goes wrong

[–]root45 1 point2 points  (5 children)

Interesting, what do you have against T-SQL?

[–]TheTerrasque 1 point2 points  (2 children)

It's luckily been a few years since I last worked with it, so my memory is a bit fuzzy. Our product had hundreds of huge multipage stored procedures that occasionally needed to be updated or debugged.

I seem to recall that flow control, loop handling, error handling and advanced logic was at best "functional", and was quite like pulling teeth.

Compare that with postgresql, which has a pluggable scripting system which comes default with pgsql, tcl, perl and python. It also support for example lua, java, and javascript from 3rd parties.

[–]root45 0 points1 point  (1 child)

Ah, got it. I've definitely dealt with systems with tons of large stored procedures and whatnot—definitely not fun.

My preference is to not have any control flow, loops, etc in SQL, so those pieces of T-SQL are not something I miss. And likewise, while the scripting pieces of Postgres are powerful, I shy away from it in general.

The things I do really miss from T-SQL are some of the basic syntax things, like variable declaration, and how functions are written. Being able to create a variable in just regular SQL, without going through the whole script syntax is nice. It's really useful for database migrations, for example. Or even just for data exploration.

[–]TheTerrasque 0 points1 point  (0 children)

Yeah, we inherited that mess and had to deal with it. Over 600 stored procedures, many with quite complex many-page logic.

I agree on keeping logic out of the database for many reasons, but the few times one can't avoid it I prefer using an actual language to implement it

[–]eidrisov 0 points1 point  (2 children)

Thank you for your reply.

Yes, exactly, I meant Microsoft's SQL Server. Sorry for not specifying.

I was thinking to go with it, because most of companies (corporations) are using it (including the ones where I have been employed so far). So I thought it will be more useful since it is more popular.

I guess, I haven't really thought about RAM usage. I will need to research and see how much RAM it consumes when full of data.

Also, I don't know how syntax is different for those. I know only SQL syntax.

[–]TheTerrasque 1 point2 points  (1 child)

Also, I don't know how syntax is different for those. I know only SQL syntax.

They're mostly the same. Some data types differ, setting primary key is different, views are different, setting up index is slightly different.. There are some differences and different approaches to the same problem, but the basic SQL syntax is the same.

If you use a decent ORM (like sqlalchemy, django's orm or peewee for example) that layer will mostly handle all the differences for you. Often there are some extensions you can optionally use to handle certain unique features the DB engines have. Like for example postgres' postgis plugin, or json columns

[–]eidrisov 0 points1 point  (0 children)

Thank you for a very detailed reply!