Amazon Aurora vs PostgreSQL

Need advice about which tool to choose?Ask the StackShare community!

Amazon Aurora

673
592
+ 1
53
PostgreSQL

65.6K
52.6K
+ 1
3.5K
Add tool

Amazon RDS for Aurora vs PostgreSQL: What are the differences?

Developers describe Amazon RDS for Aurora as "MySQL and PostgreSQL compatible relational database with several times better performance". Amazon Aurora is a MySQL-compatible, relational database engine that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. Amazon Aurora provides up to five times better performance than MySQL at a price point one tenth that of a commercial database while delivering similar performance and availability. On the other hand, PostgreSQL is detailed as "A powerful, open source object-relational database system". PostgreSQL is an advanced object-relational database management system that supports an extended subset of the SQL standard, including transactions, foreign keys, subqueries, triggers, user-defined types and functions.

Amazon RDS for Aurora can be classified as a tool in the "SQL Database as a Service" category, while PostgreSQL is grouped under "Databases".

"MySQL compatibility " is the top reason why over 11 developers like Amazon RDS for Aurora, while over 744 developers mention "Relational database" as the leading cause for choosing PostgreSQL.

PostgreSQL is an open source tool with 5.38K GitHub stars and 1.79K GitHub forks. Here's a link to PostgreSQL's open source repository on GitHub.

According to the StackShare community, PostgreSQL has a broader approval, being mentioned in 2701 company stacks & 2097 developers stacks; compared to Amazon RDS for Aurora, which is listed in 116 company stacks and 30 developer stacks.

Advice on Amazon Aurora and PostgreSQL
Maxim Ryakhovskiy
Needs advice
on
PostgreSQLPostgreSQLMongooseMongoose
and
MariaDBMariaDB

Hi all. I am an informatics student, and I need to realise a simple website for my friend. I am planning to realise the website using Node.js and Mongoose, since I have already done a project using these technologies. I also know SQL, and I have used PostgreSQL and MySQL previously.

The website will show a possible travel destination and local transportation. The database is used to store information about traveling, so only admin will manage the content (especially photos). While clients will see the content uploaded by the admin. I am planning to use Mongoose because it is very simple and efficient for this project. Please give me your opinion about this choice.

See more
Replies (7)
malekmfs
at Meam Software Engineering Group · | 4 upvotes · 77.8K views

Your requirements seem nothing special. on the other hand, MongoDB is commonly used with Node. you could use Mongo without defining a Schema, does it give you any benefits? Also, note that development speed matters. In most cases RDBMS are the best choice, Learn and use Postgres for life!

See more

The use case you are describing would benefit from a self-hosted headless CMS like contentful. You can also go for Strapi with a database of your choice but here you would have to host Strapi and the underlying database (if not using SQLite) yourself. If you want to use Strapi, you can ease your work by using something like PlanetSCaleDB as the backing database for Strapi.

See more
Recommends
MongooseMongoose

Any database will be a great choice for your app, which is less of a technical challenge and more about great content. Go for it, the geographical search features maybe be actually handy for you.

See more
Tarun Batra
Back End Developer at instabox · | 2 upvotes · 70.6K views
Recommends
MongooseMongoose

MongoDB and Mongoose are commonly used with Node.js and the use case doesn't seem to be requiring any special considerations as of now. However using MongoDB now will allow you to easily expand and modify your use case in future.

If not MongoDB, then my second choice will be PostgreSQL. It's a generic purpose database with jsonb support (if you need it) and lots of resources online. Nobody was fired for choosing PostgreSQL.

See more
Nutchanon Ninyawee

SQL is not so good at query lat long out of the box. you might need to use additional tools for that like UTM coordinates or Uber's H3.

If you use mongoDB, it support 2d coordinate query out of the box.

See more
Ruslan Rayanov
Recommends

Hi, Maxim! Most likely, the site is almost ready. But we would like to share our development with you. https://falcon.web-automation.ru/ This is a constructor for web application. With it, you can create almost any site with different roles which have different levels of access to information and different functionality. The platform is managed via sql. knowing sql, you will be able to change the business logic as necessary and during further project maintenance. We will be glad to hear your feedback about the platform.

See more
Václav Hodek
CEO, lead developer at Localazy · | 1 upvotes · 71K views
Recommends
PostgreSQLPostgreSQL

Any database engine should work well but I vote for Postgres because of PostGIS extension that may be handy for travel related site. There's nothing special about your requirements.

See more
Prithvi Singh
Application Developer at Montaigne Smart Business Solutions · | 8 upvotes · 322.8K views
Needs advice
on
PostgreSQLPostgreSQLMySQLMySQL
and
MongoDBMongoDB

I am going to work on a real estate project and have to decide on a database. Now, SQL databases can be very efficient if appropriately designed. More relations between the data and less redundancy. But with a #NoSQL database, the development time is reduced, and it is easy to query. Since this is my first time working on the real estate domain, I would like to pick a database that would be efficient in the long run.

See more
Replies (4)
Aric Fedida
Founder, CTO at ASK Technologies Inc · | 15 upvotes · 314.7K views
Recommends
PostgreSQLPostgreSQL

I recommend PostgreSQL as it’s the most powerful out of the 3 databases you mentioned. It supports JSON objects so you can mimic the MongoDB functionality, but I would also argue that SQL is actually quite powerful and in many cases significantly easier to work with than with NoSQL databases.

Stay away from foreign keys, keep it fast and simple. Define your data structures well in advance. Try to model your data structures based on your system’s vision; based on where it’s going and not based solely on what you currently need it to do. This will help you avoid drastic changes to your database after your system is launched. Populate the database with fake data and run tests. PostgreSQL allows you to create Views from multiple tables. Try to create those views and make sure you can easily create useful views from multiple tables. Run an Explain on those view queries to make sure you created your indexes correctly. Make sure it’s fast!

See more
Matthew Rothstein
Recommends
PostgreSQLPostgreSQL

Any of those three databases are going to be efficient, scalable, and reliable in the long term if you configure and use them correctly. They all also have solid hosting solutions.

All things being equal, I would agree with other posters that Postgres is my preference among the three, but there are caveats.

MongoDB and MySQL have better support for mutli-region replication in your big three cloud environments. Azure recently bought Citus Data, which was a best-in-class Postgres replication solution, so they might be the only one I trust to provide cross-region replication at the moment.

If you have a single region deployment and are on AWS, I can't recommend Aurora Postgres highly enough. It's a very good implementation and extremely performant.

See more
Josh Dzielak
Co-Founder & CTO at Orbit · | 4 upvotes · 310.3K views
Recommends
PostgreSQLPostgreSQL

I'll second another piece of advice. Postgresql's JSON columns are a dream when it comes to productivity and I use them frequently with our Rails application. In these cases, no migration is required to change schema. We store payloads with dozens or hundreds of keys and performance has not been an issue. We also have a lot of relational tables, so the joins we get with SQL are very important to us and hard to replicate with a NoQL solution.

See more
Danilo Kaltner
Recommends
PostgreSQLPostgreSQL

That really depends of where do you see you application in the long run. On any application, any of those choices are excellent. You could argue about good support on JSON binaries, but even MySQL has an excellent support for that on the latest versions.

On the long run, when your application gets hundreds of thousands of requests per second, you might start thinking about how many inputs you will have in the database compared to the outputs. PostgresSQL it’s excellent at giving you outputs, but table corruption can happen when you start receiving this massive number of inputs (Which was the reason Uber switched from Postgres to MySQL)

On our OPS Platform at CTO.ai , we decided to use Postgres, because we need a reliable and agile way to send the output to our users, so that was out best choice in the long run for our product.

See more
Needs advice
on
PostgreSQLPostgreSQL
and
MongoDBMongoDB

I am one of those who believes that MongoDB can be used for everything, this thanks to the advertising of MongoDB.

We are creating an e-commerce platform, we know that it has many relationships, but with MongoDB we can avoid some, but in the end, some relationships have to exist.

A single developer to create two native applications in Flutter, a web application with React, create the backend with multiple microservices hosted with Google Cloud Run. PostgreSQL can be heavy because it should be used with an ORM, on the contrary, with MongoDB you can avoid some relationships and avoid ORM / ODM.

We need advice from someone who has the experience and has had to choose between these two databases for an e-commerce site.

See more
Replies (4)
Recommends
PostgreSQLPostgreSQL

The real question here is not about the technology but rather your real needs and your data. Do you need to manage data that has core concepts and relations ? (such as a family, with parents and children) or do you need to manage a basic collection of similar data (such as blog entries)? PostgreSQL is definitely a relational database for managing entities and their relationships whereas MongoDB (I may be strongly opinionated here ;-) ) is more targeted at managing collection of entities (such as the blog entries). For an e-commerce site (with some products, products categories, user ratings and comments, prices, bundles...) I would go for PostgreSQL as it will support/guide you in creating a structured data set with all your products, organized in categories and with user ratings/comments attached to them. HTH

See more
Valeriy Bykanov
Founder, CEO at X1 Group · | 3 upvotes · 213.7K views
Recommends
at

Had exactly the same question when selecting data storage for our new product. Not e-commerce though, rather interactive and content-focused HR SaaS for SME.

The key arguments for PostgreSQL

  • It gives you the opportunity to use relationships where you really need it and just go with key-value tables where you don't.

  • With Jsonb datatype you can store documents/objects/arrays as JSON then use JSON elements in queries and even indexes.

  • There are more tools/integrations working with PostgreSQL which you can use out of the box, e.g. Hasura

See more
Damián Gil
Advisor at Empresa En Crecimiento · | 3 upvotes · 212.6K views
Recommends
MongoDBMongoDB

I am in your spot, exactly. A few months ago, I had decided to use Postgres because since its version 9 it showed a lot of progress for being a high-availability database. However, frankly, I didn't want to model statically all data, since I have several distinct schemas (like for different product types) and I wanted some flexibility to add or remove as I saw fit. One of the main challenges with analyzing a NoSQL database being familiar in the SQL ways, is that it's easy to look for "analogies" for what makes SQL useful, like relationship enforcing, transactions and the cascading effect on deletes, updates and inserts, and that limit your vision a lot when analyzing a tool like Mongo, especially in a micro-services pattern. Now-a-days, I really found my solution in Mongo. Not just because of it being NoSQL, but because all of the support I find in the NodeJS community through packages and utilities that make it dead easy to use it for several use-cases. Whatever Postgres offers, Mongo does it a little easier and better, like text search and geo-queries. What you need to see is to model your data in a way that makes sense with Mongo. For instance, I've got a User service that has all auth related information of a user. But then, I have the same user in the Profile service, with the same id, but totally different fields. You have two de facto ways to connect data, by reference and embedding, which in Ecommerce, both have big uses. Like using references to relate a User to a Profile, and an embed to relate a Product to an Order. There's even a third, albeit a little more "manual" implementation here, the graph relationship in which you can model data, in which you can easily model event-driven documents, like a Purchase that goes from "a customer" to "a store", which you can later use for much easier and deep analytics than with the classical SQL stance. MariaDB has it readily available, and also has many improvements over MySQL and Postgres, especially for NoSQL features and scalability. Sadly it is just seen as a MySQL clone, but it offers more than that (although its documentation could be improved). Using Mongo in a micro-service environment is even better because your models can be smaller, meaning less burden on relationships, although you do compensate with a bit of duplication, but a well-designed schema will have minimal impact on that. Whatever tool might do the job, but I want to cheer on the newer generation. Hope it helps.

See more
Needs advice
on
PostgreSQLPostgreSQLMySQLMySQL
and
MongoDBMongoDB

Hello,

I am trying to design an online ordering app similar to Doordash or Uber Eats. I'm having a hard time trying to finalise on what database (or mixture of databases) to use. I'm leaning towards using a relational database like MySQL or PostgreSQL. But, when the application grows, I don't want to join on 20 tables to get a data. Any help would be greatly appreciated. Thank you for your time.

See more
Replies (2)
Rupen Makhecha
Recommends
MySQLMySQL

Hello Suhas , We build our product www.voilacabs.com which is in the same lines as yours but we have used a combination of Mysql and MongoDB. When using MySQL, i would recommend doing the following: 1. Use Mysql only for storage only and for realtime updates we recommend MongoDB. 2. Don't try to Join more than 3 tables. ( the moment you reach 3 join stop there and try to un-normalized database. 3. Never or very rarely use Auto-increments. ( we recommend using UUIDS ) . Use UUIDS always for Auto increments for MYSQL. If you using Postgre SQL then i would suggest you to please check this https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c There is a stored procedure that generated unique keys instead of auto-increment keys and that will help you sharding or clustering database without sync errors. 4. Also For MongoDB if you can put a layer of REDIS Cache then that will boost your api performance under large loads. 5. Use Node.js programing language as that function asynchronously .

Let me know if you still need any suggestion's . Thanks & Regards Rupen Makhecha CTO @ Voila Cab's www.voilacabs.com

See more
Rafey Iqbal Rahman
Recommends
MySQLMySQL
at

I would recommend a mixture of MySQL and MongoDB. Using MongoDB for the Content Distribution Network (CDN) will make it easy to store high volume incoming data. MySQL is recommended to be used for business logic. PostgreSQL is not recommended since you will be faced with inefficient database replication features and constant migration from one PostgreSQL version to another.

See more
Needs advice
on
PostgreSQLPostgreSQLMongoDBMongoDB
and
IndexedDBIndexedDB

I'm currently developing an app that ranks trending stuff ( such as games, memes or movies, etc. ) or events in a particular country or region. Here are the specs: My app does not require registration and requires cookies and localStorage to track users. Users can add new entries to each trending category provided that their country of origin is recorded in cookies. If each category contains more than 100 items then the oldest items get deleted. The question is: what kind of database should I use for managing this app? Thanks in advance

See more
Replies (1)
Recommends
MongoDBMongoDB

I think your best and cheapest choice is going to be MongoDB, Although Postgres is probably going to be the more scaleable approach, you likely have a good idea of how you want to present your data, and the app seems small enough that you shouldn't need to worry about scaling issues. It also sounds like your app can grow in a linear capacity based on the number of users, and the amount of data, which is the perfect use-case for noSQL databases (linear, predictable scaling).

Correct me if I have any of these assumptions wrong. 1. You're looking to have a relatively high-read with a lower write volume 2. Your app is essentially a list of objects that can belong to a category 3. users can create objects in this list.

I think Mongo is going to be what you're looking for on the following basis: 1. you absolutely need a database that is shared by all users of your app, therefor IndexedDB is out of the question. 2. You have semi-structured data 3. you probably want the cheapest solution.

I think Postgres is wrong for the following reasons: 1. your app is pretty simple in concept, SQL databases will add unnecessary complexity to your system, either through ORMs or SQL queries. (use an ORM if you go with SQL) 2. Hosting SQL databases for production is not cheap! the cheapest solution I know of for Postgres is ElephantSQL. It provides 20MB for free with 5 concurrent connections, you should be okay to manage these limitations if you decide to go Postgres in the end. Whereas mongoDB Atlas has some great free-tier options.

Although your data might be easier to model in Postgres, you can certainly model your data as a single list of items that have a category attached.

I don't want to officially recommend another tool, but you should really checkout prisma, firebase, amplify, or Azure App Services for this app! Just go completely backend-less [Firebase] https://firebase.google.com/ [Amplify] https://aws.amazon.com/amplify/ [Prisma] https://www.prisma.io/ [Azure App Services] https://azure.microsoft.com/en-us/services/app-service/?v=18.51

See more
Needs advice
on
PostgreSQLPostgreSQL
and
MongoDBMongoDB

Hi everybody, I'm developing an application to be used in a gym setting where athletes fill out a health survey, and coaches can analyze the results. However, due to the dynamic nature of some aspects of the app and more static aspects of the other, I am wondering if/how I would integrate MongoDB with my existing PostgreSQL database. I would like to store things like registrations, license information, and club information in Postgres, while I am thinking about moving things like user surveys, logging, and user settings information over to MongoDB. Some fields on the survey are integers, some large blocks of text, and some are arrays. My thought is, if I moved that data to MongoDB, it would give us greater flexibility in terms of adding and removing fields and data to them, and it would scale a lot easier than Postgres. Not to mention it will be easier to organize that kind of data. Is that overkill or am I approaching this issue the right way? Thank you!

See more
Replies (4)
Brian Ploetz
Recommends
PostgreSQLPostgreSQL

You can have your cake and eat it too. If you really need the flexibility of a document store, Postgresql's JSONB support allows you to mix and match relational data and document data within the same database/table. You can just as easily run analytical queries against JSONB data in Postgresql as you can against "normal" relational data. MongoDB comes with a significant operational overhead and cost (hello replica sets), so unless you really need MongoDB's sharding capabilities (which you shouldn't until you get to extreme scaling numbers), then just stick with Postgresql and use JSONB where you need it.

See more
Recommends
MongoDBMongoDB

With PostgreSQL you could easily integrate JSON or array type columns and develope a simple interface to add columns on your application. Anyway handling all the data this way will require some intermediate skill with PostgreSQL dialect and a mix and match of syntaxes for your analitical queryes. Also you will need to have a good design for you backend to handle all this. MongoDB will handle all this in a more natural way and I believe will be more easily integrated with a Node.js backend.

See more
Max Musing
Founder & CEO at BaseDash · | 4 upvotes · 85K views
Recommends
PostgreSQLPostgreSQL
at

How are you managing your PostgreSQL schema? It doesn't have to be hard to add or remove fields. We're working on a SQL database client at BaseDash that lets you add/remove columns in a couple clicks.

If you decide to migrate some of your data to MongoDB, you can definitely manage the two databases in parallel. For any records that need to be linked, you can treat it just like a foreign key by creating a column that points to an ID in the other database. For example, you might store user settings in MongoDB, and include a UserId field that points to your User record in your Postgres database.

See more
Recommends
PostgreSQLPostgreSQL

Those types of things should fit fine in a postgres json column. You'll actually have more flexibility with postgres because you can have a field as a normal column or in a json column, and you can have constraints and indexes on fields within a json column (or not).

See more
Navraj Chohan
Needs advice
on
PostgreSQLPostgreSQL
and
MySQLMySQL

I asked my last question incorrectly. Rephrasing it here.

I am looking for the most secure open source database for my project I'm starting: https://github.com/SuPragma/SuPragma/wiki

Which database is more secure? MySQL or PostgreSQL? Are there others I should be considering? Is it possible to change the encryption keys dynamically?

Thanks,

Raj

See more
Replies (2)
Pierrick Martos
Engineering Manager at Akeneo · | 4 upvotes · 76.3K views
Recommends
PostgreSQLPostgreSQL

PostgreSQL provides more tools and builtin features around security, eg: row level security and the support of SELinux (through SE-PostgreSQL). Overall, whatever you choose, the important is to keep it updated and have the skills to apply security best practices and update them regurarly, without this, it's like putting your money in Fort knox but leaving the vault key in a public place.

See more
Umair Iftikhar
Technical Architect at Vappar · | 3 upvotes · 76.3K views
Recommends
PostgreSQLPostgreSQL

It is open-source and more tools than mySQL. PostgreSQL is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance. It is also good for small companies due to tools for free availability. PostgreSQL includes built-in support for regular B-tree and hash indexes. Indexes in PostgreSQL also support Expression & Partial Indices ( index only a part of a table). Expression Index can be created with an index of the result of an expression or function, instead of simply the value of a column.

See more
George Krachtopoulos
Needs advice
on
PostgreSQLPostgreSQL
and
MongoDBMongoDB

Hello everyone,

Well, I want to build a large-scale project, but I do not know which ORDBMS to choose. The app should handle real-time operations, not chatting, but things like future scheduling or reminders. It should be also really secure, fast and easy to use. And last but not least, should I use them both. I mean PostgreSQL with Python / Django and MongoDB with Node.js? Or would it be better to use PostgreSQL with Node.js?

*The project is going to use React for the front-end and GraphQL is going to be used for the API.

Thank you all. Any answer or advice would be really helpful!

See more
Replies (11)
Arnaud Assad
Code Alchemist at DOOLTA · | 23 upvotes · 46.1K views
Recommends
PostgreSQLPostgreSQL

If you want safety (no data corruption/loss) Postgresql is the way to go. You can use Postgresql with python/django but also node. And as a bonus postgresql performance should match those of mongodb if properly tuned...

See more
JT Turner
Lean Software Programmer · | 12 upvotes · 46.1K views
Recommends
PostgreSQLPostgreSQL

I mean what you use really comes down to your team. I would normally pick a relationship database over an object store for reporting and other things down the road that are much harder to do with a DB like MongoDB. As for language, I would pick Elixir as it does these things really well but that because that is what I know best. Python or Node.js are also sure good choices. Whoever is building should probably pick the language, framework, and database.

See more
Mikael Sand
CTO at Seaber.io at Seaber.io · | 5 upvotes · 44.2K views
Recommends
PostgreSQLPostgreSQL

I'd recommend using PostgreSQL and the built in row level security it offers. You can easily make multi-tenant real-time systems using it and https://www.graphile.org/postgraphile/ which gives you an GraphQL api guaranteed to be in sync with your database for free. Also, the transactional support in postgres shines in comparison to mongodb.

See more
Ivan Begtin
Director - NGO "Informational Culture" / Ambassador - OKFN Russia at Infoculture · | 4 upvotes · 44.3K views
Recommends
PostgreSQLPostgreSQL

If you have a lot of realtime operations and not-so-high variability of data so Postgres is the best choice. MongoDB is less suitable for high-performance projects but is good for semi-structured data and not-realtime projects. For example we use MongoDB as DB for analytics but we use Postgres for any high-load projects

See more
Václav Hodek
CEO, lead developer at Localazy · | 4 upvotes · 44.2K views
Recommends
PostgreSQLPostgreSQL

At Localazy, we have decided for PostgreSQL, because with its JSONB data type, it effectively combines both relational and document store. The relational model is still better in many situations. In combination with ACID transactions and other features, it provides you with something you are not about to get with Mongo. The ability to store JSONB gives you flexibility where you need it. Also, the SQL queries are something great and allow you to unload a lot of logic to the database server.

See more
Nikhil Gurnani
Sr. Backend Engineer at Grappus · | 4 upvotes · 44.2K views
Recommends
PostgreSQLPostgreSQL

I think the point of difference is very clear in the sense that if you're looking to manage relationships in your application, you might as well do it with a database designed and architected to do so. MongoDB is great, but managing complex relationships becomes application logic which introduces more technical debt, in my personal opinion. No matter what programming language you end up using (Postgres works well with both Python and Node.js), you should also look up how easy it will be to manage code and database designs once you begin development. Hope this helps you!

See more
Matthew Rothstein
Recommends
PostgreSQLPostgreSQL

Unless you have need for consensus writes or cross-region replication, I generally prefer Postgres over Mongo.

You can use Postgres with either language and it can be performant if you use an RDBMS or are thoughtful about your query construction.

Given your need for security, I'd definitely interact with the database through a mature RDBMS and use well-vetted frameworks and consider going through a rigorous security evaluation every so often.

I personally think that Postgres is easier to reason about than Mongo because of its relational nature and comfort with constraints. Maintaining any sort of relational features in Mongo tends to require a lot of application code for features Postgres offers out of the box.

See more
John Akhilomen
Recommends
PostgreSQLPostgreSQL

I'd have recommended MongoDB, but since you're considering Security, then PostgreSQL it is. PostgreSQL is also easy to use, stable, good documentation, and huge support community out there. PostgreSQL also handles concurrency well..

See more
Mahdi Perfect
Recommends
PostgreSQLPostgreSQL

Postgres have never disappointed me. I wouldn't choose MongoDb unless I've had strong reasons to choose it.

See more
Recommends
HasuraHasura

We build a Factoring-Software for a company in germany and realied on PostgreSQL as main banking and transactional database. Works flawlessly. It's very fast, realiable and well documented. It's a very good product.

My advice for your current needs would be to take a look at https://hasura.io/. I like the service and the fact that it's open source, too. It shippes with PostgreSQL database and gives you the power to map your database relations to a GrapQL endpoint. GraphQL works really well with a React frontend. Hasura getting started docs are well written and they also provide nice use-case and hands-down coding tutorials to get you started. Check if it matches your needs. Here are the features: https://hasura.io/all-features Here is their tutorial to kick it off: https://hasura.io/learn/graphql/hasura/introduction/ Here's an article about scaling their service for GraphQLto 1 million active subscriptions (live queries): https://hasura.io/blog/1-million-active-graphql-subscriptions/

Have a nice day! Cheers, Chris

See more
Divyansh Sharma
Data Scientist at Compound Analytics · | 2 upvotes · 44.2K views
Recommends
PostgreSQLPostgreSQL

I have been using this stack of PostgreSQL and Python/Django for a large scale project and I am happy with my decision of using this stack as it is easily scalable , quick and secure.

See more

I am a Microsoft SQL Server programmer who is a bit out of practice. I have been asked to assist on a new project. The overall purpose is to organize a large number of recordings so that they can be searched. I have an enormous music library but my songs are several hours long. I need to include things like time, date and location of the recording. I don't have a problem with the general database design. I have two primary questions:

  1. I need to use either MySQL or PostgreSQL on a Linux based OS. Which would be better for this application?
  2. I have not dealt with a sound based data type before. How do I store that and put it in a table? Thank you.
See more
Replies (6)

Hi Erin,

Honestly both databases will do the job just fine. I personally prefer Postgres.

Much more important is how you store the audio. While you could technically use a blob type column, it's really not ideal to be storing audio files which are "several hours long" in a database row. Instead consider storing the audio files in an object store (hosted options include backblaze b2 or aws s3) and persisting the key (which references that object) in your database column.

See more
Aaron Westley
Recommends
PostgreSQLPostgreSQL

Hi Erin, Chances are you would want to store the files in a blob type. Both MySQL and Postgres support this. Can you explain a little more about your need to store the files in the database? I may be more effective to store the files on a file system or something like S3. To answer your qustion based on what you are descibing I would slighly lean towards PostgreSQL since it tends to be a little better on the data warehousing side.

See more
Christopher Wray
Web Developer at Soltech LLC · | 3 upvotes · 94.9K views

Hey Erin! I would recommend checking out Directus before you start work on building your own app for them. I just stumbled upon it, and so far extremely happy with the functionalities. If your client is just looking for a simple web app for their own data, then Directus may be a great option. It offers "database mirroring", so that you can connect it to any database and set up functionality around it!

See more
Julien DeFrance
Principal Software Engineer at Tophatter · | 3 upvotes · 94.5K views
Recommends
Amazon AuroraAmazon Aurora

Hi Erin! First of all, you'd probably want to go with a managed service. Don't spin up your own MySQL installation on your own Linux box. If you are on AWS, thet have different offerings for database services. Standard RDS vs. Aurora. Aurora would be my preferred choice given the benefits it offers, storage optimizations it comes with... etc. Such managed services easily allow you to apply new security patches and upgrades, set up backups, replication... etc. Doing this on your own would either be risky, inefficient, or you might just give up. As far as which database to chose, you'll have the choice between Postgresql, MySQL, Maria DB, SQL Server... etc. I personally would recommend MySQL (latest version available), as the official tooling for it (MySQL Workbench) is great, stable, and moreover free. Other database services exist, I'd recommend you also explore Dynamo DB.

Regardless, you'd certainly only keep high-level records, meta data in Database, and the actual files, most-likely in S3, so that you can keep all options open in terms of what you'll do with them.

See more
Recommends
PostgreSQLPostgreSQL

Hi Erin,

  • Coming from "Big" DB engines, such as Oracle or MSSQL, go for PostgreSQL. You'll get all the features you need with PostgreSQL.
  • Your case seems to point to a "NoSQL" or Document Database use case. Since you get covered on this with PostgreSQL which achieves excellent performances on JSON based objects, this is a second reason to choose PostgreSQL. MongoDB might be an excellent option as well if you need "sharding" and excellent map-reduce mechanisms for very massive data sets. You really should investigate the NoSQL option for your use case.
  • Starting with AWS Aurora is an excellent advise. since "vendor lock-in" is limited, but I did not check for JSON based object / NoSQL features.
  • If you stick to Linux server, the PostgreSQL or MySQL provided with your distribution are straightforward to install (i.e. apt install postgresql). For PostgreSQL, make sure you're comfortable with the pg_hba.conf, especially for IP restrictions & accesses.

Regards,

See more
Klaus Nji
Staff Software Engineer at SailPoint Technologies · | 1 upvotes · 94.6K views
Recommends
PostgreSQLPostgreSQL

I recommend Postgres as well. Superior performance overall and a more robust architecture.

See more
Decisions about Amazon Aurora and PostgreSQL
Daniel Moya
Data Engineer at Dimensigon · | 4 upvotes · 180.2K views

We have chosen Tibero over Oracle because we want to offer a PL/SQL-as-a-Service that the users can deploy in any Cloud without concerns from our website at some standard cost. With Oracle Database, developers would have to worry about what they implement and the related costs of each feature but the licensing model from Tibero is just 1 price and we have all features included, so we don't have to worry and developers using our SQLaaS neither. PostgreSQL would be open source. We have chosen Tibero over Oracle because we want to offer a PL/SQL that you can deploy in any Cloud without concerns. PostgreSQL would be the open source option but we need to offer an SQLaaS with encryption and more enterprise features in the background and best value option we have found, it was Tibero Database for PL/SQL-based applications.

See more
Tom Klein

The Gentlent Tech Team made lots of updates within the past year. The biggest one being our database:

We decided to migrate our #PostgreSQL -based database systems to a custom implementation of #Cassandra . This allows us to integrate our product data perfectly in a system that just makes sense. High availability and scalability are supported out of the box.

See more
Kyle Harrison
Web Application Developer at Fortinet · | 11 upvotes · 374K views

MySQL has a lot of strengths working for it. It's simple and easy to set up and use. It's JSON engine is also really good these days. Mongo is also simple to setup and use, and it's speed as a document-object storage engine is first class.

Where Postgres has both beat is in it's combining of all of the features that make both MySQL and Mongo great, while adding on enterprise grade level scalability and replication. It's Postgres' stability and robustness, while still fulfilling the roles of it's contemporaries extremely well that edge Postgre for me.

See more

We used Mongo for the first iterations of our app, but the relational nature of our data was an awkward fit for a database that is not relational. We sorely lacked relational database integrity features that needed to be done on the application side (poorly) and it was a huge relief when we managed to port our application over to Postgres, which performs great and never gives us trouble, while having very user friendly extensions like JSON and PubSub that made the transition easy.

See more
Ram Kumar
CTO, Full-stack developer · | 2 upvotes · 118.6K views

PostgreSQL is enterprise level database with transactions, full-text indexes, vector indexes, JSON, BLOB, geo-spatial data and a lot more. Highly scalable, configurable and easily maintainable. all that on an open source RDBMS database and you are still looking for GPL licensed MySQL with limited features? Look again.

See more
Kyle Harrison
Web Application Developer at Fortinet · | 21 upvotes · 72K views

While there's been some very clever techniques that has allowed non-natively supported geo querying to be performed, it is incredibly slow in the long game and error prone at best.

MySQL finally introduced it's own GEO functions and special indexing operations for GIS type data. I prototyped with this, as MySQL is the most familiar database to me. But no matter what I did with it, how much tuning i'd give it, how much I played with it, the results would come back inconsistent.

It was very disappointing.

I figured, at this point, that SQL Server, being an enterprise solution authored by one of the biggest worldwide software developers in the world, Microsoft, might contain some decent GIS in it.

I was very disappointed.

Postgres is a Database solution i'm still getting familiar with, but I noticed it had no built in support for GIS. So I hilariously didn't pay it too much attention. That was until I stumbled upon PostGIS and my world changed forever.

See more
Get Advice from developers at your company using Private StackShare. Sign up for Private StackShare.
Learn More
Pros of Amazon Aurora
Pros of PostgreSQL
  • 13
    MySQL compatibility
  • 12
    Better performance
  • 10
    Easy read scalability
  • 8
    Speed
  • 7
    Low latency read replica
  • 2
    High IOPS cost
  • 1
    Good cost performance
  • 755
    Relational database
  • 508
    High availability
  • 436
    Enterprise class database
  • 380
    Sql
  • 302
    Sql + nosql
  • 171
    Great community
  • 145
    Easy to setup
  • 129
    Heroku
  • 128
    Secure by default
  • 111
    Postgis
  • 48
    Supports Key-Value
  • 46
    Great JSON support
  • 32
    Cross platform
  • 30
    Extensible
  • 26
    Replication
  • 24
    Triggers
  • 22
    Rollback
  • 21
    Multiversion concurrency control
  • 20
    Open source
  • 17
    Heroku Add-on
  • 14
    Stable, Simple and Good Performance
  • 13
    Powerful
  • 12
    Lets be serious, what other SQL DB would you go for?
  • 9
    Good documentation
  • 7
    Intelligent optimizer
  • 7
    Scalable
  • 6
    Reliable
  • 6
    Transactional DDL
  • 6
    Modern
  • 5
    Free
  • 5
    One stop solution for all things sql no matter the os
  • 4
    Relational database with MVCC
  • 3
    Faster Development
  • 3
    Full-Text Search
  • 3
    Developer friendly
  • 2
    Excellent source code
  • 2
    Great DB for Transactional system or Application
  • 2
    search
  • 1
    Free version
  • 1
    Open-source
  • 1
    Full-text
  • 1
    Text

Sign up to add or upvote prosMake informed product decisions

Cons of Amazon Aurora
Cons of PostgreSQL
  • 2
    Vendor locking
  • 1
    Rigid schema
  • 9
    Table/index bloatings

Sign up to add or upvote consMake informed product decisions

- No public GitHub repository available -

What is Amazon Aurora?

Amazon Aurora is a MySQL-compatible, relational database engine that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. Amazon Aurora provides up to five times better performance than MySQL at a price point one tenth that of a commercial database while delivering similar performance and availability.

What is PostgreSQL?

PostgreSQL is an advanced object-relational database management system that supports an extended subset of the SQL standard, including transactions, foreign keys, subqueries, triggers, user-defined types and functions.

Need advice about which tool to choose?Ask the StackShare community!

Jobs that mention Amazon Aurora and PostgreSQL as a desired skillset
What companies use Amazon Aurora?
What companies use PostgreSQL?
See which teams inside your own company are using Amazon Aurora or PostgreSQL.
Sign up for Private StackShareLearn More

Sign up to get full access to all the companiesMake informed product decisions

What tools integrate with Amazon Aurora?
What tools integrate with PostgreSQL?

Sign up to get full access to all the tool integrationsMake informed product decisions

Blog Posts

Dec 8 2020 at 5:50PM

DigitalOcean

GitHubMySQLMongoDB+11
2
1860
Nov 20 2019 at 3:38AM

OneSignal

PostgreSQLRedisRuby+8
8
3928
Jul 9 2019 at 7:22PM

Blue Medora

DockerPostgreSQLNew Relic+8
11
1798
DockerAmazon EC2Scala+8
6
2234
What are some alternatives to Amazon Aurora and PostgreSQL?
Amazon RDS
Amazon RDS gives you access to the capabilities of a familiar MySQL, Oracle or Microsoft SQL Server database engine. This means that the code, applications, and tools you already use today with your existing databases can be used with Amazon RDS. Amazon RDS automatically patches the database software and backs up your database, storing the backups for a user-defined retention period and enabling point-in-time recovery. You benefit from the flexibility of being able to scale the compute resources or storage capacity associated with your Database Instance (DB Instance) via a single API call.
Google Cloud SQL
MySQL databases deployed in the cloud without a fuss. Google Cloud Platform provides you with powerful databases that run fast, don’t run out of space and give your application the redundant, reliable storage it needs.
Azure SQL Database
It is the intelligent, scalable, cloud database service that provides the broadest SQL Server engine compatibility and up to a 212% return on investment. It is a database service that can quickly and efficiently scale to meet demand, is automatically highly available, and supports a variety of third party software.
DigitalOcean Managed Databases
Build apps and store data in minutes with easy access to one or more databases and sleep better knowing your data is backed up and optimized.
Books
It is an immutable double-entry accounting database service. It supports many clients and businesses at global scale, leaning on Google Cloud Spanner and Google Kubernetes Engine to make that possible.
See all alternatives